madd0.com

Have you heard of parameter sniffing? I hadn’t either, so let me explain shortly.

The parameters of a SQL query determine often (if not always) the number of lines that it will have to handle. It is therefore logical that the database server—I’m currently working with SQL Server 2005 and 2008—would want to optimise them by studying their parameters before establishing an execution plan. This is what parameter sniffing is all about.

The problem

Parameter sniffing doesn’t always work as you’d expect.

Yesterday I spent the better part of my afternoon trying to figure out why an isolated query was very fast, while the same query, executed within a stored procedure was extremely slow. It just so happens that the server was sniffing parameters and ending up with an execution plan that was far from optimal.

The solution

The good news is that the solution to my problem was simple.

Instead of using the stored procedure’s parameters in the queries within it, all you have to do is store their values in local variables and use the latter instead.

If you have a stored procedure that is suddenly performing badly and that looks something like this:

CREATE PROCEDURE MySP
   @UserName nvarchar(20)
AS
BEGIN
    SELECT name, email
    FROM Users
    WHERE username = @UserName
END

All you have to do is a small change like this:

CREATE PROCEDURE MySP
   @UserName nvarchar(20)
AS
BEGIN
    DECLARE @UserNameLocal nvarchar(20)
    SET @UserNameLocal = @UserName

    SELECT name, email
    FROM Users
    WHERE username = @UserNameLocal
END

And problem solved!

However, this doesn’t mean that you should systematically use local variables in all of your stored procedures. Let the server do its work and use the tip above only if you have verified that you really know better.

If you want to test parameter sniffing by yourselves, I suggest you take a look at the blog of the SQL Server Query Optimization Team, in which you will find a very complete post with examples.

(Photo: StefanB)