July 18, 2013 at 12:14 am
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
July 18, 2013 at 12:22 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 18, 2013 at 1:35 am
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,My blog: www.igormicev.com
July 18, 2013 at 2:15 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply