Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Indexes with INCLUDE columns Expand / Collapse
Posted Thursday, July 18, 2013 12:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:35 AM
Points: 60, Visits: 636
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.
Post #1474897
Posted Thursday, July 18, 2013 12:22 AM



Group: General Forum Members
Last Login: Yesterday @ 8:44 PM
Points: 2,917, Visits: 4,957
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

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
Post #1474900
Posted Thursday, July 18, 2013 1:35 AM

SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 4,006, Visits: 4,722

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.


Igor Micev,
SQL Server developer at Seavus
Post #1474921
Posted Thursday, July 18, 2013 2:15 AM



Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 7,045, Visits: 14,746

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.

Post #1474930
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse