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)
How to get query detail or script that generate missing index record.
Currently, that is not available from SQL Server.
I have an installation of SQL Server 2008 R2 (one of a few) and it’s reasonably busy system. I’m trying to optimise some of the indexes by using information from missing indexes tables.
What seems to be strange is that sys.dm_db_missing_index_group_stats table is empty?!
awesome dave, thanks!
Hello Pinal, I have one doubt about the column Avg_Estimated_Impact. Is it a percent column? what type of info is it?
Doesn’t work. Returns error
Msg 451, Level 16, State 1, Line 9
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.
Regarding the sort order of this query. I like to refer to https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-group-stats-transact-sql?view=sql-server-ver16
stated there is
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;
Why would take the avg_total_user_cost field out of the calculation? Is this by purpose what’s your vision on this?