SQL Server not using proper index for query

Question

I have a table on SQL Server with about 10 million rows. It has a nonclustered index ClearingInfo_idx which looks like:

I am running query which isn't using ClearingInfo_idx index and execution plan looks like this:

Can anyone explain why query optimizer chooses to scan clustered index ?

Answer 1

I think it suggests this index because you use a sharp search for the two columns immediate and clearingOrder_clearingOrderId. Those values are numbers, which were good to search. The column status is nvarchar which isn't the best for a search, and due to your search with in, SQL Server needs to search two of those values.

SQL Server would use the two number columns to get a faster result and searching in the status in the second round after the number of possible results is reduced due to the exact search on the two number columns.

Hopefully you get my opinion. :-) Otherwise, just ask again. :-)

Answer 2

As Luaan already pointed out, the likely reason the system prefers to scan the clustered index is because

• you're asking for all fields to be returned (SELECT *), change this to fields that are present in the index ( = index fields + clustered index-fields) and you'll probably see it using just the index. If you'd need a couple of extra fields you can consider INCLUDEing those in the index.
• the order of the index fields isn't very optimal. Additionally it might well be that the 'content' of the field isn't very helpful either. How many distinct values are present in the index-columns and how are they spread around? If you're WHERE covers 90% of the records there is very little reason to first create a (huge) list of keys and then go fetch those from the clustered index later on. Scanning the latter directly then makes much more sense.

Did you try the suggested index? Not sure what other queries run on the table, but for this particular query it seems like a valid replacement to me. If the replacement will satisfy the other queries is another question off course. Adding extra indexes might negatively impact your IUD operations and it will require more disk-space; there is no such thing as a free lunch =) That said, if performance is an issue, have you considered a filtered index? (again, no such thing as a free lunch; it's all about priorities)