A Look at Missing Indexes

Tim Ford (@SQLAgentMan) recently blogged about his Top 5 SQL Server Indexing Best Practices. I thought it was a good list, and it inspired this blog post. I’ve recently been doing a little index spring cleaning, and I thought some people may be interested in the process I go through. So, here it is… a journey through madness an overview of my general missing index process.

I start with my trusty dba_missingIndexStoredProc table. If this table sounds completely foreign to you, check out my post, Find Missing Indexes In Stored Procs. Basically, I have a process that runs every night, scanning the XML of every query plan on the server to find procs that are possibly missing indexes. I then log the details for later action.

So I take a look at my table, and I find 8 stored procedures that are possibly missing indexes. Clicking on the XML link will show me the logged query plan:

Procs With Missing Indexes

Procs With Missing Indexes

Right clicking on the “Missing Index” description will give me the details of the recommended index:

Missing Index Details

Missing Index Details

Here’s an example of what SQL Server will return for you:

Missing Index Details from ExecutionPlan2.sqlplan
The Query Processor estimates that implementing the following index 
could improve the query cost by 85.7327%.
USE [msdb]
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[sysjobhistory] ([job_id])
INCLUDE ([instance_id],[step_id],[sql_message_id],[sql_severity],

I now compare the details of this proposed index to the missing index DMV suggestions, using this query:

SELECT t.name AS 'affected_table'
    , 'Create NonClustered Index IX_' + t.name + '_missing_' 
        + CAST(ddmid.index_handle AS VARCHAR(10))
        + ' On ' + ddmid.STATEMENT 
        + ' (' + IsNull(ddmid.equality_columns,'') 
        + CASE WHEN ddmid.equality_columns IS Not Null 
            And ddmid.inequality_columns IS Not Null THEN ',' 
                ELSE '' END 
        + IsNull(ddmid.inequality_columns, '')
        + ')' 
        + IsNull(' Include (' + ddmid.included_columns + ');', ';'
        ) AS sql_statement
    , ddmigs.user_seeks
    , ddmigs.user_scans
    , CAST((ddmigs.user_seeks + ddmigs.user_scans) 
        * ddmigs.avg_user_impact AS INT) AS 'est_impact'
    , ddmigs.last_user_seek
FROM sys.dm_db_missing_index_groups AS ddmig
INNER JOIN sys.dm_db_missing_index_group_stats AS ddmigs
    ON ddmigs.group_handle = ddmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS ddmid 
    ON ddmig.index_handle = ddmid.index_handle
INNER JOIN sys.tables AS t
    ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE ddmid.database_id = DB_ID()
    --AND t.name = 'myTableName' 
ORDER BY CAST((ddmigs.user_seeks + ddmigs.user_scans) 
    * ddmigs.avg_user_impact AS INT) DESC;

I usually find the data in both places, but not always. One reason why is because the missing index DMV will only store data since your last reboot. So if I’m taking a look at this DMV on Monday and I just rebooted on Sunday, I may not have enough history to give me meaningful recommendations. This is just something to be aware of.

What I’m looking for in this DMV is the number of user_seeks and the est_impact. Also, if I haven’t rebooted my server in a while, I take a look at last_user_seek so I can determine whether or not it’s still accurate.

Next, I take a look at my existing indexes using Kimberly Tripp’s sp_helpindex2 system stored proc. I use her proc instead of sp_helpindex because I need to see included columns.

If you’re wondering why I’m looking at existing indexes, the reason is because I’m looking for indexes that can be modified slightly to accommodate my missing index needs. By “modified slightly,” I mean that I’d only want to make a change to an existing index if it did not drastically change the size or composition of an index, i.e. adding one or two narrow columns as included columns. I do NOT mean making changes that double the size of your index; in those cases, you’d probably be better off creating a brand new index.

Looking at existing indexes is actually a pretty critical part of the puzzle. If I have a proc that only gets called a few times an hour and could benefit from a better index, I may not create that index if it means adding a wide, expensive index to a busy table. If I can make a small modification to an existing index, then there’s a greater chance I’ll make the change and cover my query.

At this point, I should have enough information to start making decisions. I was going to write out the path I normally take when making decisions, but I thought, “Hey! What a great time for a diagram.” So here you go:

Decision Path

Decision Path

Disclaimer: I’m *not* a Visio wizard, so if I butchered the use of certain symbols in my diagram, please let me know so I can a) fix it, and b) learn from it!

It’s hard to really put all of the decision paths into a single, small diagram like this. There’s a lot of variables that I’m not even touching here. But I think this is a fairly good “generic” representation of the path I take. When I hit an “end” process, it means I don’t create the missing index at this time. Maybe in the future, it’ll become necessary, but I prefer to err on the side of less indexes.

So there you have it, a brief look at my missing index process. Hopefully someone finds it helpful. 🙂

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , . Bookmark the permalink.

18 Responses to A Look at Missing Indexes

  1. Pingback: Alexander Gladchenko

  2. Pingback: Log Buffer #144: a Carnival of the Vanities for DBAs | Pythian Group Blog

  3. Jim Peabbles says:

    Clicking the XML link only shows me the full XML. If I save this into a .sqlplan, management studio will display the graphical view, but I don’t have a right click -> missing index details option. Is this available for 2005 or are you referencing 2008?

  4. Jim Peabbles says:

    Can you quantify “low,medium,high and expensive” from your diagram or since they would differ on each database, maybe explain what you would do to define these initial values?

  5. @Jim you must be using SSMS 2008 to get the missing index options. This works even if you’re connecting to a SQL Server 2005 instance.

    As for the diagram, it’s hard to quantify the “estimated impact” of an index because every system and every environment is different. In my world, creating a _narrow_ index on a table with less than 10mm records is “low,” 10mm to 100mm is “medium,” and > 100mm starts to get expensive. Everything changes when the index starts to get wide or when the table is very busy. So to borrow a line from Paul Randal, “it depends.” 🙂

  6. joe tigeleiro says:

    SSMS 2008 – missing index detail. What is the code behing this option. If i have captured query plans from cache that contain this info, I want to generate the create index statement just like you would when you click on the plan

  7. @Joe I’m not sure what the code is that supports the SSMS feature. There’s some information in MSDB: http://msdn.microsoft.com/en-us/library/ms345524.aspx

    If you want to take a code-based approach, you can use the sys.dm_db_missing_index_details DMV to return similar information. I have a script that you can use in another blog post, http://sqlfool.com/2009/01/index-clean-up-scripts/.

  8. Aaron says:

    Hi Michelle,

    Tried to run you routine in our environment and received this error. Any idea what the cause would be?

    Msg 102, Level 15, State 1, Procedure dba_missingIndexStoredProc_sp, Line 118
    Incorrect syntax near ‘.’.

  9. subash says:

    Hi Michelle,
    I am using SSMS 2008 (Standard Edition), and I do not see “Missing Index Option…”. Does it require different edition of sql server?

  10. Pingback: The 5 Database Design Process Failures You Don’t Want To Make | SQLRockstar | Thomas LaRock

  11. Pingback: The 5 database design process failures you dont want to make | Tek's free zone

  12. Pingback: Database Maintenance Essentials – Resources « Voice of the DBA

  13. Pingback: Indexes: just because you can, doesn’t mean you should! | Kimberly L. Tripp

  14. Harry Liu says:

    I want to thank you Michelle for this piece of code because it helped me find the missing indexes and fix them. That made my job lot easier! I am a SharePoint developer and working on a project which uses Integrated Mode reporting service … and it took long time to process the hundreds of the reports. Now only one fifth time is needed. Thank you so much!!! Wish to read more great blogs from you!
    Thanks again.

  15. Pingback: The Accidental DBA (Day 20 of 30): Are your indexing strategies working? (aka Indexing DMVs) - Kimberly L. Tripp

  16. Pingback: The Accidental DBA (Day 20 of 30): Are your indexing strategies working? (aka Indexing DMVs) - Kimberly L. Tripp

  17. Pingback: Less Than Dot - Blog - Awesome

  18. Pingback: Indexes: just because you can, doesn’t mean you should! – Kimberly L. Tripp | SealYu

Leave a Reply

Your email address will not be published. Required fields are marked *