Performance Monitor3: Monitoring the memory pressure of SQL Server

The resources used by SQL Server are dispatched by the operating system, while SQL Server implements a set of scheduling algorithms internally to manage the resources obtained from the operating system, primarily the scheduling of memory and CPU resources. A good database system must cache enough information in memory to reduce the number of times the data is read from the physical hard disk, and SQL Server must run very slowly if the memory is a system bottleneck. Monitoring SQL Server's memory pressure requires monitoring the overall use of memory from the widnows level: from the SQL Server level, monitor SQL Server usage of memory resources.

One, monitoring the use of memory resources from the Windows level

The operating system has the ability to schedule memory with two sources: physical memory and virtual memory. Physical memory is the memory hardware provided by the high-speed access device, virtual memory is the expansion of physical memory, the operating system to create a physical disk space, as a memory space, used to store cached data files, called as cache files (Paging file), the path name is C \ Pagefile.sys, the default is hidden. The operating system transparently uses paging file to store data, and application is unable to control and perceive whether the data is stored in physical memory or in virtual memory, that is, the operating system decides to use physical memory, or paging file to store cached data. In general, monitor the use of memory resources at the Windows level by Performance Monitor.

1, monitoring physical memory

Common system-level memory counters are related to hard-to-fault-pages interrupts:

• memory:page faults/sec : The number of page Fault that occur per second, page Fault including hard Fault and Soft Fault,hard Fault means that data pages need to be read from disk, Soft Fault indicates that data pages need to be read from physical memory, Soft fault does not affect performance, and because hard fault requires access to disk, there is a significant delay.
• memory:pages input/sec: The number of hard fault that occur per second that is used to calculate the percentage of hard fault: Pages input/page faults =% hard Page faults, if percent than often more than 40%, indicating that the system needs to access disk frequently to obtain data, to some extent, the system has memory pressure.
• memory:pages/sec: The number of page reads or writes disk per second from disk, indicating the number of page interactions between memory and disk: The number of page that will store the page to disk or read data from disk to memory.

For example, the number of Page faults/sec, the average value is around 6000/s, Pages input/sec fluctuations, time is low, the duration is very short, the mean is around 50/s, the proportional relationship between the two is less than 1%, less than 40%, it can be thought that the memory pressure is small. Pages/sec and Pages input/sec are almost completely coincident, indicating that the operating system was doing a lot of physical reading operations.

2. Monitor virtual memory

The operating system consumes both physical memory and virtual memory, and the virtual memory counters are two main:

• Paging file:% usage to monitor the usage ratio of Paging File instances
• process:paging File Bytes to monitor the size of virtual memory

The more data stored in virtual memory, the greater the difference between the amount of physical memory and the actual demand, the value is only as a reference value, and if it is close to 100% for a long time, then the system is likely to be abnormal.

Second, monitor SQL Server usage of memory resources from the SQL Server level

1. Monitor overall server memory usage from the buffer pool counter

Because buffer pool is the most active, most used part of SQL Server, it is also the most likely to have a performance bottleneck, which is especially important:

• Lazy writes/sec: The number of buffer that was refreshed by lazywriter, if it is dirty, write buffer to disk and mark the buffer space as free, if it is not a dirty page, The buffer space is also marked as free,lazywriter to maintain a certain number of free BUFFER,SQL servers using free buffer to load new data pages.
• page Life expectancy: PLE, the time that the data page resides in memory. If SQL Server does not have new memory requirements, or has free memory to complete the new memory requirements, then lazy writer will not be penalized and the page will remain in buffer pool, then page life expectancy will be maintained at a relatively high level If page life is always high and low, it indicates that SQL Server has memory pressure. The reference value for ple is: Max Server memory/4gb*300s, if the ple value is lower than the reference value for a long time, there may be a bottleneck in memory.
• Page reads/sec: The number of pages of data read from disk per second, which is physical reads, and SQL Server does not need to read the page from the physical disk if the data that the user accesses is slow to exist in memory. Because of the high overhead of physical IO, the Page reads operation must affect the performance of SQL Server.
• Free list stalls/sec: The number of requests to wait for a free page, SQL Server requests to load a page from disk into memory, a buffer,buffer must be allocated in memory The manager is responsible for maintaining the free buffer list, and if there is none in the list, the request must wait until an idle buffer is used in order to load the page in disk into memory.

Based on the analysis of the chart data, SQL Server performs a large number of physical reads, resulting in a significant decrease in ple; from the free List stall and the measured value of Lazy write, SQL Server memory pressure is small:

• PLE: Greatly reduced, from 50Ks down to the mean 2Ks, indicating that the memory data page is replaced by a large number;
• Free list stalls/sec: The fluctuation is obvious, the overall value is very small, indicating that the free buffer in the system can meet the needs of SQL Server;
• Lazy write/sec: Mean value in 4/sec, smaller;
• Page reads/sec: Mean value in 4000/sec, which means that SQL Server is doing a lot of physical read operations

BCHR (Buffer cache Hit Ratio) indicates that the percentage of SQL Server reading data directly from memory is very much related to pre-reading . A hit means that when SQL Server reads data, the data exists in memory, the amount of time it resides in memory, and whether the memory is stressful, for informational purposes only.

Logical reading refers to reading data directly from memory, which refers to loading data from the physical disk file into memory, from the SQL Server perspective, bchr= logical read/(logical read + physical read).

If the data is cached in memory, SQL Server reads the data directly from memory without having to load from the physical disk into memory. The physical disk is capable of pre-reading, and the operating system pre-loads the data on the physical disk into memory, which already exists in memory when the SQL Server process accesses the data. Although SQL Server has requested a physical read operation, the measured value of BCHR does not reflect a physical read operation, because when SQL Server reads the data, the data is present in memory, and SQL Server performs a logical read operation.

Recommended reading great SQL Server Debates:buffer Cache hit Ratio:

BCHR only responds to significant memory pressure in conjunction with I/O subsystem pressure, or possibly fragmentation I. E. Under conditions, impedes page Read-ahead to the point, SQL Server becomes much less effective at populating th E data cache with the required pages, before the query processor actually requires them for use.

2. Monitor server memory overall usage from Memories Manager counters

In a very busy system, lock memory and grant memory are common counters:

• Total Server Memory (KB): Amount of RAM currently used by SQL Server
• Target Server Memory (KB): Total amount of RAM that SQL Server can use
• lock Memory (KB): Total amount of memory used by SQL Server for locks
• Grant Workspace Memory (KB): The amount of memory that SQL Server uses to perform hash, sort, and create index operations
• Memory Grants Pending (KB): The number of processes that are waiting to be granted, and if the process cannot get a specified amount of memory, the process will not start executing

The analysis chart, in addition to the grant Workspace Memory changes, the remaining 4 count values have not changed, indicating that the operation performed by SQL Server needs to grant memory, and the memory Grants Pending count value is small, almost 0, indicating that the SQL There is no memory pressure on the Server.

Conclusion: Memory is the most important resource of database system, the management of operating system and SQL Server is more complicated, from the value of above count value, it is basically able to infer whether SQL Server has memory pressure and can testify with other measured values, such as committed memory, Stolen memory,working set,paged pool,nonpaged Pool and so on, this is not the start.

Expand reading:

process:page File Bytes is the current amount of the virtual memory, in Bytes, which this Process have reserved for use In the paging file (s). Paging files is used to stores pages of memory used by the process is not contained in other files. Paging files is shared by all processes, and the lack of space in Paging files can prevent other processes from Allocatin G memory. If There is no paging file, this counter reflects the current amount of virtual memory that the process have reserved for u Se in physical memor

Reference doc:

Windows Performance Counters Explained

Does the Buffer cache hit ratio performance counter really serve as a predictor of memory bottlenecks?

Great SQL Server Debates:buffer Cache hit Ratio

SQL Server Memory Performance Metrics–part 1–memory pages/sec and Memory page faults/sec

Performance Monitor3: Monitoring the memory pressure of SQL Server