Modify an existing non clustered index or create a new index in SQL Server 2012

Question

I have a table with 150 million records and 8 indexes. One of the indexes is on userId. A lot of the current queries I have filter on userId and therefore this index works perfectly. For one of the queries, I have to expand the where clause to include regionId and productId columns. I have two options: I can create a new index with (userId, regionId, productId) or add these new columns to the existing index. Which would be a better option? If I modify the existing index, would it affect the other queries that already use userId?

My Goal:
I want my current query which expands the where clause to include RegionId and productId to run faster but I don't want my other queries that only have userId in the where clause to be affected. If I add a new index on (userid, regional, productid) I can accomplish that but I'm not sure if I'm duplicating the index since I already have an existing index on userId which I can expand to include regionid and productId. But I'm not sure about the consequences of modifying the existing index.

Thanks for your time!

Answer 1

I want my current query which expands the where clause to include RegionId and ProductId to run faster but I don't want my other queries that only have UserId in the where clause to be affected. If I add a new index on (UserId, RegionId, ProductId) I can accomplish that...

Assuming your query which filters on RegionId and ProductId also filters on UserId in the same set of predicates, then yes you can potentially improve those queries by defining your nonclustered index as (UserId, RegionId, ProductId).

Any index changes to your database can affect the existing queries though. Even though you already have an index on just (UserId) that your queries are currently using, there's no guarantee they won't switch to use the index on (UserId, RegionId, ProductId), even as a separate new index.

but I'm not sure if I'm duplicating the index since I already have an existing index on UserId which I can expand to include RegionId and ProductId.

Yes, you very likely would be creating a redundancy with no actual benefit. It's rather edge case when there's a scenario for redundant indexes such as what you're proposing. Additionally, you create more overhead when the table is INSERTed, UPDATEed, or DELETEd from because more indexes need to be maintained for those DML changes. You generally would be better off expanding the existing index's definition.

But I'm not sure about the consequences of modifying the existing index.

Only way to find out is by testing. You can save the original index definition and put it back as it was should you run into issues. You can also disable indexes by either right clicking on them in SSMS and clicking on Disable or via T-SQL, for example: ALTER INDEX IndexName ON SchemaName.TableName DISABLE;.

It's generally good strategy to create as little indexes as necessary that cover as many queries as possible. And again, the cases where redundant indexes are needed are quite few and far between, so it's likely you'll do fine by dropping the old index on just (UserId) and replacing it with the better defined index on (UserId, RegionId, ProductId).

---

But of course a lot of this is guesswork without knowing the exact predicates of all of your queries involved. You should generally add some of the relevant queries to your Posts, along with the full table and existing index definitions, when asking performance questions on here.

For example, if the queries you're trying to improve use an inequality search on UserId then your proposed index would likely be unhelpful to those queries anyway, and a separate index on just (RegionId, ProductId) (while leaving the original index on just UserId alone) might be the best answer. It really depends on the specifics of what your queries are doing.