Date index SEEK is chosen when using OPTION RECOMPILE but not with OPTION OPTIMIZE FOR

I have a table containing 10 years worth of 'package scans'. Somebody scans a package and it records the date and username. Let's pretend for now that retaining 10 years of data actually has a purpose.

I have a page to show a summary for the past week, so clearly I only want to read 1 week's worth of data.

Here's the query, to be run in SSMS twice, once with a hardcoded recent date and again with an old date in 2013. It's originally a parameterized query, but in SSMS I'm replacing @p0 with the date:

SELECT [t0].[VerifyDate], [t0].[PackageId], [t0].[Username]
FROM [dbo].[PackageVerification] AS [t0]
INNER JOIN [dbo].[Package] AS [t1] ON [t1].[PackageId] = [t0].[PackageId]

WHERE ([t1].[PackageStatus] <> 99) AND ([t0].[VerifyDate] > @p0)   
ORDER BY [t0].[VerifyDate] DESC

Before I execute it, I'd like to introduce my date index.

Now my date index is not on my PackageVerification table, but instead is on a 'helper view' which performs the same join seen above. The query above is able to magically use this indexed view because I have SCHEMABINDING enabled.

CREATE NONCLUSTERED INDEX [IX_Helper_PackageVerification_USER_SCAN_HISTORY] ON [dbo].[Helper_PackageVerification]
(
    [VerifyDate] DESC,
    [PackageStatus] ASC
)
INCLUDE (
    [VerifyDateDate],
    [Username]
)

When I run the query in SSMS with an old and new date it uses scan or seek as expected. The threshold seems to be somewhere around 2015. So anything remotely recent should definitely be using a seek. Here's the results of that:

When I run it as a parameterized query from my application I always get a full scan, which for some reason uses a parallelized plan.

At least it's using my helper index.

I'm actually not sure why I don't get parameter sniffing for this. I always pass a very recent date so I would have thought it may have preferred a scan but I'm fine with it choosing the above plan given the circumstances. There's a million+ rows and it takes about 150ms.

Incidentally th

Using OPTIMIZE FOR is not the same as OPTION (RECOMPILE). The former uses supplied parameter values in cardinality estimation for a plan that might be reused with other parameter values. The recompile option embeds the runtime parameter value, and produces a disposable plan that will never be reused.

The OPTIMIZE FOR plan therefore needs to ensure correct operation for all possible values. The recompile plan can use additional optimizations that are only valid for the present value. It can also use optimizations that only work with literal values e.g. pushing a filter past a window function.

This matters in your case because when the OPTIMIZE FOR plan matches the indexed view, it adds additional IS NOT NULL residual predicates on VerifyDate and PackageStatus:

The recompile plan can remove this logic because the supplied values are known to be not null. The presence of these extra implied predicates are enough to prevent index matching for a seek. It's usually best to ensure source columns are constrained to be not null, or explicitly rejected in the indexed view definition to minimize this sort of thing.

Now, the optimizer has a wide variety of plan choices for your queries. One indication of that is the number of statistics objects loaded - 17. Small differences in the path taken through the optimizer can produce different outcomes.

Automatic indexed view matching is a neat feature, technically, but it does have limitations. SQL Server needs to add some things and apply particular rewrites to achieve matching, which can have unexpected side-effects (note the reversed @p1 predicate above). The post-match plan is also not always completely cleaned up to match what a query written against the view would produce. These are not bugs, just implementation details.

I normally advise people to write queries against the view directly and specify a NOEXPAND hint, where this is practical. You might well find that testing your queries written that way would produce the outcomes you are looking for.

Related articles I have written:

• Parameter Sniffing, Embedding, and the RECOMPILE Options

• Indexed Views and Statistics

• Another Reason to Use NOEXPAND hints in Enterprise Edition

StackExchange Database Administrators Q#290245, answer score: 5