Some people have missed the point about my last post, called Covering, Schmuvvering. If you haven’t read it, then do so. Have a think about what’s going on, and why the Covering Index really isn’t that good, and then come back here and see if you’ve understood it right.
My point is that often in a Seek, the Seek Predicate isn’t filtering out much of the data, and we’re left with a Seek which isn’t much better than a Scan.
The plan that I didn’t show in the last one is the one that follows. To make this, I’ve created a non-covering index, and forced (by hinting – you know, one of those hints that husbands understand isn’t really a ‘hint’ at all) the Query Optimizer to use it.
CREATE INDEX rf_ix_NotCovering2 ON Production.Product(DaysToManufacture);
SELECT Name, ProductNumber
FROM Production.Product with (index(rf_ix_NotCovering2))
WHERE DaysToManufacture < 4
AND ReorderPoint < 100
AND Color = 'Red';
The tooltip on this Seek shows that the Residual Predicate is no longer in play here. It can’t be – the included columns from the previous post simply aren’t there any more. (If you’re wondering what I’m talking about, then remember I did ask you to read the first post first)
A covering index is simply there to remove the cost of having to perform the Key Lookup – and rightly so, those things are expensive! We really can eliminate a big chunk of the cost of this query by using a covering index. But it’s the size of that arrow that concerns me.
The Seek Predicate here, testing the DaysToManufacture field, is the same Seek Predicate used in the original Covering Index Seek operation (from the earlier post, shown below). It still returns 407 rows, which each need to be checked to see if they satisfy the Residual Predicate.
The Covering Index Seek operation here feels very similar to the box I’ve drawn here around some of the operations in the Lookup scenario. You’ll notice a Single Row arrow coming out of the box. But most importantly, you’ll notice the big thick arrow coming out of the Seek Predicate bit.
Yes, that Residual Predicate, testing Color and ReorderPoint needs to be performed 407 times – that’s how many times our Key Lookup is performed.
With our Covering Index plan, it’s much harder to see. In the diagram below, I’m showing the Tooltip from the original Covering Index plan, showing where features correlate to the plan with the Key Lookup. I’ve drawn arrows to show you what corresponds to what. Remember this is the tooltip from the Covering Index, with arrows pointing at the Non-Covering Index example. The green arrow shows the Seek Predicate, which is located in the Index Seek on rf_ix_NotCovering2. The pink arrow shows where the Residual Predicate is being applied. The blue arrow shows us where we see a single row being returned. What we DON’T see in the tooltip is that the Seek Predicate is satisfied by 407 rows.
The Covering Index Seek shows us that a Seek is being done, and it shows us that a single row is outputted. What it doesn’t show us is that our Seek could pretty much be a scan.
In fact, I can easily demonstrate a Seek which is no better at all than a Scan.
where ProductID < 999999999;
This is a Seek. It says it’s a Seek. There’s even a Seek Predicate involved, which is doing a Seek.
But of course, all the rows in the Clustered Index are returned. It’s the same as a Scan. The Estimated Subtree Cost is exactly the same as a Scan, because it’s performing a Range Scan where the Range just happens to be the whole table. I could even put in a Residual Predicate (like AND reverse(Name) = 'niahC'), to make it look like it’s an effective Seek.
where ProductID < 999999999
and reverse(Name) = 'niahC';
Don’t fool yourself into thinking that this CIX Seek is good – this is still scanning the entire table, checking every row to see if the name spells ‘nianC’ backwards. It’s just that the plan doesn’t tell us how many rows are satisfying the Seek Predicate, and therefore having to be checked by the Residual.
I hope you see why a Covering Index can often hide the truth. If you just look on the surface, things can seem rosy. A Seek returning a small number of rows is good. But included columns can’t be involved in a Seek Predicate, and if your Seek is hardly better than a Scan, you could well be failing to realise the true benefit of a Covering Index.
Use Covering Indexes they way they were intended – to include things that are being returned by your query, not things that need to be tested. And try forcing a non-covering index to be used, to get a feel for just how thick that arrow is – the one that a Covering Index covers up.