Welcome to the 99th edition of T-SQL Tuesday, hosted this month by friend (and coworker) Aaron Bertrand. We were given a choice and I decided to dive into Aaron’s Index of T-SQL bad habits to find my inspiration. I knew I found a winner when I saw Aaron’s “Don’t just blindly create those “missing” indexes!” post. Conveniently, I will be at SQLBits presenting “Uncovering Duplicate, Redundant, and Missing Indexes!”
Recommended… with Reservations
There are many out there who choose to deploy missing index recommendations blindly, but that’s a terrible idea. In fact, did you know that Microsoft has a published article that lists numerous other limitations of the missing index recommendations? Really!
Anecdotally, I find that many know about how the scope of a missing index recommendation is limited to a specific query, which limits its usefulness. But there’s another limitation that seems to catch people by surprise.
Order Matters
When defining an index with multiple key columns, the order that you define them directly impacts how useful that index can be to the query optimizer. Think about it – if you have a large address book, sorted by last name, first name, but had to find everyone with the first name of “Andy,” you’ll be scanning the entire thing.
Well did you know that missing index recommendations do not consider optimal index key order at all? It may give you a missing index recommendation ordered with Column A, B, C, when the ideal ordering is actually C, B, A!
Which Way is the Right Way
What should you take into account when considering index key order? Well, you need to have some knowledge of your overall workload to make effective decisions. You also need to be familiar with the underlying data itself – particularly how selective is the data in each key column? And consider which index operation you really want for the query in question: a seek, scan, or range scan?
Back to the simple example from earlier, should you implement a missing index of first then last name? Or last then first name? Having first name first and able to seek on “Andy” might help that first query in question, but it might be terrible because most of your workload uses last name predicates.
Indexing: Art & Science
I strongly believe that effective indexing is both an art and a science. The variety of questions that one must ask about one’s data and query workload is a key reason that I believe this. Unfortunately I still encounter individuals who think that blindly implementing missing index recommendations, despite knowing some of their drawbacks, is still better than nothing. I hope you’ll think of this in the future and reconsider.
