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:
$Query = 'SELECT deqp.query_plan FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp WHERE deqp.query_plan IS NOT NULL;' $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = 'Server=ServerX\DOJO;Database=master;trusted_connection=true' $PlanQuery = new-object System.Data.SqlClient.SqlCommand $PlanQuery.CommandText = $Query $PlanQuery.Connection = $SqlConnection $PlanAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $PlanAdapter.SelectCommand = $PlanQuery $PlanSet = new-object System.Data.DataSet $PlanAdapter.Fill($PlanSet) foreach($row in $PlanSet.Tables[0]) { $i+=1 $row[0] | Out-File -FilePath "c:\plans\$i.sqlplan" }
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.
The post Export All Plans From Cache to a .SQLPLAN File appeared first on Grant Fritchey.