SQL Server Access Methods counters |
Description |
AU Cleanup batches/sec |
The number of batches successfully completed per second for background tasks that are responsible for clearing deferred deletion of allocation units. |
AU cleanups/sec |
The number of allocation units successfully deleted per second by the background task responsible for clearing the deferred deletion allocation unit. Deleting each allocation unit requires multiple batches. |
By-reference Lob Create Count |
The Count of large object (LOB) values that are passed by reference. LOBs passed by reference will be used in some bulk operations to avoid the overhead required to pass these objects by value. |
By-reference Lob Use Count |
The count of LOB values passed by reference that were used. LOBs passed by reference will be used in some bulk operations to avoid the overhead required to pass these objects by value. |
Count Lob Readahead |
Executes the count of the LOB pages that are read ahead of time. |
Count Pull in Row |
The Count of column values in the row that have been requested from outside the row. For more information about row overflow, see row overflow data exceeds 8 KB. |
Count Push Off Row |
The Count of column values that have been pushed from within the row to the outside of the row. For more information about row overflow, see row overflow data exceeds 8 KB. |
Deferred Dropped Aus |
Waits for the number of allocation units deleted by the background task that is responsible for clearing deferred deletion of the allocation unit. |
Deferred Dropped Rowsets |
Waits for the number of rowsets created by the online index build operation to be deleted by the background task that cleans up deferred deletion of the rowset. |
DROPPED Rowset Cleanups/sec |
The number of rowsets that were created by a background task that cleans up deferred deleted rowset per second successfully deleted due to the abort of the online index build operation. |
Dropped Rowsets Skipped/sec |
The number of rowsets that the background task that is responsible for clearing deferred deleted rowsets skips per second, as a result of the abort of the online index build operation. |
Extent deallocations/sec |
The number of extents freed per second in all databases in this instance of SQL Server. |
Extents Allocated/sec |
The number of extents allocated per second in all databases in this instance of SQL Server. |
Failed AU Cleanup Batches/sec |
The number of batches that failed per second and need to be retried by the background task responsible for clearing deferred deletion of the allocation unit. The failure may be due to insufficient memory or disk space, hardware failure, and other reasons. |
Failed Leaf Page Cookie |
The number of times a leaf-page cookie could not be used during an index search since a change occurred on the leaf-level page. Cookies are used to speed up index searches. |
Failed Tree Page Cookie |
The number of times that a tree page cookie could not be used during an index search since a change occurred on the parent page of the tree page. Cookies are used to speed up index searches. |
Forwarded records/sec |
The number of records fetched per second by a forward record pointer. |
FreeSpace Page fetches/sec |
Number of pages extracted per second scanned by free space. These scans search for free space within a page that has been assigned to an allocation unit to satisfy requests to insert or modify a record fragment. |
FreeSpace scans/sec |
The number of scans per second that are initiated to search for free space within a page that has been assigned to an allocation unit to insert or modify a record fragment. Multiple pages may be found per scan. |
Full scans/sec |
The number of full scans that are unrestricted per second. These scans can be either a base table scan or a full-text index scan. |
Index searches/sec |
Number of index searches per second. Index search is used to initiate a range scan, relocate a range scan, re-verify a scan point, extract a single index record, and search down the index to determine where the new row will be inserted. |
Lobhandle Create Count |
The temporary LOB count that was created. |
Lobhandle Destroy Count |
The temporary LOB count that was destroyed. |
LOBSS Provider Create Count |
The LOB Storage Service provider (LOBSSP) count that has been created. Create a worksheet for each LOBSSP. |
LOBSS Provider Destroy Count |
The LOBSSP count that has been destroyed. |
LOBSS Provider truncation Count |
The LOBSSP count that has been truncated. |
Mixed page Allocations/sec |
Number of pages allocated per second from the mixed area. These pages can be used to store IAM pages and the first eight pages assigned to an allocation unit. |
Page Compression attempts/sec |
The number of pages that are computed for page-level compression. Because space can be greatly reduced, uncompressed pages are included. Includes all objects in an instance of SQL Server. For information about specific objects, see sys.dm_db_index_operational_stats (Transact-SQL). |
Page deallocations/sec |
Number of pages freed per second in all databases in this instance of SQL Server. This includes pages from the mixed zone and the unified zone. |
Page splits/sec |
Page split scores per second due to overflow of index pages. |
Pages allocated/sec |
Number of pages allocated per second in all databases in this instance of SQL Server. These pages include pages that are assigned from the mixed zone and the unified zone. |
Pages compressed/sec |
Use page compression to compress the number of pages of data. Includes all objects in an instance of SQL Server. For information about specific objects, see sys.dm_db_index_operational_stats (Transact-SQL). |
Probe scans/sec |
The number of probe scans per second that are used to find up to one qualifying row directly in the index or base table. |
Range scans/sec |
The number of scans per second that are scoped by the index. |
Scan Point Revalidations/sec |
The number of times per second that the scan point must be re-verified to continue scanning. |
Skipped ghosted records/sec |
Number of ghost Records skipped per second during the scan. |
Table Lock escalations/sec |
The number of times the lock on the table was upgraded to table or HoBT granularity. |
Used leaf page Cookie |
The number of times the leaf-level page cookie was successfully used during an index search, since no changes have occurred on the leaf-level page. Cookies are used to speed up index searches. |
Used Tree Page Cookie |
The number of times the tree page cookie was successfully used during an index search since no change has occurred on the parent page of the tree page. Cookies are used to speed up index searches. |
Workfiles created/sec |
The number of working files created per second. For example, a working file can be used to store temporary results for hash joins and hash aggregations. |
Worktables created/sec |
The number of worksheets created per second. For example, worksheets can be used to store temporary results for query spooling, LOB variables, XML variables, and cursors. |
Worktables from Cache Ratio |
The percentage of created worksheets that are not allocated on the first two pages but can be used directly from the worksheet cache. (When the worksheet is deleted, the two pages may remain allocated and returned to the worksheet cache.) This will improve performance. ) |