SQL SERVER – 2008 – Missing Index Script – Download

Click to Download Scripts

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.

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.

Any way, the scripts is good starting point. You should pay attention to Avg_Estimated_Impact when you are going to create index. The index creation script is also provided in the last column.

Click to Download Scripts

-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011
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,''),', ','_'),'[',''),']','') +
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
+ 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

Reference: Pinal Dave (http://blog.SQLAuthority.com)

45 thoughts on “SQL SERVER – 2008 – Missing Index Script – Download

  1. I need to prepare a report with the following feilds.
    Agent ID
    Agent name
    Login time
    Log out time
    Break In Time
    Break out Time
    Meeting In Time
    Meeting Out Time

    I need the output like Agent ID Agent name Login time Log out time Break In Time Break out Time Meeting In Time Meeting Out Time
    pls send me the source code


  2. I am storing login and logout time, break in time, break out time, meeting intime, meeting outtime in table. If a user logged in — logged out, again logged in and logged out and gone for break and for meeting too( multiple times on the same day), how can I calculate the individual time as well as total time
    I shall highly appreciate if you please help me.
    Thank you in advance for your help


  3. AgentID [47620] Agentname [xyz] Logintime [2010-12-28 16:58:56.617] Logouttime [2010-12-28 17:18:44.218] BreakInTime [2010-11-29 12:42:11.120] BreakoutTime [2010-11-29 12:42:15.320] MeetingInTime [2010-11-29 12:40:00.000] MeetingOutTime [2010-11-29 13:10:00.000]
    this is the sample data. can u do me one more favour like i need the data in both ways with (date with time) format and only time format thanx in for ur help in advance


  4. I need the report in the following format (include the feilds breakintime, breakouttime, meetin intime,meeting outtime)
    AgentId AgentName LoginTime LogOutTime
    1 xyz 5/20/2008 12:40AM 5./20/2008 1:10 AM
    2 xyz 5/20/2008 1:15 AM 5/20/2008 2:10 AM
    3 abc 5/20/2008 2:15 AM 5/20/2008 3:20 AM
    4 def 5/20/2008 3:40 AM 5/20/2008 4:00 AM


    • Check if the below query helps….

      SELECT AgentID, AgentName,
      CONVERT(VARCHAR,Logouttime – Logintime,108) as Login_Duration,
      BreakInTime, BreakoutTime,
      CONVERT(VARCHAR,BreakoutTime – BreakInTime,108) as Break_Duration,
      MeetingInTime, MeetingOutTime,
      CONVERT(VARCHAR,MeetingOutTime – MeetingInTime,108) as Meeting_Duration,
      CONVERT(VARCHAR,(Logouttime – Logintime) + (BreakoutTime – BreakInTime) + (MeetingOutTime – MeetingInTime),108)
      FROM Agent_Log


  5. But The feilds i have given are Client Requirement in the report. I dont have exact feild names in the tables. As i am very new to this SQL server. I need to create a stored procedure.


  6. The Requirement is…
    If the Agent is logged in at 9am (I need Login Time)
    If the Agent goes for break by 11am (I need Break on time)
    If the Agent has come back from break by 11:15am (I need Break Off Time)
    If the Agent goes for meeting by 1pm (I need meeting On Time)
    If the Agent Has come back from Meeting by 1:30pm (I need Meeting Out Time)
    If the Agent is Logged off by 6pm(I need Logout time)
    I need this for daily wise report


  7. Hi Guys, Suppose if the agent gets logged in, Login time should be noted, if the agent had gone for break several breaks, then break times should be noted and if the agent goes for meeting, the meeting time should be displayed and logout time.
    I need Login Time, number of Break times with time if possible(how many times the agent had gone for break)and meeting In time and meeting out time


  8. Seriously Abdul,

    Why exactly are you taking on “client requirements” in a space that you obviously know nothing about? Do you intend to tell your customers that you’re an expert in SQL Server, and then beg people who have actually invested time into their craft to do all of their work for you?



  9. I have to say, this is probably in the top 10 most useful scripts you have provided for the greater good.
    Together with a duplicated Index finding script I found, these 2 will really open up transactional performance wide open.

    You are doing some serious master work.
    Appreciated by DBA’s and Testers alike.


  10. Pingback: SQL SERVER – Query to Find Duplicate Indexes – Script to Find Redundant Indexes Journey to SQLAuthority

  11. its works good ….

    i have some below doubts .

    Problem statement.

    create index abc on table xyz

    select * from xyz with (index abc)

    when we run this statement in one transaction the index is not gets created
    but separately it do.

    i.e. when i run this in one go i get error like index specified in the from
    clause does not exist.

    when you create one SP which embeded this statement it will throw this above mentioned error.

    Your response to my question is much appreciate.



  12. Pingback: SQLAuthority News – An Year Worth Remembering and Looking Forward to Better Next Year « SQL Server Journey with SQLAuthority

  13. Pingback: SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31 « SQL Server Journey with SQL Authority

  14. Thanks so much for this, it’s been tremendous.

    One question, I keep getting the same suggested index for a foreign key on a table. Is there a reason that an item that is a foreign key would show up as needing a index when it doesn’t suggest any other columns to be included?

    CREATE INDEX [IX_foreign_table_key_id] ON [DATABASE_NAME].[dbo].[table] ([foreign_table_id])
    where foreign_table_id is a foreign key in SQL Server.


  15. Pingback: SQL SERVER – Identify Numbers of Non Clustered Index on Tables for Entire Database « SQL Server Journey with SQL Authority

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  17. hi ,
    i create the index by using this query…. but when i run the query for unused index it gives the name of newly created index in that query….
    do we need to do anything else after creating indexs…..


    • You’d probably need to rebuild the indexes on the table (if not the entire DB), so the index fields can be included in the tree structure of the database files. This is usually a scheduled job you set up to run every week or two during some low-demand timespan; exactly how often you should run something like this, and how long it will take, depends on what you’re indexing on and how often those field values change.


  18. It’s very trouble-free to find out any topic on net as compared to books, as I found this paragraph at this web site.


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



  20. 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?



  21. 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”?


  22. Hi,


    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?


  23. 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.


  24. 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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s