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?