Blog

It’s incredibly easy to make big mistakes with indexes in SQL Server. In this free 30 minute session, Kendra Little will share the three most common mistakes she finds people make with their indexing. You’ll learn how these mistakes can have disastrous impacts and practical steps you can take right away to identify if these problems exist in your environment.

Hungry for More Indexes?

Check out sp_BlitzIndex®, our free index tool that helps you diagnose index insanity.

Or take a tour of our new 6.5 hour video training on SQL Server index tuning here.

↑ Back to top
  1. Good video, but I feel the internet already has enough comprehensive indexing blog posts and videos. I think your excellent skills would be better suited for explaining something new.

    • Hey Tobi,

      Thanks for the kind words. I never personally get tired of learning more about indexes and I find that it’s one of the biggest ways I can help my clients and students– so for me the topic never gets old.

      Kendra

  2. Yeah, but you can trust the information from Brent Ozar Unlimited. sp_Blitz is a one of the main tools in my DBA toolbox. : )

    Looking forward to using sp_BlitzIndex! Thanks, you guys!

  3. Excellent material Kendra, thank you. Is there any chance you could improve audio quality? It sounds like an international call, perhaps a (USB) headset would be possible?

  4. One other thing, can’t seem to find the “freemaintenance” page and haven’t been able to attend the actual webinar. Could you repost the exact link here as well? Thanks!

  5. Kendra, thank you for making all this available. I’d also like to compliment you on your presentation skills. You speak of these things as comfortably as if you were born knowing them.

    Also, would you mind sharing the names of the tools that you use to produce these presentations? I do a lot of training myself (currently 100% in a live environment) and my company would like me to explore some different methodologies for making my materials available.

  6. Great Video once again from you all. Thanks very much. I have been using sp_blitzindex for over six months now and I find this tool really useful. i run it against the whole database and on our highest used tables by using the parameter every month before we restart to install the patches and save the results. Using these results I managed to identify 340 identical indexes and 234 indexes with zero reads. One question I do have is that if I have two recommended missing indexes on a table , the first recommended index is on columns A, B,C and D. The second recommended index is on Columns A, B and C. I thought if I created the first index on columns A,B,C and D that this would meet both requirements, but it still recommends that there is a missing index for columns A,B and C. why wouldn’t the queries on these columns not use the “covering” index created?

    • I think Kendra mentioned that sp_blitzindex doesn’t merge missing index requests. What you see is probably two types of queries, with one benefiting from ABC index alone, the other one requesting ABCD. As you pointed out, introducing ABCD would work in both cases and would follow the strategy of introducing small changes, so running sp_blitzindex after ABCD is created would also no longer show the need for ABC one.

      • That is the problem though after I have created the index for ABCD and it still requests the need for ABC.

        • What version of SQL Server are you running? I have run into several cases where a missing index request is thrown when an index already exists. Here’s a link to a connect item that I filed for it back in 2009: http://connect.microsoft.com/SQLServer/feedback/details/400578/query-plan-missing-index-recommendation-doesnt-check-if-an-index-actually-exists#details

          I haven’t run into this in SQL Server 2012 yet (where it’s been reported as fixed), so if you’re running into it there I’d be especially interested.

          Edit: I should also say that the order of columns represented in the missing index DMV “doesn’t matter” (it’s not asking for key columns in a specific order, it’s just listing them out)– if the query is important and speeding it up is critical, since this is a multi-column index I would consider index order. Of course since you already have a similar/different order index then you need to make sure you’re not adding to many, etc. Hope this helps!

          • Thanks for your answer. We are running SQL Server 2005 so that would explain it. Glad to hear that even Microsoft can make mistakes, thought I might have been missing something. Combining Indexes is something that there is not much information on and this was my first attempt so I got worried when one of the indexes was still showing as missing. Thanks again.

  7. Great video Kendra! Always love watching the vids on this site. In your presentation part of weird index facts (key columns sorted, include columns not)… it got me thinking. If i have 2 indexes with the same key column and the same included columns BUT the included columns were in a different order (i.e. IndexA on ColumnA INCLUDE (B,C,D) and IndexB on ColumnA INCLUDE (D,B,C)..

    Would you consider those duplicate indexes? Thanks!

    • Hey Mark!

      Thanks so much, glad we can be helpful!

      Yep, an index with Key (A) INCLUDE (B,C,D) is functionally equivalent to another index with Key (A) INCLUDE (D,B,C) — you got it!

  8. Have not finished this but it is very interesting. I wish I had known you guys offered training last month but now I am signed up to go to another training course. Curious if this Blitz tool might help me with a issue with procedure Cache. My processor gets very high and none of the normal stuff helps. Stats, indexes ect. As soon as i run DBCC FREEPROCCACHE the processor drops.

  9. Thanks for this. Very interesting!

    Just one question…

    I’ve been running the BliztIndex script against some of my tables. For the one table is suggested 4 missing indexes. Now I don’t want to just go an add all the indexes that it suggests as I don’t want to over index.

    Below is the estimated benefit and impact for the suggested missing indexes.

    Estimated Benefit:

    1. [finance].[GeneralLedger] Est Benefit: 95,160
    2. [finance].[GeneralLedger] Est Benefit: 59,395
    3. [finance].[GeneralLedger] Est Benefit: 3,786
    4. [finance].[GeneralLedger] Est Benefit: 280

    Estimated Impact:

    1. 16 uses; Impact: 97.5%; Avg query cost: 61.0
    2. 2569 uses; Impact: 57.8%; Avg query cost: 0.4
    3. 3 uses; Impact: 97.1%; Avg query cost: 13.0
    4. 1 use; Impact: 96.7%; Avg query cost: 2.9

    Using the above information what is the best way to determine if I should go ahead and create these indexes?

    Thanks

    Kevin

    • Hi Kevin,

      Specific indexing advice isn’t something I can do right in blog comments (there’s just too much other information needed), so I’m going to outline a quick general process for you. First ask: is this a database you can index? If this is a 3rd party database, there might be some rules.

      If you can create indexes: Look at the definitions of the most critical missing index requests (filtering out those that are rare or for “cheap” queries). Are the requests similar to one another? Can they be combined? Look at the indexes on the table and ask the same thing– can you combine any indexes? You’ve also got to estimate the size and read/write ratio of the new index and make a judgment call about whether or not its worth it.

      In a nutshell, the game is always about creating the lightest index structure possible that helps your workload. So your impulse to not create all four is right on, but you do need to dig in much more to figure out what (if anything) to do.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php