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.
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.
Next Steps
- Check out more query plan related tips.
Bhavesh Patel is a SQL Server database professional with 10+ years of experience. Most of his career has been working on database development and administration. He has a strong interest in continuous learning and likes facing challenges to learn new things. In his spare time, he spends time with his family. Currently, his main role is query tuning with respect to optimization and server performance.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2019 | Rookie of the Year Contender – 2017