The following metrics can be monitored on Microsoft SQL Server 2005 systems:
% Processor Time is the percentage of elapsed time that all of the threads of this process used the processor to execute instructions.
Number of unrestricted full scans. These can either be base table or full index scans.
Number of index searches. Index searches are used to start range scans, single index record fetches, and to reposition within an index.
The number of times locks on a table were escalated.
Percentage of pages found in the buffer cache without having to read from disk.
Number of pages flushed by checkpoint or other operations that require all dirty pages to be flushed.
Number of buffers written by buffer manager's lazy writer.
Number of requests to find a page in the buffer pool.
Number of physical database page reads issued.
Number of physical database page writes issued.
Number of pages read in anticipation of use.
Number of active cursors.
Amount of memory consumed by cursors in kilobytes (KB).
Number of active update transactions for the database.
The rate data is being moved by Autoshrink, DBCC SHRINKDATABASE or SHRINKFILE.
Number of transactions started for the database.
Number of users connected to the system.
The average amount of wait time (milliseconds) for each lock request that resulted in a wait.
Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.
Number of lock requests that resulted in a deadlock.
Total amount of dynamic memory the server is willing to consume.
Total amount of dynamic memory the server is currently consuming.
Number of SQL batch requests received by server.
The number of SQL compilations per second.
The number of SQL re-compiles per second.
The amount of space (in kilobytes) available in tempdb. There must be enough free space to hold both the snapshot isolation level version store and all new temporary objects created in this instance of the database engine.
The number of currently active transactions of all types.
All Microsoft SQL Server 2005 counters are still available in Microsoft SQL Server 2008 and 2008 R2 systems. Additional metrics are also available:
Number of committed transactions recorded in the commit table for the database.
Number of transactions which wrote to the database in the last second.
Total number of connection resets per second.
Number of duplicate tempdb rowset id generated
Number of plan executions per second in which a plan guide could not be honored during plan generation
Number of plan executions per second in which the query plan has been generated by using a plan guide.