Be selective while creating an Index in SQL Server

I have just received a question in my “ASK Profile” in asking me that he has big table with hundreds of thousands of rows inone table and there is PK on fields so it suppose to have clustered index byits own on that field so it is working fast when we keep that PK field in WHEREclause but working very slow when they keep other than that PK field in WHEREclause. He wants to make as many as possible index on his table so it worksfast with any of the fields in WHERE clause.
This seems interesting case to me so I am writing this blogpost, actually I have seen this kind of confusion and concerns in many otherdevelopers too. Let us make it clear.
Till SQL Server 2005, there was a limit of 249 nonclusteredindex on one table and 1 clustered index on the same table but nonclusteredindex limit was increased from 249 to 999 in SQL Server 2008 version so peoplemight think that Microsoft has given us facility to create so many indexes onone table, why shouldn’t we use it?
Keep one thing in mind that, Index may boost up performanceof you SELECT statement but it puts overhead in Insert/Delete DML commands aswhile manipulating records in the table, it has to go to each nonclusteredindex and add/remove records in each index which simply decrease theperformance of your Insert/Delete statement if you have unnecessary indexes.
Apart from the reason given above, each index needs diskspace to store its data and cache memory to load all indexes which simplyaffect the performance again due to high IO hits.
There is one more reason, if you have so many indexes; yourSQL Server optimizer might get confused regarding what index to go for whileexecuting the query so it evaluates all indexes to find the best suited indexto run for, which is simply waste of time and resources especially if you arehaving less cache.
So keeping long story short, you should have to be selectivewhile creating index. You shouldn’t have to create nonclustered index on everyfields but find out the best selectivity in your table and create index on thatonly.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
Ask me any SQL Server related question at my “ASK Profile”.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: