Basic counters
Here is a list of Windows and SQL Server performance counters to monitor to ensure SQL Server is functioning properly.
CPU / % CPU time
The percentage of total CPU time, and optionally per core, to monitor thread distribution.
Physical Disk / Average disk s/read
To monitor on disks that contain data files for SQL Server.
Corresponds to the average time in seconds for reading data from the disk. Latency measurement. Depending on the type of disk (local or remote) the value should ideally be below .025 (therefore 25 milliseconds of latency).
Physical Disk / Average disk s/write
Monitor on disks that contain transaction log files for SQL Server, and tempdb files.
Corresponds to the average time in seconds for writing data to disk. Latency measurement. Depending on the type of disk (local or remote) the value should ideally be below .025 (therefore 25 milliseconds of latency).
SqlServer:Buffer Manager / Buffer cache access rate
The access rate of data pages in the buffer (SQL Server data cache) calculated over the last thousand pages. Should stay above 97% in average OLTP utilizations.
SqlServer:Buffer Manager / Page life expectancy
Average life expectancy of a page in the buffer.
Should stay above 1000 (at least) in average OLTP usages.
SqlServer:Databases/Transactions/sec
Choose the instances of this counter on your most important bases, and tempdb.
Number of transactions per second, therefore requests for data modifications. Monitor the counter over time to establish a baseline, and especially on tempdb.
SqlServer: Plan Cache / cache object counts
optional
Choose the _Total instance
Monitors the plan cache and possible cleaning of this cache, due to memory pressure or administrative actions.
SqlServer:SQL Statistics / Batch Requests/sec
Number of queries processed by SQL Server per second. Monitor the meter over time to establish a baseline and general indication of activity.