Performance Counters

You can use the following performance counters to identify I/O bottlenecks.

PhysicalDisk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your I/O system is overloaded, more read/write operations will be waiting. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, then you might have an I/O bottleneck.

Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. Any number

    Less than 10 ms - very good

    Between 10 - 20 ms - okay

    Between 20 - 50 ms - slow, needs attention

    Greater than 50 ms – Serious I/O bottleneck

    Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. Please refer to the guideline in the previous bullet.

Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck.

Avg. Disk Reads/Sec is the rate of read operations on the disk. You need to make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.

Avg. Disk Writes/Sec is the rate of write operations on the disk. Make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.

Buffer Manager: Page Life Expectancy

“Duration, in seconds, that a page resides in the buffer pool”

SQL Server has more chances to find the pages in the buffer pool if they stay there longer. If the page is not in the buffer pool, it will be read from disk, which affects performance. If there’s insufficient memory, data pages are flushed from buffer cache more frequently, to free up the space for the new pages. When there’s sufficient memory on the server, pages have a high life expectancy. The normal values are above 300 seconds (5 minutes) and the trend line should be stable.

Buffer Manager:Buffer Cache Hit Ratio

Buffer Cache Hit Ratio shows how SQL Server utilizes buffer cache

It gives the ratio of the data pages found and read from the SQL Server buffer cache and all data page requests. The pages that are not found in the buffer cache are read from the disk, which is significantly slower and affects performance. Ideally, SQL Server would read all pages from the buffer cache and there will be no need to read any from disk. In this case, the Buffer Cache Hit Ratio value would be 100. The recommended value for Buffer Cache Hit Ratio is over 90. When better performance is needed, the minimal acceptable value is 95. A lower value indicates a memory problem.

Perfmon - Important performance counters

CPU -> Processor -> Processor Time

CPU -> Processor -> Processor queue length

Memory -> Available Mbytes                     more is better

Memory -> Pages/sec

Disk ->  Avg. disk sec/read (100ms)

Disk -> Avg. disk sec/write                          

SQL Server -> Access Methods full scans/sec                      >1 is bad

SQL Server -> buffer manager - buffer cache hit ratio

SQL Server -> buffer manager – Page life expectancy

SQL Server -> locks – average wait time                                 should be low

SQL Server -> Memory Manager – Memory Grants Pending (no of queries waiting for memory allocation)

Performance Monitor Counters to Analyze Generic SQL Pressure

Object(Instance[,InstanceN])                Counter

SQLServer:Access Methods                     FreeSpace Scans/sec

Full Scans/sec

Table Lock Escalations/sec

Worktables Created/sec

SQLServer:Latches                                  Total Latch Wait Time (ms)

SQLServer:Locks(_Total)                          Lock Timeouts/sec

Lock Wait Time (ms)

Number of Deadlocks/sec

SQLServer:SQL Statistics                         Batch Requests/sec

SQL Re-Compilations/sec

SQLServer:General Statistics                    Processes Blocked

User Connections

Temp Tables Creation Rate

Temp Tables for Destruction