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

Question

I need to use the same query twice, but I have a slightly different where clause. I was wondering if it is possible to simply call the same stored process with a bit value and have an IF ... ELSE ... statement that determines which fields to compare.

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

I would like to know this in more detail, although correctly understood. How is the execution plan compiled? Is there one for every block of code in every IF ... ELSE ...?

Or is it compiled as one big execution plan?

Answer 1

You are right to worry about the execution plan being cached.

Martin gives a good example showing that the plan is cached and will be optimized for a specific branch of your logic on first run. After the first execution, this plan is reused, even if you call the stored procedure (sproc) with another parameter, as a result of which the executable thread selects another branch. This is very bad and will kill performance. I have seen this happen many times, and it takes some time to find the root cause.

The reason for this is called the “Forced parameter” , and it is worth exploring.

The general suggested solution (which I do not recommend) is to split your sproc into several tiny ones. If you call sproc inside sproc, then internal sproc will get an execution plan optimized for the passed parameter.

Splitting sproc into several smaller ones when there is no good reason (modularity is a good reason) is an ugly workaround. Martin shows that you can recompile the instruction by introducing a circuit change. I would use OPTION (RECOMPILE) at the end of the statement. This instructs the optimizer to recompile taking into account the current value of all variables: not only the parameters are taken into account, but also local variables, which can distinguish between a good and a bad plan.

Let's get back to your question about building a query with another where clause according to the parameter. I would use the following template:

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

The downside is that the execution plan for this statement is never cached (it does not affect caching to the point of the statement, though), which can have an effect if sproc is executed many times, since the compilation time should now be taken into account. Performing a test with product quality data will give you an answer if this is a problem or not.

On the plus side, you can code readable and elegant sprocs without having to set the optimizer on the wrong foot.

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

Additional 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 to the procedure. Although some statements may be delayed compilation, in which case they will be compiled with any parameter values ​​when they are eventually compiled.

You can see this from the below and see 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 

Starting Case 2 shows the approximate number of lines following from T as 1 not 1000 , because this statement was compiled according to the initial parameter value passed to 1 . Triggering event 3 gives an accurate count of 1000 , since a link to a temporary table (not yet created) means that the statement obeys pending compilation.