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
hi Sir,
I getting performance issue on database.There is max rows in 11lack only.
i check checked that there is some missing index on some column for table but same column for table is showing unused index…
Is there any way how check real performance issue…
Regards,
SQLDBA
Pinal, if you are interested I have a missing index script that would be very beneficial. You can see it here.
Hi,
I am a young DBA with 3 years experience and I am looking to improve my knowledge/skills of performance tuning SQL server? Can you please guide me to a book?
Thanks
Rory
this is for what SELECT *FROM sys.dm_db_missing_index_details
One question; is there a unit of measure for the “Avg Estimated Impact”, something where I could take the numbers to a management team and say “on average, not having this index is costing our user base an average of X seconds every time they touch this table”?
not documented by Microsoft on https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-group-stats-transact-sql?view=sql-server-2017
Hi,
SQL2008R2
The output from this changes when a backup of the log is taken. There is no bulk update going on and therefore the stats should not change too much.
Any ideas?
Never seen that earlier. Do you have simple repro? I don’t think there is an easy way to clean the DMVs used by my query unless database/SQL is restarted
sir, what is the possible cause of losing the tables in the database?
-Roy
I’ve check the schema changes history of my database and all tables of my database are dropped but i never undergone drop table. all of the sudden all tables are gone
That report picks data from Default trace. There would be two possible reasons of not seeing data.
1. Default trace is disabled.
2. Drop was old enough and not covered in 5 trace files.
You can open default trace manually and check the first event.
The Default trace is enabled. What is the meaning of #2 possible reasons?
Very good solution to get all missing index, i have solved last 3 year issue for sql server performance.
I added the DROP INDEX statement and the REBUILD STATEMENT by altering your script and that ensures I have a backout and get the indexes rebuilt after creating new ones.
What does it mean when your script suggests an index that already exists?
How can we get the script that made the server create a missing index record?
Hi Pinal,
When I run the script, I see some significantly high numbers in the Avg_Estimated_Impact column. 14552564.3 for example.
What does the Avg_Estimate_Impact number represent? Is it a percentage increase, a number of seconds decrease, or what?
Hi Cedrick,
I think it doesn’t have a unit per say, it’s just to give the sensation of how much improvement that index would make. I’ve also seen the following: migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
SELECT CONVERT (varchar, getdate(), 126) AS runtime,
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
‘CREATE INDEX missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)
+ ‘ 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 CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (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
Hi,
I need to merge each 2 rows form table.
i.e.. Table name is ‘Attendance’
AttId Date Time EmpId
1520 21/09/2016 09.00.00 ITE0025
1525 21/09/2016 09.30.00 ITE0025
1530 21/09/2016 10.25.00 ITE0025
1536 21/09/2016 13.15.00 ITE0025
1542 21/09/2016 15.30.00 ITE0025
..
..
Now i need the Output Is Like
Sl.No Date Time In Time Out
1 21/09/2016 09.00.00 09.30.00
2 21/09/2016 10.25.00 13.15.00
3 21/09/2016 15.30.00
.
.
​Help to Fix. Thanks in Advance. Please Do the Needful.​
Excellent script Dave!
But I Have a problem.
Some indexes are suggested to some errors in the join.
you can retrieve such queries requires these indexes?
Best Regards
Marco
Hi Dave,
I have a script to find missing index, could you please review those as well. for the same server, this script shows nothing where as other script shows some missing indexes. I really like to know more about this since I have to suggest my clients on creating missing indexes based on the report.
Regards,
Naveen Chandran
Thank you for this wonderful blog. However I have one question which I was not able to find an answer in other forums:
The Missing Index Script suggests me to create two indexes:
CREATE INDEX IX_indexname1
ON table1 (column1)
INCLUDE (column_x, column_y)
CREATE INDEX IX_indexname2
ON table1(column1, column2)
INCLUDE (column_y, column_z)
IX_indexname2 already includes column1 and column2. Should I create both the indexes or just seconds one is sufficient? I believe creating a single index on (column1, column2) including (column_x, column_y,column_z) should be sufficient. This is what my “common sense” says. So the only index that i’m using is:
CREATE INDEX IX_indexname3
ON table1(column1, column2)
INCLUDE (column_x, column_y, column_z)
Will this single index be sufficient to handle the two suggested indexes? Please correct me if I’m wrong.
Hi Pranay,
I believe you are right, the combination of first two will work as you mentioned as third one
CREATE INDEX IX_indexname3
ON table1(column1, column2)
INCLUDE (column_x, column_y, column_z)
How to get index script in SQL server