Pages

Wednesday, 28 March 2012

SQL Parameter Sniffing and Performance Issues

Recently a client had a problem with a a stored procedure taking over 1 and a half minutes to execute. This is usually the case when a stored procedure is written that works perfectly well until massive amounts of data are stored by the application and required to be processed by the stored procedure.

On examining the stored procedure in question however, it had been very well written and there was nothing obvious about the code that would cause a problem. To test this, I extracted out the query from the stored procedure and ran it in a new query window in SQL Management Studio. The query ran in 3 seconds! I ran the stored procedure manually again, and it took over a minute and a half again....

The problem it turns out is Parameter Sniffing.

For those of you who are unfamiliar with parameter sniffing, the definition from Microsoft is:

“Parameter sniffing” refers to a process whereby SQL Server’s execution environment “sniffs” the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word “current” refers to the parameter values present in the statement call that caused a compilation or a recompilation.

The query contained in the stored procedure required just one input parameter, however the parameter was used multiple times. The results from the stored procedure could also vary wildly depending on the parameter value being passed in each time the stored procedure is called.

The solution

The solution was actually fairly straightforward: disable parameter sniffing for that stored procedure.

To do this, you need to create a local variable in your stored procedure, and set the value of that variable to the value of your input parameter.

For example, your original stored procedure may look like:


 CREATE PROCEDURE GetEvents
     @EventId int
 AS
 BEGIN
     SELECT Title 
     FROM dbo.Events
     WHERE Id = @EventId
 END


To disable parameter sniffing, you would change the above example to:


 CREATE PROCEDURE GetEvents
     @EventId int
 AS
 BEGIN
    DECLARE @LocalEventID int
    SET @LocalEventID = @EventId
    SELECT Title 
    FROM dbo.Events
    WHERE Id = @LocalEventID
 END


1 comment:

  1. Their strengths are that they are pretty tough as they are usually made from a kind of plastic glass. Since they are relatively simple technology they are also quite cheap, so a resistive touchscreen will keep costs down.
    Buy LOL Account

    Cheap League of Legends Elo Boost

    ReplyDelete