Perfmon counters and thresholds to detect the memory pressure :
Following are counters to identify SQL Server Memory consumption at the OS level:
References: Microsoft SQLGuru
Following are counters to identify SQL Server Memory consumption at the OS level:
- SQL SEVER: memory manager: total server memory
- SQL SEVER: memory manager: target server memory
- Target server memory is equal to max server memory as we set in sp_configure parameter.
- Total server time not higher than equal to target memory.
- If Total Server Memory is much smaller than Target Server Memory it can be a sign of Memory Pressure or Max Server Memory is set to too low.
- Lazy Writes/sec which tells you that number of times the buffer pool flushed dirty pages to disk outside of the CHECKPOINT process. This should be near zero.
- Memory\Available MBytes:
- Not below 2 GB to 4GB, or If this value falls below 5% of installed RAM
- Buffer Manager:Page life expectancy
- Look at Page Life Expectancy, which should be well above the 300 number
- Page life expectany if less than 300 seconds, pages are flushed from buffer.
- SQL Server: Buffer Manager: Buffer Cache Hit Ratio
- Add more memory(ram) until the value is consistently greater than 90 percent
- Memory:Page Writes/sec
- The same as Page reads/sec, the recommended value for Page writes/sec is below 90
- SQLServer:Buffer Manager\Lazy writes/sec
- It write dirty pages to disk to make availbale buffer
- When this indicator is greater than 20, then it indicates a need for more memory
- Memory Grants Pending
- Processes waiting for memory,value of this measurement should always be less or equal to 1.
- Perfmon: Memory\Commit Limit
- This is the total amount of memory that can be used on the system, and is the sum of RAM and pagefile space. If pagefiles are set to automatically extend, and there is disk space for them to do so, then the commit limit can increase.
- Perfmon: Memory\Committed Bytes
- Committed bytes includes memory that is not yet allocated to a process, but is reserved for future use.
- If commit limit exceeds system extends pagefile space if possible.
- Make sure: Committed Bytes < Commit Limit
- Threshold issue:- if Committed Bytes > Commit Limit
- Process(process_name)\Working Set:
- The WorkingSet counter shows the amount of memory that is used by a process.
- The working set is the set of memory pages that were touched recently by the threads in the process.
- If this number is consistently below the amount of memory that is set by the min server memory and max server memory server options, SQL Server is configured to use too much memory.
- Process(process_name)\Private Bytes
- Shows the size, in bytes, that this process has allocated that cannot be shared with other processes.
- Memory CacheFaults/sec
- Memory - Page Faults/sec counter
- The Page faults/sec counter shows both hard and soft page faults.
- Soft page faults cause no performance issues reading from memory rather than disk(hard fault).
- The normal values are 10 to 15, but even 1,000 page faults per second can be normal in specific environments.
- The value depends on the type and amount of memory, and the speed of disk access.
- Memory \ Pages /sec
- Memory \ Pages /sec is the rate at which pages are read from or written to disk to resolve hard page faults.
- It is the sum of Memory \ Pages Input/sec and Memory \ Pages Output/sec.
- Both Pages Input/sec and Pages Output/sec values are recommended to be lower than 10 means below 20 for Memory\ pages/sec.
- A high rate for the Pages/sec counter could indicate excessive paging.
- If the Pages/sec value is constantly higher than 50 or 70% , to confirm that hard page faults.
- Monitor disk behavior and paging via memory and disk counters, such as Pages Input/sec, Pages Output/sec, Disk Reads/sec, and Avg. Disk Read Bytes/sec
- If the Pages / sec multiplied by 4,000 (the 4k page size) is greater than 70% of the total number of LogicalDIsk: Disk Bytes/ sec to the disk(s)
- Memory\ Page Reads/sec
- The Page Reads counter allows you to track hard page faults.
- Compare the value of Memory \ Pages Reads/sec to the value of Memory \ Pages Input/sec to determine the average number of pages read during each operation.
- The same as Page reads/sec, the recommended value for Page writes/sec is below 90