Having an effective Database Indexing Strategy is a key to your application performance. When we think about optimizing SQL query performance, generally the first thing which comes to our mind are ‘Indexes’. And rightly so, because Indexes tremendously help in fetching your data faster from SQL Server.
Do you have slow running queries as a result of missing indexes in your tables?
I consider running the Missing Index DMV as an easy win. It is generally a helpful indicator about the indexes which are required by the SQL Server Engine to process the workload faster.
Dynamic management views (DMVs) return server state information and can be used to monitor the health of your database server instance and diagnose problems. It gives great insight about what is going on inside SQL Server.
You can identify missing indexes in your SQL queries by 3 ways primarily —
- Running the Database Engine Tuning Advisor
- Executing Missing Index Dynamic Management Views
- SQL Server Engine prompts missing indexes when you generate Execution Plans in SSMS
There are 3 primary DMVs which provide information about the Missing Indexes in SQL Server —
|–Returns detailed information about missing indexes|
|SELECT * FROM sys.dm_db_missing_index_details|
|–Returns information about what missing indexes are contained in a specific missing index group|
|SELECT * FROM sys.dm_db_missing_index_groups|
|–Returns summary information about groups of missing indexes|
|SELECT * FROM sys.dm_db_missing_index_group_stats|
Permission required to run the Missing Index DMV
You need to have VIEW SERVER STATE permission to be able to execute DMVs.
Query to identify Missing Indexes
|SELECT DISTINCT CONVERT(DECIMAL(18, 2) , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) AS [index_advantage] ,|
|mid.[statement] AS [Database.Schema.Table] ,|
|OBJECT_NAME(mid.[object_id]) AS [Table Name] ,|
|p.rows AS [Table Rows]|
|FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )|
|INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle|
|INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle|
|INNER JOIN sys.partitions AS p WITH ( NOLOCK ) ON p.[object_id] = mid.[object_id]|
|WHERE mid.database_id = DB_ID()|
|ORDER BY index_advantage DESC|
|OPTION ( RECOMPILE );|
The above query has worked great for me. Execute the query against your database and any time the Index Advantage for a particular index is above 1000, start evaluating whether the particular index will be beneficial or not.
Word of Caution
Remember that like other DMVs, the information stored by the Missing Index DMV gets deleted when the SQL Server is restarted.
Do not blindly create new Indexes in your database based on the results of the Missing Index DMV or the suggestion from Database Engine Tuning Advisor.
The line is a thin one – you need enough indexes to speed up your SELECT queries and at the same time you should not create redundant indexes which could potentially slow down your UPDATE/DELETE operations.
Always keep in mind the flip side of the coin — Having redundant indexes can decrease the performance of your data manipulation queries – INSERT/UPDATE/DELETE. And not to forget the additional disk space that is needed to store them.