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 Manager counters. 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:
For space consumption in tempdb at the file, task and session level, use these dynamic management views:
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. |
Count Push Off Row |
Count of column values that were pushed from in-row to off-row. |
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.) |