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.
有办法吗?由于查询中不必要地使用了一个索引(如sql server 2008中的查询执行计划中显示的那样)，我目前面临的问题是，它降低了查询性能。不能删除该索引，因为它对其他查询很有用。
6 个解决方案
#1
1
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?
您可以给SQL Server一个查询提示，例如强制它使用一个特定的索引——但是据我所知，没有办法阻止它使用一个特定的索引。你能找到你的查询可以替代的其他索引吗?
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.
此外，有了SQL Server 2008，您就有了新的查询提示，称为optimization FOR，这可能有助于您向查询优化器定义(并解释)它应该优化什么。
See the MSDN documentation on query hints for complete details.
有关查询提示的详细信息，请参阅MSDN文档。
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.
这是在添加索引时必须进行的典型和基本的权衡——是的，它可能会提高您正在查看的一个查询的性能——但同时，它可能会降低其他查询的性能。这里确实没有简单的解决方案——另一个查询的收益大于这个查询的痛苦——然后保留索引。否则下降。
#2
2
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)).
如果要强制执行表扫描，请使用提示:(索引(0))。
#3
2
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.
我认为这里唯一的选项实际上是强制SQL Server使用某些特定的执行计划。要做到这一点，你首先需要“创建”这个计划。尝试使用空数据库/重新计算统计数据/查询和表提示来获得您想要的计划。之后，应该保存计划并将其应用到查询中。您可以在这里和这里阅读详细的说明。
#4
1
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) = ?
#5
1
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.
我建议您重新构建索引或更新统计数据。
#6
0
I know this is an old question but for anyone who stumbles across this (like me) there IS a way to prevent an index from being used, although its a little circuitous.
我知道这是一个古老的问题，但对于任何遇到这个问题(像我一样)的人来说，有一种方法可以防止索引被使用，尽管它有点曲折。
You need to tell SQL to not use any indexes and then tell it exactly which indexes it can use.
您需要告诉SQL不要使用任何索引，然后告诉它可以使用哪些索引。
Essentially you end up with a white list of indexes.
实际上，您最终得到了一个白色的索引列表。
FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))
More info here: http://msdn.microsoft.com/en-us/library/ms187373.aspx
更多信息:http://msdn.microsoft.com/en-us/library/ms187373.aspx
#1
1
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?
您可以给SQL Server一个查询提示，例如强制它使用一个特定的索引——但是据我所知，没有办法阻止它使用一个特定的索引。你能找到你的查询可以替代的其他索引吗?
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.
此外，有了SQL Server 2008，您就有了新的查询提示，称为optimization FOR，这可能有助于您向查询优化器定义(并解释)它应该优化什么。
See the MSDN documentation on query hints for complete details.
有关查询提示的详细信息，请参阅MSDN文档。
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.
这是在添加索引时必须进行的典型和基本的权衡——是的，它可能会提高您正在查看的一个查询的性能——但同时，它可能会降低其他查询的性能。这里确实没有简单的解决方案——另一个查询的收益大于这个查询的痛苦——然后保留索引。否则下降。
#2
2
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)).
如果要强制执行表扫描，请使用提示:(索引(0))。
#3
2
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.
我认为这里唯一的选项实际上是强制SQL Server使用某些特定的执行计划。要做到这一点，你首先需要“创建”这个计划。尝试使用空数据库/重新计算统计数据/查询和表提示来获得您想要的计划。之后，应该保存计划并将其应用到查询中。您可以在这里和这里阅读详细的说明。
#4
1
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) = ?
#5
1
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.
我建议您重新构建索引或更新统计数据。
#6
0
I know this is an old question but for anyone who stumbles across this (like me) there IS a way to prevent an index from being used, although its a little circuitous.
我知道这是一个古老的问题，但对于任何遇到这个问题(像我一样)的人来说，有一种方法可以防止索引被使用，尽管它有点曲折。
You need to tell SQL to not use any indexes and then tell it exactly which indexes it can use.
您需要告诉SQL不要使用任何索引，然后告诉它可以使用哪些索引。
Essentially you end up with a white list of indexes.
实际上，您最终得到了一个白色的索引列表。
FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))
More info here: http://msdn.microsoft.com/en-us/library/ms187373.aspx
更多信息:http://msdn.microsoft.com/en-us/library/ms187373.aspx