SQL SERVER – Introduction to Three Important Performance Counters

Performance Counters are very important to evaluate. There are more than thousands of Performance Counters. Today I will cover three basic but very important Performance Counters.

Processor:% Processor Time
It reports the total processor time with respect to the available capacity of the server. If counter is between 50 to 70 % consistently, investigate the process which is taking long time.

PhysicalDisk:Avg.Disk Queue Length
It indicates wait time for processes to use disk resources. As a disk is reading and writing data some requests cannot be immediately filled, those requests are queued. If many simultaneous requests are waiting, investigate the process which is taking long time.

PhysicalDisk:Disk Read Bytes/sec and PhysicalDisk :Disk Write Bytes/sec
It report the number of bytes read from and written to the disk, respectively. Slow SELECT queries with high physical reads and low queue lengths demonstrates under performance of Disk read and write. Index optimization can reduce this problem.

You can watch above performance counters using windows perfmon utility.

SQL SERVER – Introduction to Performance Monitor

How to Read These Performance Counters Without Guessing

One sample tells you very little. A CPU spike for a few seconds is normal. What matters is a value that stays high for a long time, so collect the counters over a normal working day and keep the result as your baseline. Repeat it after big changes, such as new hardware or a new release of your application. When users complain later, you compare against that baseline instead of guessing what normal looks like.

For disks, I also add Avg. Disk sec/Read and Avg. Disk sec/Write. They show how long each read and write takes, and on modern storage with many disks behind one volume, that latency is often easier to judge than the queue length.

SQL Server has its own counters too, under the SQLServer objects, such as Batch Requests/sec and Page life expectancy. You can also read them with T-SQL from sys.dm_os_performance_counters, which is handy when you cannot open Perfmon on the server. Look at the Windows and SQL Server numbers side by side, because one alone rarely tells the whole story.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.