Local Variables vs. Parameterized Stored Procedures in SQL Server

Kendra (from Brent Ozar) has a nice video that explains why when testing a parametrized stored procedure’s script by replacing the parameters with local variables_ for example:

The SP:

ALTER PROCEDURE dbo.parameterizeMe

       @CustomerNo int

AS

       SELECT COUNT(*) from dbo.Customers Where CustomerNo=@CustomerNo

GO

exec dbo.parameterizeMe 0

Testing it with:

DECLARE @CustomerNo int=0;

SELECT COUNT(*) from dbo.Customers Where CustomerNo=@CustomerNo

Why when doing so, sometimes the execution plans differs and the performance becomes worse? …

She said that the reason is that because the local variable does not behave as the parameter in the stored procedure does à that is because in the case of the stored procedure, the Query Optimizer estimated the number of rows much much more accurately than the execution plan of query with the local variable à and that is because in the case of stored procedure parameter, the Query Optimizer sniffs the parameter that went in and looked at how many rows actually has the @CustmerNo of value 5, however in the case of the local variable, the Query Optimizer estimates the number of rows that it thinks it will process by: multiplying the overall density of the CustomerNo column by the number of rows in the table, that _most of the times_ gets a number far from the actual number of rows the variable @CustomerNo  of value 5 has in the table, which makes the Query Optimizer generates a wrong execution plan… by the way you can get those statistics using something like:

DBCC SHOW_STATISTICS('Customers','KI_Customers_CustomerNo')

So, and to test the stored procedure using the same execution plan it will use in production, you can create a temporary stored procedure! … as the following:

CREATE PROCEDURE #parameterizeMe

       @CustomerNo int

AS

       SELECT COUNT(*) from dbo.Customers Where CustomerNo=@CustomerNo

GO

Watch this for a better way to explain the subject:Local Variables vs. Parameterized Stored Procedures in SQL Server