Performance Tuning is quite interesting and Index plays a vital role in it. A proper index can improve the performance and a bad index can hamper the performance. In this blog post we will discuss about Missing Index. Here is the script from my script bank, which I use to identify missing indexes on any database.
Please note, if you should not create all the missing indexes this script suggest. This is just for guidance. You should not create more than 5-10 indexes per table. Additionally, this script sometime does not give accurate information so use your common sense.
Anyway, the scripts is a good starting point. You should pay attention to Avg_Estimated_Impact when you are going to create an index. The index creation script is also provided in the last column.
-- Missing Index Script
-- Original Author: Pinal Dave
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
If you need further help with this script, do leave a comment or send me email at my email address.
Note: It goes without saying, please test all the script on your development server and after they pass your test, deploy them on production server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
92 Comments. Leave new
what is meant by the average estimated impact
Hey Pinal, great article. Why not use avg_total_user_cost but dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact to find most impacting indexes.
Good Point. I will keep in mind in when I am building V2.
7 years, is there a v2? :) Do you know if this is (more or less) what Azure is utilizing under the covers to send index suggestions to its customers?
The script still works 100% accurate!
Yeah but you said “Good Point. I will keep in mind in when I am building V2.”, so my question was if you have incorporated these changes and if there is a v2.
And I also had another question regarding Azure “Do you know if this is (more or less) what Azure is utilizing under the covers to send index suggestions to its customers?”. Do you know something about this?
Hello Sir I want to Rename MDF file and ldf file through coding frontend can you help
through back end its possible but i want it from front end
How it is estimated?
Done by optimizer.
I ran the query on our database and it recommended creating 3 indexes on bit fields. Only 5% of my data is used and creating an index the way you suggested would waste plenty of disk space. If I was going to do that on a bit field I would create a partial index. Something you may want to modify in your blog entry as Microsoft doesn’t always make the best suggestions so you want to carefully review those results.
how can we identify missing indexes on the JSON data.
thank you
Hi, I am new to sql server database administration. We are using sql server 2008. We have a sql server database that is built by a 3rd party vendor. I feel that we have lot more indexes than needed. For example, there is one table that has 30 columns and there is one index for each column. There are lots of other tables as well where this is the scenario and this can be improved by having better index strategy.
Can you help me with a list of queries which I can use to see which indexes are not needed at all and can be removed all together and also suggest new index build based on the usage.
Thank you
Hi …
There is a small bug with this query.
The list of includes are not aggregated which causes duplicates of the indexes.
I would like to have that as well.
It doesn’t show duplicate
Thank you for this outstanding article and query! I’ve added a couple of bits to this to specify which file group I’m going to add the index to and to select a specific table or time period for inpection.
SELECT TOP 25
dm_mid.database_id AS DatabaseID, dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
‘CREATE INDEX [IXBT_’ + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ‘_’+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,”),’, ‘,’_’),'[‘,”),’]’,”)
+ CASE WHEN
dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ‘_’ ELSE ” END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,”),’, ‘,’_’),'[‘,”),’]’,”)
+ ‘]’+’ ON ‘ + dm_mid.statement + ‘ (‘ + ISNULL (dm_mid.equality_columns,”)
+ CASE WHEN
dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END
+ ISNULL (dm_mid.inequality_columns, ”)+ ‘)’
+ ISNULL (‘ INCLUDE (‘ + dm_mid.included_columns + ‘)’,”)
/* — specify tablespace here */
+’ on IXBT_LARGE;’
AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
/* — Where you want to look at a specific table — */
and OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) like ‘FINANCIAL%’
/* — Where you want to look at recommendations in the last XX minutes — */
and dm_migs.last_user_seek > DATEADD(mi,-30,GETDATE())
ORDER BY Avg_Estimated_Impact DESC
GO
Hi Pinal,
Any possibility to run this script on a shared hosting?
Thanks
Good article. Is it possible to add space required for those indexes to create. So that it will helpful to have an estimation before implement.
This is a great idea. If you can help us it will be big help.
Possible to add required space to add those estimated indexes. It will give an idea how much space required before implement.
When I ran the scrip on SQL 2016 it returned this error:
“Msg 451, Level 16, State 1, Line 10
Cannot resolve collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS_KS_WS_SC” in add operator occurring in SELECT statement column 5.”
A web search brought me to this page:
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607
Changing line 24 to this allowed it to run correctly:
+ ISNULL (‘ INCLUDE (‘ + dm_mid.included_columns + ‘)’, ”) COLLATE DATABASE_DEFAULT AS Create_Statement
My context info:
Server Collation: SQL_Latin1_General_CP1_CI_AS
Database collation: SQL_Latin1_General_CP1_CI_AS
@@Version: Microsoft SQL Server 2016 (SP1-CU10-GDR) (KB4293808) – 13.0.4522.0 (X64) Jul 17 2018 22:41:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
Hello,
Do you have a version for MariaDB?
I’ve try this one but I have multiple errors around the [ ] like [TableName]. Any help?
Thanks!
Thank you for sharing this script. I found that the top index listed as missing already exists. Why would SQL say it is missing when we already have this index?
I think I will have to investigate it more. You may send me email at pinal at sqlauthority.com
HI Pinal,
We are facing high CPU consumption issue but neither frequently nor on specific day. for this we have configured one Index rebuild job for database so whenever application performs slow we run the rebuild job then cpu came to normal and application performs fast, But now we need permanent fix for this issue, could anyone help on this issue how to fix this.
La ejecución me genera el siguiente error: como lo puedo resolver?
Msg 451, Level 16, State 1, Line 7
Cannot resolve collation conflict between “Modern_Spanish_CI_AS” and “Latin1_General_100_CI_AS_KS_WS_SC” in add operator occurring in SELECT statement column 5.
Is there a possibility to know which process, e.g. select-statement, caused the Optimizer to estimate the missing index?
I’ve added another useful peice of information to this query – row count.
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
RowCountStat.row_count as NumRows,
….