SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Josef Richberg / Covering Index using Included Columns / Latest Posts

RE: Covering Index using Included Columns

Lexa — Fri, 23 Sep 2011 07:48:09 GMT

Hi,I've a follow up question regarding this discussion. Query below which is called 10K a day causes of long PAGEIOLATCH_SH waits.UPDATE table1.list_item SET first_name = CASE WHEN ls.has_first_name = 1 THEN ls.first_name ELSE li.first_name END, last_name = CASE WHEN ls.has_last_name = 1 THEN ls.last_name ELSE li.last_name END, address_1 = CASE WHEN ls.has_address_1 = 1 THEN ls.address_1 ELSE li.address_1 END, address_2 = CASE WHEN ls.address_2 is not null THEN ls.address_2 ELSE li.address_2 END, city = CASE WHEN ls.has_city = 1 THEN ls.city ELSE li.city END, state = CASE WHEN ls.has_state = 1 THEN ls.state ELSE li.state END, zip_code = CASE WHEN ls.has_zip_code = 1 THEN ls.zip_code ELSE li.zip_code END, country = CASE WHEN ls.has_country = 1 THEN ls.country ELSE li.country END, home_phone = CASE WHEN ls.has_home_phone = 1 THEN ls.home_phone ELSE li.home_phone END, work_phone = CASE WHEN ls.has_work_phone = 1 THEN ls.work_phone ELSE li.work_phone END, cell_phone = CASE WHEN ls.has_cell_phone = 1 THEN ls.cell_phone ELSE li.cell_phone END, got_money = CASE WHEN ls.got_money = 1 THEN ls.got_money ELSE li.no_money END, has_mansion = CASE WHEN ls.has_mansion = 1 THEN ls.has_mansion ELSE li.no_mansion ENDFROM @list_items ls INNER JOIN table1.list_item li ON ls.list_item_id = li.list_item_id WHERE li.org_id = @org_idThe table1 is partitioned by org_id which is also a clustred index. I've created a non-clustered index on table1 like soCREATE NONCLUSTERED INDEX [IX_list_item_list_item_id_org_id] ON [table1] ( [list_item_id] ASC, [org_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, FILLFACTOR = 80, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)GOand I still see some performance issues.I'm considering #1. adding list_item_id to the clustred index, so I'll have org_id and list_item_id as a composit clustered index or #2. add some INCLUDE columns to IX_list_item_list_item_id_org_id index like has_work_phone, has_cell_phone since they are bit types. I can't add all the columns in the "INCLUDE" that are involved in the update (First Name, address, etc.) due to size and that would be too much. What do you think?Thanks in advance

RE: Covering Index using Included Columns

BeerBeerBeer — Thu, 11 Mar 2010 17:53:54 GMT

So, if I have this clear in my head, if I have a query that joins several tables and and I'm selecting columns from a few of those tables, I might consider putting covering indexes on the columns I am selecting from a table in conjunction with it's SARG from the where clause.As soon as I add another column to my query that is not in one of the covering indexes I would expect my execution time to return to the pre-covering index speed as the SQL engine is now required to go all the way back to the table for the extra column data.Makes sense to me

RE: Covering Index using Included Columns

Fal — Sun, 14 Feb 2010 18:48:33 GMT

[quote][b]carl.anderson-1037280 (2/11/2010)[/b] In our data warehouse, all of our indexed tables use at least one clustered index (as per DBA mandate). [url=http://edw.northwestern.edu]Carl AndersonData ArchitectNorthwestern University EDW[/url][/quote]Not sure about a mandate, as a clustered index relates to how the data is physically stored on disk, hence you can only have one. Given that, it's best to have your clustered index on column(s) that will give a benefit in that regard. My own preference is on column(s) that are required in common range lookups. So for unique IDENTITY() columns: No. But for datetime columns, or a "category" column, then you get the benefit of the physical order when reading pages BETWEEN <here> and <there>.S.

RE: Covering Index using Included Columns

TravisDBA — Thu, 11 Feb 2010 12:05:21 GMT

Although using INCLUDE columns in an Index is not practical in all situations, using them to eliminate a Bookmark Lookup (RID) in an execution plan in a lot of situations I have generally found to be a good thing in query response times. Grant Fritchey has spoken on this in the past as well and he is an expert on Query Execution Plans IMHO.:)

RE: Covering Index using Included Columns

Oleg Netchaev — Thu, 11 Feb 2010 09:37:44 GMT

[quote][b]RichardBo (2/11/2010)[/b][hr]What does SARG mean?[/quote]The best possible prelude will be Gail's own excellent article titled [b]Introduction to Indexes[/b]. It has 3 parts, first was published here last September and others a little later. The url to the first part is [url=http://www.sqlservercentral.com/articles/Indexing/68439/]http://www.sqlservercentral.com/articles/Indexing/68439/[/url]. Gail's article includes the link to Brad Mcgehee article about SARGable predicates.Oleg

RE: Covering Index using Included Columns

sqlrunner — Thu, 11 Feb 2010 09:36:28 GMT

[quote][b]carl.anderson-1037280 (2/11/2010)[/b][hr]This article is in regard to indexes with included column on [i]heap tables[/i]. This means the table doesn't have a clustered index, right? In our data warehouse, all of our indexed tables use at least one clustered index (as per DBA mandate). How does this article change if we're talking about a table with a clustered index?Also, is there a compelling reason to use heap tables?Thanks for the article![url=http://edw.northwestern.edu]Carl AndersonData ArchitectNorthwestern University EDW[/url][/quote]The leaf level of the non-clustered index would point, not to the heap table, but to the clustered index. The concepts still hold. You would make a covering index if your clustered did not satisfy your query. Regarding Heap Tables. Personally, I only put a clustered index on a table if there is a reason to, not just because there is a mandate. I prefer to have clustered indexes unique, although with 2005+ there is a uniquifier column just in case your index is not unique by key. If there are primary/foreign key relationships, then you have a clustered index.

RE: Covering Index using Included Columns

carl.anderson-1037280 — Thu, 11 Feb 2010 09:27:29 GMT

This article is in regard to indexes with included column on [i]heap tables[/i]. This means the table doesn't have a clustered index, right? In our data warehouse, all of our indexed tables use at least one clustered index (as per DBA mandate). How does this article change if we're talking about a table with a clustered index?Also, is there a compelling reason to use heap tables?Thanks for the article![url=http://edw.northwestern.edu]Carl AndersonData ArchitectNorthwestern University EDW[/url]

RE: Covering Index using Included Columns

GilaMonster — Thu, 11 Feb 2010 03:25:40 GMT

[quote][b]RichardBo (2/11/2010)[/b][hr]What does SARG mean?[/quote]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.

RE: Covering Index using Included Columns

RichardBo — Thu, 11 Feb 2010 02:42:41 GMT

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.

RE: Covering Index using Included Columns

jshailendra — Thu, 11 Feb 2010 01:31:19 GMT

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

RE: Covering Index using Included Columns

pslavik — Thu, 11 Feb 2010 01:21:32 GMT

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 [b]48.9% space in the intermediate levels [/b] (actually little less because of pointers to the next level) but that amounts to about [b]0.4% for the whole index[/b]! 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.[b]Full index[/b]LEAF LEVEL: 1,000,000 leaves, 50B/leaf ==> 6,250 pagesINTERMEDIATE LEVEL: 6,250 nodes, 50B/node ==> 160 nodes/page ==> 40 pagesROOT LEVEL: 40 nodes, 50B/node ==> 160 nodes/page ==> 1 pageTotal# pages: 6,291[b]Index with included columns[/b]LEAF LEVEL: 1,000,000 leaves, 50B/leaf ==> 160 leaves/page ==> 6,250 pagesINTERMEDIATE LEVEL: 6,250 nodes, 20B/node ==> 400 nodes/page ==> 16 pagesROOT LEVEL: 16 nodes, 20B/node ==> 400 nodes/page ==> 1 pageTotal# pages: 6,2676,291 / 6,267 = 1.003829 ... ~ 1.004Clearly, 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.

RE: Covering Index using Included Columns

arr.nagaraj — Thu, 11 Feb 2010 00:43:49 GMT

@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.

RE: Covering Index using Included Columns

Thomas-428301 — Thu, 11 Feb 2010 00:20:39 GMT

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.

RE: Covering Index using Included Columns

SQLRNNR — Thu, 11 Feb 2010 00:13:04 GMT

Thanks Josef. Nice concise explanation. The data space requirements is also good information.

RE: Covering Index using Included Columns

arr.nagaraj — Thu, 11 Feb 2010 00:07:21 GMT

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.[url]http://strictlysql.blogspot.com/2009/11/covering-index-include-nonkey-columns.html[/url]

RE: Covering Index using Included Columns

Selvam Rajendran — Wed, 10 Feb 2010 22:15:14 GMT

Hi Josef Richberg,I couldn't see the Real World Examples in this Page. I tried in all browsers.Regrdsselvam R

Covering Index using Included Columns

sqlrunner — Wed, 10 Feb 2010 22:13:39 GMT

Comments posted to this topic are about the item [B]Covering Index using Included Columns[/B]