In this post I’ll demo how to use PowerShell to retrieve execution plan files from a SQL Server instance. It’s pretty much the same method I’ve used in PSBlitz to accomplish this.
Repeated manual work isn’t fun
The downside to using sys.dm_exec_cached_plans, sys.query_store_plan, and even Brent Ozar‘s awesome sp_BlitzCache and sp_BlitzWho to get execution plans is that, if you want to save more than a couple of those execution plans as standalone files, it can get a bit tiresome.
Why save execution plans and/or deadlock graphs as files in the first place?
There are a few good reasons why you would want to have either of these as standalone files:
- portability
- it makes them easy to copy from one server to your workstation
- share them through Teams/Slack/e-mail with your team
- commit them to your GitHub repository for a demo
- have customers email the plans to you for troubleshooting without having to interact with their databases
- quickly dumping execution plans for further analysis in case the instance or host might go down
- view them in tools such as SQL Sentry Plan Explorer
- read the XML in your favorite file editor
Here’s where PowerShell comes in
PowerShell is a great way to automate tasks and it comes and, being built on top of the .NET Framework, means it has access to a bunch of nifty .NET Framework classes.
The three .NET Framework classes needed here are XmlDocument, StringWriter, and XmlTextWriter.
These are used in the below function to ingest the XML that makes up the execution plan or deadlock graph, format and properly indent it, and then output it.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | function Format-XML { [CmdletBinding()] Param ( [Parameter( ValueFromPipeline=$true, Mandatory=$true)] [string]$XMLInput ) $XMLDoc = New-Object -TypeName System.Xml.XmlDocument $XMLDoc.LoadXml($XMLInput) $SW = New-Object System.IO.StringWriter $Writer = New-Object System.Xml.XmlTextwriter($SW) $Writer.Formatting = [System.XML.Formatting]::Indented $XMLDoc.WriteContentTo($Writer) $SW.ToString() } |
Note that I didn’t came up with this function, I found it on Stack Overflow while looking for ways to overcome Out-File‘s limitations.
Demo
For my demo, I’ll use the following query to get the top 5 most executed queries that ran against AdventureWorks2019.
| 1 2 3 4 5 6 7 8 9 | SELECT TOP(5) DB_NAME(ep.[dbid]) + '_' + CONVERT(NVARCHAR(31), cp.plan_handle, 1 ) + '.sqlplan' AS sqlplan_file, /*this is the file name*/ ep.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) ep WHERE ep.query_plan IS NOT NULL AND ep.[dbid] = DB_ID(N'AdventureWorks2019') ORDER BY cp.usecounts DESC; |
There are a couple of ways of getting the data from SQL Server and into PowerShell for further handling and outputting.
The Invoke-Sqlcmd way
The heading says it all. This relies on Invoke-Sqlcmd to execute the query and retrieve the data as a DataSet object.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | #Path where the .sqlplan files will be saved $PlanOutDir = "F:\ExecPlans" #The SQL Server Instance name $ServerName = "Localhost\VSQL2019" #The query that will be executed $Query = "SELECT TOP(5) DB_NAME(ep.[dbid]) + '_' + CONVERT(NVARCHAR(31), cp.plan_handle, 1 ) + '.sqlplan' AS sqlplan_file, /*this is the file name*/ ep.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) ep WHERE ep.query_plan IS NOT NULL AND ep.[dbid] = DB_ID(N'AdventureWorks2019') ORDER BY cp.usecounts DESC;" #Function to ingest and format the XML data correctly function Format-XML { [CmdletBinding()] Param ( [Parameter( ValueFromPipeline=$true, Mandatory=$true)] [string]$XMLInput ) $XMLDoc = New-Object -TypeName System.Xml.XmlDocument $XMLDoc.LoadXml($XMLInput) $SW = New-Object System.IO.StringWriter $Writer = New-Object System.Xml.XmlTextwriter($SW) $Writer.Formatting = [System.XML.Formatting]::Indented $XMLDoc.WriteContentTo($Writer) $SW.ToString() } #Execute the query and save the output in a DataSet object $PlanSet = Invoke-Sqlcmd -ServerInstance $ServerName -Query $Query -MaxCharLength 9999999 -As DataSet $PlanTbl = New-Object System.Data.DataTable $PlanTbl = $PlanSet.Tables[0] |
After executing the above block, $PlanTbl will contain the data set table returned by the query.
Next, I execute the following block of code to iterate through the result set’s records, pass the query_plan value to the Format-XML function and then output it to a .sqlplan file.
| 1 2 3 4 5 6 7 8 9 10 11 12 | #Set counter used for row retrieval [int]$RowNum = 0 #Iterate through each row in the data set foreach($row in $PlanTbl){ #Set the filename [string]$SQLPlanFile = $PlanTbl.Rows[$RowNum]["sqlplan_file"] #Write execution plan to file $PlanTbl.Rows[$RowNum]["query_plan"] | Format-XML | Set-Content ` -Path $PlanOutDir\$($SQLPlanFile) -Force #Increment row retrieval counter $RowNum+=1 } |
And now, the resulting 5 execution plan files are in the output directory.
And the execution plans look good in both SSMS, SQL Sentry Plan Explorer, as well as in raw XML.
The .NET way
This just requires replacing the Invoke-Sqlcmd line of the PowerShell code with a block that does the same thing (connect to the instance, execute the query against it and retrieve the result set as a DataSet object).
The advantage of this method is that it doesn’t require installing the SqlServer module and relies on .NET Framework classes and methods that are already available on Windows.
This means that the following lines (since I used a back-tick to split the line for readability):
| 1 2 | $PlanSet = Invoke-Sqlcmd -ServerInstance $ServerName ` -Query $Query -MaxCharLength 9999999 -As DataSet |
Will be replaced by this block of code:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | #Execute the query and save the output in a DataSet object $SqlConnection = New-Object System.Data.SqlClient.SqlConnection #Using trusted connection here, update to user and pass in case of SQL auth $SqlConnection.ConnectionString = "Server=$ServerName;Database=$Plan;trusted_connection=true" $PlanQuery = new-object System.Data.SqlClient.SqlCommand $PlanQuery.CommandText = $Query $PlanQuery.Connection = $SqlConnection $PlanQuery.CommandTimeout = 200 $PlanAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $PlanAdapter.SelectCommand = $PlanQuery $PlanSet = new-object System.Data.DataSet $PlanAdapter.Fill($PlanSet) | Out-Null $SqlConnection.Dispose() |
With the rest of the code used in the previous example staying the same.
The result will be the same.
This same process can be used to output deadlock graph files when saving sp_BlitzLock’s output to a table.