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 12»»

Covering Index using Included Columns Expand / Collapse
Author
Message
Posted Wednesday, February 10, 2010 10:13 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 8:08 AM
Points: 29, Visits: 155
Comments posted to this topic are about the item Covering Index using Included Columns

Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
Post #863818
Posted Wednesday, February 10, 2010 10:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 4, 2012 4:23 AM
Points: 147, Visits: 98
Hi Josef Richberg,


I couldn't see the Real World Examples in this Page. I tried in all browsers.

Regrds
selvam R
Post #863820
Posted Thursday, February 11, 2010 12:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 2:08 AM
Points: 490, Visits: 1,348
Hi Josef,

Good write up. Just a bit surprised that you didnt get much into when to use a Included column covering index..ie Index selection.

I did notice that there was a sentence 'Anything that is in the where clause would be a key, anything in your select statement would be an included column.' Though it works at most of the cases, it could have better had it been explained bit more.

It would think twice before including a column which is very huge/including too many number of columns, or a frequently updated column. Perhaps this one can be a relevant read.

http://strictlysql.blogspot.com/2009/11/covering-index-include-nonkey-columns.html


Regards,
Raj

Strictlysql.blogspot.com
Post #863865
Posted Thursday, February 11, 2010 12:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 18,064, Visits: 16,098
Thanks Josef. Nice concise explanation. The data space requirements is also good information.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #863870
Posted Thursday, February 11, 2010 12:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 16, 2010 12:15 AM
Points: 17, Visits: 149
I like the benefit you have shown of the include clause but it’s not very clear how the pages were accessed at the file level and how there are lesser bytes at intermediate levels when compared.
Post #863875
Posted Thursday, February 11, 2010 12:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 2:08 AM
Points: 490, Visits: 1,348
@Thomas-428301,
Included columns are not present at intermediate levels and are present only at leaf levels. So it saves space. You can verify the same using the DMV
sys.Dm_db_index_physical_stats which provides details at each level of the index.


Regards,
Raj

Strictlysql.blogspot.com
Post #863880
Posted Thursday, February 11, 2010 1:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 21, 2012 12:43 AM
Points: 2, Visits: 12
First of all, good article explaining the basics of included columns.

Unfortunately, the space saving of 48.9% is completely wrong. Yes, you are saving 48.9% space in the intermediate levels (actually little less because of pointers to the next level) but that amounts to about 0.4% for the whole index! 0.4%! That's it. Not worth talking about since fragmentation will cost you much more.

Anyway, here are more details, somebody please check my math

Assume 8kB ~ 8000B per page (it's actually 8096 but this will make the math easier).
Assume a pointer to the next level is 4B (it is 6B but 4B will make it simpler).
Assume that a pointer to a table row is also 4B (that depends on the table, in most cases it's more than 4B).
Ignore any row overhead.
In any case, these assumptions give advantage to an index with included columns, so no harm done.

Let's take a table with 1,000,000 rows.

Full index
LEAF LEVEL: 1,000,000 leaves, 50B/leaf ==> 6,250 pages
INTERMEDIATE LEVEL: 6,250 nodes, 50B/node ==> 160 nodes/page ==> 40 pages
ROOT LEVEL: 40 nodes, 50B/node ==> 160 nodes/page ==> 1 page

Total# pages: 6,291

Index with included columns
LEAF LEVEL: 1,000,000 leaves, 50B/leaf ==> 160 leaves/page ==> 6,250 pages
INTERMEDIATE LEVEL: 6,250 nodes, 20B/node ==> 400 nodes/page ==> 16 pages
ROOT LEVEL: 16 nodes, 20B/node ==> 400 nodes/page ==> 1 page

Total# pages: 6,267

6,291 / 6,267 = 1.003829 ... ~ 1.004

Clearly, there are slight differences in the final number depending on the table size but no matter what, the number will be very similar for any number of rows.

Also, in Josef's example, the leaf node/index key was relatively small and therefore the number of intermediate nodes compared to the # leaf nodes was very small. With wider indexes, the number of intermediate nodes increases and so does the amount of saved space.
Post #863895
Posted Thursday, February 11, 2010 1:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
Josef, thanks for the article...I learned about included columns..
Post #863901
Posted Thursday, February 11, 2010 2:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 20, 2014 4:13 PM
Points: 371, Visits: 250
What does SARG mean?

I'll go and look it up now, but I like to think of these articles as stand alone so would like an explanation the article itself.

Whether the maths is right or not, it is a useful tool for the toolbox. Give it try when the situation arises see if the performance increases.
Post #863939
Posted Thursday, February 11, 2010 3:25 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 @ 8:36 AM
Points: 40,615, Visits: 37,080
RichardBo (2/11/2010)
What does SARG mean?


Searchable ARGument. It means a predicate in the where clause that can be used for an index seek operation. As a very high level summary, that's a predicate that directly compares a column to an expression/value. So no functions or expressions on the column side.

That's very high level, there's a lot more to it. A google search should turn up several arguments.



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

Add to briefcase 12»»

Permissions Expand / Collapse