SQL SERVER – Missing Index Script – Download

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.

SQL SERVER - Missing Index Script - Download missingIndex-800x302

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)

SQL Download, SQL Index, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Plan Cache and Data Cache in Memory
Next Post
SQL SERVER – Unused Index Script – Download

Related Posts

91 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

    Reply
  • Pinal, if you are interested I have a missing index script that would be very beneficial. You can see it here.

    Reply
  • 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

    Reply
  • this is for what SELECT *FROM sys.dm_db_missing_index_details

    Reply
  • 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”?

    Reply
  • 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?

    Reply
    • 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

      Reply
  • sir, what is the possible cause of losing the tables in the database?

    -Roy

    Reply
  • 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

    Reply
    • 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.

      Reply
  • The Default trace is enabled. What is the meaning of #2 possible reasons?

    Reply
  • Very good solution to get all missing index, i have solved last 3 year issue for sql server performance.

    Reply
  • 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.

    Reply
  • What does it mean when your script suggests an index that already exists?

    Reply
  • How can we get the script that made the server create a missing index record?

    Reply
  • 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?

    Reply
    • 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

      Reply
  • 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.​

    Reply
  • 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

    Reply
  • Naveen Chandran
    May 3, 2017 10:49 am

    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

    Reply
  • Pranay Choudhary
    July 19, 2017 5:19 pm

    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.

    Reply
  • Naveen Chandran
    July 19, 2017 11:16 pm

    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)

    Reply
  • How to get index script in SQL server

    Reply

Leave a Reply