Which dynamic management object should you use?

You have a database named Saleshistory that records sales transactions for your organization. You create indexes in the database. The database has grown over time and now contains hundreds of indexes. You need to identify the indexes that are not being used.

Which dynamic management object should you use?
A . sys.dm_os_sys_info
B . sys.dm_dbjndex_operational_stats
C . sys.dm_db_index_usage_stats
D . sys.dm_db_stats_properties

View Answer

Answer: C

Explanation:

The sys.dm_db_index_usage_stats returns counts of different types of index operations and the time each type of operation was last performed.

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic­management-views/sys-dm-db-index-usage-stats-transact-sql