MS SQL: Parameter Sniffing
A while ago one of our clients reported a bit of a peculiar performance problem when accessing a certain report
in one of their systems. Generally they could retrieve the report in no time, but all of a sudden it took about
3 minutes to retrieve a relatively small amount of data.
Strangely enough the older entries for this report could still be retrieved almost instantly.
So I went through all the motions to find a solution and analyzed the database, found it to be terribly
designed and poorly maintained. The developer didn't even bother to create indexes for any of the foreign keys.
Like a
good Jedi I added the appropriate indexes for the
appropriate fields, but that just made things worse, it now oddly enough even took longer to retrieve data.
I decided to have a look at the guilty stored procedure only to astonishingly find that the
stored procedure runs super fast within SSMS
(SQL Server Management Studio).
At this point I got up and phoned an exorcist, they came out and prayed over the source code and anointed the server
while a bunch of scary looking evil spirits seeped out of the sides of the server box like toxic gases.
Unfortunately that didn't solve the problem at all
(fixed an unusual buzzing sound I've been hearing lately though), but all
of a sudden I heard the voice of my old master Obi-Wan Kenobi telling me to use the force
(google).
I opened my browser and did some research using Google and found the real reason - parameter sniffing.
Uhm okay, maybe I stretched the truth a bit in the last few paragraphs
(they're complete BS, except for the google part).
What is parameter sniffing all about?
When sending parameters to a stored procedure for the first time, SQL generates an execution plan
based on those parameters and stores it for reuse
(generating these plans can be resource intensive & lengthy at times, therefore
storing it will improve performance).
Note that we wont necessarily only have one execution plan for a stored procedure, SQL will
generate multiple plans based on
various settings.
Now sometimes it happens that the stored execution plan sucks, so instead of being efficient it might turn
out to be terribly inefficient.
So how do we solve this problem?
One popular solution is to reassign parameters to local variables like seen below
(theory being that SQL won't/can't create a cached execution plan thanks to the local variables).
CREATE PROCEDURE [dbo].[ProcedureName]
@Param1 DATETIME,
@Param2 DATETIME
AS
BEGIN
DECLARE @LocalParam1 DATETIME
DECLARE @LocalParam2 DATETIME
SET @LocalParam1 = @Param1
SET @LocalParam2 = @Param2
-- Logic
END
Another option is to disable parameter sniffing alltogether for a stored procedure by creating it using
the "WITH RECOMPILE" option like seen in the next snippet.
CREATE PROCEDURE [dbo].[ProcedureName]
@Param1 DATETIME,
@Param2 DATETIME
WITH RECOMPILE
AS
BEGIN
-- Logic
END
One can also disable sniffing per query using OPTION (RECOMPILE).
CREATE PROCEDURE [dbo].[ProcedureName]
@Param1 DATETIME,
@Param2 DATETIME
AS
BEGIN
SELECT * FROM SometTable
WHERE SomeDate BETWEEN @Param1 AND @Param2
OPTION (RECOMPILE)
END
Or optimize for a typical parameter value.
CREATE PROCEDURE [dbo].[ProcedureName]
@Param1 DATETIME,
@Param2 DATETIME
AS
BEGIN
SELECT * FROM SometTable
WHERE SomeDate BETWEEN @Param1 AND @Param2
OPTION (OPTIMIZE FOR (@Param1 = '2012/02/02'))
END
As of MSSQL 2008 its possible to use the optimize for "UNKNOWN" hint which
instructs the query optimizer to use statistical
data instead of the initial values for all local variables when the query is compiled and optimized.
CREATE PROCEDURE [dbo].[ProcedureName]
@Param1 DATETIME,
@Param2 DATETIME
AS
BEGIN
SELECT * FROM SometTable
WHERE SomeDate BETWEEN @Param1 AND @Param2
OPTION (OPTIMIZE FOR (@Param1 UNKNOWN, @Param2 UNKNOWN))
END
I am not going to go into too much detail about the pros/cons of each solution, but you can read a lot more about
this issue over
here.
Posted by - Christoff Truter
Date - 2012-05-21 22:38:48
Post comment