Clustered index scan in query with non clustered index filter 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?
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