Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: More > 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.

create procedure

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


Last Update:






About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel has been working with SQL Server since 2007 as a Senior DBA.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools