Available Microsoft SQL Server Performance Measures

Performance Measures on Microsoft SQL Server 2005

The following metrics can be monitored on Microsoft SQL Server 2005 systems:

  • Process(sqlservr)\% Processor Time

    % Processor Time is the percentage of elapsed time that all of the threads of this process used the processor to execute instructions.

  • SQLServer:Access Methods\Full Scans/sec

    Number of unrestricted full scans. These can either be base table or full index scans.

  • SQLServer:Access Methods\Index Searches/sec

    Number of index searches. Index searches are used to start range scans, single index record fetches, and to reposition within an index.

  • SQLServer:Access Methods\Table Lock Escalations/sec

    The number of times locks on a table were escalated.

  • SQLServer:Buffer Manager\Buffer cache hit ratio

    Percentage of pages found in the buffer cache without having to read from disk.

  • SQLServer:Buffer Manager\Checkpoint pages/sec

    Number of pages flushed by checkpoint or other operations that require all dirty pages to be flushed.

  • SQLServer:Buffer Manager\Lazy writes/sec

    Number of buffers written by buffer manager's lazy writer.

  • SQLServer:Buffer Manager\Page lookups/sec

    Number of requests to find a page in the buffer pool.

  • SQLServer:Buffer Manager\Page reads/sec

    Number of physical database page reads issued.

  • SQLServer:Buffer Manager\Page writes/sec

    Number of physical database page writes issued.

  • SQLServer:Buffer Manager\Readahead pages/sec

    Number of pages read in anticipation of use.

  • SQLServer:Cursor Manager by Type\Active cursors

    Number of active cursors.

  • SQLServer:Cursor Manager by Type\Cursor memory usage

    Amount of memory consumed by cursors in kilobytes (KB).

  • SQL Server:Databases\ Active Transactions

    Number of active update transactions for the database.

  • SQL Server:Databases\ Shrink Data Movement Bytes/sec

    The rate data is being moved by Autoshrink, DBCC SHRINKDATABASE or SHRINKFILE.

  • SQL Server:Databases\ Transactions/sec

    Number of transactions started for the database.

  • SQLServer:General Statistics\User Connections

    Number of users connected to the system.

  • SQLServer:Locks(_Total)\Average Wait Time (ms)

    The average amount of wait time (milliseconds) for each lock request that resulted in a wait.

  • SQLServer:Locks(_Total)\Lock Waits/sec

    Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.

  • SQLServer:Locks(_Total)\Number of Deadlocks/sec

    Number of lock requests that resulted in a deadlock.

  • SQLServer:Memory Manager\Target Server Memory (KB)

    Total amount of dynamic memory the server is willing to consume.

  • SQLServer:Memory Manager\Total Server Memory (KB)

    Total amount of dynamic memory the server is currently consuming.

  • SQLServer:SQL Statistics\Batch Requests/sec

    Number of SQL batch requests received by server.

  • SQLServer:SQL Statistics\SQL Compilations/sec

    The number of SQL compilations per second.

  • SQLServer:SQL Statistics\SQL Re-Compilations/sec

    The number of SQL re-compiles per second.

  • SQLServer:Transactions\Free Space in tempdb (KB)

    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.

  • SQLServer:Transactions\Transactions

    The number of currently active transactions of all types.

Performance Measures on Microsoft SQL Server 2008, 2008 R2

All Microsoft SQL Server 2005 counters are still available in Microsoft SQL Server 2008 and 2008 R2 systems. Additional metrics are also available:

  • SQL Server:Databases\Tracked transactions/sec

    Number of committed transactions recorded in the commit table for the database.

  • SQL Server: Databases\Write Transactions/sec

    Number of transactions which wrote to the database in the last second.

  • SQL Server: General Statistics\Connection reset/sec

    Total number of connection resets per second.

  • SQL Server: General Statistics\Tempdb rowset id

    Number of duplicate tempdb rowset id generated

  • SQL Server: SQL Statistics\Misguided plan executions/sec

    Number of plan executions per second in which a plan guide could not be honored during plan generation

  • SQL Server: SQL Statistics\Guided plan executions/sec

    Number of plan executions per second in which the query plan has been generated by using a plan guide.