Crappy Missing Index Requests

When you’re tuning queries

It’s sort of a relief when the first time you get your hands on it, you get the plan and there’s a missing index request. Even if it’s not a super high-value one, something in there is crying for help. Where there’s smoke, there’s a bingo parlor.

But does adding missing indexes from requests always make things better?

The question goes for any tool, whether it’s DTA, or the missing index DMVs, or your own wild speculation. Testing is important.

Not all requests are helpful

In fact, some of them can be harmful. Let’s look at a recent example from the Orders database. After running for a while, I noticed the UpdateShipped stored procedure was asking for an index. And not just any index, but one that would reduce query costs by 98.5003%. That’s incredible. That’s amazing. Do you take DBA Express cards?

The code in question is the part where the update actually happens.

The index that it’s currently using is very thoughtful. Extra thoughtful. Maybe the most thoughtful index I’ve ever created for free. Though somewhat forgetfully named.

What about the query plan?

Aside from some baked-in problems, it’s pretty normal. It has a cost of 2829 query bucks. Pretty high! Like I said, baked in problems.

El Stinko

The baked in problems are an exercise for you, dear reader.

So what happens to it when we add the missing index?

It’s crappy! The query doesn’t even use it, but we do now have to update it. ShipDate is in the index, ShipDate is being updated. We have to update the index, like, now. Duh. This query now has a cost of 6792 query bucks. That’s the opposite of a reduction, and a far cry from the 98 point some-odd percent reduction the missing index DMV promised us.

Totally crappy

If we go a step further. Or farther? You tell me. We can add an index hint to force the matter, and of course, forcing the matter makes matters worse. And that matters. The forced index query has a crappy cost of 6837 query bucks. This is why our cost based estimator does not choose this plan on its own.

Most crappiest


Legacy of Frugality

Lies And DMV Lies

When running sp_BlitzIndex, we often recommend testing out any index with an estimated benefit of >1mm per day. But that’s the key word: testing. A missing index request that gets added and causes harm will rarely harm the query that’s asking for it. I got pretty lucky here in demoland with an example. Usually you have to add the index, make sure it doesn’t hurt the query you’re adding it for, and then do regression testing on other queries in play. This includes modification queries.

Thanks for reading!

Previous Post
It’s now easier to read the archives by category.
Next Post
Let’s Corrupt a SQL Server Database Together, Part 1: Clustered Indexes

9 Comments. Leave new

  • Hi Erik,

    great blog post and interesting excercise! Unfortunately I don’t have the Orders Database to play with that query right now. Could you please post the executionplans on pasteThePlan in order to allow for a more detailed analysis?

    As a novice performance tuner two toughts came to my mind:
    * The baked-in-problem possibly is the crappy predicate on order date. I assume all that DATEADD, DATEDIFF leads to a non-sargeable-predicate and would have expected an index scan…(however it’s an index seek…but that could be on 99% of the table as well resulting in the same scenario as a scan would it?).
    * Regarding indexing perhaps it would help to try a filtered index on ship date…however that depends how much rows are actually NULL. In an real world scenario I would guess most of the rows should already have a ship date and ship date NULL only makes up a small portion of the table. However the update will affect such a filtered index as well…leading to rows being removed from it. Therefore we definitely would need to test if the performance gain of filtering on ship Date NULL is higher than the associated management activity on the index.

    I am looking forward to explanations and test results from other readers


    • Martin – to play along, your best bet is to download the database. It’s not really fair for you to give us a list of things you want us to try – that’s why we put so much work into making our sample databases available for the public.

      But I’ll make an exception: you asked us for something, and I’ll ask you for something. Unfortunately, I don’t have a 2017 Porsche 911 Targa 4S in paint-to-sample Mexico Blue with a natural leather interior (including the steering column – can’t believe they charge money for that), PDK transmission, and the Burmester audio. If you can get me one of those, I’ll get you what you’re looking for here. 😉

  • Hi, Brent!
    When I run the sp_BlitzIndex, the result of the missing Index always is Empty.

  • Thanks, Brent!
    But the following query returns three missing indexes.
    Select * from sys.dm_db_missing_index_details
    where database_id = db_id() and object_id = OBJECT_ID(N’TableName’)

    • They’re likely very low value – like queries that have only run a few times on a server that’s been up for days.

      For further support questions, go ahead and read the readme file on where to ask questions. Thanks!


Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.