Query plan returns NULL when using SQL Server DMV sys.dm_exec_query

By: Bhavesh Patel | Comments | Related: > Query Plans

Problem

I was working with SQL Server DMV sys.dm_exec_query_plan to get the query plan, but I was getting "NULL" returned instead of the query plan. In this tip we look at examples of this issue and how you can get the query plan when this occurs.

Solution

There are multiple reasons why you can get a plan that has a NULL value when using DMV sys.dm_exec_query_plan. Here I will try to show you cases for more clarification.

I created a simple stored procedure name "dbo.test". The procedure has a condition and uses a temp table.

USE MASTER
GO

CREATE DATABASE TEST
GO

USE TEST
GO

CREATE PROCEDURE [dbo].[TEST]
(
  @intType INT ---- 1,2
)
AS
BEGIN
   CREATE TABLE #CUST
   (
     Id INT IDENTITY(1,1),
     Name VARCHAR(200)
   )

   IF @intType = 1
   BEGIN
      INSERT INTO #CUST
      SELECT 'TEST1'
   END
   ELSE
   BEGIN
      INSERT INTO #CUST
      SELECT 'TEST2'
   END
END
GO

Now I will execute the stored procedure with parameter @intType = 1.

EXEC TEST @intType = 1

The procedure was executed and the first condition was used to compile the plan.

Now I am going to check the cached plan value for this procedure with the below query.

select
text,
queryplan.query_plan
from sys.dm_exec_cached_plans cachedplan
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) queryplan
where text like '%TEST%' and objtype = 'proc'

We can see the query_plan column returns NULL.

populate procedure Test plan cached details - Description: populate procedure Test plan cached details

Now I am going to execute the stored procedure with parameter @intType = 2, which will execute the ELSE part of the procedure.

EXEC TEST @intType = 2

Now I will query for the cached plan again and we can see the query_plan columns returns the XML plan this time.

The reason this didn't show the query plan the first time was due to deferred compilation. I concluded that due to deferred compilation with the temporary table it couldn’t get the XML query plan until it compiled all conditional statements within the batch.

Statement with OPTION(recompile)

I created a new stored procedure and I am using the OPTION(RECOMPILE) to see if that makes a difference.

USE TEST
GO

Create Table Test1
  (
  Id int
  )
GO

CREATE PROCEDURE [dbo].[TEST_recompile]
(
  @intType int ---- 1,2
)
AS
BEGIN
  IF @intType = 1
  BEGIN
     SELECT * 
     FROM Test1 
     OPTION(RECOMPILE)
   END
   ELSE
   BEGIN
      SELECT * 
      FROM Test1 
      OPTION(RECOMPILE)
   END
END
GO

Now I am going to execute this procedure with parameter @intType = 1.

exec [TEST_recompile] @intType = 1

After execution, I run the below query again to get the cached query plan.

select
text,
queryplan.query_plan
from sys.dm_exec_cached_plans cachedplan
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) queryplan
where text like '%TEST_recompile%' and objtype = 'proc'

We can see that query_plan column is NULL.

Populate Procedure Test_recompile plan cached details - Description: populate procedure Test_recompile cached plan

Now I am going to execute this procedure with parameter @intType = 2.

exec [TEST_recompile] @intType = 2

Again we can see the query_plan is returned.

Populate Procedure Test_recompile plan cached details - Description: Populate Procedure Test_recompile plan cached detail

Again, I have concluded the reason is due to deferred compilation even with the option(recompile) until all conditional statements within the batch were used.

In addition, there are other cases where this is possible due to plan size, nested complexity, temp tables, recompilation hints, etc.

Alternative Solution To Get Query Plan

Below, I have an alternative script to get the cached plan.

SELECT (SELECT TOP 1 SUBSTRING(sql_text.text,statement_start_offset / 2+1 ,   
       ((CASE WHEN statement_end_offset = -1   
         THEN (LEN(CONVERT(nvarchar(max),sql_text.text)) * 2)
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS text,  
        cast(txt_query_plan.query_plan as xml) query_plan
FROM sys.dm_exec_query_stats AS Query_Stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
cross apply sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) txt_query_plan

Now I am going to apply this script in database TEST. This returns everything, but you can also add a WHERE clause to find specific statements you are looking for.

Populate Statement wise plan cached details - Description: Using This Query get

Sporadic issue with above alternate query

You may have sporadic errors raised like “XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels” because XML is not rendered or prepared due to the nested level exceeds its depth of 128. In such occurrences, we could modify the above query and remove the cast function on txt_query_plan. query_plan and execute the query and export the data using BCP and then open the XML in SSMS.

Next Steps

Check out more query plan related tips.

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Query plan returns NULL when using SQL Server DMV sys.dm_exec_query_plan