I was asked this question the other day on linkedin and figured it might be good to write a post on it considering the confusion around the topic. The question was
I always read that in case of multiple execution paths, execution plan is saved for the first ever executed path. So if my SP has following logic if @x=1 select * from x else select * from y so execution plan will be saved either for table X or table Y. I found many links supporting this,http://dba.stackexchange.com/questions/9835/using-if-in-t-sql-weakens-or-breaks-execution-plan-caching as per this we should have multiple SPs for each execution path.
The basic outline for the question is if there is a procedure that executes different code blocks based on conditional IF parameter being passed in the input. Does SQL create a new plan for each condition or try to reuse the first plan? In order to understand this we need to look at the plan cache which is where SQL stores the execution plans. The below script which was provided by the OP helps identify what’s actually happening.
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'x'
)
DROP TABLE x;
GO
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'y'
)
DROP TABLE y;
GO
CREATE TABLE x (
id INT PRIMARY KEY identity(1, 1)
,val NVARCHAR(20)
)
INSERT INTO x (val)
VALUES ('a')
,('b')
,('c')
,('d')
,('e')
,('f')
CREATE TABLE y (
id INT PRIMARY KEY identity(1, 1)
,val1 NVARCHAR(20)
,val2 NVARCHAR(20)
)
INSERT INTO y (
val1
,val2
)
VALUES (
'aa'
,'aa'
)
,(
'bb'
,'bb'
) GO
IF object_id('test') IS NOT NULL
DROP PROCEDURE test;
GO
CREATE PROCEDURE dbo.test @id INT
AS
BEGIN
SET NOCOUNT ON;
SELECT @id % 2;
IF (@id % 2) = 0
SELECT *
FROM dbo.x
WHERE ID = @Id;
ELSE
SELECT *
FROM dbo.y
WHERE ID = @Id;
END GO
DBCC FREEPROCCACHE GO
EXEC test 1;--Notice PK_Y exec test 2; --Notice PK_X go
If you run the above script with Show Actual execution plan you will see the plan as below when executing the procedures individually.
For input value 1
For input value 2
As can be seen from the above, SQL is querying from different tables based on the input parameters. So does this means a new plan was created? It obviously doesn’t mean that the plan was reused, Right? Wrong!!!
When looking at the Actual execution plan we are seeing what the optimizer went ahead and executed, this doesn’t mean it’s the plan that was compiled. It’s only showing us the part within the original plan that was being used. To see what the original plan was run the statement below
SELECT *
FROM sys.dm_exec_cached_plans c
OUTER APPLY sys.dm_exec_query_plan(c.plan_handle) d
WHERE objtype = 'Proc'
AND dbid = DB_ID()
Notice the use count is 7 indicating the same plan is being used regardless of the input parameter.
So the question becomes what does the original plan look like?
Based on the input parameter SQL is simply executing different branches of the plan and that is what we see when we are running the Show Actual Execution plan. If you really want to see the behavior try passing NULL as the input and you will see the combined execution plan when viewed under Show Estimated Plan.
Please Consider Subscribing
