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




Related Articles :

11 Responses to “Avoiding Parameter Sniffing in SQL Server”

  1. Hi

    SELECT * FROM tblData
    WHERE Loc = ‘LocB

    is missing a final single quote

  2. I think the best way around this is to just use local variables in your stored procedures. This is also the traditional way and works in most versions.

  3. In the past we sometimes had this similar problem. a Stored Procedure running forever. We solved it by added a local parameter for the use in the stored procedure. Like the example below:

    CREATE PROC GetDataForLoc2
    (@Loc as CHAR(5))
    AS
    BEGIN
    SET NOCOUNT ON
    declare @internalLoc as CHAR(5)
    SET @internalLoc =@Loc

    SELECT * FROM tblData
    WHERE Loc = @internalLoc
    END
    GO

    While using this version I see the stored procedure is still using the wrong execution plan using value LocB. Adding WITH RECOMPILE has no effect on this version of the stored procedure. Strange…?

  4. Thanks for the article. Learned a new concept (OPTIMIZE FOR option ) today.

  5. SQL2008 and newer allows optimize for unknown. It will make a query plan not based on that particular parameter value.

    Syntax:
    OPTION (OPTIMIZE FOR (@LocA UNKNOWN))

  6. During my career I have seen many times when the query being executed (sp or adhoc) uses a “bad” or unoptimized query plan, which can cause less than spectacular performance. and I have used this approach when we had a procedure that used “country_code” as a parameter, and it never failed that when the queries shifted from Canada (CN) to China (CH), the optimizer was still looking for CN

    But to play “devils advocate”, what is your cost of recompiling the procedure every time it is executed? What, if any, negative impact could manifest as a result (missing statistics, increase in compile times, etc)

  7. There is another option – to use OPTION (RECOMPILE) at the statement level. Thus your hint will work even if the data change:

    CREATE PROC GetDataForLoc (
    @Loc AS char(5)
    )
    AS
    SET NOCOUNT ON;

    SELECT *
    FROM tblData
    WHERE Loc = @Loc
    OPTION (RECOMPILE)
    GO

  8. Vincent Snijders Reply June 20, 2013 at 9:18 pm

    What about using optimize for unknown?

  9. What are the risks associated with having parameter sniffing in your system? Performance or security?

  10. Greg Larson’s article covers some other options and is worth reading https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

    I often use the “Bait and switch” method that is well covered here: http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/

    i.e. Pass in parameters to the proc, declare variables in the proc and set them equal to the IN parameters. Then make use of the variables in the query, not the parameters.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |