2

Lately I've been refactoring the SQL Server indexes of the products that I have developed for my employer. One of these products is an online dashboard containing multiple user levels.

Each user (on admin level) can create projects that they can then link to users of the user level below. Each project has its own Name field and also the field AdministratorUserId to keep track of the project admin. I created a combined non-clustered unique index on these two fields, meaning that project names have to be unique per administrator.

Now here's the catch: I am noticing that I often do a select on AdministratorUserId only, and not on Name. According the indexing guidelines, and please correct me if I'm wrong, I am to create a non-clustered index on the field in order to optimize my queries.

My question being - does my existing index on Name + AdministratorUserId already improve my queries or is it advisable to create a separate index besides it? Would there be any downside to AdministratorUserId being present in two separate indexes?

2 Answers 2

6

The order of the columns in indexes is critical.
If your AdministratorUserId is the first column of the index, then separating the indexes will not improve anything.
The index works like an order by clause, meaning that it is sorted by the first column, then the second, then the third and so on. Because of that, an Index on AdministratorUserId is functionally equivalent to an index on AdministratorUserId and Name. Note that it's possible in sql server to include columns to a non-clustered index that are non key columns. This will increase the storage size of the index but may improve performance of queries since it will save a lookup operation on columns that are included in the index.

4

Run this script in your database:

SELECT obj.*,usage.*
FROM sys.dm_db_index_usage_stats AS usage
INNER JOIN sys.indexes as obj
        ON usage.object_id = obj.object_id
WHERE database_id = db_id()

It will provide you with the index usage of your indexes. You can take a look (or filter on it) to see how often your index is used for scans or for seeks. After that you can make you an own picture of your indices.

If you filter always over AdministrationUserId and your index is build name, AdministrationUserId it won't benefit from it.

2
  • In other words: just try and test. Good to know, thanks for the query.
    – RuubW
    Jun 18, 2015 at 9:10
  • Well not really try and test. ;-) Your environment currently use the query. whether as it's just exist or as a real data source. You can compare the write and seeks to compare if it's worth updating it. After that you can try to add another additional nonclustered index and observe hits stats. If it's better you can remove the old one.
    – Ionic
    Jun 18, 2015 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.