Is there way to do it? I am currently facing problem as one index is being used unnecessarily in my query (as displayed in the execution plan of the query in sql server 2008) and it is degrading the query performance. This index can not be dropped as it is useful for some other query.
+1
A:
Use hints to direct the optimizer to use specific indices or even (from the link)
If you want to force a table scan, use the hint: (INDEX(0)).
akf
2010-02-26 06:20:17
I can't think of many situations where forcing a table scan is a good idea.
Mitch Wheat
2010-02-26 06:42:51
How about when you know you need to read the entire table? Would you read an entire book by looking at every entry in the index, and then reading the pages referenced by the index?
Adam Musch
2010-03-01 20:33:04
Table space scans are good! If you are going to read more than 40% of the rows then slurping up the whole table and filtering out the unwanted rows is more efficient than bouncing around indexes.
James Anderson
2010-03-02 07:16:35
+1
A:
You can give SQL Server a query hint, e.g. force it to use a specific index - but to my knowledge, there's no way to prevent it from using a specific index. Can you find some other index that your query in question could use instead?
Also, with SQL Server 2008, you have new additional query hints, called OPTIMIZE FOR, which might help you define (and explain) to the query optimizer what it should optimize for.
See the MSDN documentation on query hints for complete details.
This is the typical and fundamental tradeoff you must make when adding indices - yes, it might increase performance of one query you're looking at - but at the same time, it might decrease others. There's really no easy solution here - either the gain for the other query is greater than the pain for this query - then keep the index. Otherwise drop it.
marc_s
2010-02-26 06:21:11
A:
Not sure if this still works but in the old days wrapping a function around a column name usually stopped any index usage e.g.
where substring(mycol,1,16) = ?
James Anderson
2010-02-26 06:38:52
A:
Query hints should be avoided unless absolutely necessary (and most of the time a hint is not required). Using one will come back to bite you...
I suggest you rebuild your indexes or update your statistics.
Mitch Wheat
2010-02-26 06:39:50
+1
A:
I think that the only option here is actually to force SQL Server to use some particular execution plan. In order to do this, you will need to 'create' this plan first. Try playing with empty database/recalculate statistics/query and table hints in order to get the plan you want. After that you should save the plan and apply it to your query. You can read a detailed description of how to do this here and here.
AlexS
2010-02-26 06:46:28