I recently ran into this case with an index. The table had an index on the column “Status”, however when querying on “Status” the SQL optimizer was deciding not to use the Index, even though this actually hurts performance. The issue was in checking multiple values of “Status” in the select:
Where Status=’Active’ – uses index
Where Status=’New’ – uses index
Where Status IN (‘Active’,’New’) – ignores index
Where Status=’Active’ OR Status=’New’ – ignores index
The interesting thing was that when this table had less than 50,000 rows it used the index, but after the table grew in size the SQL Optimizer decided that the query was nolonger “selective” enough to justify using the index. By ignoring the index the Optimizer lowered the CPU usage of the query but as a side effect increased disk IO (The Optimizer figured this solution was better overall). However, this caused the number of reads for a look up to be significantly higher (On average reading 500,000+ plus rows on every lookup) costing us a great deal of disk IO. For our system IO was the bottleneck (Not CPU) and ignoring this index caused significant production issues.
So the fix was to use a UNION ALL, and do two selects, rather than one select that checks two values (Yes you could also hard code the index usage into the select but that has its own problems as well). Note: the reason we use UNION ALL here is because it avoids the cost of having to check for any duplicate values in the results. In our case we do not have to worry about duplicates (no value can have both status A and N at the same time) so we can use UNION ALL to improve performance.
Select FirstName, LastName From dbo.Friends Where Status='Active' UNION ALL Select FirstName, LastName From dbo.Friends Where Status='New'
The Tipping Point
If you are interested in reading about the cases in which an index in SQL Server will no longer be used you can find some great information at sqlskills.com. Kimberly Tripp has an article explaining The Tipping Point.
My Adventures in Coding 