Look at the following 3 scripts:

-- 1. Hardcoded
SELECT
    *
FROM
    Sales.Orders
WHERE
    DateAndTime < '19900101'

-- 2. Variable
DECLARE @FilterDate DATETIME = '19900101'
SELECT
    *
FROM
    Sales.Orders
WHERE
    DateAndTime < @FilterDate

-- 3. Stored Procedure
CREATE PROCEDURE Sales.GetOrders
    @FilterDate DATETIME2(0) 
AS
    
    SELECT
        *
    FROM
        Sales.Orders
    WHERE
        DateAndTime < @FilterDate
GO
EXECUTE Sales.GetOrders '19900101'

All three run the same query : The first uses a hard coded filter value, the second uses a variable, and the third uses a Stored Procedure.

Same logical process but they are compiled in a very different way leading to huge different performance outcomes.

What is parameter sniffing?

When a query is compiled, the optimizer doesn’t know the value of variables. This is very important, so I’ll add some exclamation points!!!!!!!

For instance, in the following query:

-- 2. Variable
DECLARE @FilterDate DATETIME = '19900101'
SELECT
    *
FROM
    Sales.Orders
WHERE
    DateAndTime < @FilterDate

The optimizer doesn’t know the value of

@FilterDate

. Only after compilation is finished and the execution plan is be executed will SQL Server calculate the value of

@FilterDate

.

However, there are a few exclusions to the above rule (that the optimizer doesn’t know the value of variables) for instance: Stored Procedures, sp_executesql and a parameterized query.

In these special cases, SQL Server knows (sniffs) the values of the input parameters and uses these values when compiling the statements! This process is called parameter sniffing.

For example, in the following query:

-- Stored Procedure
CREATE PROCEDURE Sales.GetOrders
    @FilterDate DATETIME2(0) 
AS
    
    SELECT
        *
    FROM
        Sales.Orders
    WHERE
        DateAndTime < @FilterDate
GO
EXECUTE Sales.GetOrders '19900101'

The optimizer at compile time knows

@FilterDate = '19900101'

.

What happens if we run later on the Stored Procedure with a different input? For example:

EXECUTE Sales.GetOrders '20000101'

This leads us to our second rule:

When parameter sniffing is applied, the same execution plan is used regardless of the input values. Therefore, the first run of a Stored Procedure is critical, the input parameters of the first run will dictate the values used at compile time and hence on the outcome execution plan which stays in cache for future run!! So in our case, SQL Server will use the same execution plan compiled for the first run and won’t create a new plan for the following runs.

Cons and Pros of Parameter sniffing:

Pro #1:

The obvious and most definite pro of parameter sniffing is that only one compilation is needed to serve a whole range of values – we’re saving so many compilations!

Con #1:

That’s the con as well! One compilation means one execution plan for any value passed. So we’re actually reusing the same execution plan without optimizing it for that specific value. Yes, in most cases it’s OK but sometimes that’s really bad!

Look at the following scenario:

Suppose the Orders table contains many orders from 2007 till 2011. The first run of the Stored Procedure (from above): Sales.GetOrders will dictate the execution plan. So let’s first run it with a very selective value (very few rows are returned):

EXECUTE Sales.GetOrders '20000101'

You can see in the Execution Plan that the optimizer estimated correctly that almost no rows would return.

However, when running the following:

EXECUTE Sales.GetOrders '20120101'

The optimizer uses the same execution plan and therefore estimates miss correctly that 1 row would return. As you can see, the estimated number of rows is very different than the actual number of rows – causing the optimizer to use Lookup for many rows – a very destructive plan!

Con #2:

When an input parameter value is changed inside the Stored Procedure, the optimizer isn’t aware of the change and refers to the original value prior to the change. Look at the following code:

ALTER PROCEDURE Sales.GetOrders
    @FilterDate DATETIME2(0) 
AS
    IF (@FilterDate IS NULL)
        SET @FilterDate = SYSDATETIME()
        
    SELECT
        *
    FROM
        Sales.Orders
    WHERE
        DateAndTime < @FilterDate
GO
EXECUTE Sales.GetOrders NULL

At compile time, the optimizer isn’t aware of the change to @FilterDate. Therefore, when running the Stored Procedure with NULL, the optimizer compiles the SELECT statement evaluating @FilterDate to be NULL and not SYSDATETIME(). You can see in the Execution Plan, the optimizer estimates no rows to return although the whole table is returned.

Of course, there are many ways to surmount these cons, I’ll go over them in my next post.

Happy New Year!

The post Pro and Cons of Parameter Sniffing appeared first on Madeira Data Solutions.