Is there anyway to force the query analyzer to look beyond this first column in its index search?
The query analyzer is defaulting to an index scan on the primary key when there is a valid index (IN_DATEOFSERVICE) for the first condition in the where clause (DATEOFSERVICE). I see the missing index recommendation with 'CORRECTION,DATEOFSERVICE'. Correction (Y/N) is of no use because only .85% of the records have a 'Y' in correction. Is there anyway to force the query analyzer to look beyond this first column in its index search?
You can try an INDEX option like this: SELECT FirstThing, NextThing, DATEOFSERVICE FROM TheTableWithData WITH (INDEX(IN_DATEOFSERVICE)) Overriding the Query Analyzer does not always generate better results and as your data changes so can the effectiveness of doing so. Try it and test it.
Yes and no. The basic issue comes back to statistics, not the index. Statistics store two basic sets of data, the histogram, based on the first column, and the selectivity, based on all the columns. The optimizer primarily focuses on the histogram for determining how many rows are likely to be returned by a query against an index. You can't escape that since the optimizer is built around determining cost and the one mechanism it has for measuring cost are the statistics. So, to modify behavior, you have to look to modify the indexes to work better with the code or modify the code to work better with the indexes. Overall selectivity is used as part of the determining factor for indexes, which is why compound indexes are actually useful. But the cost estimator always goes to the histogram.