One of the really cool things that SQL Server does is keep up with index statistics behind the scenes. It knows how an index has been used, how often it is used, or if it is even being used at all. But SQL Server doesn’t just monitor the indexes that you currently have, it even keeps up with statistics for indexes that it wishes you had!
What Are Missing Indexes?
When SQL Server is processing a query, it will sometimes make a suggestion for an index that it believes will help that query run faster. These are known as Missing Indexes, or as I like to refer to them, Missing Index Suggestions. The reason that I stress “suggestions” is that while SQL Server is very good at determining when an index is needed, it sometimes isn’t so good at recommending what that index should be.
What Is Wrong With The Missing Index Recommendations?
When SQL Server recommends a missing index, it does so based on the execution of that query at that specific point in time. It doesn’t take into consideration any other factors such as current server load, or any existing indexes, so you should consider these index recommendations as just that, recommendations. Do not just blindly create whatever indexes SQL Server is recommending.
How Do I Find Missing Index Recommendations?
The below query will show missing index suggestions for the specified database. It pulls information from the sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_details DMVs. You can also narrow it down to a specified table by un-commenting the AND statement and specifying the table name. This script also generates a CREATE INDEX script for each record, so that you can take a better look at the index and decide if you want to use it. I don’t remember where I initially got this script, I think it may be based off of Glenn Berry’s Diagnostic Script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
USE YourDatabase GO SELECT db.[name] AS [DatabaseName] ,id.[object_id] AS [ObjectID] ,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName] ,id.[statement] AS [FullyQualifiedObjectName] ,id.[equality_columns] AS [EqualityColumns] ,id.[inequality_columns] AS [InEqualityColumns] ,id.[included_columns] AS [IncludedColumns] ,gs.[unique_compiles] AS [UniqueCompiles] ,gs.[user_seeks] AS [UserSeeks] ,gs.[user_scans] AS [UserScans] ,gs.[last_user_seek] AS [LastUserSeekTime] ,gs.[last_user_scan] AS [LastUserScanTime] ,gs.[avg_total_user_cost] AS [AvgTotalUserCost] -- Average cost of the user queries that could be reduced by the index in the group. ,gs.[avg_user_impact] AS [AvgUserImpact] -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented. ,gs.[system_seeks] AS [SystemSeeks] ,gs.[system_scans] AS [SystemScans] ,gs.[last_system_seek] AS [LastSystemSeekTime] ,gs.[last_system_scan] AS [LastSystemScanTime] ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost] ,gs.[avg_system_impact] AS [AvgSystemImpact] -- Average percentage benefit that system queries could experience if this missing index group was implemented. ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage] ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN ',' ELSE '' END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex] ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate] FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK) INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle] INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle] INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id] WHERE db.[database_id] = DB_ID() --AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName' ORDER BY ObjectName, [IndexAdvantage] DESC OPTION (RECOMPILE); |
It is not uncommon to see many similar recommendations, usually with the same columns that vary slightly in order or between an include statement. This goes back to what I said earlier about SQL Server only considering indexes for a query at that specific point in time. Seeing multiple recommendations for the same index that only vary slightly is a good indicator that multiple queries could benefit from an index.
It’s important to mention that these Missing Index statistics get reset every time SQL Server is restarted, so if you’ve recently rebooted the server you may not have many index stats accumulated yet.
Also, if you’re interested in code that will Persist And Aggregate Index Statistics for your server, take a look at my SQL Server Metrics Pack project that is available on GitHub.
EDIT – 8/27/2019 – I have also added a new post on how you can Find The Queries That Generated The Missing Index Requests.
I’ve got very large tables and somehow the proposed index column is truncated after 4000 chars. Is there a way to fix that?
Hey George! All that does is create an index statement based off of the values returned in “EqualityColumns”, “InEqualityColumns” and “IncludedColumns”, so you should be able to take those values and create your own statement.
However, I would point out that this sounds like a really big index, and I would be cautious about creating something like that. It’s really going to depend on your specific table design, but it sounds like you either have some really long column names, or SQL Server is suggesting sticking everything and the kitchen sink into this index. Remember, SQL Server is very good at determining when an index is needed, but it sometimes isn’t so good at recommending what that index should be.
Hope this helps!
This code is amazing to find missing indexes
Thanks!
Hi Eric
Just used your code
Ran perfectly and I have a rake of suggestions.
I am new to SQL and we are getting very little help from our vendor with there ERP software so its pretty much down to me.
I was wondering if you could give some general advice on how to determine from the suggested indexes script which ones should be looked at
Which of the columns should I be looking at , user seeks,the average total user cost , the average user impact or the index advantage?
In particular one suggestion has an index advantage of 317011.7788 and has an average user impacts of 99.84 and a user seek of 43002 – would this general be a useful index to have ?
Thanks Sean, I’m glad my scripts helped! As for deciphering the results, that’s going to depend a lot on your specific system. I typically take notice of any results that have a “high” number for user seeks, user impact, or index advantage. For example, an index advantage of 317011 is certainly one that I would look into. However, everyone has a different definition of what is determined as “high”, depending on their specific situation. For example, 43000 index seeks could be high, depending on how long the SQL Server has been gathering these statistics (since the last restart). Is that 43000 seeks in the last 24 hours? Last month? 18 months?
It sounds like you’ve got some good candidates for useful indexes, I would just recommend a little investigation and testing to see. I would also recommend checking out my Index Metrics scripts, to show you whether or not an index is being used after you add it to your database.
I ran the ‘missing index recommendation‘ script yesterday and took top recommendation and created an index in my DB.
Now today when I am running the ‘missing index recommendation‘ script on the same DB, it is recommending me the same index [column combinations] to create again on the same DB on which I created that index yesterday.
When does the system DMVs are expected to be refreshed in order to not recommend the same index again which I already created.
[sys].[dm_db_missing_index_group_stats]
[sys].[dm_db_missing_index_groups]
[sys].[dm_db_missing_index_details]
Hi Sachin. The system DMVs are cleared out whenever the SQL Server services are restarted.
Pingback: SQL Scripts: Find Queries That Have Missing Index Requests - SQL Nuggets
Hi Eric,
Have you certified your SQL Server Metrics Pack on SQL Server 2017 or SQL Server 2019?
Not yet, but if you’d like to give it a try I’d love to hear your findings! 🙂
Dear,
i’m looking for missing indexes on my databases, can i lunch this script without making any modification to my databases or Indexes?
Thanks!
Yes, that query uses SQL Server’s internal “sys” views, so as long as you have permissions to access them you can run that query without having to make any modifications to your databases or indexes.
This Query is an absolute Gem. Me and my developers have used this and slashed the Usage on our Servers by half. God Send in finding where to index. Cant than you enough.
Thanks Lee, that’s great to hear!
This code is good, thank you for share.
Pingback: The Power Of Proper Indexing - SQL Nuggets
Thanks for the excellent query! Helped me out a lot.