58

I'm currently doing some testing to determine the performance implications of including an index on a given column in 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.

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.

Mike Bailey
  • 12,479
  • 14
  • 66
  • 123

3 Answers3

82
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.

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.

Amy B
  • 108,202
  • 21
  • 135
  • 185
  • So if I have on `MyTable` the indexes `PK_MyTable` and `IX_MyTable_MyIndexedColumn`, I can just use `WITH (INDEX(PK_MyTable))` to force it *not* to use the index on `MyIndexedColumn`? – Mike Bailey Jun 13 '12 at 14:33
  • 1
    Yes. Examine the ExecutionPlan to confirm this behavior. http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Amy B Jun 13 '12 at 14:36
  • What about cases when other non-clustered indexes present? How to make sql server use rest on index and not the newly created index, to check how the new index helps the query? – Satyajit Nov 20 '14 at 07:28
  • @Satyajit as explained in the answer, use WITH (INDEX(IndexName)) – Amy B Nov 21 '14 at 13:59
  • 2
    I realize I am WAY late to this party but this really does not answer the OP's question, even though he "accepted" the answer. As Satyajit pointed out, this example shows how to USE a different index, not how to force SQL to not use a specific index and if there are other indexes on the table the Optimizer may forego using any of those and I don't want to figure out what other indexes I should include using WITH. I upvoted Markus Winand's answer below, which, IMO, actually answers the OP's question. – Andrew Steitz Feb 25 '16 at 17:56
  • The index hint can take multiple values. So the OP can do this: 'SELECT * FROM MyTable WITH (INDEX(Index1,Index2,Index3)) WHERE MyIndexedColumn = 0' then compare that to 'SELECT * FROM MyTable WITH (INDEX(Index4,Index5)) WHERE MyIndexedColumn = 0' – J.Hendrix Apr 24 '19 at 20:48
  • @J.Hendrix That won't work. Using multiple indexes in the WITH clause forces the query optimizer to use **all** of them. They must have been using some pretty strong stuff when they designed SQL Server 2016, because this renders the option useless for more than a single index. – Tom Lint Aug 16 '23 at 10:19
9

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.

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. when a where clause makes the database believe it's best resolved using an index, but it isn't.

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).

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

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

Chris Catignani
  • 5,040
  • 16
  • 42
  • 49
Markus Winand
  • 8,371
  • 1
  • 35
  • 44
  • 1
    That's horribly clever, but seems to take advantage of a deficiency rather than a documented feature, and probably not the best option, depending on the need. – Paul Jul 30 '15 at 10:24
  • I'm missing something: if no row in `MyTable` contains the value `0` under `MyIndexedColumn`, then the query would return 0 records (because that's what `WHERE` filtered), while the aim is to test a query that *does* get some results, isn't it? (this comment also applies to the accepted answer, but [it's already been pointed out](https://stackoverflow.com/questions/11016935/how-can-i-force-a-query-to-not-use-a-index-on-a-given-table/18841824#comment58951127_11017099) that it doesn't answer the question) – OfirD Feb 19 '20 at 16:08
  • @HeyJude I also don't get your comment: The "+ 0" doesn't change the result of the query, but it "prevents" an index being used. Whether the query happens to return no rows or all of the table, doesn't change anything here. – Markus Winand Feb 20 '20 at 09:40
  • @Markus Winand, it's the `= 0` part that I don't get. It filters the results, returning only those with value of 0 in that column. The OP didn't mention any filtering, though. – OfirD Feb 20 '20 at 11:21
  • @HeyJude It doesn't matter which value you are searching for. My response is just based on the previous response by @AmyB. The same pattern applies when searching for different values or even different comparisons. E.g. `MyIndexedColumn + 0 > 1234` – Markus Winand Feb 21 '20 at 09:36
  • @Markus Winand, then I think you should mention that your answer only applies when doing some filtering (which the OP didn't mention). – OfirD Feb 21 '20 at 09:58
  • @HeyJude But filtering is the **very** commen case what an index is used for. What where you thinking of? – Markus Winand Feb 21 '20 at 11:43
  • @Markus Winand, people come to this question looking for a way to ignore an index, regardless of what their query nature is. Their own use case may include filtering, but in many cases - they may not, but still they want to ignore an index that's being used when they run their query. It's worth mentioning which use cases your answer refers to. – OfirD Feb 21 '20 at 11:53
  • 1
    @HeyJude I'v added that part: when a `where` clause makes the database believe it's best resoluved using an index, but it isn't. – Markus Winand Feb 21 '20 at 11:56
-7

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.

zlangner
  • 249
  • 2
  • 7
  • 5
    Your query is locking complete database schema until all batches in transaction are executed! – Milan Jaric Dec 25 '14 at 15:30
  • I did not down vote but I have to agree with Milan. Not only that, but many times I have to test something like this in a Production database and there is no way the DBAs are going to give me permission to alter an index in Production. – Andrew Steitz Feb 25 '16 at 17:50