Question

You administer an Azure SQL Database supporting an OLTP application. Users report that several reporting queries have become slow. You want to identify which missing index would provide the greatest overall performance benefit before making any changes, using built-in dynamic management views. Which combination of DMVs should you query, and how should you prioritize the results?

Accepted Answer

Query sys.dm_db_missing_index_details joined with sys.dm_db_missing_index_group_stats, and prioritize by the product of avg_total_user_cost, avg_user_impact, and (user_seeks + user_scans).

Answer

Query sys.dm_db_index_usage_stats and prioritize indexes with the highest user_updates value.

Answer

Query sys.dm_db_index_physical_stats and prioritize indexes with the highest avg_fragmentation_in_percent.

Answer

Query sys.dm_exec_query_stats and prioritize by total_worker_time only.