Index utilization issues in SQL Server queries
Your query is looking for the minimum maturity_date from rows where the as_of_date is greater than a given value.

The optimizer has to choose between two main execution strategies:


Scan an index ordered by maturity_date ASC until it finds the first row where as_of_date &gt;= @as_of_date_from.

As soon as the query processor finds this first row, the scan of the index stops. This is a point often missed when reading execution plans: an index scan does not necessarily always scan the whole index.

If the first qualifying row can be found quickly, a partial scan of the ordered index is a great strategy for your query. On the other hand, this is a poor strategy if the first row matching the as_of_date predicate occurs toward the end of the index (in maturity_date order).

The optimizer therefore makes an assessment of how many rows from the index it will have to scan before it finds one that matches the as_of_date predicate. This assessment is based on an assumption of uniform distribution (often incorrect in practice).

The plan you provided shows that the Index Scan had to read 1,101,479 rows (in maturity date order) before it found the first one matching the as_of_date predicate.
Seek an index (with as_of_date as the leading key) to find all rows where as_of_date &gt;= @as_of_date_from, then compute the minimum maturity_date found in those rows.

This is a good strategy if the as_of_date predicate matches relatively few rows. It is not so good if many rows are expected to qualify. The expected cost of this strategy depends on the value of @as_of_date_from, and statistical information about the as_of_date column.

Your query uses a local variable, so the optimizer cannot 'see' the value contained in it, and makes a guess at how selective the unknown value will be.

For an inequality predicate, the default guess is that 30% of the rows will qualify. For a table of 130 million rows, the optimizer will therefore guess that 30% * 130,000,000 = 39,000,000 rows will match. This is a lot of rows to compute the minimum maturity_date on, the the optimizer prefers the other strategy.

You can allow the optimizer to 'see' the value inside the variable at execution time by adding an OPTION (RECOMPILE) hint to the query. This enables the Parameter Embedding Optimization, at the cost of having to recompile the query plan every time the query is executed.

If the @as_of_date_from variable value varies a lot between executions, using the OPTION (RECOMPILE) hint may be worthwhile for you. The optimizer may still choose the partial ordered scan strategy, depending on its assessment of the relative costs of the two strategies.




In any case, you should change the data type of the @as_of_date_from to datetime to match the as_of_date column to avoid an implicit conversion.

The data type of @tenor_from should probably be datetime not varchar(20). Or change the query to CONVERT(varchar(20),MIN(spc.maturity_date),103). The current CONVERT makes no sense.