You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application.

You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.

What should you do?

A. Query the sys.dm_db_index_usage_stats DMV.

B. Query the sys.dm_db_missing_index_details DMV.

C. Use the Database Engine Tuning Advisor.

D. Query the sys.dm_db_missing_index_columns DMV.

Explanation:

The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server.

Incorrect Answers:

A: sys.dm_db_index_usage_stats returns counts of different

types of index operations and the time each type of operation was last performed in SQL Server.

B: sys.dm_db_missing_index_details returns detailed information about missing indexes, excluding spatial indexes.

D: sys.dm_db_missing_index_columns returns in formation about database table columns that are missing an index, excluding spatial indexes.

References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/database-engine-tuning-advisor