SQL - Performance Counter

SQL Server, Latches Object

The SQLServer:Latches object in Microsoft SQL Server provides counters to monitor internal SQL Server resource locks called latches. Monitoring the latches to determine user activity and resource usage can help you to identify performance bottlenecks.

This table describes the SQL Server Latches counters.

SQL Server Latches counters	Description
Average Latch Wait Time (ms)	Average latch wait time (in milliseconds) for latch requests that had to wait.
Latch Waits/sec	Number of latch requests that could not be granted immediately.
Number of SuperLatches	Number of latches that are currently SuperLatches.
SuperLatch Demotions/sec	Number of SuperLatches that have been demoted to regular latches in the last second.
SuperLatch Promotions/sec	Number of latches that have been promoted to SuperLatches in the last second.
Total Latch Wait Time (ms)	Total latch wait time (in milliseconds) for latch requests in the last second.

SQL Server, Locks Object

The SQLServer:Locks object in Microsoft SQL Server provides information about SQL Server locks on individual resource types. Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released. Minimizing locks increases concurrency, which can improve performance. Multiple instances of the Locksobject can be monitored at the same time, with each instance representing a lock on a resource type.

This table describes the SQL Server Locks counters.

SQL Server Locks counters	Description
Average Wait Time (ms)	Average amount of wait time (in milliseconds) for each lock request that resulted in a wait.
Lock Requests/sec	Number of new locks and lock conversions per second requested from the lock manager.
Lock Timeouts (timeout > 0)/sec	Number of lock requests per second that timed out, but excluding requests for NOWAIT locks.
Lock Timeouts/sec	Number of lock requests per second that timed out, including requests for NOWAIT locks.
Lock Wait Time (ms)	Total wait time (in milliseconds) for locks in the last second.
Lock Waits/sec	Number of lock requests per second that required the caller to wait.
Number of Deadlocks/sec	Number of lock requests per second that resulted in a deadlock.

SQL Server can lock these resources.

Item	Description
_Total	Information for all locks.
AllocUnit	A lock on an allocation unit.
Application	A lock on an application-specified resource.
Database	A lock on a database, including all objects in the database.
Extent	A lock on a contiguous group of 8 pages.
File	A lock on a database file.
Heap/BTree	Heap or BTree (HOBT). A lock on a heap of data pages, or on the BTree structure of an index.
Key	A lock on a row in an index.
Metadata	A lock on a piece of catalog information, also called metadata.
Object	A lock on table, stored procedure, view, etc, including all data and indexes. The object can be anything that has an entry in sys.all_objects.
Page	A lock on an 8-kilobyte (KB) page in a database.
RID	Row ID. A lock on a single row in a heap.

SQL Server, Memory Manager Object

The Memory Manager object in Microsoft SQL Server provides counters to monitor overall server memory usage. Monitoring overall server memory usage to gauge user activity and resource usage can help you to identify performance bottlenecks. Monitoring the memory used by an instance of SQL Server can help determine:

• If bottlenecks exist from inadequate physical memory for storing frequently accessed data in cache. If memory is inadequate, SQL Server must retrieve the data from disk.
• If query performance can be improved by adding more memory or by making more memory available to the data cache or SQL Server internal structures.

This table describes the SQL Server Memory Manager counters.

SQL Server Memory Manager counters	Description
Connection Memory (KB)	Total amount of dynamic memory the server is using for maintaining connections.
Granted Workspace Memory (KB)	Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations.
Lock Blocks	Current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row.
Lock Blocks Allocated	Current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases.
Lock Memory (KB)	Total amount of dynamic memory the server is using for locks.
Lock Owner Blocks	Number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there will be three lock owner blocks.
Lock Owner Blocks Allocated	Current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks and the number of allocated lock blocks depend on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically.
Maximum Workspace Memory (KB)	Maximum amount of memory available for executing processes such as hash, sort, bulk copy, and index creation operations.
Memory Grants Outstanding	Total number of processes that have successfully acquired a workspace memory grant.
Memory Grants Pending	Total number of processes waiting for a workspace memory grant.
Optimizer Memory (KB)	Total amount of dynamic memory the server is using for query optimization.
SQL Cache Memory (KB)	Total amount of dynamic memory the server is using for the dynamic SQL cache.
Target Server Memory (KB)	Total amount of dynamic memory the server can consume.
Total Server Memory (KB)	The committed memory from the buffer pool (in kilobytes). Note This is not the total memory used by SQL Server.

SQL Server, Plan Cache Object

The Plan Cacheobject provides counters to monitor how SQL Server uses memory to store objects such as stored procedures, ad hoc and prepared Transact-SQL statements, and triggers. Multiple instances of thePlan Cache object can be monitored at the same time, with each instance representing a different type of plan to monitor.

This table describes are the SQLServer:Plan Cachecounters.

SQL Server Plan Cache counters	Description
Cache Hit Ratio	Ratio between cache hits and lookups.
Cache Object Counts	Number of cache objects in the cache.
Cache Pages	Number of 8-kilobyte (KB) pages used by cache objects.
Cache Objects in use	Number of cache objects in use.

SQL Server, Wait Statistics Object

The SQLServer:Wait Statistics performance object contains performance counters that report information about wait status.

The table below lists the counters that the Wait Statistics object contains.

SQL Server Wait Statistics counters	Description
Lock waits	Statistics for processes waiting on a lock.
Log buffer waits	Statistics for processes waiting for log buffer to be available.
Log write waits	Statistics for processes waiting for log buffer to be written.
Memory grant queue waits	Statistics for processes waiting for memory grant to become available.
Network IO waits	Statistics relevant to wait on network I/O.
Non-Page latch waits	Statistics relevant to non-page latches.
Page IO latch waits	Statistics relevant to page I/O latches.
Page latch waits	Statistics relevant to page latches, not including I/O latches.
Thread-safe memory objects waits	Statistics for processes waiting on thread-safe memory allocators.
Transaction ownership waits	Statistics relevant to processes synchronizing access to transaction.
Wait for the worker	Statistics relevant to processes waiting for worker to become available.
Workspace synchronization waits	Statistics relevant to processes synchronizing access to workspace.

Each counter in the object contains the following instances:

Item	Description
Average wait time (ms)	Average time for the selected type of wait.
Cumulative wait time (ms) per second	Aggregated wait time per second, for the selected type of wait.
Waits in progress	Number of processes currently waiting on the following type.
Waits started per second	Number of waits started per second of the selected type of wait.

SQL Server, Buffer Manager Object

The Buffer Manager object provides counters to monitor how SQL Server uses:

• Memory to store data pages, internal data structures, and the procedure cache.
• Counters to monitor the physical I/O as SQL Server reads and writes database pages.

Monitoring the memory and the counters used by SQL Server helps you determine:

• If bottlenecks exist from inadequate physical memory. If it cannot store frequently accessed data in cache, SQL Server must retrieve the data from disk.
• If query performance can be improved by adding more memory, or by making more memory available to the data cache or SQL Server internal structures.
• How often SQL Server needs to read data from disk. Compared with other operations, such as memory access, physical I/O consumes a lot of time. Minimizing physical I/O can improve query performance.

You can also monitor Address Windowing Extensions (AWE) activity in SQL Server with the AWE counters. For example, you can make sure that SQL Server has enough memory allocated for AWE to run properly. For more information, see Memory Architecture, Using AWE, or awe enabled Option.

This table describes the SQL Server Buffer Manager performance objects.

SQL Server Buffer Manager counters	Description
AWE lookup maps/sec	Number of times per second that a database page was requested by the server, found in the buffer pool, and mapped. When it is mapped, it is made a part of the server's virtual address space.
AWE stolen maps/sec	Number of times per second that a buffer was taken from the free list and mapped.
AWE unmap calls/sec	Number of calls to unmap buffers per second. When a buffer is unmapped, it is excluded from the virtual server address space. One or more buffers may be unmapped on each call.
AWE unmap pages/sec	Number of SQL Server buffers that are unmapped per second.
AWE write maps/sec	Number of times per second that it is necessary to map in a dirty buffer so it can be written to disk.
Buffer cache hit ratio	Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.
Checkpoint pages/sec	Number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
Database pages	Number of pages in the buffer pool with database content.
Free list stalls/sec	Number of requests per second that had to wait for a free page.
Free pages	Total number of pages on all free lists.
Lazy writes/sec	Number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and makes them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.
Page life expectancy	Number of seconds a page will stay in the buffer pool without references.
Page lookups/sec	Number of requests per second to find a page in the buffer pool.
Page reads/sec	Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.
Page writes/sec	Number of physical database page writes issued per second.
Readahead pages/sec	Number of pages read per second in anticipation of use.
Reserved pages	Number of buffer pool reserved pages.
Stolen pages	Number of pages used for miscellaneous server purposes (including procedure cache).
Target pages	Ideal number of pages in the buffer pool.
Total pages	Number of pages in the buffer pool (includes database, free, and stolen pages).

SQL Server, Access Methods Object

The Access Methods object in SQL Server provides counters to monitor how the logical data within the database is accessed. Physical access to the database pages on disk is monitored using the Buffer Managercounters. Monitoring the methods used to access data stored in the database can help you to determine whether query performance can be improved by adding or modifying indexes, adding or moving partitions, adding files or file groups, defragmenting indexes, or by rewriting queries. The Access Methods counters can also be used to monitor the amount of data, indexes, and free space within the database, thereby indicating data volume and fragmentation for each server instance. Excessive index fragmentation can impair performance.

For more detailed information about data volume, fragmentation and usage, use the following dynamic management views:

• sys.dm_db_index_operational_stats (Transact-SQL)
• sys.dm_db_index_physical_stats (Transact-SQL)
• sys.dm_db_partition_stats (Transact-SQL)
• sys.dm_db_index_usage_stats (Transact-SQL)

For space consumption in tempdb at the file, task and session level, use these dynamic management views:

• sys.dm_db_file_space_usage (Transact-SQL)
• sys.dm_db_task_space_usage (Transact-SQL)
• sys.dm_db_session_space_usage (Transact-SQL)

This table describes the SQL Server Access Methods counters.

SQL Server Access Methods counters	Description
AU cleanup batches/sec	The number of batches per second that were completed successfully by the background task that cleans up deferred dropped allocation units.
AU cleanups/sec	The number of allocation units per second that were successfully dropped the background task that cleans up deferred dropped allocation units. Each allocation unit drop requires multiple batches.
By-reference Lob Create Count	Count of large object (lob) values that were passed by reference. By-reference lobs are used in certain bulk operations to avoid the cost of passing them by value.
By-reference Lob Use Count	Count of by-reference lob values that were used. By-reference lobs are used in certain bulk operations to avoid the cost of passing them by-value.
Count Lob Readahead	Count of lob pages on which readahead was issued.
Count Pull In Row	Count of column values that were pulled in-row from off-row. For more information about row overflow, see Row-Overflow Data Exceeding 8 KB.
Count Push Off Row	Count of column values that were pushed from in-row to off-row. For more information about row overflow, see Row-Overflow Data Exceeding 8 KB.
Deferred Dropped Aus	The number of allocation units waiting to be dropped by the background task that cleans up deferred dropped allocation units.
Deferred Dropped rowsets	The number of rowsets created as a result of aborted online index build operations that are waiting to be dropped by the background task that cleans up deferred dropped rowsets.
Dropped rowset cleanups/sec	The number of rowsets per second created as a result of aborted online index build operations that were successfully dropped by the background task that cleans up deferred dropped rowsets.
Dropped rowsets skipped/sec	The number of rowsets per second created as a result of aborted online index build operations that were skipped by the background task that cleans up deferred dropped rowsets created.
Extent Deallocations/sec	Number of extents deallocated per second in all databases in this instance of SQL Server.
Extents Allocated/sec	Number of extents allocated per second in all databases in this instance of SQL Server.
Failed AU cleanup batches/sec	The number of batches per second that failed and required retry, by the background task that cleans up deferred dropped allocation units. Failure could be due to lack of memory or disk space, hardware failure and other reasons.
Failed leaf page cookie	The number of times that a leaf page cookie could not be used during an index search since changes happened on the leaf page. The cookie is used to speed up index search.
Failed tree page cookie	The number of times that a tree page cookie could not be used during an index search since changes happened on the parent pages of those tree pages. The cookie is used to speed up index search.
Forwarded Records/sec	Number of records per second fetched through forwarded record pointers.
FreeSpace Page Fetches/sec	Number of pages fetched per second by free space scans. These scans search for free space within pages already allocated to an allocation unit, to satisfy requests to insert or modify record fragments.
FreeSpace Scans/sec	Number of scans per second that were initiated to search for free space within pages already allocated to an allocation unit to insert or modify record fragment. Each scan may find multiple pages.
Full Scans/sec	Number of unrestricted full scans per second. These can be either base-table or full-index scans.
Index Searches/sec	Number of index searches per second. These are used to start a range scan, reposition a range scan, revalidate a scan point, fetch a single index record, and search down the index to locate where to insert a new row.
LobHandle Create Count	Count of temporary lobs created.
LobHandle Destroy Count	Count of temporary lobs destroyed.
LobSS Provider Create Count	Count of LOB Storage Service Providers (LobSSP) created. One worktable created per LobSSP.
LobSS Provider Destroy Count	Count of LobSSP destroyed.
LobSS Provider Truncation Count	Count of LobSSP truncated.
Mixed page allocations/sec	Number of pages allocated per second from mixed extents. These could be used for storing the IAM pages and the first eight pages that are allocated to an allocation unit.
Page compression attempts/sec	Number of pages evaluated for page-level compression. Includes pages that were not compressed because significant savings could be achieved. Includes all objects in the instance of SQL Server. For information about specific objects, see sys.dm_db_index_operational_stats (Transact-SQL).
Page Deallocations/sec	Number of pages deallocated per second in all databases in this instance of SQL Servere. These include pages from mixed extents and uniform extents.
Page Splits/sec	Number of page splits per second that occur as the result of overflowing index pages.
Pages Allocated/sec	Number of pages allocated per second in all databases in this instance of SQL Server. These include pages allocations from both mixed extents and uniform extents.
Pages compressed/sec	Number of data pages that are compressed by using PAGE compression. Includes all objects in the instance of SQL Server. For information about specific objects, see sys.dm_db_index_operational_stats (Transact-SQL).
Probe Scans/sec	Number of probe scans per second that are used to find at most one single qualified row in an index or base table directly.
Range Scans/sec	Number of qualified range scans through indexes per second.
Scan Point Revalidations/sec	Number of times per second that the scan point had to be revalidated to continue the scan.
Skipped Ghosted Records/sec	Number of ghosted records per second skipped during scans.
Table Lock Escalations/sec	Number of times locks on a table were escalated to the TABLE or HoBT granularity.
Used leaf page cookie	Number of times a leaf page cookie is used successfully during an index search since no change happened on the leaf page. The cookie is used to speed up index search.
Used tree page cookie	Number of times a tree page cookie is used successfully during an index search since no change happened on the parent page of the tree page. The cookie is used to speed up index search.
Workfiles Created/sec	Number of work files created per second. For example, work files could be used to store temporary results for hash joins and hash aggregates.
Worktables Created/sec	Number of work tables created per second. For example, work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors.
Worktables From Cache Ratio	Percentage of work tables created where the initial two pages of the work table were not allocated but were immediately available from the work table cache. (When a work table is dropped, two pages may remain allocated and they are returned to the work table cache. This increases performance.)