Friday, July 3, 2015

Query Plan when Stored Procedure refers to Non-Existing Objects

Testing environment: SQL 2014 Developer Edition

I knew about “Deferred Name Resolution” in stored procedures but was not sure how SQL creates and caches query plan. So, I decided to test it out and see for myself.

I create a test stored procedure with an IF statement in it. The code is given below.

--Create test table

CREATE TABLE [dbo].[ObjExists](

       [names] [VARCHAR](100) NULL

)

 
--Create test stored procedure

CREATE PROC usp_testproc

@input INT

AS

BEGIN

   IF @input = 1

   SELECT * FROM [dbo].[ObjExists]

   ELSE

   SELECT * FROM [dbo].[ObjNotExists]

END

 
First branch of code in stored proc refers to an existing table “ObjExists” while the other branch refers to non-existing table “ObjNotExists”.

 
SQL creates the procedure without any errors/warnings about non existing object. I try to get estimated plan for below statement by clicking the “Display Estimated Execution Plan” button in menu.
EXEC usp_testproc 1


It gives error about invalid object but nevertheless gives plan for the part that refers to existing object “ObjExists”.


Estimated Plan:



I then enable “Include Actual Execution Plan” and execute the statement to run stored procedure.

EXEC usp_testproc 1




It runs successfully and gives results. There are no errors/warnings.


Actual execution plan looks like



I then execute below statement where the proc has to go into branch of code where object does not exist.
EXEC usp_testproc 0

SQL throws error as in screen shot and no actual execution plan.

 

Using the query below, I bring up cached plans.

--Query to get cached plans

SELECT cp.objtype AS ObjectType,

OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

cp.usecounts AS ExecutionCount,

st.TEXT AS QueryText,

qp.query_plan AS QueryPlan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

WHERE st.text LIKE '%usp_testproc%'

 

Query plan shows null for that stored procedure in row 1 in screen shot. SQL did not store query plan for it.




 I then alter the procedure such that the second branch of code also contains existing object (ObjectExists_1).

ALTER PROC usp_testproc

@input INT

AS

BEGIN

   IF @input = 1

   SELECT * FROM [dbo].[ObjExists]

   ELSE

   SELECT * FROM [dbo].[ObjectExists_1]

END

I get a valid estimated plan for below statement:

 
EXEC usp_testproc 0

Estimated Plan: Notice that plan shows two branches, one for each branch in IF statement.



I execute the stored procedure and it runs successfully along with giving an actual query plan.

EXEC usp_testproc 0

Actual plan:

 
Cached plan:

 
I click on the XML plan to bring up details of the plan.
 


When all objects exist, SQL caches the query plan for reuse.

Notice the difference between Estimated plan, Cached plan, and Actual plan. Actual plan only shows the branch it executes. Cached plans show all possible code branches. Estimated plan tries to create query plan for all branches but stops where it encounters the first error.

In the stored procedure code, I reverse the sequence of objects such that ObjNotExists comes first and ObjExists comes later and get estimated plan. Notice that the estimated execution plan does not show plan for either branch. SQL stops getting estimated plan at the first error it encounters.

ALTER PROC usp_testproc

@input INT

AS

BEGIN

  IF @input = 1

  SELECT * FROM [dbo].[ObjNotExists]

  ELSE

  SELECT * FROM [dbo].[ObjExists]

END

 
Estimated Plan:

Compare this with estimated plan below when ObjExists was in the code before ObjNotExists. It encountered error after it had got the plan for first branch containing ObjExists.

Now let us test it with temp tables:

DROP PROC usp_testproc

GO

CREATE PROC usp_testproc

AS

BEGIN

   SELECT * INTO #temp FROM [dbo].[ObjExists]

   SELECT * FROM #temp

END

 
I try to get Estimated Plan for below statement 
 

EXEC usp_testproc

 SQL gives error about invalid temp table but, still gives estimated plan:

Estimated plan:
 

When I execute the proc, it runs successfully.



Actual execution plan:

 SQL also caches this plan in plan cache.

Conclusion: SQL creates stored procedure even if object does not exist. It even executes procedure successfully as long as it does not go into the code branch with non-existing objects although it does not cache the plan. Exception to this are objects that get created as part of the execution.