Local Variable Estimates: Why the Density Vector Wins

Put a literal into a local variable and the query's estimate can change. Local variable estimates usually lack the value-specific histogram information available to a literal. A density-based estimate can be reasonable for balanced data and poor for skewed data.

Build a Deliberately Skewed Column

The optimizer needs an estimate before choosing access paths and join strategies. A literal can reveal which status value the predicate requests. A local variable normally does not supply that value during ordinary compilation. SQL Server then needs a value-independent estimate instead of treating all statuses as equally common in reality.

I compare estimated and actual rows before attributing the difference to parameter sniffing. A declared local variable and a procedure parameter have different compilation behavior. Calling every value-related estimate problem parameter sniffing makes the next fix less precise.

Create a disposable table with many Common rows and a small Rare group. The numeric quantities here define the input distribution; they are not measured execution results. Include a payload so the status index does not cover every selected column.

CREATE TABLE dbo.LocalEstimateDemo
(RowID int NOT NULL PRIMARY KEY, StatusCode varchar(10) NOT NULL,
 Payload char(100) NOT NULL);
WITH Digits AS
(SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))d(n)),
Numbers AS
(SELECT a.n+10*b.n+100*c.n+1000*d.n+1 AS n
 FROM Digits a CROSS JOIN Digits b CROSS JOIN Digits c CROSS JOIN Digits d)
INSERT dbo.LocalEstimateDemo
SELECT n,CASE WHEN n<=20 THEN 'Rare' ELSE 'Common' END,
       REPLICATE('x',100) FROM Numbers;
CREATE INDEX IX_LocalEstimateDemo_Status
ON dbo.LocalEstimateDemo(StatusCode);
UPDATE STATISTICS dbo.LocalEstimateDemo
    IX_LocalEstimateDemo_Status WITH FULLSCAN;

Compare Literal and Local Variable Estimates

Enable the actual plan in SSMS, then run both statements with the same requested status. Inspect the access operator's estimated and actual row counts. Read the returned data as well. The predicate asks the same business question even when its estimate changes.

SELECT RowID,Payload FROM dbo.LocalEstimateDemo
WHERE StatusCode='Rare';
DECLARE @s varchar(10)='Rare';
SELECT RowID,Payload FROM dbo.LocalEstimateDemo
WHERE StatusCode=@s;

Keep the variable type aligned with the column type. An implicit conversion can add a separate issue to the comparison. Do not change the projection or add another predicate between tests and then attribute the entire plan difference to the local variable.

A plan can still be identical despite different estimates. The available indexes and cost thresholds determine whether the estimate changes the chosen strategy. This demonstration provides a distribution that makes the estimate difference visible; it does not promise one particular physical plan on every installation.

Read the Density Vector

DBCC SHOW_STATISTICS exposes the histogram and density vector for the chosen statistic. Use the single-column StatusCode density entry for the equality comparison. The StatusCode, RowID entry describes the combination with the clustered key and is not interchangeable with that value.

DBCC SHOW_STATISTICS
('dbo.LocalEstimateDemo','IX_LocalEstimateDemo_Status')
WITH DENSITY_VECTOR;
DBCC SHOW_STATISTICS
('dbo.LocalEstimateDemo','IX_LocalEstimateDemo_Status')
WITH HISTOGRAM;
SELECT COUNT_BIG(*) AS TotalRows,
       COUNT(DISTINCT StatusCode) AS DistinctStatuses
FROM dbo.LocalEstimateDemo;

For this simple, non-null, two-value distribution, the density concept is approximately one divided by distinct values. Multiply the displayed density by the relevant row count to work through the unknown-value equality estimate. Here that is 0.5 times 10,000 rows, or 5,000 rows, while the Rare histogram step holds 20. The resulting average does not describe the rare group specifically.

Use the value actually displayed by DBCC rather than inventing a measured estimate. Cardinality-estimator version, filtering, joins, and null handling affect more complex calculations. A density-times-rows explanation is useful here because the example deliberately removes those complications.

Let Recompile Replace Local Variable Estimates

OPTION RECOMPILE compiles the statement for its current execution. SQL Server can use the current local variable value during that compilation. Compare the resulting estimate and access path with the literal version.

DECLARE @s varchar(10)='Rare';
SELECT RowID,Payload FROM dbo.LocalEstimateDemo
WHERE StatusCode=@s
OPTION (RECOMPILE);

The hint belongs on the statement that needs it. Recompiling an entire procedure can impose unnecessary work on unrelated statements. Keep the scope narrow and measure the complete workload before adopting it.

Compilation consumes CPU on each execution. A statement called repeatedly can exchange one problem for another when recompile is applied without evidence. Consider the query's frequency, execution cost, and variability. A good estimate is useful, but it is not a free resource.

Compare a Procedure Parameter

A parameterized procedure can use a parameter's value during compilation. That introduces plan reuse behavior distinct from the local-variable example. Run the procedure with Rare and Common values and inspect the plans and execution conditions.

CREATE PROCEDURE dbo.ReadLocalEstimateDemo
    @s varchar(10)
AS
BEGIN
 SET NOCOUNT ON;
 SELECT RowID,Payload FROM dbo.LocalEstimateDemo WHERE StatusCode=@s;
END;
GO
EXEC dbo.ReadLocalEstimateDemo @s='Rare';
EXEC dbo.ReadLocalEstimateDemo @s='Common';
GO

SQL Server 2022 and later can use Parameter Sensitive Plan optimization for eligible predicates under the required compatibility level. That can produce a dispatcher and variants rather than one reused plan. Check the actual behavior instead of assuming every current installation follows the older single-plan model.

Copying a parameter into a local variable can hide useful value information. It is not a universal cure for plan sensitivity. It replaces one estimation behavior with another, so document the intended trade-off before making that change.

Recognize When Local Variable Estimates Are Acceptable

If statuses are evenly distributed, a density estimate can be close enough for every value. A stable plan can serve that workload well without extra compilation. The existence of a local variable is not itself a performance defect.

I focus on cases where the estimate error changes a costly decision. Look at memory grants, join selection, lookup volume, and spills. A numerical difference that leaves an efficient plan unchanged does not demand an intervention. Which concrete part of the execution gets worse because of this estimate?

Test more than the rare value. An intervention that improves Rare can worsen Common, especially if it forces one narrow access path. The workload includes both distributions, and its execution frequency determines their combined cost.

Preserve the Evidence for the Choice

Record compatibility level, statistics update time, parameter or variable value, and actual plan with the comparison. Keep the same dataset and projection across tests. Use repeated controlled executions when comparing duration, without clearing shared caches on a busy server.

Choose between ordinary reuse, targeted recompile, and another supported plan strategy using that evidence. Check statistics quality before changing compilation behavior. The density vector explains the unknown-value estimate; it does not automatically prescribe the final application design.

A histogram also describes the leading statistics column only. Before multiplying density by rows, verify the density entry belongs to StatusCode alone. Do not choose a density for a combined key because its number looks closer to the desired result. The comparison should explain the optimizer's available information, not select whichever statistic supports a preferred conclusion. Local variable estimates need comparison with actual rows for the same requested value. Evaluate the plan consequence before changing compilation behavior to improve local variable estimates.

Related reading on this blog: Filtered Statistics: Fixing Estimates for Skewed Data and Parameter Sniffing and OPTION (RECOMPILE).

A local variable estimate is not a measurement of its current value, it is a compile-time distribution assumption whose cost depends on the resulting plan.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.