How to exclude certain index in a given query to see that index's impact

Question

Lets say i have multiple indexes on a table

I want to exclude only 1 of them when executing the query

Lets say my indexes are named as index_a,index_b,index_c

The query is select * from myTable where columnA='var1'

So how can i exclude only index_b from this query?

The aim here is testing the impact of index. So I will run the query 2 times at the same query. At the first query i will have it with index enabled and in the second query i wont have it enabled. So the include actual execution plan will show me their relative to the batch query cost

I am giving example (real case is much more complex)

Answer 1

Options:

• Index hints (Multiple index hints are a bad idea see link)
• Disable index
• Drop index (won't recommend it for testing, could be a large table that takes some time to recreate the index)
• Copy the schema and data, except the index you want to exclude. (Less relevant, as statistics will be different for the newly created indexes)

Index hints

You could try executing the query with a specific index to test the difference:

select * 
from myTable WITH (INDEX(index_a)) -- or index_b or index_c
where columnA='var1'

Or with multiple hints:

select * 
from myTable WITH (INDEX(index_a, index_c)) -- Bad idea, only for testing.
where columnA='var1'

You can also specify index hints on JOIN, see the documentation for table hints and query hints

Disabled index

To disable an index:

ALTER INDEX index_b ON myTable   
DISABLE;

To enable an index see the documentation