By: Bhavesh Patel | Updated: 2017-06-16 | Comments | Related: More > Query Plans
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.
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.
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.
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.
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.
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.
- Check out more query plan related tips.
Last Updated: 2017-06-16
About the author
View all my tips