Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

When I execute the query exactly as written above, SQL Server scans the first index, resulting in 189,703 logical reads and a 2-3 second duration.

When I inline the @Id variable and execute the query again, SQL Server seeks the second index, resulting in only 104 logical reads and a 0.001 second duration (basically instant).

I need the variable, but I want SQL to use the good plan. As a temporary solution I put an index hint on the query, and the query is basically instant. However, I try to stay away from index hints when possible. I usually assume that if the query optimizer is unable to do its job, then there is something I can do (or stop doing) to help it without explicitly telling it what to do.

So, why does SQL Server come up with a better plan when I inline the variable?

With a local variable:

Outcomes

When you use a literal value, and your plan isn't a) Trivial and b) Simple Parameterized or c) you don't have Forced Parameterization turned on, the optimizer creates a very special plan just for that value.

When you use a parameter, the optimizer will create a plan for that parameter (this is called parameter sniffing), and then reuse that plan, absent recompile hints, plan cache eviction, etc.

When you use a local variable, the optimizer makes a plan for... Something.

I'm going to assume that you have skewed data, that you don't want to use query hints to force the optimizer what to do, and that you need to get good performance for all possible input values of @Id. You can get a query plan guaranteed to require just a few handfuls of logical reads for any possible input value if you're willing to create the following pair of indexes (or their equivalent):

It's designed to take advantage of the ordering of the indexes to find the min or max value with a few logical reads. The CROSS JOIN is there to get correct results when there aren't any matching rows for the @Id value. Even if I filter on the most popular value in the table (matching 6.5 million rows) I only get 8 logical reads:

Table 'MyTable'. Scan count 2, logical reads 8

Here's the query plan:

Both index seeks find 0 or 1 rows. It's extremely efficient, but creating two indexes might be overkill for your scenario. You could consider the following index instead:

Now the query plan for the original query (with an optional MAXDOP 1 hint) looks a bit different:

The key lookups are no longer necessary. With a better access path that should work well for all inputs you shouldn't have to worry about the optimizer picking the wrong query plan due to the density vector. However, this query and index won't be as efficient as the other one if you seek on a popular @Id value.

This incurs a risk that the table or indices may change in the future such that this optimization becomes dysfunctional, but it's available if you need it. Hopefully someone can offer you a root cause answer, as you requested, rather than this workaround.