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?
|
1 2 |
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Orders] ([ShipDate],[OrderDate]) |
The code in question is the part where the update actually happens.
|
1 2 3 4 5 6 |
UPDATE o SET o.ShipDate = DATEADD(HOUR, 24, o.OrderDate) FROM dbo.Orders as o WHERE o.ShipDate IS NULL AND o.OrderDate >= DATEADD(DAY, DATEDIFF(DAY, 0, @MinOrderDate), '19000101') AND o.OrderDate <= DATEADD(DAY, DATEDIFF(DAY, 0, @MinOrderDate), '23:59:59') |
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.
|
1 |
CREATE UNIQUE NONCLUSTERED INDEX ix_Orders_OD_SD ON dbo.Orders (OrderDate, ID) INCLUDE (ShipDate) WHERE (ShipDate IS NULL) |
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.
The baked in problems are an exercise for you, dear reader.
So what happens to it when we add the missing index?
|
1 |
CREATE NONCLUSTERED INDEX Crappy ON dbo.Orders (ShipDate, OrderDate) |
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.
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.
See?
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!
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
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. 😉
If Brent’s Porsche is too expensive a proposition, I work much cheaper.
Hi Brent,
I did not intend to give somebody a list of things to try. Indeed I will grab the sample database and try it myself.
I was just too excited by the blog post and tried to phrase my thinking into some words.
If that’s not playing by the rules…then I probably need to get to learn the rules a bit better.
I must admit your sarcasm hurts and does not motivate much.
Nonetheless your website is great…I have been learning a lot here and will continue to…maybe I just try to comment less in the future
Martin – thanks sir! I tend to have a little fun here in the comments. It’s only to encourage you to grow and run your own experiments. Check out this post: https://ozar.me/2017/01/the-two-best-things-paul-randal-taught-me/
Hi, Brent!
When I run the sp_BlitzIndex, the result of the missing Index always is Empty.
Mehdi – that means your server hasn’t generated any missing index requests for the database you’re running it in.
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!