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