Clustered index scan in query with non clustered index filter

Question

I have an Operations table with a nonclustered index on my foreign key to a Organizations table; however, when trying to run the following query, SQL Server does a clustered index scan instead of using my non clustered index:

SELECT OpType, UserId, OpTime 
FROM Operations 
WHERE OrganizationId = 'some uniqueindetifier'

I tried doing a join or using in instead and the result was the same. If I change the query to SELECT OrganizationId FROM... then I actually get my intended nonclustered index seek, but naturally returning only this column would be pointless and adding any other column goes back to a clustered scan.

There's dozens of millions of rows in the table but there should be around only 50 000 rows with the OrganizationId I'm looking for, so I'm sure that a query plan that uses the non clustered index would be faster.

I executed the same query for a different OrganizationId (that returned about 30 000 rows) and got the result in less than 2 minutes, while doing this query for the OrganizationId that does a clustered scan didn't finish in 8 minutes even though it should return around 50 000.

I don't have any columns included in the Organization index but I don't want to double the size of this fairly large table by adding all columns to the index as there's a lot of rows getting inserted and I only want to extract all columns from it once a month for a report.

So aside from including columns to the index, what else can I do to make SQL Server use my non clustered index?

Answer 1

One way to coax the optimizer to use narrow nonclustered indexes is to use a CTE:

WITH fix_me AS (
SELECT OrganizationId 
FROM Operations 
WHERE OrganizationId = 'some uniqueindetifier'
)
SELECT *
FROM Operations AS o
JOIN fix_me AS f
ON f.OrganizationId = o.OrganizationId

I'm guessing that your nonclustered index isn't covering, meaning the optimizer has a choice between doing a Key Lookup, or scanning the Clustered Index.

Using a CTE in this way can help guide the optimizer to use a narrow index to get only the necessary rows, but may also be ignored.

Since you gave an incomplete picture of your query and table design, feel free to adjust that to fit your needs.

I talk about this technique more in this presentation:

Improving SELECT * Query Performance

Answer 2

Why do you want to make SQL use your non-clustered index? Have you tried using SET STATISTICS TIME ON and SET STATISTICS IO ON and running the query when you force it to use a non-clustered index and when it uses the clustered index willingly? (Make sure to test this with hot and cold storage / memory.)

I'm betting that the clustered index scan is better. When your non-clustered index does not have the column included, it's going to have to do a nested loop against the clustered index and pull back that single row repeatedly. SQL is essentially thinking that this takes too much time and it's going to just scan the clustered index in one swoop because it contains that row already.

You can force an index usage with a query hint. Pinal Dave on SQL Authority shows you how. Brent Ozar covers some of the trepidation I have with using hints.

Can you paste the plan? Ideally, you would paste two using cold storage. One where you force the index hint and one where you do not. This way we can compare and see what SQL sees and we can dive deeper from there.

Answer 3

As others have pointed out, the optimizer is making a decision on what query plan will be most efficient. It uses the available indexes, statistics (of the data distribution), etc. in doing that. Things can throw the optimizer off - bad/outdated statistics, parameter sniffing issues, etc. But sometimes the optimizer is smarter than you. You can force the use of your index with a hint, and compare the plans between using your index and the optimizer's choice of a clustered index scan. Look at estimated vs. actual rows from each operation, and the cost of each operation.

One item to note is that your index is not "covering" - SQL Server can not return the result set by only looking at your index, it needs to use your index to find qualifying rows, and then get the non-indexed columns for those rows from the table/clustered index. You can create a covering index that should be preferred with:

CREATE INDEX ix_MyConveringIndex
ON Operations (OrganizationId) 
INCLUDE (OpType, UserId, OpTime)

Because all the values to return are now in the non-clustered index leaf level (by using INCLUDE for the non-SARG columns), SQL Server no longer needs to look at the clustered index to get those columns. All standard caveats about index maintenance and performance implications apply.