Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

dm_exec_query_plan Returning NULL Query Plan

DZone's Guide to

dm_exec_query_plan Returning NULL Query Plan

· Java Zone
Free Resource

What every Java engineer should know about microservices: Reactive Microservices Architecture.  Brought to you in partnership with Lightbend.

I recently hit a scenario (SQL Server 2012 Standard, 11.0.5058) where I was trying to pull out the execution plan for a stored procedure from the plan cache, but the following query was returning a NULL query plan:
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE  text LIKE '%MyStoredProcedure%'
 AND objtype = 'Proc'

Each time I ran the stored procedure, the usecounts was incrementing, but I just could not get the query plan to be returned. Initially I thought I'd found the answer on this blog post:

However, dm_exec_text_query_plan also returned NULL for the plan handle so it was a dead end for this scenario. So, a bit more digging around and came across this question on StackOverflow. This was pretty much the scenario I was experiencing - my stored procedure had a conditional statement that wasn't being hit based on the parameters I was supplying to the stored procedure. I temporarily removed the IF condition, ran it again and hey presto, this time an execution plan WAS returned. Re-instating the condition then, sure enough, made it no longer return the plan via `dm_exec_query_plan`. I tried to create a simplified procedure to reproduce it, with multiple conditions inside that weren't all hit, but a query plan was successfully returned when I tested it - so it wasn't as straight forward as just having multiple branches within a procedure.

I was just starting to suspect it was something to do with temporary table jiggery-pokery that was being done within the conditional statement, and trying to create a very simplified repro when...

This was pretty much exactly the scenario I was hitting. I carried on with my ultra-simplified repro example which shows the full scope/impact of this issue (see below). As noted in the forum post provided above, it's an issue that occurs when using a temp table in this context, but table variables do NOT result in the same behaviour (i.e. testing a switch over to a table variable instead of a temp table sure enough did result in query plan being returned by dm_exec_query_plan ). N.B. It goes without saying, this is not an endorsement for just blindly switching to table variables!

-- 1) Create the simple repro sproc
CREATE PROCEDURE ConditionalPlanTest 
 @Switch INTEGER
AS
BEGIN
 CREATE TABLE #Ids (Id INTEGER PRIMARY KEY)
 DECLARE @Count INTEGER

 IF (@Switch > 0)
  BEGIN  
   INSERT INTO #Ids (Id) VALUES (1)
  END 

 IF (@Switch > 1)
  BEGIN
   INSERT #Ids (Id) VALUES (2)
  END

 SELECT * FROM #Ids
END
GO

-- 2) Run it with a value that does NOT result in all conditions being hit
EXECUTE ConditionalPlanTest 1
GO

-- 3) Check plan cache - no query plan or text query plan will be returned, 
--    usecounts = 1
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 4) Now run it with a different parameter that hits the 2nd condition
EXECUTE ConditionalPlanTest 2
GO

-- 5) Check the plan cache again - query plan is now returned and 
--    usecounts is now 2.
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 6) Recompile the sproc
EXECUTE sp_recompile 'ConditionalPlanTest'
GO

-- 7) Confirm nothing in the cache for this sproc
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 8) This time, run straight away with a parameter that hits ALL conditions
EXECUTE ConditionalPlanTest 2
GO

-- 9) Check the plan cache again - query plan is returned and usecounts=1.
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 10) Now change the sproc to switch from temp table to table variable
ALTER PROCEDURE ConditionalPlanTest 
 @Switch INTEGER
AS
BEGIN
 DECLARE @Ids TABLE (Id INTEGER PRIMARY KEY)
 DECLARE @Count INTEGER

 IF (@Switch > 0)
  BEGIN  
   INSERT INTO @Ids (Id) VALUES (1)
  END 

 IF (@Switch > 1)
  BEGIN
   INSERT @Ids (Id) VALUES (2)
  END

 SELECT * FROM @Ids
END
GO

-- 11) Execute the sproc with the parameter that does NOT hit all the conditions
EXECUTE ConditionalPlanTest 1
GO

-- 12) Check the plan cache - query plan is returned, usecounts=1
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, qp.query_plan, 
    tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 13) CLEANUP
DROP PROCEDURE ConditionalPlanTest
GO

Microservices for Java, explained. Revitalize your legacy systems (and your career) with Reactive Microservices Architecture, a free O'Reilly book. Brought to you in partnership with Lightbend.

Topics:

Published at DZone with permission of Adrian Hills, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}