dm_exec_query_plan returning NULL query plan

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 query shown below 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:

Getting a NULL query_plan from dm_exec_query_plan, scratched head for a bit, then found this - https://t.co/DGcF2qMvK4 #sqlserver
— Adrian Hills (@AdaTheDev) August 11, 2014

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…

@AdaTheDev read this one? https://t.co/g7XKObB24E
— Arthur Olcot (@sqlserverrocks) August 12, 2014

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 '%ConditionallanTest%'  
    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

sql server

See also