Apr 23, 2014

Parameter sniffing problem in sql server with examples and Solutions

Some time we may face problem for some parameters query is executing fast and some other parameters executing slow. After some time for same parameter, it is running slow which was executing fast previously.  It means your stored procedure may be victim of parameter sniffing.

As we know query parametrization is one of the best feature as far as query optimization is concerned. By using this feature queries doesn't get compile each time and it reuses the cached plan.  One of the major side effects of the query parametrization is parameter sniffing.  To understand parameter sniffing you has to good understating of query parametrization.  

What is query parametrization?

Let us assume there is user table in your application and your application passes user id to get the user information. For example:

SELECT * FROM tblUser WHERE ntUserID = 100

To execute this query efficiently, sql server analyze best execution plan and then saved the plan in the plan cache. Next time when your application executes this query:

SELECT * FROM tblUser WHERE ntUserID = 101

Now sql server think both queries are exactly same except parameter so it would be better to use saved cached plan instead of paying extra cost to recompile and analyzing new best execution plan for second queries since both should have same best execution plan. In this way sql server reduced the recompilation time!!

In the above case it true but it is not always true. I'm explaining this by a very good example:

I'm creating two tables and inserting some records into it by following script:

CREATE TABLE tblUser(
     ntUserID BIGINT PRIMARY KEY IDENTITY,
     vcUserName VARCHAR(50)
)

CREATE TABLE tblOrder(
     ntOrderID BIGINT PRIMARY KEY IDENTITY,
     ntUserID BIGINT REFERENCES tblUser,
     dtOrderDate DATETIME
)

NSERT INTO tblUser VALUES('Scott'),('Greg'),('Alain')

INSERT INTO tblOrder
SELECT 1,GETDATE() - severity FROM sys.messages
UNION ALL
SELECT 2, GETDATE()

Creating a index on the table tblOrder:

CREATE INDEX NCI_ntUserID ON tblOrder(ntUserID)

Creating a stored procedure:

CREATE PROCEDURE uspGetOrderByUser(
     @ntUserID BIGINT
)
AS
BEGIN

     SELECT
          DAY(O.dtOrderDate),
          MAX(O.ntUserID)
     FROM tblUser U
     INNER JOIN tblOrder O
     ON U.ntUserID = O.ntUserID
     INNER JOIN tblUser UU
     ON U.ntUserID < UU.ntUserID
     WHERE U.ntUserID = @ntUserID
     GROUP BY DAY(O.dtOrderDate)
     ORDER BY 2

END

I'm also turning on statistics IO and time:

SET STATISTICS TIME ON
SET STATISTICS IO ON

Now I'm going to execute this stored procedure by passing the parameter 1:

EXECUTE dbo.uspGetOrderByUser 1

In message table I got:

Logical read for tblorder: 1152
SQL Server Execution Times: CPU time = 109 ms,  elapsed time = 103 ms.

And its execution plan is:


This is the best execution plan according query optimizer. Now I'm going the clear the cached plan:

DBCC FREEPROCCACHE

Now I'm going to execute same stored procedure with parameter 2:

EXECUTE dbo.uspGetOrderByUser 2

This time query got complied for parameter 2 and saved the best query plan for parameter 2 in the plan cache. Its execution plan is:


We can observe for parameter 2 plan is little different than parameter 1.  With parameter 2 there is key look up and index seek while not with parameter 1.
  
Now what will happen if we will execute the same stored procedure with parameter 1. As I told sql server will reuse cached plan.  Lets see what happens:

EXECUTE dbo.uspGetOrderByUser 1

In the message table I'm getting:

Logical read for tblorder: 590448
SQL Server Execution Times: CPU time = 640 ms,  elapsed time = 778 ms.

Both logical read and execution time is much higher than previous execution for same parameter 1.
  
This problem is called parameter sniffing.  If we will check the execution plan:


It is exactly same as parameter 2. Sql server is reusing execution plan for parameter 2 instead of generating best plan for parameter 1.  

In the execution plan if you will right click on select operator and click on properties you will find:



It says above execution plan has complied for value 2 while actual value passed in the run time is 1.

That is why second time with same parameter query is running slow.

Solution of parameter sniffing:

1. If you find, for two different parameters, there is big difference in the execution plan create two stored procedure for each type of execution plan. For example:

CREATE PROCEDURE uspGetOrderByUserForParameter1(
     @ntUserID BIGINT
)
AS
BEGIN

     SELECT
          DAY(O.dtOrderDate),
          MAX(O.ntUserID)
     FROM tblUser U
     INNER JOIN tblOrder O
     ON U.ntUserID = O.ntUserID
     INNER JOIN tblUser UU
     ON U.ntUserID < UU.ntUserID
     WHERE U.ntUserID = @ntUserID
     GROUP BY DAY(O.dtOrderDate)
     ORDER BY 2

END

GO

CREATE PROCEDURE uspGetOrderByUserForParameter2(
     @ntUserID BIGINT
)
AS
BEGIN

     SELECT
          DAY(O.dtOrderDate),
          MAX(O.ntUserID)
     FROM tblUser U
     INNER JOIN tblOrder O
     ON U.ntUserID = O.ntUserID
     INNER JOIN tblUser UU
     ON U.ntUserID < UU.ntUserID
     WHERE U.ntUserID = @ntUserID
     GROUP BY DAY(O.dtOrderDate)
     ORDER BY 2

END

2. Check the all possible execution plan for different parameters. For example: For parameters 1 to 100, execution plan is EP1 and for for parameter  101 to 110, execution plan is EP2. You also observe most of the cases your application only passes parameters from 1 to 100 and it passes 101 to 200 rarely. So it would be better to save execution plan EP1 in the plan cache instead of EP2. We can force to save EP1  by using query hints OPTIMIZE FOR. Take any parameters from 1 to 100, say 1 and optimize the procedure for parameter 1. For examples:

CREATE PROCEDURE uspGetOrderByUser(
     @ntUserID BIGINT
)
AS
BEGIN

     SET NOCOUNT ON

     SELECT
          DAY(O.dtOrderDate),
          MAX(O.ntUserID)
     FROM tblUser U
     INNER JOIN tblOrder O
     ON U.ntUserID = O.ntUserID
     INNER JOIN tblUser UU
     ON U.ntUserID < UU.ntUserID
     WHERE U.ntUserID = @ntUserID
     GROUP BY DAY(O.dtOrderDate)
     ORDER BY 2
     OPTION(OPTIMIZE FOR(@ntUserID = 1))

END    

Now if I execute this stored procedure by passing parameter 2 event that it will generate the best execution plan for parameter 1. For example:

EXECUTE dbo.uspGetOrderByUser 2

Execution Plan:


As we know above execution plan is good for parameter 1 not for 2.

Note: You can use following script to check the plan in plane cache:

SELECT
     objtype,
     text,
     usecounts,
     query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS P
WHERE text LIKE N'%uspGetOrderByUser%'
     AND TEXT NOT LIKE  '%sys.dm_exec_cached_plans%'

No comments:

Post a Comment