sql-server add tag
rainbolt (imported from SE)
I have a SQL query that I am trying to optimize:

    DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
    FROM dbo.MyTable
    WHERE Id = @Id
      AND SomeBit = 1

`MyTable` has two indexes:

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

    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?
Top Answer
meme (imported from SE)
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)
    	SELECT COUNT(*) AS records
    	FROM   dbo.Users AS u
    	WHERE  u.Reputation = @Reputation;

With a **local variable**:
    DECLARE @Reputation INT = 1
    SELECT COUNT(*) AS records
    FROM   dbo.Users AS u
    WHERE  u.Reputation = @Reputation;

When you use a **literal** value, and your plan isn't a) [Trivial][1] and b) Simple Parameterized or c) you don't have [Forced Parameterization][2] 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][3]), 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][4]. 

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:

    ON dbo.MyTable (Id, SomeBit)
    INCLUDE (TotallyUnrelatedTimestamp, SomeTimestamp, SomeInt)

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][8], by Paul White
 - [Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)][9], Kendra Little

  [1]: https://dba.stackexchange.com/questions/206304/why-does-a-plan-with-full-optimization-show-simple-parameterization
  [2]: https://www.brentozar.com/blitzcache/forced-parameterization/
  [3]: http://www.sommarskog.se/query-plan-mysteries.html
  [4]: https://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/
  [5]: https://i.stack.imgur.com/uJLI6.jpg
  [6]: https://i.stack.imgur.com/iiSdl.jpg
  [7]: https://i.stack.imgur.com/A5CZh.jpg
  [8]: https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
  [9]: https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/
Answer #2
Joe Obbish (imported from SE)
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):

    CREATE INDEX GetMinSomeTimestamp ON dbo.MyTable (Id, SomeTimestamp) WHERE SomeBit = 1;
    CREATE INDEX GetMaxSomeInt ON dbo.MyTable (Id, SomeInt) WHERE SomeBit = 1;

Below is my test data. I put 13 M rows into the table and made half of them have a value of `'3A35EA17-CE7E-4637-8319-4C517B6E48CA'` for the `Id` column.

    CREATE TABLE dbo.MyTable (
    	Id uniqueidentifier,
    	SomeTimestamp DATETIME2,
    	SomeInt INT,
    	SomeBit BIT,
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2;
    SELECT '3A35EA17-CE7E-4637-8319-4C517B6E48CA', CURRENT_TIMESTAMP, 0, 1, REPLICATE('Z', 100)
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2;

This query might look a little strange at first:

    DECLARE @Id UNIQUEIDENTIFIER = '3A35EA17-CE7E-4637-8319-4C517B6E48CA'

    FROM (
    	SELECT TOP (1) SomeInt, Id
    	FROM dbo.MyTable
    	WHERE Id = @Id
    	AND SomeBit = 1
    	ORDER BY SomeInt DESC
    ) si
    	SELECT TOP (1) SomeTimestamp, Id
    	FROM dbo.MyTable
    	WHERE Id = @Id
    	AND SomeBit = 1
    	ORDER BY SomeTimestamp ASC
    ) st;

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:

[![enter image description here][1]][1]

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:

    CREATE INDEX CoveringIndex ON dbo.MyTable (Id) INCLUDE (SomeTimestamp, SomeInt) WHERE SomeBit = 1;

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

[![enter image description here][2]][2]

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.

> Table 'MyTable'. Scan count 1, logical reads 33757

  [1]: https://i.stack.imgur.com/cRe9Q.png
  [2]: https://i.stack.imgur.com/fRHSP.png

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.