The metrics in this category search through and measure the allocation of Microsoft SQL Server database objects, such as the number of index searches or number of pages that are allocated to indexes and data.
Default Collection Interval — Every 30 minutes
Table 1-1 Access Methods Metrics
Metric | Description |
---|---|
Access Method Counter Name (key column) |
Performance metric name. See Table 1-2. |
The Access Method Counter Name key column contains several metrics. Table 1-2 provides a list of these metrics and a description for each.
Table 1-2 Access Method Counter Name Metrics
Metric | Description |
---|---|
Extents Deallocations/sec |
Number of extents deallocated per second from database objects used for storing index or data records. |
Extents Allocated/sec |
Number of extents allocated per second to database objects used for storing index or data records. |
Forwarded Records/sec |
Number of records per second fetched through forwarded record pointers. |
FreeSpace Page Fetches/sec |
Number of pages returned per second by free space scans used to satisfy requests to insert record fragments. |
FreeSpace Scans/sec |
Number of scans per second that were initiated to search for free space in which to insert a new record fragment. |
Full Scans/sec |
Number of unrestricted full scans per second, which can be either base-table or full-index scans. |
Index Searches/sec |
Number of index searches per second. These are used to start range scans and single index record fetches and to reposition an index. |
Mixed Page Allocations/sec |
Number of pages allocated per second from mixed extents. These are used for storing the first eight pages that are allocated to an index or table. |
Page Deallocations/sec |
Number of pages deallocated per second from database objects used for storing index or data records. |
Page Splits/sec |
Number of page splits per second that occur because of overflowing index pages. |
Pages Allocated/sec |
Number of pages allocated per second to database objects used for storing index or data records. |
Probe Scans/sec |
Number of probe scans per second. These are used to directly find rows in an index or base table. |
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. |
Workfiles Created/sec |
Number of workfiles created per second. |
Worktables Created/sec |
Number of work tables created per second. |
Worktables from Cache Base |
Denominator ("base") of a fraction that the performance counter Worktables from Cache ratio represents. |
Worktables from Cache Ratio |
Percentage of work tables created where the initial pages were immediately available in the work table cache. |