Covering Index using Included Columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Josef Richberg » Covering Index using Included Columns

17 posts, Page 1 of 2

1 2 »»

Covering Index using Included Columns

Rate Topic

Display Mode

Topic Options

Author

Message

sqlrunner

Posted Wednesday, February 10, 2010 10:13 PM

SSC 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

Selvam Rajendran

Posted Wednesday, February 10, 2010 10:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 04, 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

arr.nagaraj

Posted Thursday, February 11, 2010 12:07 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

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

SQLRNNR

Posted Thursday, February 11, 2010 12:13 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #863870

Thomas-428301

Posted Thursday, February 11, 2010 12:20 AM

Grasshopper

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

arr.nagaraj

Posted Thursday, February 11, 2010 12:43 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

@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

pslavik

Posted Thursday, February 11, 2010 1:21 AM

Forum 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 Smile

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

jshailendra

Posted Thursday, February 11, 2010 1:31 AM

Old Hand

Group: General Forum Members
Last Login: Friday, December 21, 2012 5:24 AM
Points: 366, Visits: 436

Josef, thanks for the article...I learned about included columns..

Post #863901

RichardBo

Posted Thursday, February 11, 2010 2:42 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:28 AM
Points: 369, Visits: 228

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

GilaMonster

Posted Thursday, February 11, 2010 3:25 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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 »

17 posts, Page 1 of 2

1 2 »»

Permissions