Related Query Tuning Features
The following SQL Server query tuning features can be used with the missing indexes feature:
sys.dm_db_index_usage_stats and sys.dm_exec_query_stats dynamic management views
Database Engine Tuning Advisor
sys.dm_db_index_usage_stats collects index usage information for existing indexes on an instance of SQL Server. sys.dm_exec_query_stats returns aggregated performance statistics for cached query plans. It captures information such as elapsed time for execution of the cached plan, and the number of logical and physical reads performed when a cached plan executes.
Database Engine Tuning Advisor is a stand-alone tool that analyzes an entire SQL Server workload and produces recommendations for configurations of physical design structures, such as indexes, indexed views, or partitioning.
The following section provides a detailed comparison of Database Engine Tuning Advisor with the missing indexes feature.
Missing Index Feature vs. Database Engine Tuning Advisor
Missing indexes is a lightweight, server-side, always-on feature for catching and correcting indexing mistakes. In contrast, Database Engine Tuning Advisor is a comprehensive tool that assesses the physical database design and recommends new physical design structures for performance improvement. Database Engine Tuning Advisor considers not only indexes, but also considers whether indexed views or partitioning should be used to improve query performance.
The following table compares the missing indexes feature and Database Engine Tuning Advisor in greater detail:
Comparison point |
Missing indexes feature |
Database Engine Tuning Advisor |
|---|---|---|
How deployed? |
Server side |
Client-side, stand-alone application. |
Availability? |
Always on |
Started by a database administrator or invoked in a script. |
Scope of analysis? |
Quick, ad hoc analysis, providing limited information about missing indexes only. |
Thorough workload analysis, providing full recommendation report about the best physical database design configuration in the context of a submitted workload. |
UPDATE, INSERT, and DELETE statements factored into analysis? |
No |
Yes |
Available disk storage space factored into analysis? |
No |
Yes |
Columns ordered in recommended index? |
No, you must manually order the columns in the CREATE INDEX statement. |
Automatically determines the column order in recommended indexes based on query execution cost. |
Recommends clustered indexes? |
No |
Yes |
Recommends covering indexes? |
Yes |
Yes |
Recommends non-covering indexes? |
Yes |
Yes |
Recommends indexed views? |
No |
Yes |
Recommends partitioning? |
No |
Yes |
Basis of recommendations? |
Approximate query execution costs are estimated by the query optimizer. |
Query execution costs are estimated by the query optimizer. |
What is the impact of implementing recommendations? |
Approximate impact of adding a missing index is reported. For more information, see sys.dm_db_missing_index_group_stats (Transact-SQL). |
Fifteen different analysis reports are generated, which provide information about the impact of implementing recommendations. For more information, see Choosing a Database Engine Tuning Advisor Report. |
If identified missing indexes are implemented, query performance might improve. You can use the Database Engine Tuning Advisor user-specified configuration feature and the evaluate mode to determine the impact of implementing missing indexes on an entire workload. For more information, see Exploratory Analysis Using Database Engine Tuning Advisor.
Even for a single-query workload, Database Engine Tuning Advisor and the missing indexes feature can return different recommendations. This occurs because the key columns for indexes that the missing indexes feature recommends are not order-sensitive. On the other hand, Database Engine Tuning Advisor recommendations include ordering of the key columns for indexes to optimize query performance.
Summary
The missing indexes feature can be used to catch and correct indexing errors, whereas, Database Engine Tuning Advisor can be used to correct indexing errors and to tune the workload running on a server as a whole. You can use the missing indexes feature to generate candidate indexes, but should validate them using Database Engine Tuning Advisor.