How does Sql Server compile execution plans for logic flow in stored procedures?

Question

I need to use the same query twice but have a slightly different where clause. I was wondering if it would be efficient to simply call the same stored proc with a bit value, and have an IF... ELSE... statement, deciding on which fields to compare.

Or should I make two stored procs and call each one based on logic in my app?

I'd like to know this more in detail though to understand properly. How is the execution plan compiled for this? Is there one for each code block in each IF... ELSE...?

Or is it compiled as one big execution plan?

Answer 1

You are right to be concerned about the execution plan being cached.

Martin gives a good example showing that the plan is cached and will be optimized for a certain branch of your logic the first time it is executed. After the first execution that plan is reused even if you call the stored procedure (sproc) with a different parameter causing your executing flow to choose another branch. This is very bad and will kill performance. I've seen this happen many times and it takes a while to find the root cause.

The reason behind this is called "Parameter Sniffing" and it is well worth researching.

A common proposed solution (one that I don't advice) is to split up your sproc into a few tiny ones. If you call a sproc inside a sproc that inner sproc will get an execution plan optimized for the parameter being passed to it.

Splitting up a sproc into a few smaller ones when there is no good reason (a good reason would be modularity) is an ugly workaround. Martin shows that it's possible for a statement to be recompiled by introducing a change to the schema. I would use OPTION (RECOMPILE) at the end of the statement. This instructs the optimizer to do a statement recompilation taking into account the current value of all variables: not only parameters but local variables are also taken into account which can makes the difference between a good and a bad plan.

To come back to your question of constructing a query with a different where clause according to a parameter. I would use the following pattern:

WHERE
(@parameter1 is null or col1 = @parameter1  ) 
AND
(@parameter2 is null or col2 = @parameter2  ) 
...
OPTION (RECOMPILE)

The down side is that the execution plan for this statement is never cached (it doesn't influence caching up to the point of the statement though) which can have an impact if the sproc is executed many time as the compilation time should now be taken into account. Performing a test with production quality data will give you the answer if it's a problem or not.

The upside is that you can code readable and elegant sprocs and not set the optimizer on the wrong foot.

Another option to keep in mind is that you can disable execution plan caching at the sproc level (as opposed to the statement level) level which is less granular and, more importantly, will not take into account the value of local variables when optimizing.

More information at http://www.sommarskog.se/dyn-search-2005.html http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Answer 2

It is compiled once using the initial value of the parameters passed into the procedure. Though some statements may be subject to deferred compile in which case they will be compiled with whatever the parameter values are when eventually compiled.

You can see this from running the below and looking at the actual execution plans.

CREATE TABLE T
  (
     C INT
  )

INSERT INTO T
SELECT 1 AS C
UNION ALL
SELECT TOP (1000) 2
FROM   master..spt_values
UNION ALL
SELECT TOP (1000) 3
FROM   master..spt_values

GO

CREATE PROC P @C INT
AS
    IF @C = 1
      BEGIN
          SELECT '1'
          FROM   T
          WHERE  C = @C
      END
    ELSE IF @C = 2
      BEGIN
          SELECT '2'
          FROM   T
          WHERE  C = @C
      END
    ELSE IF @C = 3
      BEGIN
          CREATE TABLE #T
            (
               X INT
            )

          INSERT INTO #T
          VALUES     (1)

          SELECT '3'
          FROM   T,
                 #T
          WHERE  C = @C
      END

GO

EXEC P 1

EXEC P 2

EXEC P 3

DROP PROC P

DROP TABLE T 

Running the 2 case shows an estimated number of rows coming from T as 1 not 1000 because that statement was compiled according to the initial parameter value passed in of 1. Running the 3 case gives an accurate estimated count of 1000 as the reference to the (yet to be created) temporary table means that statement was subject to a deferred compile.