SQL Server ignores proper index with included column

Question

I have tblClaims(ClaimID, ValidityTo, ...) and tblClaimServices(ClaimServiceId, ClaimID, ValidityTo, ....) with an obvious foreign key on ClaimID. The ValidityTo is used for history, so actual data has ValidityTo=null.

These tables have respectively 3 million and 13 million rows.

The query:

select * from tblClaimServices where ClaimID=1234567 and ValidityTo is null

takes 5 seconds to execute ! Querying ... where ClaimID=1234567 is instantaneous. Note that we're not doing select * but specifying almost all columns. This is an ORM (Django)

The explain plan shows that it's using a clustered index on (ClaimServiceID, ValidityTo) and then working hard to query the ClaimID within those. That's insane ! ValidityTo is null for 98% of the rows.

We created an index on (ClaimID, ValidityTo) but it wasn't used. We then created an index on ClaimID with an included column for validityto:

CREATE NONCLUSTERED INDEX idx_test1 ON tblClaimServices (ClaimID) include (ValidityTo) WHERE ValidityTo IS NULL

But wasn't used either. (So taking 5 seconds to find 0 to 10 rows)

However, using a hint

from tblClaimServices with (index(idx_test1))

Does work great. Instant results.

Now, I can't and don't want to have to include hints. SQL Server should be able to use an index that is so specific ! And it would require me to update an old app that uses a ORM and including the hints there would be a major pain. And make the app pretty fragile or become very slow in other queries.

How can I improve SQL Server's decision to use that proper index ?

Answer 1

I discovered that this strange behavior disappeared when the database was in 2012 compatibility mode. In a more recent mode, the database avoids using the date index validity_to.

We do have a similar field that points from soft-deleted records to the current one which is an integer. Replacing the date condition ( is null ) with the integer makes all these queries use the index properly and return results immediately.

I am still not 100% sure why the index isn't used for the validity_to but my problem is solved.