Sql-server – SQL Server not using nonclustered index

indexsql servert-sql

I have table which contains about 470mln of rows. I would like to select data based on date. I have two indices created on this table. One is clustered one and other is non clustered on date column (date is stored as INT). I have simple select statement:

select *
from big_table
where [date] BETWEEN 20200820 AND 20200828

The problem is that query plan uses clustered index scan instead of nonclustered seek + keylookup. Plan as follows:
non using clustered index plan

Estimates generated in query plan are fine, statistics are up to date. This date range should deliver about 5mln of rows.
When I provide index hint then this select completes in couple of seconds – without hint it takes couple of minutes to finish.

This is SQL Server 2019 and generaly speaking I noticed that db prefers clustered index scans that using non clustered + keylookups even on bigger tables.

I would rather not using hint because:

  • sometimes I select wider ranges where clustered index scan should be desirable
  • table is used in view and I cannot supply index hint to the view

Is there any explanation why db is not using NC index it this case?

Links to query plans:

Best Answer

It looks like SQL Server is not using that index by default because:

  • it's a filtered index, and
  • your query is parameterized

You can see this warning in the execution plan XML:

<UnmatchedIndexes>
  <Parameterization>
    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" />
  </Parameterization>
</UnmatchedIndexes>
<Warnings UnmatchedIndexes="1" />

SQL Server doesn't know what the parameter values are (because they're in variables), so it can't safely use the filtered index.

One solution is to use index hints (as you mentioned, this isn't ideal).

Another way to work around that is to use dynamic SQL, as described by Jeremiah Peschka here:

Filtered Indexes and Dynamic SQL

I don't know how the filtered index is...filtered. You might be able to get away with embedding the literal on only one of the two values, to limit plan cache bloat.

Related Question