THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Covering Index because it’s Covering up the truth?

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Wednesday, May 25, 2011 1:20 AM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



AlexK said:

I do not believe this. "Covering indexes good, clustered scans bad" - everybody with 1+ month of experience knows it! You should do some research before posting your half-baked "insights" ;)

May 24, 2011 11:27 AM

Rob Farley said:

I really hope everyone notices your wink there, Alex. :)

May 24, 2011 5:23 PM

Robert L Davis said:

Reading this for the first time today, and I agree with the sentiment, but disagree with the demo.

The "Covering index" created for the sample is not a true covering index. Columns that should be in the key of the index are in the Includes list.

I'm assuming that this was contrived on purpose to demonstrate how a covering index seek may not be any better than a clustered scan. Let's skip the whole fill factor aspect of it for now. This was admittedly contrived to force the seek of the covering index to require more logical reads (16) than the clustered index scan (15). Setting fill factor to the default value (100) reduces the logical page reads to 7.

However, even more telling is that simply creating the correctcovering index reduces the page reads to 2.

Using your example, I created the 2 following indexes:

CREATE INDEX rf_ix_Covering ON Production.Product(DaysToManufacture)

INCLUDE (Name, ProductNumber, Size, ReorderPoint, Color);

CREATE INDEX rf_ix_TrueCovering ON Production.Product(Color, DaysToManufacture, ReorderPoint)

INCLUDE (Name, ProductNumber, Size) ;

Then I ran the following forms of the query forcing it to use your covering index, the clustered index, and allowing it to pick its own index (it uses the covering index I created). I also used the recompile option and enabled Statistics IO and actual execution plan settings:

SELECT Name, ProductNumber

FROM Production.Product with(index(rf_ix_Covering))

WHERE DaysToManufacture < 4

AND ReorderPoint < 100

AND Color = 'Red'


SELECT Name, ProductNumber

FROM Production.Product with(index(1))

WHERE DaysToManufacture < 4

AND ReorderPoint < 100

AND Color = 'Red'


SELECT Name, ProductNumber

FROM Production.Product

WHERE DaysToManufacture < 4

AND ReorderPoint < 100

AND Color = 'Red'


Statistics IO output (with row counts removed and LOB counts truncated) was:

Table 'Product'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0...

Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0...

Table 'Product'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0...

And the actual execution plans showed that the cost of the three queries when executed as a single batch are respectively 29%, 56%, and 15% of the total cost of the batch. This clearly demonstrates that the "bad covering index" is almost 50% the cost of the clustered index scan, and twice the cost of using the appropriate covering index.

May 24, 2011 5:58 PM

Rob Farley said:

Hi Robert,

Thanks for your comment.

I agree that a better index could be used. But my point is that a Covering Index Seek hides a lot of the truth if you don't know to look at the Residual Predicate.

But tell me - why have you put ReorderPoint in as your third key? Can you explain why you consider this is better than having it in the Included list?


May 24, 2011 7:00 PM

Rob Farley said:

Of course, Robert, the ideal index for this query is the filtered index that I put at the end of the original post (, which causes an Index Scan that doesn't check the predicates at all.

May 24, 2011 7:10 PM

Paul White said:

Hi Rob,

Another important consideration people overlook with seeks is how it knows when to stop...

Just thought I'd chip that in ;c)


May 24, 2011 7:18 PM

Rob Farley said:

Indeed Paul. A filtered index is definitely better, and if it's covering as well, then great. A filtered index whose predicates match the query's predicates doesn't even need to check what's there, it just grabs all the rows. It's a thing of beauty, with no Residualicity.

May 24, 2011 7:24 PM

Robert L Davis said:

Excellent point Rob. Having a 2nd inequality value in the index doesn't improve the usage. If anything, it will make the index a little bit bigger.

Anywho, I definitely agree with what you are saying here. I just got distracted by the fact that the covered index shouldn't have been used in the first place.

May 24, 2011 9:03 PM

Rob Farley said:

No problem, Robert. I figure there are scenarios where an Index Seek is in place, and people just assume it's ideal, because people just love Seeks and hate Scans.

May 25, 2011 6:19 AM

Rob Farley said:

For those people who seek seeks, here’s a “Pro Tip”. Suppose you have a query that involves a Scan. It

May 25, 2011 7:50 AM

Amit Banerjee said:

The important point here is that consider the amount of rows being fetched and the number of executes involved in evaluating the join/filter condition in the plans. This is one of the reason I am still a fan of the text execution plan format. It lets me look at the operator, residuals, rows and executes along with the estimates. However, I am old school and people might differ with me on the use of text vs XML plans.

Just because it's a SEEK doesn't mean that you have the best plan! That is an urban-legend. :)

May 25, 2011 9:20 AM

Rob Farley said:

Hi Amit,

Not quite. There is nowhere that tells us how many rows are being satisfied by the Seek Predicate and then need to be checked by the Residual.

But for the mostpart, I agree with you wholeheartedly.

Thanks for the comment!

May 25, 2011 9:35 AM

Leave a Comment


This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG

Privacy Statement