Counter |
Description |
Expected Value |
---|
Memory |
---|
Available Bytes |
Free physical memory |
Should not be too low |
Pages/sec |
Rate of hard page faults.
A page fault occurs when a process requires data that is not in its space in physical memory. If the faulted page is found elsewhere in physical memory, then it is called a soft page fault. A hard page fault occurs when a process requires data that must be retrieved from disk.
The speed of a disk access is in the order of milliseconds, whereas a memory access is in the order of nanoseconds.
|
Average Value < 50 |
Page Faults/sec |
Rate of total page faults (soft page faults plus hard page faults) |
Compare with its baseline value for trend analysis |
SQLServer:Buffer Manager |
---|
Buffer cache hit ratio |
Percentage of requests served out of buffer cache.
A low value indicates that few requests could be served out of the buffer cache, with the rest of the requests being served from disk. |
Average Value >= 90% in an OLTP system. |
Page Life Expectancy |
Time page spends in buffer cache without being referenced. |
A low value means that pages are being removed from the buffer, lowering the efficiency of the cache and indicating the possibility of memory pressure. |
Checkpoint Pages/sec |
Pages written to disk by a checkpoint operation.
A dirty page is one that is modified while in the buffer. When it’s modified, it’s marked as dirty and will get written back to the disk during the next checkpoint. |
Average Value < 30 |
Lazy writes/sec |
Dirty aged pages flushed from buffer. |
Average Value < 20 |
SQLServer:Memory Manager |
---|
Memory Grants Pending |
Number of processes waiting for memory grant. If this counter value is high, then SQL Server is short of memory.
See sys.dm_ exec_query_memory_grants dmv.
|
Average value = 0. |
Target Server Memory (KB) |
Indicates the total amount of dynamic memory SQL Server is willing to consume. |
Close to size of physical Memory |
Total Server Memory (KB) |
Amount of physical memory currently assigned to SQL. |
Close to Target server Memory (KB) |
Process |
---|
Private Bytes |
Size, in bytes, of memory that this process has allocated that can’t be shared with other processes |
|