如何强制查询不使用给定表上的索引？

I'm currently doing some testing to determine the performance implications of including an index on a given column in SQL Server 2005.

我目前正在进行一些测试，以确定在SQL Server 2005中包含索引在给定列上的性能影响。

The test data set I'm using has approximately ~72 million rows (about 6 GB of data). In order to actually test the performance of the index I need to be able to compare the performance with and without the index there.

我使用的测试数据集大约有大约7200万行（大约6 GB的数据）。为了实际测试索引的性能，我需要能够比较有和没有索引的性能。

That's all well and fine, but creating an index in the first place is not a cheap operation. If I want to test the table without the index, I need to, at the very least, disable the index. To test with the index I need to re-enable it which takes quite a long time.

这一切都很好，但首先创建一个索引并不是一个便宜的操作。如果我想测试没有索引的表，我至少需要禁用索引。要使用索引进行测试，我需要重新启用它，这需要相当长的时间。

Is there any way that I can force SQL Server 2005 to ignore a given index when it's executing a query? I don't want to have to disable the index just to test a query since it takes such a long time to disable the index.

有什么办法可以强制SQL Server 2005在执行查询时忽略给定的索引吗？我不想为了测试查询而禁用索引，因为它需要很长时间才能禁用索引。

3 个解决方案

#1

SELECT *
FROM MyTable WITH (INDEX(0))
WHERE MyIndexedColumn = 0

Query would normally use the index on MyIndexedColumn, but due to the table hint, it will instead tablescan.

查询通常会使用MyIndexedColumn上的索引，但由于表提示，它将改为表扫描。

SELECT *
FROM MyTable WITH (INDEX(IndexName))
WHERE MyIndexedColumn = 0

Query would normally use the index on MyIndexedColumn, but due to the table hint, it will instead use the index named IndexName.

查询通常会使用MyIndexedColumn上的索引，但由于表提示，它将改为使用名为IndexName的索引。

#2

I'm working with all different kinds of DBs and can never remember the specific hint when I need it. Therefore I'm using a pure SQL approach that (currently) works with all the DBs that cross my way.

我正在使用所有不同类型的DB，并且在我需要它时永远不会记住特定的提示。因此，我正在使用纯SQL方法（当前）与所有跨越我的方式的DB一起使用。

The idea is just to make it impossible for the DB to use the specific index by obfuscating the respective expression in the SQL. E.g.

这个想法只是让DB不可能通过混淆SQL中的相应表达式来使用特定的索引。例如。

SELECT *
  FROM MyTable
 WHERE MyIndexedColumn + 0 = 0

Similarly, you can add an empty string to a string value. Current optimizers do no resolve such expressions can cannot use an index on (MyIndexedColumn).

同样，您可以将空字符串添加到字符串值。当前优化器无法解析此类表达式无法使用索引（MyIndexedColumn）。

This is actually an anti-pattern I described in my book. Here are some on page about math in SQL

这实际上是我在书中描述的反模式。以下是关于SQL中数学的一些信息

It's definitively good enough for ad-hoc testing. In production code, hints are more expressive of course!

它对于临时测试来说确实足够好。在生产代码中，提示更具表现力！

#3

-2

You can disable the index you don't want to use in the same transaction that you're running the test code in, just be sure to rollback the transaction at the end. This ensures the test code won't use the index but prevents the index from actually being disabled.

您可以在运行测试代码的同一事务中禁用您不想使用的索引，只需确保在最后回滚事务。这可以确保测试代码不会使用索引，但会阻止索引实际被禁用。

BEGIN TRANSACTION

    ALTER INDEX [MyIndex] ON MyTable DISABLE;

    EXEC TestCode;

ROLLBACK

This works better if you've got a complex case where your test code uses a number of indexes at different times and you want to test if the addition of a new one would improve things.

如果你有一个复杂的情况，你的测试代码在不同的时间使用了许多索引，而你想测试是否添加一个新的东西可以改善一些事情，那么效果会更好。

#1

SELECT *
FROM MyTable WITH (INDEX(0))
WHERE MyIndexedColumn = 0