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


Indexes with INCLUDE columns


Indexes with INCLUDE columns

Author
Message
lianvh 89542
lianvh 89542
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 654
Good Day.
Include columns sound wonderful , but I would like to know is How many columns should be allowed as INCLUDE columns ? I ran a missing index report once and it suggested up to 10 columns should be added to the INCLUDE columns list . I did not accept the advice as it did not look right at the time . Does it not cause unnecessary overhead on very busy OLTP systems ? I had instances where we were severely impacted by INClUDE columns where we only added 4 columns to the INCLUDE list .
Thanks for you feedback in advance.
Lian
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4994
Performance Considerations

Avoid adding unnecessary columns. Adding too many index columns, key or nonkey, can have the following performance implications:

Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency.

More disk space will be required to store the index. In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. This is because the column values are copied into the index leaf level. Therefore, they reside in both the index and the base table.

Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements. For more information about evaluating query performance, see Query Tuning.


Check the link given below
http://msdn.microsoft.com/en-us/library/ms190806%28v=sql.100%29.aspx


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 4845
Hi,

It never happened to someone to have those problems like "will the INCLUDE section satisfy my requirement to create 10,20,50... columns" It usually supports 3-digit or more number of columns.
It depends what types are the columns you plan to include and their definition. As many as you include as bigger in size the index becomes.
It also depends if your table already has many indexes, and the operations performed on the table. If it faces mostly read operations than it could be of value. If it faces 'quite' big amount of writes than it could withdraw a slowing down of update operations on the table. The table size is very important too. And of course some other factors should be considered too...

In general you'll be happy because naturally systems perform much more reads than writes. I'll suggest you to test. Create it, compare the maintenance impact, monitor your system, see the gain.

Best,
IgorMi


Igor Micev,SQL Server developer at Seavuswww.seavus.com
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7381 Visits: 15095
Lian

Be careful with missing index recommendations. They only suggest indexes that would improve performance on individual queries, not on the workload as a whole.

Adding an included column to an index can improve performance more than adding the same column to the index key. This is because, since the values are only stored at leaf level, it takes fewer writes to maintain and fewer reads to return data. You will only want to do this if the column is being added to cover a query, not if it is being used in a search operation such as a WHERE clause or a JOIN predicate.

John
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