The Access Methods object in Microsoft® SQL Server™ provides counters to monitor how the logical pages within the database are accessed. Physical access to the database pages on disk is monitored using the Buffer Manager counters. Monitoring the methods used to access database pages can help you to determine if query performance can be improved by adding or modifying 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 (excessive fragmentation can impair performance).
These are the SQL Server Access Methods counters.
| SQL Server Access Methods counters | Description |
|---|---|
| Extent 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. These 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 as the result 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 find rows 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. |
| Workfiles Created/sec | Number of work files created per second. |
| Worktables Created/sec | Number of work tables created per second. |
| Worktables From Cache Ratio | Percentage of work tables created where the initial pages were immediately available in the work table cache. |
| Pages and Extents | Indexes |