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
]]>