Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Covering Index using Included Columns


Covering Index using Included Columns

Author
Message
sqlrunner
sqlrunner
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 159
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
Selvam Rajendran
Selvam Rajendran
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
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
arr.nagaraj
arr.nagaraj
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 1590
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

http://Strictlysql.blogspot.com
SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27022 Visits: 18342
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

Thomas-428301
Thomas-428301
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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.
arr.nagaraj
arr.nagaraj
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 1590
@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

http://Strictlysql.blogspot.com
pslavik
pslavik
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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.
jshailendra
jshailendra
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 455
Josef, thanks for the article...I learned about included columns..
RichardBo
RichardBo
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 251
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

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


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