IF Statement Branching and Parameter Sniffing

on July 12, 2017

It can be tricky to remember that SQL Server doesn’t short circuit on IF statements in stored procedures. Even when you know this, sometimes it’s hard to recognize the impacts.

Here’s an example

First, the parameter is sniffed…

The first time that dbo.ReviewFlags is executed after the database comes online, it’s with an invalid parameter, like this:

EXEC dbo.ReviewFlags @Flag = null;
GO

This is caught by the IF block, hits the RAISERROR, and goes down to the THROW block, and the output is:

Msg 50000, Level 11, State 1, Procedure ReviewFlags, Line 8 [Batch Start Line 70] @Flag must be a value between 1 and 5

But even though SQL Server didn’t execute the SELECT statement, it still compiled it. And it also cached the plan.

Then, the “sniffed” plan is re-used

When we run the procedure again with a valid value for @Flag…

EXEC dbo.ReviewFlags @Flag = 1;
GO

We get a very undersized execution plan for @Flag = 1. The row estimate is super low.

We can confirm that this plan was optimized for @Flag = null by looking at the properties of the SELECT operator in the actual plan:

We compiled for @Flag = NULL, even though this statement can never execute with that value for @Flag

We’d get a different plan if we optimize for @Flag = 1

For testing purposes, I can execute the procedure WITH RECOMPILE to tell SQL Server to not re-use the plan, and compile this fresh for me. (I only use this for testing, and note that if you have nested procedures or dynamic SQL, it will re-use sub-plans.)

EXEC dbo.ReviewFlags @Flag = 1 WITH RECOMPILE;
GO

This time I get a better plan, optimized for @Flag = 1…

How to work around this?

Realistically, the first thing to consider is how likely the procedure is to be run with invalid values. If this procedure is typically executed from an application with a drop-down that doesn’t contain the invalid values and RAISERROR is unlikely to catch an unloved parameter value, you probably don’t care much (unless you work for NASA).

If you do want to make sure that the SELECT statement is optimized only when it’s executed, there are a variety of options, with different pros and cons.

You could use OPTION RECOMPILE on the statement, but the trade-off is extra CPU and a more difficult time monitoring performance with the execution plan cache long term.

Personally, I would usually rather do a different solution.

mmmm, Dynamic SQL

One option is a dynamic SQL style solution that allows plan re-use, like this:

DROP PROC IF EXISTS dbo.ReviewFlags;
GO
CREATE PROC dbo.ReviewFlags
    @Flag TINYINT 
AS
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        IF ISNULL(@Flag,0) NOT IN (1,2,3,4,5)
            RAISERROR ('@Flag must be a value between 1 and 5', 11, 1);

        DECLARE @dsql NVARCHAR(MAX) = N'
        SELECT
            fnbd.FirstNameId,
            fn.FirstName,
            fnbd.Gender
        FROM agg.FirstNameByYearState as fnbd
        JOIN ref.FirstName as fn on
            fnbd.FirstNameId = fn.FirstNameId
        WHERE fnbd.Flag = @Flag';

        EXEC sp_executesql @stmt = @dsql, @params = N'@Flag TINYINT', @Flag=@Flag;
    END TRY
    BEGIN CATCH
        THROW
    END CATCH
GO

The parameterized dynamic SQL still allows “healthy” (hopefully) parameter sniffing on the value passed in for @Flag, but that statement will only compile the first time that sp_executesql is actually executed. Therefore it will only “sniff” allowed parameters.

Similar to this, you could use a separate stored procedure for the SELECT statement.

Forcible sniffing with OPTIMIZE FOR

Another option is to direct SQL Server what value to “sniff”. A perma-sniff, if you will. We can do this with an “optimize for” hint:

DROP PROC IF EXISTS dbo.ReviewFlags;
GO
CREATE PROC dbo.ReviewFlags
    @Flag TINYINT 
AS
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        IF ISNULL(@Flag,0) NOT IN (1,2,3,4,5)
            RAISERROR ('@Flag must be a value between 1 and 5', 11, 1);

        SELECT
            fnbd.FirstNameId,
            fn.FirstName,
            fnbd.Gender
        FROM agg.FirstNameByYearState as fnbd
        JOIN ref.FirstName as fn on
            fnbd.FirstNameId = fn.FirstNameId
        WHERE fnbd.Flag = @Flag
            OPTION (OPTIMIZE FOR (@Flag = 1))
    END TRY
    BEGIN CATCH
        THROW
    END CATCH
GO