What is Parameter Sniffing, How it can affect the Performance, and What is the Solution

What is parameter sniffing? How it can affect the performance. What is solution of parameter sniffing?

Let us try to answer these questions,

Whenever a stored procedure is executed for the first time, its execution plan is created. An execution plan for a stored procedure is created the first time a stored procedure is executed. When the SQL SERVER Database engine compiles a stored procedure it looks at the parameters being passed to the stored procedure and then creates an execution plan based on these parameters. The process of looking at parameter values when compiling a stored procedure is called "Parameter sniffing". Sometimes Parameter sniffing can lead to inefficient execution plans, especially when a stored procedure is called with parameter values that have different cardinality.

Parameter sniffing can be considered a plus to the performance of the system, but in those cases where it's not, various mechanisms can be used to avoid it. One of the simplest is to use local variables. You can also use the OPTIMIZE FOR query hint or the WITH RECOMPILE query hint. In extreme cases you can force specific execution plans onto procedures.

We can also say that Parameter sniffing is a process that occurs when executing a stored procedure for the first time that allows SQL Server to build an effective query plan. Normally that is a good thing, but in some cases can actually hurt performance of the query.

According to the white paper Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 Published in the Microsoft Site:

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation"

When a SP is not in the procedure cache, when it is executed the query optimizer needs to compile the SP to create an execution plan. In order to do this the query optimizer needs to look at the parameters that are passed and the body of the SP to determine the best method to go about processing the SP. When the query optimizer looks at the SP's parameters, to help determine how to optimize the execution of the SP, it is known as parameter sniffing.

Before I show you with an example about parameter sniffing, you need to know that the query execution plan generated by the SQL Server depends on lot of factors; parameter sniffing is just one of them. So the execution plan I show here might be different from the execution plan that you will generate.

Let's look at the following code

USE AdventureWorks

GO

CREATE PROCEDURE GetCustOrders (@FirstCust int, @LastCust int)

AS

SELECT * FROM Sales.SalesOrderHeader

WHERE CustomerID between @FirstCust and @LastCust;

This procedure GetCustOrders accepts two parameters @FirstCust int, @LastCust int . These parameters are passed in the where clause of the query to get the customer orders. Now look at the execution plan of the procedure by execution this procedure by passing different parameters.

USE AdventureWorks

GO

DBCC FREEPROCCACHE

EXEC GetCustOrders 1, 1000

I had used the statement DBCC FREEPROCCACHE to clear the procedure cache so that query optimizer should make a new plan for this execution instead of using any existing one.

Look at the

Actual number of Rows 31465 and

Estimated number of Rows 31465

Here you can see that query optimizer performed a Clustered Index scan operation to get the records.

Now execute EXEC GetCustOrders 600,610 and see the execution plan

It uses the Clustered index scan for this operation as well, because this plan was present in the procedure cache.

Check the number of actual rows and estimated rows,

Actual number of Rows 31465

Estimated number of Rows 31465

Note the in both cases the actual and estimated number of rows are same.

Now clear the procedure cache and execute the procedure with smaller values

DBCC FREEPROCCACHE

EXEC GetCustOrders600,610

Exmaince the actual number of rows and estimated number of rows, they are different now, because optimizer created a new plane for this

Query optimizer performed the seek operation. This means that by passing the smaller range of values , you can get the Index Seek operation, depending on your execution. If you execute the procedure with smaller values first and then execute the procedure with larger values. Then you can get Index seek for both of the executions.

The first compile of the Procedure generates the execution plan depending on the the parameters passed to the procedure and that plan is kept in the procedure cache for use of future executions of that procedure.

How to eliminate parameter sniffing?

There are three methods to avoid parameter sniffing,

By using local variables in the procedure By using RECOMPILE Query Hint Query hint OPTIMIZE FOR

By using Local variables

Parameter sniffing can be disabled by using the local variables in the stored procedure.

CREATE PROCEDURE GetCustOrders (@FirstCustint, @LastCustint)

AS

DECLARE @FC int/td>

DECLARE @LC int

SET @FC = @FirstCust

SET @LC = @LastCust

SELECT * FROM Sales.SalesOrderHeader

WHERE CustomerID BETWEEN @FC AND @LC

You can create two local variables @FC AND @LC that takes the values from parameters of the stored procedure and then pass those values to the query. In this way the actual values of the parameters are no longer contained in the BETWEEN clause in the SELECT statement, instead only those local variables are present.

This method of removing the parameter sniffing problem doesn’t mean you will get a best plan for each execution of the SP

By using Recompile

Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure

USEAdventureWorks

GO

CREATE PROCEDURE GetCustOrders(@FirstCust int,@LastCust int)

WITH RECOMPILE

AS

SELECT*FROMSales.SalesOrderHeader

WHERECustomerIDbetween@FirstCustand@LastCust ;

By using Optimize For

Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization

CREATE PROCEDUREGetCustOrders(@FirstCustint,@LastCustint)

WITH RECOMPILE

AS

SELECT*FROMSales.SalesOrderHeader

WHERECustomerID between @FirstCustand @LastCust

OPTION (OPTIMIZE FOR (@FirstCust UNKNOWN,@LastCust UNKNOWN));

go

You can read more about Query Hints from msdn

SQL Load Generator

A really nice tool for Load testing on SQL Server. SQL Load Generator is used to generate a load simulating several different users. It is is used to run multiple concurrent queries against SQL Server. The user can choose the number of concurrent queries to run, provide different queries, choose SQL or domain accounts, and provide application name settings.

read more or if you want to download SQL Load Generator you need to go on codeplex SQL Load Generator

Unable to start T-SQL Debugging. Could not attach to SQL Server Process

How to Enable TSQL Debugging in SQL SERVER ?


Click on the menu Debug >> Start Debugging
An error will be produced "Unable to start T-SQL Debugging. Could not attach to SQL Server Process"

Here is the solution to get rid of this error

Expand the SQL Sever Management Studio
Expand the Security

Right Click on the Logins
Click on New Login
A new window will appear to define the Login

Specify the Login Name.
Specify the Server roles.

Check the sysadmin Server role and then press OK.
Now Debug your Query /SP Debug >> Start Debugging

This can also be done using TSQL.
Here is the Way to do this using TSQL

sp_addsrvrolemember 'Domain\Name', 'sysadmin'
GO

EXEC master..sp_addsrvrolemember @loginame = N'Dmain-Name\Administrator', @rolename = N'sysadmin'
GO

if you are not using any domain, then try your system name.