Kendra Little Explains How to Design Smarter Indexes

How do you know which indexes to create to improve your database performance? Microsoft Certified Master Kendra Little shows you how to find a missing index, decide on the best index definition, and measure the usefulness of your new index. If you know the basics of what a database index does and want to learn the smart way to design indexes, this talk is for you.

Want to play along with the video with the sample scripts? Scripts are below the video.

Here’s what you need to miss (and fix) these indexes at home:

  • Get the ContosoRetailDW database from Microsoft
  • Then check out the missing index samples below

Workload Script

This set of queries generates sample missing index information for the Contoso Retail DW database. This uses queries that cause conversion errors on purpose: missing index recommendations will be generated, but this will still run very fast.

This script is designed to be saved as a file and run periodically during the demo.

Demo SCRIPT

This script will prompt you several times to run a “fake workload” using the script above.

Previous Post
6 Blog Projects to Improve Your Writing
Next Post
DotNetRocks Podcast

23 Comments. Leave new

  • My kind of girl, smart and cute :-)…

    Jokes apart, thanks! Very nice presentation. If you can post or share the TSQL scripts so we can practice at home too, would be nice.

    Awesome job!

    • Hi Jose,

      A link to the scripts is right below the video. Guessing it wasn’t easy to see, so I updated the post with a note above the video about where the links are hiding. Have fun!

  • Hi Kendra,

    What is the range for “Impact” from your view that requires immediate attention?

    When the view recommends to build an index on Inequality Columns, how does the approach of creating index differ from Equality Columns?

    Thanks

    • Hey Alexey,

      These are great questions, and might make really nice future webcasts!

      My ‘generic’ answer to the impact question is to look at any impacts over 1 million. This is, of course, generic! Some systems have lower thresholds, some have higher. System uptime can really impact this.

      For inequality and equality questions, I’d consider a few things:

      • Limitations on the order listed in the missing index feature— what to understand about order (or lack thereof) and how costing is done for inequality only. Read more here.
      • The problem you’re trying to solve. In some cases, you may want to provide a narrow index that can help the most queries. In this case you may experiment and see if inequality columns can serve you just as well as leading columns as equality columns– the results will depend on the selectivity of your workload and the commonality of the queries.
      • The general rules on selectivity and column order. Here’s a good article from SQLServerPedia on the topic. (Not beginner stuff!)

      For people starting out, figuring out when to follow the rules for column order and when to disregard the rules is typically overwhelming. This is why I like to show how even a simple index (which doesn’t lead with the most selective equality column, which would frequently win the “best in show” award) can address a good chunk of a workload in many cases. Although it won’t cover everything– and you need to measure if it covers ‘enough’ for you. (I’m not sure if that’s a pun or not, but there it is.)

  • Vasiliy Goncharenko
    October 3, 2011 7:31 am

    Hi Kendra,

    Thank you for a very interesting and helpful video and scripts.

    We are working on a similar task, that we call “Dynamic Indexes” (DI).
    The idea is to analyze periodically DMV, identify missing indexes and delete indexes that are not in use anymore.
    The DI task should be completely automated.
    SQL job supposed to take care of these 2 tasks (create new/delete old), but we are stuck on tweaking – identifying valuable info and thresholds.
    Your video gave us fresh ideas. 🙂

    Thanks again,
    Vasiliy

    • Hi Vasiliy,

      Automated index tuning is indeed tempting, and I can think of many environments where it would save a lot of time– however, I have several clients who have tried it and have had disappointing results. As I suspect you’ve found, it can be difficult to properly combine indexes without using too much space— and it’s also very difficult to automate determining when a filtered index would work and when it would not.

      If you hit road bumps and are blocked from the full project, don’t let it spoil the goal of developing tools to make index tuning either. Even if you end up with a tool that helps you collect indexing information for manual review and decisions (which is my personal preference, I admit), those tools can be really helpful.

      kl

  • Vasiliy Goncharenko
    October 12, 2011 10:33 am

    > … I can think of many environments where it would save a lot of time
    > – however, I have several clients who have tried it
    > and have had disappointing results.

    Yes, I agree, the idea is simple and beneficial, tweaking and tuning is challenging.

    While tuning auto-indexing, we end-up with an additional table, kind of DMV nightly snapshot, that allows us to compare efficiency of auto-created views and, more importantly, identify indexes that need to be auto-removed by checking recent activities.
    It would be nice to have a team effort.

  • Wow, such a great video! I’ve never seen such helpful information on indexes before. Thanks so much for doing this and taking the time to really explain what is going on and *practical* approach to indexing. So, so helpful!

  • Im obliged for the post.Much thanks again. Want more.

    • Thanks for the feedbac, Rylie! I was just thinking of putting together a full length index presentation, and perhaps a few more 30 minute webcasts on the topic. Now that you commented, I *know* we need to do it!

  • Hi Kendra Little,

    Link Broken:

    The link “Then download our MissingIndexSamples” is broken.

    • Hi Kendra Little,

      The link to “MissingIndexSamples” is broken
      So,

      Can you please email me the “MissingIndexSamples” on my Email ID?

      Or

      please provide the link to the attachment.?

      Many Thanks.

      • Hello!

        Sorry about that– not sure how that got broken. I updated the link, tested it, and it works for me now. Please give it another shot.

        Thanks much for letting me know.
        Kendra

  • Thanks! Kendra Little,

    Yes it is Now WORKING 🙂

    Cheers…!!!

  • I would like to add one thing. I’ve utilized this script on a server to see exactly where it stands. This is a server that is coming to us shortly. The view would not run. The resolution is rather depressing…. We had to convert impact to BIGINT to get it to work. /sigh.

    Thank you again for your wonderful work!!! And of course saving our little DB’s.

    • Kendra Little
      January 24, 2013 1:26 pm

      Hi Bill!

      I’m sorry your missing index recommendation was so large 🙂

      Thanks for letting me know this is still the case in the sample script here– adding a to-do item to update the data type in the scripts. (probably be a few days before I can get to it, but it’s on the list.)

  • Thank you so much for this video. It really helped me to understand indexes better than I ever have. The scripts have been a very helpful too for me as well.

    SQL keeps recommending that I create an Index and the Index already exist and is being used The recommendation only has Equality and Inequality columns. What would cause it to tell me to create the same index again?

  • George Copeland
    August 23, 2018 1:55 pm

    Holy smoke is this a perfect post. Thank you very much Kendra. I like your squid. 🙂

Menu
{"cart_token":"","hash":"","cart_data":""}