Missing Indexes DMV
-
Posted on June 17, 2009 by Derek Dieter
-
1
This query will show the indexes that are missing ordered according to those having the most impact. It will also provide the create index script needed in order to help you create the index.
[cc lang=”sql”]
SELECT
mid.statement
,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
‘CREATE INDEX [missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)
+ ‘_’ + LEFT (PARSENAME(mid.statement, 1), 32) + ‘]’
+ ‘ ON ‘ + mid.statement
+ ‘ (‘ + ISNULL (mid.equality_columns,”)
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END
+ ISNULL (mid.inequality_columns, ”)
+ ‘)’
+ ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
[/cc]
What you need to know about this script however is what it does not account for. It does not account for an index that should be clustered. One of the warning signs that an index should be clustered is when this query suggests to you an index that contains a lot of columns (or has a lot of include columns). It is suggesting that, because it does not want to do a bookmark lookup to get the columns it needs for the select list. In those cases, you want to compare the number of user_seeks this query tells you, with the number that is shown in the Index Usage DMV. If this query’s seeks are higher, then consider making this the clustered index.
Also, once the queries are created, you can check to see if your assumption was correct by using the Clustered Index Checker DMV Script.
The original author’s article of this popular query (Bart Duncan) can be found here
Don’t stop now — Learn everything about indexes and indexing with a downloadable tutorial
Only $49
Only $49
We will go over subjects
clustered vs nonclustered
bookmark lookups
cardinality
include statement
how to idenfity if an index is needed
how to identify missing indexes
how to apply an index to your newly written queries
poor indexes that cause deadlocks
..and much more
I’m confident after this you will know all the concepts there is to know about indexing.
For the price below, the video will be yours to download and keep.
If you’re not satisfied, I’ll give you a full refund.
Only $49
- Comments (RSS)
- Trackback
- Permalink
Thanks for this…using it right now!
my stored proecedure run 4 min off peak time and 30 mins in peak time, i am not able to find the cause for 30 mins duration.Please tell me the various ways to find the bottleneck cause solution.
we has transaction replication setup on the same server.
Hi Praful,
What sounds like is happening, is during peak time the data returned for the query is not in memory and needs to be retrieved from disk. It definitely sounds like you are missing indexes or possibly using table variables. You should get the execution plan for the query and see if there are any indexes missing.
Derek
Why do the these queries that are supposed to show indexes that the system feels are needed often contain indexes that already exist?
Hi Curt,
This is because a lot of times, the indexes are suggested in order to avoid a “bookmark lookup”. In other words, the index used to find the fields does exist, however it needs to go back to the clustered index (or heap) in order to get the required columns. The query optimizer does not like this scenario, so it will often suggest new indexes that contain “include” columns. These suggestions can be misleading. The other problem with the suggestions is that it does not take into account the fact that you may need to change your clustered index.
With all that said, the index suggestions are still very useful and something I find myself considering a lot. Much better than the suggestions in SQL 2000..
Thanks Derek!
Verma – Can you give me more details and what etlxcay do you mean with disabling web hosting? A web hosting is a type of Internet hosting service that allows making public websites. If you want to make your web content inaccessible, just change read permissions on document root. In that moment, web server will not be able to read any file and web site will be closed.
Thanks for the script. Makes using the DMVs much faster.
[…] CPU – (Historically Medium/High) For a CPU that has historically been high, the most common issue is a lack of indexes. This is an easy fix with the new DMVs introduced in SQL Server 2005. Usually adding indexes for the top 5 offending queries will resolve this issue. Follow this to Find Missing Indexes. […]
