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.

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.


For my demo, I’ll use the following query to get the top 5 most executed queries that ran against AdventureWorks2019.

Screenshot showing the result set generated by the above query.
5 rows, 2 columns (sqlplan_file, query_plan)
sqlplan_file is the file name composed of the database name, the first 31 characters of the plan_handle and the .sqlplan extension
query_plan contains the actual execution plan XML

PowerShell execution plan files

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.

After executing the above block, $PlanTbl will contain the data set table returned by the query.

Screenshot displaying a portion of a Windows Terminal Window with the contents of the $PlanTbl variable

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.

And now, the resulting 5 execution plan files are in the output directory.

Screenshot of a Windows Terminal window with the output of the gci $PlanOutDir command:
PS C:\Users\Vlad> gci $PlanOutDir

    Directory: F:\ExecPlans

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a---           1/16/2023  9:55 PM        1437075 AdventureWorks2019_0x06000E001C77500B80EFF8134002.sqlplan
-a---           1/16/2023  9:55 PM          66002 AdventureWorks2019_0x06000E001E358420302AD5F63F02.sqlplan
-a---           1/16/2023  9:55 PM          64315 AdventureWorks2019_0x06000E00B5DC37289022D5F63F02.sqlplan
-a---           1/16/2023  9:55 PM          85621 AdventureWorks2019_0x06000E00C8B0832A7039D5F63F02.sqlplan
-a---           1/16/2023  9:55 PM          26969 AdventureWorks2019_0x06000E00F975A32ED031D5F63F02.sqlplan

And the execution plans look good in both SSMS, SQL Sentry Plan Explorer, as well as in raw XML.

Screenshot of one of the execution plans in SQL Sentry Plan Explorer.

PowerShell execution plan files
Screenshot of the same execution plan opened as an XML file in Notepad++

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):

Will be replaced by this block of code:

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.