Why does SQL Server use a better execution plan when I inline the variable?

I have a SQL query that I am trying to optimize:

DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'

SELECT 
  Id,
  MIN(SomeTimestamp),
  MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @Id
  AND SomeBit = 1
GROUP BY Id

MyTable has two indexes:

CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes
ON dbo.MyTable (SomeTimestamp ASC)
INCLUDE(Id, SomeInt)

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp)

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?

In SQL Server, there are three common forms of non-join predicate:
With a literal value:

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = 1;

With a parameter:

CREATE PROCEDURE dbo.SomeProc(@Reputation INT)
AS
BEGIN
    SELECT COUNT(*) AS records
    FROM   dbo.Users AS u
    WHERE  u.Reputation = @Reputation;
END;

With a local variable:

DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

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.

If you were to run this query:

DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

The plan would look like this:

And the estimated number of rows for that local variable would look like this:

Even though the query returns a count of 4,744,427.

Local variables, being unknown, don't use the 'good' part of the histogram for cardinality estimation. They use a guess based on the density vector.

SELECT 5.280389E-05 * 7250739 AS [poo]

That'll give you 382.86722457471, which is the guess the optimizer makes.

These unknown guesses are usually very bad guesses, and can often lead to bad plans and bad index choices.
Fixing It?

Your options generally are:

• Brittle index hints

• Potentially expensive recompile hints

• Parameterized dynamic SQL

• A stored procedure

• Improve the current index

Your options specifically are:

Improving the current index means extending it to cover all the columns needed by the query:

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp, SomeTimestamp, SomeInt)
WITH (DROP_EXISTING = ON);

Assuming that Id values are reasonably selective, this will give you a good plan, and help the optimizer by giving it an 'obvious' data access method.
More Reading

You can read more about parameter embedding here:

• Parameter Sniffing, Embedding, and the RECOMPILE Options, by Paul White

• Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables), Kendra Little

StackExchange Database Administrators Q#206815, answer score: 48