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


Indexes with Include


Indexes with Include

Author
Message
Ray Laubert
Ray Laubert
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1770 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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46607 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Ray Laubert
Ray Laubert
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1770 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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46607 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98997 Visits: 38998
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.

Cool
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)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101609 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)

Group: General Forum Members
Points: 235036 Visits: 46376
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, 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


Ray Laubert
Ray Laubert
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1770 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)

Group: General Forum Members
Points: 235036 Visits: 46376
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, 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 (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101609 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
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