SQL SERVER Performance counters to capture to analyze Memory pressure

We  need to define objects and counters in Windows Performance Monitor (PERFMON) .

To analyze memory pressure, you can capture the performance monitor objects such as

memory,

SQLServer: Buffer Manager,

SQLServer: Memory Manager and the associated counters for these objects.

For the MEMORY object, you can add counters such as

Available Bytes,

Pages/sec,

Page Faults/sec,

Pages Input/sec, and Pages Output/sec.

 The following describes each of the counters and the recommended value for each:

• • \Memory\Available Bytes: Indicates the free physical memory in the host server. This value is system dependent. If the value stays consistently low, it is usually a sign of memory pressure or stress.

  • \Memory\Page Faults/sec: Page fault is an undesirable condition where the required data pages are not in cache and they have to be read from memory or disk. When read from memory, it is known as soft page fault. When read from disk, it is known as hard page fault. Page Faults/sec measure the rate of total page faults per second, both the soft page faults and hard page faults.  This value is usually used for comparison with the baseline for analysis.

  • \Memory\Pages Input/sec: The rate of reading pages from the disk. We need to compare the average value with the baseline for further analysis.

  • \Memory\Pages Output/sec: The rate of dirty pages that need to be written to disk. This is not a desirable condition. Usually this indicates memory pressure. We need to compare the average value with the baseline for further analysis.

  • \Memory\Pages/sec: The number of pages read from or written to disk for hard page faults. The recommended average value should be less than 50.

 For the SQLServer:Buffer Manager object, you can add counters such as                                                                                                                  

Buffer cache hit ratio,    Page Life Expectancy,     Database Pages,    Checkpoint Pages/sec  and    Lazy writes/sec.

• Memory to store data pages, internal data structures, and the procedure cache.

  • Counters to monitor the physical I/O as SQL Server reads and writes database pages.

The following describes each of the counters and the recommended value for each:

• • \SQLServer:Buffer Manager\Buffer cache hit ratio: This counter measures the percentage of pages that are read from the buffer cache without having to read from disk. The buffer cache is the pool of buffer pages and it is usually the biggest part of the SQL Server memory pool. This value should stay consistently high or as close to 100% as possible.However, a rate of 90 percent or higher is desirable If it averages below 97%, you may need to consider increasing more memory.

SELECT [cntr_value] FROM sys.dm_os_performance_counters

WHERE [object_name]LIKE '%Buffer Manager%' AND [counter_name] = 'Buffer cache hit ratio'

• • \SQLServer:Buffer Manager\ Checkpoint Pages/sec: This counter represents the number of pages that are moved to disk when a checkpoint operation occurs. A high value indicates a high number of writes. The recommended average should be below 30 per second.

  • \SQLServer:Buffer Manager\ Lazy writes/sec: This counter measures the dirty buffers, data pages that have been modified, that need to be written to disk to free up memory. The recommended average value should be less than 20

                                          SELECT [counter_name], [cntr_value] FROM sys.dm_os_performance_counters

                                                         WHERE [object_name] LIKE '%Buffer Manager%'

                                            AND [counter_name] IN ('Page reads/sec', 'Page writes/sec', 'Lazy writes/sec')

• • \SQL Server:Buffer Manager\ Page Life Expectancy: This counter indicates the duration a data page can stay in the buffer pool without being referenced. The higher the value the better. A low average value is a sign of memory pressure. Years ago, when the host servers were running under less than optimum machines, experts would usually recommend the average value should be 300 (5 minutes). With advanced in the power of the host server, the average should be 480 (8 minutes or more).

SELECT [cntr_value]FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Page life expectancy'

For the SQLServer:Memory Manager object, you can add counters such as Memory Grants Pending, Target Server Memory (KB), and Total Server Memory (KB)

• • \SQLServer:Memory Manager\Memory Grants Pending: This counter represents the number of processes waiting for memory grant within SQL Server memory. The recommended average is 0. If this counter’s value is high, it is a clear indication that SQL Server is short of memory.

  • \SQLServer:Memory Manager\Target Server Memory (KB): Shows the amount of memory that SQL Server wishes to consume.

  • \SQLServer:Memory Manager\Total Server Memory (KB): The name may be misleading.  This is not the total memory used by SQL Server. This counter shows the amount of memory that SQL Server is current using and it will grow to the value that’s close to or equal to Target Server Memory (KB). If this counter’s value is much less than the value for the Target Server Memory (KB), the most likely causes can be the SQL Server memory requirement is low or the max server memory configuration is set to a value that’s too low.