SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes with Include


Indexes with Include

Author
Message
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36482 Visits: 11361
Grant Fritchey (6/28/2009)
Every time I ran the query, it picked the widest set of includes, regardless of the order in which the indexes were created. I didn't check reads or timings.

I ran some tests on 2005 and 2008 with AdventureWorks and about the only constant was that if an exactly-covering index existed, it was chosen. (These were additional indexes I created - not the shipped ones).

Other than that, even with twenty indexes to choose from, with varying numbers of INCLUDEd columns, in different orders, no clear pattern emerged. I thought for a moment that indexes with the required column *last* in the creation order were preferred; but no.

Fascinating question, but I ran out of time with it.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231323 Visits: 46350
I'll do some tests during the week and write up a blog post if I can come to any conclusions.

Actually, I'll blog even if I can't come to a conclusion.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100425 Visits: 33014
Paul White (6/28/2009)
Grant Fritchey (6/28/2009)
Every time I ran the query, it picked the widest set of includes, regardless of the order in which the indexes were created. I didn't check reads or timings.

I ran some tests on 2005 and 2008 with AdventureWorks and about the only constant was that if an exactly-covering index existed, it was chosen. (These were additional indexes I created - not the shipped ones).

Other than that, even with twenty indexes to choose from, with varying numbers of INCLUDEd columns, in different orders, no clear pattern emerged. I thought for a moment that indexes with the required column *last* in the creation order were preferred; but no.

Fascinating question, but I ran out of time with it.

Paul


Huh. Odd. I did the same thing. I had about five different indexes, all with the same key, but varying include columns, but duplicated, like the OP. Every single time, it chose the index with the most columns. Couldn't tell you why. I just didn't dig much past that yet.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SwePeso
SwePeso
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9869 Visits: 3433
One would think that using the index with most columns would minimize the risk of having to bookmark and use key lookup later in the execution plan.
But on the other hand, SQL Server should be smart enough to see if the index with most columns does cover the query or not.
If the index covers the query, use the index with less columns but still cover.

If the index does not cover the query, then things get more complicated. Should the query engine then use the index with less columns (for faster processing) and make a key lookup at the clustered index?
What if there is no clustered index?

Well, you get the idea that there are many thing to consider. And for getting a Best Practice out of this, DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE must be used to create a steady baseline for testing.
Not something you would want to do in a production environment...


N 56°04'39.16"
E 12°55'05.25"
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100425 Visits: 33014
Peso (6/29/2009)
One would think that using the index with most columns would minimize the risk of having to bookmark and use key lookup later in the execution plan.
But on the other hand, SQL Server should be smart enough to see if the index with most columns does cover the query or not.
If the index covers the query, use the index with less columns but still cover.

If the index does not cover the query, then things get more complicated. Should the query engine then use the index with less columns (for faster processing) and make a key lookup at the clustered index?
What if there is no clustered index?

Well, you get the idea that there are many thing to consider. And for getting a Best Practice out of this, DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE must be used to create a steady baseline for testing.
Not something you would want to do in a production environment...


I'm sorry if I caused confusion. The index was always covering, yes. It was just over-covered. It used a five column include when it could have used a one column include to meet the same needs.

You're right about the DROPCLEANBUFFERS and FREEPROCCACHE.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231323 Visits: 46350
Grant Fritchey (6/29/2009)
You're right about the DROPCLEANBUFFERS and FREEPROCCACHE.


Personally I prefer to run all test queries twice and ignore the times of the first result. That way, what I'm seeing in tests does not include compile time or time to read off disk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100425 Visits: 33014
GilaMonster (6/29/2009)
Grant Fritchey (6/29/2009)
You're right about the DROPCLEANBUFFERS and FREEPROCCACHE.


Personally I prefer to run all test queries twice and ignore the times of the first result. That way, what I'm seeing in tests does not include compile time or time to read off disk.


I do both. It really depends on what I'm trying to find out. Having been burned by compile times in the past, if the query is even marginally complex, I like to do a lot of testing with a clean cache.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33163 Visits: 8680
1) I would imagine (hope) that the optimizer would choose the 'most-compact' covering index in the case where multiple indexes cover a query.

2) DTA and the missing index subsystem really needs to be taken with a grain of salt and thoroughly examined before throwing it's recommendations into production. I have seen it do INCREDIBLY awful things to databases!!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100425 Visits: 33014
TheSQLGuru (6/30/2009)
1) I would imagine (hope) that the optimizer would choose the 'most-compact' covering index in the case where multiple indexes cover a query.

2) DTA and the missing index subsystem really needs to be taken with a grain of salt and thoroughly examined before throwing it's recommendations into production. I have seen it do INCREDIBLY awful things to databases!!!



Boy do I agree with this. I've got negative faith in the DTA.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33163 Visits: 8680
>> negative faith

New term - I think I may like it! Although perhaps antifaith could be better. :-)

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search