Avoiding Parameter Sniffing in SQL Server
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
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
And the execution plan is :
This is expected. Next, execute the
procedure with the other parameter.
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.
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.
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.
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