asked
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?
Thanks everyone. So I go into the table's statistics folder and start working down till I find the 'correction' statistical properties. 'No statistics information available'. Does this mean that since there are no stats, it will always be pushed to the front of the index search? Would stats maybe prevent its selection? By the way, the longest running queries are generally from MS ACCESS and I don't know how to insert "WITH(INDEX(IN_DATEOFSERVICE))" in their sql view or wherever. Any help there appreciated also.
To see the statistics in question you want to look at DBCC SHOW_STATITISTICS. I'm absolutely not a fan of query hints until all other tuning opportunities have been exhausted. But, if you have queries you can't edit, but you still want to use a hint, you can look at plan guides:
http://technet.microsoft.com/en-us/library/ms190417.aspx
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
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.
16 People are following this question.
