GilaMonster (12/4/2009)
sashikanta.mishra (12/3/2009)
I agree that all the column should not be part of index column. So i only choose the column to be indexed and rest columns are in INCLUDE . So there is some improvement around 20% improvementWe're saying that having all the columns in the index, as key or include, is a very bad idea. Does the index really, really, really need to be that large? Is that need sufficient to justify using double (or more)
More, absolutely more. The key values plus the include values plus the clustered key, it has to add up to more.
the space of the table itself in total? Does it justify the costs of rebuilding an index that large and maintaining and index that large?
I can't tell you whether you should or shouldn't remove those includes without seeing the queries that run against that table. Do some investigation, look at all the queries that run against that table. Which ones use that index? Will they still use it if it's smaller?
110% agreement.
Even if this index is getting "used" doesn't mean it should be there. This is a classic case of achieving balance between index management and the desire to eliminate key lookups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning