Be selective while creating an Index in SQL Server

I have just received a question in my “ASK Profile” in BeyondRelational.com. Poster asking me that he has big table with hundreds of thousands of rows in one table and there is PK on fields so it suppose to have clustered index by its own on that field so it is working fast when we keep that PK field in WHERE clause but working very slow when they keep other than that PK field in WHERE clause. He wants to make as many as possible index on his table so it works fast with any of the fields in WHERE clause.

This seems interesting case to me so I am writing this blog post, actually I have seen this kind of confusion and concerns in many other developers too. Let us make it clear.

Till SQL Server 2005, there was a limit of 249 nonclustered index on one table and 1 clustered index on the same table but nonclustered index limit was increased from 249 to 999 in SQL Server 2008 version so people might think that Microsoft has given us facility to create so many indexes on one table, why shouldn’t we use it?

Keep one thing in mind that, Index may boost up performance of you SELECT statement but it puts overhead in Insert/Delete DML commands as while manipulating records in the table, it has to go to each nonclustered index and add/remove records in each index which simply decrease the performance of your Insert/Delete statement if you have unnecessary indexes.

Apart from the reason given above, each index needs disk space to store its data and cache memory to load all indexes which simply affect the performance again due to high IO hits.

There is one more reason, if you have so many indexes; your SQL Server optimizer might get confused regarding what index to go for while executing the query so it evaluates all indexes to find the best suited index to run for, which is simply waste of time and resources especially if you are having less cache.

So keeping long story short, you should have to be selective while creating index. You shouldn’t have to create nonclustered index on every fields but find out the best selectivity in your table and create index on that only.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”.