SQL Performance: Avoiding Parameter Sniffing in SQL Server

References: http://www.sql-server-performance.com/2013/avoiding-parameter-sniffing-in-sql-server/

Avoiding Parameter Sniffing in SQL Server

By Dinesh Asanka

Parameter sniffing is when SQL Server compiles a stored procedure’s execution plan with the first parameter that has been used and then uses this plan for subsequent executions regardless of the parameters.

First let’s look at a worked example. Assume we have table as below.

CREATE Table TblData
(ID INT IDENTITY PRIMARY KEY ,
Name Char(100),
Loc char(5),
CreateDateTime datetime DEFAULT getdate(),
Status char(2) default 'AC')
CREATE INDEX idx_Loc on tblData(Loc)
 

the table is populated with sample data as below:

 INSERT INTO TblData
(Name,Loc)
VALUES
('Test1','LocA')
GO 100000
INSERT INTO TblData
(Name,Loc)
VALUES
('Test1','LocB')
GO 10
 

Let us run following two queries and analyze the relevant query plans.

 SELECT * FROM tblData
WHERE Loc = 'LocA'
 

It is quite obvious as we are selecting more 90% of data, rather than using the non-clustered index it will be better to use the Clustered Index Scan.

SELECT * FROM tblData
WHERE Loc = 'LocB'
 

Now let us create a stored procedure with a Loc parameter.

CREATE PROC GetDataForLoc
(@Loc as CHAR(5))
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM tblData
WHERE Loc = @Loc
END
 

Now let us execute this with one of the parameters.

GetDataForLoc 'LocA'

And the execution plan is :

This is expected. Next, execute the procedure with the other parameter.

GetDataForLoc 'LocB'

Now, this should take non clustered index as we experienced when we executed the query before. However, since the procedure is cached from the first execution, subsequent executions will use the same execution plan.

This behavior is called parameter sniffing as SQL Server will compile a stored procedure’s execution plan with the first parameter that has been used.

Solution

Clear the procedure cache.

First of all, let us check existing query plan. For this you can use, following query.

 SELECT OBJECT_NAME(s.object_id) SP_Name
 ,eqp.query_plan
FROM sys.dm_exec_procedure_stats s
CROSS APPLY sys.dm_exec_query_plan (s.plan_handle) eqp
WHERE DB_NAME(database_id) = 'ParamSnif'
 

By running following script, we can clear the procedure cache. If you run the previous script again, no records will be returned which means there is no cache for the relevant proc. So the next execution will create new query plan.

sp_recompile 'GetDataForLoc'

Create Procedure WITH RECOMPILE

You can add a WITH RECOMPILE option when creating a stored procedure as shown below. This will cause the stored procedure to be recompiled every time it is executed.

CREATE PROC [dbo].[GetDataForLoc]
(@Loc as CHAR(5)) WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM tblData
WHERE Loc = @Loc
END
 

After this you can execute stored procedure with two different parameters.

EXEC [dbo].[GetDataForLoc] @Loc = N'LocB'
EXEC [dbo].[GetDataForLoc] @Loc = N'LocA'
 

Unlike last time, these two executions will have two different execution plans. If you examine the cached query plan there will be no saved query plans.

3. Execute with RECOMPILE

If you add WITH RECOMPILE to the execute statement, SQL Server will recompile the procedure before running it and will not store the query plan.

EXEC [dbo].[GetDataForLoc] @Loc = N'LocA' WITH RECOMPILE

Creating Procedure with OPTIMIZE FOR option

This is a query hint which will allow users to specify the value of a parameter that should be used when compiling the procedure, regardless of the actual value of the parameter that users are passing into the store procedure.

 CREATE PROC [dbo].[GetDataForLoc]
(@Loc as CHAR(5))
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM tblData
WHERE Loc = @Loc
OPTION ( OPTIMIZE FOR (@Loc = 'LocA') )
END
 

FREE SQL SERVER WEBINARS

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s