When Indexes Are No Help
In an earlier tip I discussed how indexes can speed up processing. It's only fair that I also talk about when indexes don't help.
First: Because indexes don't always help but do always increase the time it takes to update a table (though usually by a negligible amount) you should always run a test. Before applying an index to a table, take a moment to run a query that you think your index will help run faster -- ideally, some query used in production. Run the query twice and measure how long the query takes to complete the second time. Then, after applying your index, run the query twice again and compare the results of the second run.
If you don't get a difference that you can measure with your wrist watch, drop the index: SQL Server has decided that your index isn't helpful and is ignoring it.
Which raises the question: Why is SQL Server ignoring the index? If your table has a lot of rows and SQL Server is ignoring your index, typically the reason is that your index isn't subdividing the table sufficiently to make your index worthwhile to use. If using the index will still require SQL Server to look at "too many" rows, SQL Server will decide it's faster to just do a scan of the whole table to find the rows it needs.
How many is "too many" will vary from one version of SQL Server to another (and other factors, including the size of the table) but SQL Server typically requires an index to return a relatively small percentage of the total number of rows in the table. This number is often as small as 20 percent -- if SQL Server has to look at more than 20 percent of your rows, SQL Server will decide that it's faster just to look at all of the rows in the table. An index on a column holding gender information (where 51 percent are F and 49 percent are M) is probably going to be ignored, for example.
Posted by Peter Vogel on 04/03/2015