Thursday, 21 June 2018

SQL server performance counters

Object Counter Description Expected Value
CPU Processor Queue Length This is the length of the queue of threads waiting for processor time. Should be Low, <5
CPU % Processor Time CPU usage for the overall server < 80%
CPU % Privileged Time CPU time spent on Windows kernel commands, such as SQL Server I/O requests < 30%
CPU % User Time The amount of CPU spent on user processes, including SQL Server < 80%
Memory Page life expectancy Average how long each data page is staying in buffer cache before being flushed out to make room for other pages MAXBP(MB)/1024)/4)*300 so for 16GB of Buffer Pool, it is (16000/1024)/4*300. The above formula considers each 4GB of BP (per NUMA node)
Memory Free list stalls / sec Number of times a request for a “free” page had to wait for one to become available. If there were no free pages in the buffer cache, a request is stalled and has to wait until a page in the buffer is freed The recommended value is below 2. When the Free list stalls/sec value is higher than the recommended, check the Page Life Expectancy and Lazy Writes/sec values, as well. If the Page Life Expectancy value is below 300 seconds and Lazy Writes/sec above 2, it’s a clear sign of memory pressure
Memory Checkpoint Pages/sec Checkpoint Pages/sec shows the number of pages that are moved from buffer to disk per second during a checkpoint process. At a checkpoint, all dirty pages containing both committed and uncommitted transactions are flushed to disk <20 Per Sec
Memory Lazy writes / sec How many times per second lazy writer has to flush dirty pages out of the buffer cache instead of waiting on a checkpoint. The lazy writer is a process that periodically checks the available free space in the buffer cache between two checkpoints and ensures that there is always enough free memory. When the lazy writer determines free pages are needed in the buffer for better performance, it removes the old pages before the regular checkpoint occurs If the Lazy Writes value is constantly higher than 20, to be sure that the server is under memory pressure, check Page Life Expectancy.
Memory Memory Grants Pending Number of processes waiting on a workspace memory grant. Expected Value 0
Memory Buffer cache hit ratio Percentage of pages that were found in the buffer pool without having to incur a read from disk. Should be Always Above 90% - 95%
PhysicalDisk Avg. Disk Queue Length The average number of read and write requests that were queued on the selected physical disk. The higher the number the more disk operations are waiting >2 Is a Problem. If you have multiple drives you should take this number and divide by the number of drives. For example, you have 4 drives and a disk queue length of 10, this would be 10/4 = 2.5, so this would be the value you want to use not 10
PhysicalDisk Avg. Disk Sec/Read  Average Disk sec/Read is proportional to time needed for one disk rotation. For example, a disk that makes 3,600 round per minute needs 60s/3600 = 0.016 seconds, i.e. 16 milliseconds to make one rotation. Average Disk sec/Read for that disk should be a multiple of 16 milliseconds.
< .008ms excellent
.008 - .012 Good
.012 - .020 Fair;
>.020 investigate
PhysicalDisk Avg. Disk Sec/Write shows the average time in seconds needed to write data to disk
< .008ms excellent
.008 - .012 Good
.012 - .020 Fair;
>.020 investigate

No comments:

Post a Comment

CPU queries in SQL server

-- Database Wise CPU Utilization report -- CPU_Time(Ms): CPU Time in Milliseconds -- CPUPercent: Let’s say this instance is using 50% CP...