Basic counters

Here is a list of the Windows and SQL Server performance counters to watch, to make sure SQL Server is running properly.

Processor / % Processor Time

Total processor time as a percentage, and per core where useful, to watch how the threads are distributed.

Physical Disk / Avg. Disk sec/Read

Watch this on the disks holding the SQL Server data files.

It is the average time in seconds of a data read from disk — a latency measurement. Depending on the type of disk, local or remote, the value should ideally stay below .025, that is 25 milliseconds of latency.

Physical Disk / Avg. Disk sec/Write

Watch this on the disks holding the SQL Server transaction log files, and the tempdb files.

It is the average time in seconds of a data write to disk — a latency measurement. Depending on the type of disk, local or remote, the value should ideally stay below .025, that is 25 milliseconds of latency.

SQLServer:Buffer Manager / Buffer cache hit ratio

The hit ratio of data pages in the buffer, SQL Server’s data cache, calculated over the last few thousand pages. It should stay above 97 % in an average OLTP workload.

SQLServer:Buffer Manager / Page life expectancy

The average life expectancy of a page in the buffer.

It should stay above 1000 at the very least in an average OLTP workload.

A widespread way to calculate a target is: 300 × (GB of RAM allocated to SQL Server / 4).

That derives from a Microsoft recommendation of 300, dating from a time when servers commonly had 4 GB of RAM.

SQLServer:Databases / Transactions/sec

Select the instances of this counter for your most important databases, and for tempdb.

The number of transactions per second, that is, of data modification statements. Watch it over time to establish a baseline — especially on tempdb.

SQLServer:Plan Cache / Cache Object Counts

optional

Select the _Total instance.

Watches the plan cache and any flushes of it, caused by memory pressure or by administrative actions.

SQLServer:SQL Statistics / Batch Requests/sec

The number of batches SQL Server processes per second. Watch it over time to establish a baseline and as a general indication of activity.