You have an Azure SQL database named DB1 that includes a nonclustered index named index1.
End users report slow queries when using index1.
You need to identify the operations being performed on the index.
Which dynamic management view should you use?
To identify the actual operations being performed on an index, use sys.dm_db_index_operational_stats, which returns current low-level I/O, locking, latching, and access-method activity per index (for example range_scan_count, singleton_lookup_count, leaf/nonleaf insert/update/delete counts, and page-latch/lock waits). This is what reveals contention and access patterns behind slow queries on index1. sys.dm_db_index_usage_stats (choice D, also misspelled) only reports how many query plans used the index (seeks/scans/lookups/updates counts) rather than the operations and waits occurring on it, and physical_stats reports fragmentation. Hence the operational_stats DMV is correct.
Community Discussion