November 23, 2009 at 1:12 am
Dear Friends,
i ran a script to find the missing indexes on our tables.. It asked me to create non clustered indexes with included columns. How does SQl serer decide which columns to include? When we are creating nonclustered index how to decide which columns should be implmented as included columns?
Please share some light on this.
November 23, 2009 at 1:26 am
Take a look at this series: http://www.sqlservercentral.com/articles/Indexing/68439/
Columns specified as include are ones in the select clause, but not used in where/join. Don't trust the missing index dmvs totally. Test out their recommendations, consider what they're recommending. All too often there are duplicate indexes or really massive indexes that perhaps aren't the best to create.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2009 at 2:28 am
The data for the included columns are contained in the non clustered index itself so that it does not need to go back to heap or clustered index for the data. It will not be wise to include columns of bigger data types.
November 23, 2009 at 2:56 am
Thank you Gail for your article. It's a brilliant artice. It is much clearer now how indexes work.:-)
November 24, 2009 at 7:31 am
I want to add that you should be VERY careful about just implementing all of what the missing index DMVs or DTA give you!! They LOVE to include columns and you will wind up with incredible index bloat!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply