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 Friday, June 26, 2009 8:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 8:35 AM
Points: 1,608, Visits: 198
I am performance tuning a database for a client. I have used several methods to identify tables that could use some indexes, but also want to keep the indexing workload to a minimum.

SQL and Profiler's recommendations include several new indexes on some of the tables that recommend include columns. My question is does SQL look at the order of the include columns when making an index selection.

Here is an example:

Index 1 recommendation
Index Key: [SCHOOL_YEAR]
Include Key: [COURSE_KEY], [STUDENT_KEY], [DISTRICT_KEY], [LOCATION_KEY]

Index 2 Recommendation
Index key: [SCHOOL_YEAR]
Include Key: [COURSE_KEY], [STUDENT_KEY], [PERIOD_KEY], [SECTION_CODE], [REQST_SEQUENCE], [ENROLL_KEY], [EFFECTIVE_DATE], [DISTRICT_KEY], [LOCATION_KEY], [DELIVERY_MODEL_KEY]

Index 3 Recommendation
Index Key: [SCHOOL_YEAR]
Include Key: [COURSE_KEY], [STUDENT_KEY], [PERIOD_KEY], [SECTION_CODE], [REQST_SEQUENCE], [ENROLL_KEY], [EFFECTIVE_DATE], [DISTRICT_KEY], [LOCATION_KEY]

Index 4 Recommendation
Index Key: [SCHOOL_YEAR]
Include Key: [COURSE_KEY], [STUDENT_KEY], [SECTION_CODE], [DISTRICT_KEY], [LOCATION_KEY]

Index 5 Recommendation
Index Key: [SCHOOL_YEAR]
Include Key: [COURSE_KEY], [STUDENT_KEY], [SECTION_CODE], [REQST_SEQUENCE], [ENROLL_KEY], [EFFECTIVE_DATE], [DISTRICT_KEY], [LOCATION_KEY], [DELIVERY_MODEL_KEY]

Index 6 Recommendation
Index Key: [SCHOOL_YEAR]
Include Key: [DISTRICT_KEY], [LOCATION_KEY]

All of these values are either DATETIME or BIGINT.

Goal is to keep the number of indexes to a minimum while providing the most benefit for reports and queries.

My thoughts are recommendations 2 & 5. But if the order or number of non used included columns is a factor in the index selection, then I would have to re-evaluate based on percentage of use/improvement and Index recommendation 6 would be the only one created.

I could not find the selection criteria on how SQL chooses an index with include columns other than it would use one the had ALL of the columns required to fulfill a query.

Unfortunately, I do not have the access nor the time left on the contract to test each of these scenarios out.

Thanks for your inputs


Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
Post #742705
Posted Friday, June 26, 2009 9:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 11,314, Visits: 13,101
My understanding of Included columns in an index is that they do not affect whether or not an index is selected. Index use is based on the key column(s) in the index from left to right (See the blog posts by Gail Shaw (GilaMonster here on SSC) here and here). Included columns allow you to create covering indexes without creating a huge composite key.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #742768
Posted Friday, June 26, 2009 9:48 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 8:35 AM
Points: 1,608, Visits: 198
You are correct on the selecting an index as a 'candidate index' to be used. Only the index key values are used. However, after an candidate index is identified, the included columns are used to determine if it is a covering index or not. If it is determined that it is a covering index than that index will be used in the execution of the query.

The question then becomes does SQL treat the selection of covering indexes the same way it does the selection of candidate indexes. left to right and stopping at the first mismatch or does it just look for all the columns required before determining if it is a covering index.



Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
Post #742808
Posted Friday, June 26, 2009 10:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 11,314, Visits: 13,101
Good question, I guess I misunderstood exactly what you were asking the first time. One thing I think I'd do in this instance is run a query like this:

SELECT
DDMID.index_handle,
DDMID.database_id,
DDMID.[object_id],
DDMID.equality_columns,
DDMID.inequality_columns,
DDMID.included_columns,
DDMID.statement,
DDMIGS.unique_compiles,
DDMIGS.user_seeks,
DDMIGS.user_scans,
DDMIGS.last_user_seek,
DDMIGS.last_user_scan,
DDMIGS.avg_total_user_cost,
DDMIGS.avg_user_impact,
DDMIGS.system_seeks,
DDMIGS.system_scans,
DDMIGS.last_system_seek,
DDMIGS.last_system_scan,
DDMIGS.avg_total_system_cost,
DDMIGS.avg_system_impact
FROM
sys.dm_db_missing_index_groups AS DDMIG JOIN
sys.dm_db_missing_index_group_stats AS DDMIGS
ON DDMIG.index_group_handle = DDMIGS.group_handle JOIN
sys.dm_db_missing_index_details AS DDMID
ON DDMIG.index_handle = DDMID.index_handle
Where
DDMID.database_id = DB_ID('yourdbname')
ORDER BY
DDMIGS.user_seeks DESC,
DDMIGS.avg_user_impact desc





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #742836
Posted Friday, June 26, 2009 10:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 23,397, Visits: 32,237
You really only need one of those indexes from what I can see. That would be the one that includes all the columns listed in the INCLUDED column lists. The actual index portion is identical in all 6 recommended indexes.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #742853
Posted Friday, June 26, 2009 10:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 15,735, Visits: 28,139
I wanted to be sure of the answer before I posted it, but it seems that SQL Server will most frequently pick the index with the most columns. I tried disabling them, changing the order of columns, varying the number of columns included in the query, but it generally went for whichever index had the most columns in the include clause at the time the query was run.

----------------------------------------------------
"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 #742865
Posted Friday, June 26, 2009 12:12 PM


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 @ 12:15 PM
Points: 43,017, Visits: 36,179
Ray Laubert (6/26/2009)
The question then becomes does SQL treat the selection of covering indexes the same way it does the selection of candidate indexes. left to right and stopping at the first mismatch or does it just look for all the columns required before determining if it is a covering index.


The order of include columns does not matter. Only the order of key columns matters and then only for seek predicates.



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 #742908
Posted Friday, June 26, 2009 12:54 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 8:35 AM
Points: 1,608, Visits: 198
Thanks, I was pretty sure that the order and number of columns for the include columns didn't matter, but I wanted to get some second opinions.

Have a great weekend. Just found out that as of Tues, this assignment is done. So I guess I start vacation on the 1st instead of the 6th hehe.




Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
Post #742934
Posted Friday, June 26, 2009 1:46 PM


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 @ 12:15 PM
Points: 43,017, Visits: 36,179
Grant Fritchey (6/26/2009)
I wanted to be sure of the answer before I posted it, but it seems that SQL Server will most frequently pick the index with the most columns. I tried disabling them, changing the order of columns, varying the number of columns included in the query, but it generally went for whichever index had the most columns in the include clause at the time the query was run.


That's odd, I would have thought it would go for the narrower index because it would incur the fewest IOs.

Time for some experimentation and maybe a blog post?



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 #742953
Posted Sunday, June 28, 2009 5:44 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 15,735, Visits: 28,139
GilaMonster (6/26/2009)
Grant Fritchey (6/26/2009)
I wanted to be sure of the answer before I posted it, but it seems that SQL Server will most frequently pick the index with the most columns. I tried disabling them, changing the order of columns, varying the number of columns included in the query, but it generally went for whichever index had the most columns in the include clause at the time the query was run.


That's odd, I would have thought it would go for the narrower index because it would incur the fewest IOs.

Time for some experimentation and maybe a blog post?


Not a bad idea. I'd need to do more experiments before I would try to claim I understood what I saw.

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.


----------------------------------------------------
"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 #743249
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse