Export All Plans From Cache to a .SQLPLAN File

Home / PowerShell / Export All Plans From Cache to a .SQLPLAN File

I was asked if there was an easy way to export all the plans from cache to a .SQLPLAN file. My immediate answer was, “I’m sure there’s a PowerShell script out there somewhere.” However, rather than do a Gingle search, I figured I’d quickly knock up an example.

The Script

I’ve gone minimal on the script. I’m creating a connection to the local instance, defining a command, and returning the data into a data set. From there, since the data set consists of a single column, I’m walking through them all to export out to a file:

Obviously, you’d want to edit the connection and the path for your own circumstances. Also, you can modify the base query to only retrieve plans for a particular database or some other filter criteria. However, this was a quick and easy way to get the job done.

I’m sure there are more efficient ways to get this written. I just wanted to get it written. I’ve tested it multiple times and it works a charm. Feel free to use it as you will or modify it to meet your own special needs.

2 Comments

OK, fine, but what do you think?