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

Indexes with INCLUDE columns Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 12:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:23 PM
Points: 43, Visits: 286
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
Post #1474897
Posted Thursday, July 18, 2013 12:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
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/
Post #1474900
Posted Thursday, July 18, 2013 1:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 2,725, Visits: 2,635
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
Post #1474921
Posted Thursday, July 18, 2013 2:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 5,074, Visits: 8,910
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
Post #1474930
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse