Local Variables and Execution Plan

Do you use Local Variables in the WHERE clause ?
If yes, there is something important you should know.

As you might know, the SQL Server builds the execution plan
by taking into account the values you provide as filter.

In case you use input parameters or literals as filters in your
queries, the SQL Server can know what the values are
(parameter sniffing), but in case of using local variables,
the SQL Server does not know the actual values and builds
the execution plan by supposing that 30% of the table`s rows
will be returned by the query. (A hard coded guess)

According to this, the SQL Server optimizer can choose to use
Hash Match instead of Nested Loop or Table/Index scan
instead of Index seek. This can lead to inefficient
execution plan.

Let`s see an example:


DECLARE @StartOrderDate datetime
SET @StartOrderDate = '20040731'
SELECT *
FROM Sales.SalesOrderHeader as h
INNER JOIN Sales.SalesOrderDetail as d 
                                 ON h.SalesOrderID = d.SalesOrderId
WHERE h.OrderDate >= @StartOrderDate

If you execute this statement above , you will see in the execution
plan that the SQL Server expects 9439.5 rows and this is 30% out
of total rows in the SalesOrderHeader table.This because the
SQL Server do not know the value of the @StartOrderDate
parameter and use hard coded guess of 30%.
Also Merge Join operator is used in the plan.

Now execute the same statement with OPTION(RECOMPILE)
in order to allow to the SQL Server to 'sniff' the value of the
@StartOrderDate local parameter and use it to estimate cardinality
and cost during optimization of the query.
GO

DECLARE @StartOrderDate datetime
SET @StartOrderDate = '20040731'
SELECT *
FROM Sales.SalesOrderHeader as h
INNER JOIN Sales.SalesOrderDetail as d 
                              ON h.SalesOrderID = d.SalesOrderId
WHERE h.OrderDate = @StartOrderDate
OPTION(RECOMPILE)


Now the SQL Server uses Nested Loops and expects 40 rows
from the SalesOrderHeader table(See 'Filter' operator)

You can see more examples in Itzik Ben-Gan`s  article
or in the following article.