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.

Execution Time difference between a SP and same code executed as a batch

Have you ever encountered situation when a stored
procedure runs more time than the same code executed as a batch
via Query Analyzer/SSMS?

This might be due to "Parameter Sniffing".
Now in English:
It`s relevant only to SPs with input parameters(s).
When a SP executed for a first time,SQL Server will
create optimized execution plan that reflects provided value
of the input parameter.
In other words SQL Server will create execution plan based
and optimized according to input parameter.
And SQL Server will use this generated plan for
every SP call.

Let`s suppose that the created execution plan uses a
Clustered Index Scan.The 'Clustered Index Scan' was
chosen according to statistics (taking into account the values
distribution of filtered column).
Now,consider situation when we provide different value for
the input parameter and 'Clustered Index Seek' can be used.
But 'Clustered Index Scan' will be used instead of 'seek',due
to existing execution plan.

In case of executing the same code in 'Query Analyzer',
SQL Server cannot know(sniff) the value of local variable,so
it will create execution plan according to average distribution
values.

There are 5 methods to fix this issue:
1) Use Local Variable instead of input.
2) Use Recompile option.
3) Use OPTIMIZE FOR query hint.
4) Use OPTION (RECOMPILE) for some statement(s) in your SP.
5) Use sp_create_plan_guide,in case you don`t have permissions
to change the SP.

How you can find out if SQL Server used a sniffed parameter?
If the parameter is sniffed, the information can be seen by setting
the following option before execution the SP.
set showplan_xml on.

and look for this in generated XML Showplan:

ParameterList>
ColumnReference Column="@p" ParameterCompiledValue="(XXXX)" />
ParameterList>

where XXXX specifies the initial parameter value that has been
used to build the execution plan.

See the following link,it contains some examples with very good
explanations:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html