Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Management Pack for SCOM User Guide

SQL Server | Home Page | Memory Panel

The Memory panel displays the following information:

Component Description

Total Memory

The Memory gauge shows the total memory that SQL Server is currently using, scaled against the maximum amount of memory it could use.

By default, SQL Server dynamically manages its memory. It will request extra memory from the operating system as required to satisfy the queries being run. SQL Server also monitors the stress on the Windows memory manager, and when it finds that the memory manager is having trouble satisfying the memory requests of other Windows processes, SQL Server will voluntarily release some of its memory back to Windows.

This dynamic memory management means SQL Server can automatically tune its memory allocations based on the load it is processing, and the demands of other Windows processes on the server.

Each page of memory used by SQL Server is assigned to one of several caches. Each cache will grow and shrink in size as required. The main caches are the Buffer Cache, which holds recently accessed database pages, and the Procedure Cache which holds recently compiled query execution plans.

This Memory gauge shows the total amount of memory currently allocated to SQL Server.

The maximum value shown on this gauge is the lower of SQL Server's Max Memory parameter and the amount of physical RAM on the machine. It indicates the maximum amount of memory SQL Server can use.

Two SQL Server parameters can be used to influence the amount of memory SQL Server uses. The Min Memory and Max Memory parameters both default to 0, which causes SQL Server to manage its total memory use as it sees fit. However, you can set these parameters to non-zero values.

A non-zero Max Memory parameter causes SQL Server to never request more than that amount of memory.

A non-zero Min Memory parameter prevents SQL Server from giving memory back to Windows if its total memory allocation is less than the value specified. Note that SQL Server does not automatically request this amount of memory on startup, but will request additional memory from Windows as it needs it, and will not release any memory back to the operating system until it has allocated more than this "minimum" amount.

Clicking this gauge takes you to the SQL Memory drilldown, which shows you how SQL Server is currently using its memory.

Buffer Cache Size

This shows the amount of memory currently allocated to the Buffer Cache.

The Buffer Cache is an in-memory copy of recently used database pages.

Page Life Expectancy

This shows the current Page Life Expectancy.

SQL Server holds recently accessed database pages in a memory area called the Buffer Cache. If a SQL process needs to access a database page and that page is already in the buffer cache, then SQL Server does not need to read the page from disk. This can save a significant amount of disk I/O and can speed up queries significantly.

Page Life Expectancy is the length of time in seconds that a database page will stay in the buffer cache without references. Large values mean that pages are staying in the buffer cache longer and that the buffer cache is behaving efficiently. Small values mean that pages are being flushed out of the cache within a small period of time and that the buffer cache is not being effective. Microsoft recommends 300 seconds as the minimum value for this; any less is indicative of a shortage of memory.

Procedure Cache Size

This shows the amount of memory currently allocated to the Procedure Cache.

The Procedure Cache holds execution plans for stored procedures, triggers, adhoc SQL, and so on.

 

Data Flows

Component Description

Physical Reads/s

This flow from the Disk Storage panel to the Memory panel represents normal page reads per second.

When a connection needs a page that is not already in the Buffer Cache, SQL Server issues an I/O request, and the user is required to wait until the page has been read before they can continue.

Physical Writes/s

This flow represents data/index pages written to disk.

Normally SQL users don't have to wait for database write operations to complete. Most modifications to database pages are made in the Buffer Cache, and, once the change has been recorded in the Log, the user can continue without having to wait for the modified pages to be written to disk.

Read Ahead Pages/s

This flow from the Disk Storage panel to the Memory panel represents pages being read from disk before they are requested.

When SQL Server thinks data or index pages are being read in a sequential manner, it will pre-fetch the pages into the Buffer Cache before they are requested by the user. This is known as Read Ahead processing.

Read Ahead can improve the performance of queries that have to process a lot of table or index pages sequentially, by having the pages already in the Buffer Cache before they are required.

In some circumstances, a high level of Read Ahead activity can be an indication of poorly-coded SQL or inadequate indexes. Read Ahead processing is triggered by table scans or index range scans. In some cases, defining more suitable indexes or tuning SQL statements can get SQL Server to use more efficient access methods (e.g. direct index accesses) instead of these scans.

Checkpoint Pages/s

This flow from the Memory panel to the Background Processes panel represents the number of pages being written to disk by the Checkpoint process.

Lazy Writes Pages/s

Represents the number of pages per second that the lazy writer is flushing to disk.

Log Flushes Flushes/s

This flow from the Memory panel to the Background Processes panel represents the number of log pages being written to disk by the Log Writer process.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating