On occasion you might query sys.dm_exec_query_plan with a valid plan_handle and it will return NULL for the query_plan column.  This is despite seeing the plan_handle in sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, or sys.dm_exec_cached_plans.  In this short post I’ll explain how to replicate this behavior and why you might see it.

Replicating

For this example I’ll be using a stored procedure I created in the AventureWorks2012 database, usp_ExecQueryPlanExample.

Below is the source for usp_ExecQueryPlanExample:

CREATE PROCEDURE usp_ExecQueryPlanExample(@QueryNumber INT)
AS
BEGIN
IF(@QueryNumber = 1)
BEGIN
SELECT * INTO #EmailAddressTemp FROM Person.EmailAddress
SELECT * FROM #EmailAddressTemp
END
ELSE IF (@QueryNumber = 2)
BEGIN
SELECT * INTO #ContactTypeTemp FROM Person.ContactType
SELECT * FROM #ContactTypeTemp
END
END

This procedure selects all rows from Person.EmailAddress into a temp table and returns it, or it selects all rows from Person.ContactType into a temp table and returns it.  Either one of these logic paths is taken based on the @QueryNumber parameter passed in.

In order to pull the newly created proc into the cache we can execute it a couple times as follows (be sure to only execute it for @QueryNumber = 1 for now):

exec usp_ExecQueryPlanExample 1

Now that it should probably be cached I can query sys.dm_exec_procedure_stats to see if it is cached and get the plan_handle:

select b.text, a.* from sys.dm_exec_procedure_stats a cross apply sys.dm_exec_sql_text(a.sql_handle) b where a.database_id = db_id('AdventureWorks2012') and object_id = object_id('usp_ExecQueryPlanExample')

The output of this will be as follows:

If we try looking up that plan_handle with sys.dm_exec_query_plan:

SELECT * FROM sys.dm_exec_query_plan(
0x05000F00D915F5621056BB1D0000000001000000000000000000000000000000000000000000000000000000
)

A NULL is returned for the query_plan:

If this procedure continues to always get called with @QueryNumber = 1 this behavior will continue to be the case.

Let’s try calling this with @QueryNumber = 2 and see what happens

exec usp_ExecQueryPlanExample 2

Now we’ll try getting the plan again:

SELECT * FROM sys.dm_exec_query_plan(
0x05000F00D915F5621056BB1D0000000001000000000000000000000000000000000000000000000000000000
)

By re-running the same query against sys.dm_exec_query_plan we ran earlier (with the same plan_handle) we now get a plan returned.

Why do I get this behavior?

By default SQL Server will try and compile a whole stored procedure and every statement in it at the time it is first called.  All statements in the table will be compiled unless they are nested in an IF block and the statement nested in the block contains a temp table.  So if this example didn’t use a temp table we would not have seen the above behavior.  When you call sys.dm_exec_query_plan it is returning the execution plan as XML and it should contain all the statements associated with the plan; however if there are certain areas of the plan that aren’t generated the plan wouldn’t be complete so instead a NULL is returned.

Where else might I see this?

If you use OPTION(RECOMPILE) hints nested in IF statements when temp tables are not involved you will see similar behavior.

How do I get around this?

Easy.  Query sys.dm_exec_text_query_plan instead.  When you specify the statement_start_offset, and statement_end_offset parameters it will only pull the individual statements.  To get the statement_start_offset and statement_end_offset you will need to join to sys.dm_exec_query_stats.

Using the plan_handle from our example would yield the following:

select
SUBSTRING(c.text, (a.statement_start_offset/2)+1,
((CASE a.statement_end_offset
WHEN -1 THEN DATALENGTH(c.text)
ELSE a.statement_end_offset
END - a.statement_start_offset)/2) + 1) AS statement_text,
convert(XML, b.query_plan)from
sys.dm_exec_query_stats a cross apply
sys.dm_exec_text_query_plan(a.plan_handle, a.statement_start_offset, a.statement_end_offset) b
cross apply sys.dm_exec_sql_text(a.sql_handle) c
where a.plan_handle = 0x05000F00D915F5621056BB1D0000000001000000000000000000000000000000000000000000000000000000

Below are the individual queries and plans returned:

Conclusion

When you query sys.dm_exec_query_plan it attempts to pull the full execution plan for the given plan_handle.  As a result if certain pieces of it are not in the plan cache it will return NULL.  Instead use sys.dm_exec_text_query_plan to only pull the pieces of the plan that exist in the plan cache.  One final thought.  If you retrieve a plan_handle from a long running request in sys.dm_exec_requests you also might run into this, but this is a slightly different scenario where the plan has never been cached before and the proc has not completed.

 

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store:

Free you say?  How can that be?