Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Indexes with Include Expand / Collapse
Author
Message
Posted Sunday, June 28, 2009 5:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #743253
Posted Sunday, June 28, 2009 6:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #743255
Posted Sunday, June 28, 2009 7:31 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #743320
Posted Monday, June 29, 2009 1:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 2,393, Visits: 3,399
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"
Post #743391
Posted Monday, June 29, 2009 4:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #743456
Posted Monday, June 29, 2009 4:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #743463
Posted Monday, June 29, 2009 5:40 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #743516
Posted Tuesday, June 30, 2009 9:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 4,319, Visits: 6,112
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 at GMail
Post #744537
Posted Tuesday, June 30, 2009 9:44 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #744555
Posted Tuesday, June 30, 2009 10:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 4,319, Visits: 6,112
>> 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 at GMail
Post #744611
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse