Adding Multiple Indexes on a Single Table For Different Queries

Question

I have a table which will have 3 different queries run against it.

• The first query has a where clause which uses two of the columns
• The second three of the columns
• The third four of the columns

If I run each query through the Estimated Execution Plan, and SQL Server Management Studio suggests adding a new different index for each query.

I'm happy to add three different index's for maximum performance. The table is never updated and rarely insert into.

However is it a good idea to add multiple index's to the same table each to accommodate a different query.

Answer 1

If you know the queries well enough, then add the indexes.

Indexes primarily add overhead when you are modifying the data (insert/update/delete). They do incur a bit of extra overhead by also taking up memory in the page cache. This cuts two ways. Sometimes the index itself can entirely replace the table. Sometimes both are needed, depending on the query and the indexes.

There is little downside if the data is not changing, and a lot of potential upside. Because SQL Server is recommending the indexes, you can be pretty confident that they will get used and should increase the performance of the query.