SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Parameter Sniffing In Action

By Nisarg Upadhyay,

SQL Server uses a process called parameter sniffing when it executes a stored procedure that has parameters. When the procedure is compiled or recompiled, the value passed into the parameter is evaluated and used to create an execution plan that will be stored in the plan cache. The process of query compilation, or recompilation, is costly so we want SQL Server to use the plan that is already in the plan cache. This is the normal and expected behavior of SQL Server.

The first time a procedure will be executed with a parameter(s), the plan that was generated by SQL Server will be stored in the plan cache. Even when the procedure is executed with different parameters, the same plan will be used. Let’s assume that data in table is not evenly distributed and one parameter value retrieves 10 rows while another parameter retrieves 10000. In such a case, we want SQL Server to use the optimum plan for data retrieval, but since the procedure is parameterized, it will use the same plan it first compiled. 

In the below example, I will demonstrate parameter sniffing. I am going to run two queries to show you the behavior, using the WideWorldImporters database on SQL Server 2016. The two queries are:

select * from [Sales].[CustomerTransactions] where CustomerID=401  
select * from [Sales].[CustomerTransactions] where CustomerID=976  

In the first query, SQL Server thinks that for "customerID 401", an index scan will be the more appropriate option, instead of using an Index Seek and key lookup.

I have created stored procedure for the same query  and executed it.

exec GetCustomerTransaction 401

Look at the execution plan for customerID 401.

For "customerID 401", 23,233 rows have been returned. Considering the amount of data being retrieved, SQL Server chose to use an Index scan. Let's use another parameter for the same procedure

exec GetCustomerTransaction 976

For "customerID 976", only 250 rows are returned, but SQL Server is still performing an Index Scan. Also, see the difference between estimated number of rows and actual number of rows in the image above.

After reviewing this, the first question that came to my mind was: is parameter sniffing bad?

The answer is: it depends on the query and the amount of data being returned. Each execution of this query may return the results in an acceptable amount of time. It is only when query performance reaches an unacceptable threshold, you may need to do something to prevent it.

 
Total article views: 1395 | Views in the last 30 days: 1395
 
Related Articles
FORUM

executing oracle procedure from sql server

executing oracle procedure from sql server

FORUM

Execute Stored Procedure with Select Statement as Input Parameters

Execute Stored Procedure with Select Statement as Input Parameters

FORUM

stored procedure in SQL server 2005 that takes a lot of time on first execution but after subsequent execution it give instantaneous results regardless of parameter values

stored procedure in SQL server 2005 that takes a lot of time on first execution but after subsequen...

BLOG

SQL Server 2011 “Denali”: Get T-SQL query metadata without executing it

SQL Server 2011 “Denali” introduces couple of new procedures which enable you to read query metadata...

FORUM

problem executing stored procedure having table valued parameters

stored procedure having table valued parameters works fine from SQL server but not from ASP.NET code...

 
Contribute