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