The Case of the NULL Query_Plan

As a DBA, we're often asked to troubleshoot performance issues for stored procedures.  One of the most common tools at our disposal is the query execution plan cached in memory by SQL Server. Once we have the query plan, we can dissect what SQL Server is doing and hopefully find some places for improvement.

Grabbing the actual XML query plan for a stored procedure from the cache is fairly easy using the following query.

USE AdventureWorks2012;
GO
SELECT qp.query_plan FROM sys.dm_exec_procedure_stats ps
    JOIN sys.objects o ON ps.object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
WHERE ps.database_id = DB_ID()
    AND s.name = 'dbo'
    AND o.name = 'usp_MonsterStoredProcedure';
GO

From this point, we can open the XML query plan in Management Studio or Plan Explorer to start our investigation. But what happens if SQL Server returns NULL for the query plan?

Let's back up a little bit.  We were pretty sure the query plan is still in cache, right?  Let's verify it.

USE AdventureWorks2012;
GO
SELECT * FROM sys.dm_exec_procedure_stats ps
    JOIN sys.objects o on ps.object_id = o.object_id
WHERE o.name = 'usp_MonsterStoredProcedure';
GO

Sure enough.  The query plan is still cached in memory, and we even can even see the plan_handle.

So why did our first query not return the XML plan?  Let's copy the plan_handle and manually run it through the sys.dm_exec_query_plan function.

SELECT * FROM sys.dm_exec_query_plan(0x05000500DD93100430BFF0750100000001000000000000000000000000000000000000000000000000000000);
GO

Why are we getting NULL returned for the XML query plan when we know is in the cache?  In this case, because the query plan is so large and complex, we're hitting an XML limitation within SQL Server.  "XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels".  

Let's try to pull the text version of query plan.

SELECT * FROM sys.dm_exec_text_query_plan(0x05000500DD93100430BFF0750100000001000000000000000000000000000000000000000000000000000000,DEFAULT,DEFAULT);
GO

It looks as though we have solved the issue; however, we didn't.  Management Studio has a 65535 character limit in grid results and 8192 character limit in text results.  Our query plan has been truncated far from the end.  Now it seems we are back to square one.  

We still know the query plan is in cache, but we just need a tool other than Management Studio to retrieve it.  This is where Powershell enters the picture.

With Powershell, we can create a simple script to execute the sys.dm_exec_text_query_plan function and then output the data to a file.  All we need is to pass two variables.  The first is the SQL Server name where the plan is cached, and the second is the plan_handle. 

param (    
    [Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]
    $SqlInstance

   ,[Parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]
    $PlanHandle
)

The script will simply execute a TSQL script and capture the output into a string variable.

$SqlCommand = "SELECT query_plan FROM sys.dm_exec_text_query_plan(" 
    + $PlanHandle + ",DEFAULT,DEFAULT);"
$QueryPlanText = $cmd.ExecuteScalar()

The final step will use System.IO.StreamWriter() to output the data to a file.

$stream = New-Object System.IO.StreamWriter($FileName)
$stream.WriteLine($QueryPlanText)

The Powershell script will save the entire XML query plan to a file named output.sqlplan.  As you can see below, the actual plan was over 5MB.

Finally we're able to view the entire query plan in our favorite tool and see the complexity of the stored procedure.

This is just another example of why  DBAs need to set aside some time to learn Powershell.  The entire script is posted below.  Feel free to modify it as needed to fit your environment.

######################################################################################
#
#   File Name:    Get-QueryPlan.ps1
#
#   Applies to:   SQL Server 2008
#                 SQL Server 2008 R2
#                 SQL Server 2012
#
#   Purpose:      Used to retrieve an XML query plan from cache.
#
#   Prerequisite: Powershell must be installed.
#                 SQL Server components must be installed.
#
#   Parameters:   [string]$SqlInstance - SQL Server name (Ex: SERVER\INSTANCE)
#                 [string]$PlanHandle - Binary query handle
#
#   Author:       Patrick Keisler
#
#   Version:      1.0.0
#
#   Date:         08/30/2013
#
#   Help:         http://www.patrickkeisler.com/
#
######################################################################################

#Define input parameters
param ( 
  [Parameter(Mandatory=$true)]
  [ValidateNotNullOrEmpty()]
  [string]
  $SqlInstance
  
  ,[Parameter(Mandatory=$true)]
  [ValidateNotNullOrEmpty()]
  [string]
  $PlanHandle
  )

Write-Host "Script starting."

#Grab the path where the Powershell script was executed from.
$path = Split-Path $MyInvocation.MyCommand.Path

#Build the SQL Server connection objects
$conn = New-Object System.Data.SqlClient.SqlConnection
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cmd = New-Object System.Data.SqlClient.SqlCommand

#Build the TSQL statement & connection string
$SqlCommand = "SELECT query_plan FROM sys.dm_exec_text_query_plan(" + $PlanHandle + ",DEFAULT,DEFAULT);"
$builder.psBase.DataSource = $SqlInstance
$builder.psBase.InitialCatalog = "master"
$builder.psBase.IntegratedSecurity = $true
$builder.psBase.ApplicationName = "Get-QueryPlan"
$builder.psBase.Pooling = $true
$builder.psBase.ConnectTimeout = 15
$conn.ConnectionString = $builder.ConnectionString
$cmd.Connection = $conn
$cmd.CommandText = $SqlCommand

try
{
 if ($conn.State -eq "Closed")
 {
  #Open a connection to SQL Server
  $conn.Open()
 }
 
 #Execute the TSQL statement
 [string]$QueryPlanText = $cmd.ExecuteScalar()

 #Write the output to a file
 $FileName = $path + "\output.sqlplan"
 $stream = New-Object System.IO.StreamWriter($FileName)
 $stream.WriteLine($QueryPlanText)

 if ($stream.BaseStream -ne $null)
 {
  #Close the stream object
  $stream.close()
 }

 if ($conn.State -eq "Open")
 {
  #Close the SQL Server connection
  $conn.Close()
 }
 
 Write-Host "Script completed successfully."
}
catch
{
 #Capture errors if needed
 if ($_.Exception.InnerException)
 {
  $Host.UI.WriteErrorLine("ERROR: " + $_.Exception.InnerException.Message)
  if ($_.Exception.InnerException.InnerException)
  {
   $Host.UI.WriteErrorLine("ERROR: " + $_.Exception.InnerException.InnerException.Message)
  }
 }
 else
 {
  $Host.UI.WriteErrorLine("ERROR: " + $_.Exception.Message)
 }
 
 Write-Host .
 Write-Host "ERROR: Script failed."
}