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 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”.
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.
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:
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:
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
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
EXEC usp_testproc
Estimated plan:
When I execute the proc, it runs successfully.
Actual execution plan:
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.
No comments:
Post a Comment