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

  • what is meant by the average estimated impact

    Reply
  • Subhendu Swain
    November 1, 2017 11:17 am

    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.

    Reply
    • Good Point. I will keep in mind in when I am building V2.

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

  • Kshitij Bhatia
    March 27, 2018 6:29 pm

    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

    Reply
  • Kanika Sarkar
    April 3, 2018 10:05 pm

    How it is estimated?

    Reply
  • Wayne Hamberg
    May 18, 2018 3:33 am

    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.

    Reply
  • N M Reddy Arimanda
    May 19, 2018 11:09 am

    how can we identify missing indexes on the JSON data.

    thank you

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

    Reply
  • Hi …

    There is a small bug with this query.
    The list of includes are not aggregated which causes duplicates of the indexes.

    Reply
  • Russell Cannon
    January 25, 2019 4:35 am

    I would like to have that as well.

    Reply
  • John Schenck
    March 5, 2019 12:28 am

    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

    Reply
  • Hi Pinal,
    Any possibility to run this script on a shared hosting?
    Thanks

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

    Reply
  • Possible to add required space to add those estimated indexes. It will give an idea how much space required before implement.

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

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

    Reply
  • Caroline Wise
    January 3, 2020 10:35 pm

    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?

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

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

    Reply
  • Is there a possibility to know which process, e.g. select-statement, caused the Optimizer to estimate the missing index?

    Reply
  • John Schenck
    June 24, 2020 6:59 pm

    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,
    ….

    Reply

Leave a ReplyCancel reply

Exit mobile version