YOU’RE RESPONSIBLE FOR SQL SERVER PERFORMANCE AND ONE OF THE DEVEL...

14. You’re responsible for SQL Server performance and one of the developers

reports INSERTS are particularly slow. You suspect that a previous developer

has created some redundant nonclustered indexes and these are hindering

INSERT performance. How can you tell if an index is used?

A. Sys.dm_db_index_usage_stats

B. Sys.dm_db_index_request_count

C. Sys.dm_redundant_indexes

D. Sys.dm_db_missing_index_details

Answer A is correct; index usage stats includes a count of user seeks and user

scans, which can be useful to determine whether the optimizer has used an

index since last restart. Options B and C are invalid DMVs. Answer D shows

suggestions for new indexes to create, does not include details on usefulness of

existing indexes.