Export All Plans From Cache to a .SQLPLAN File [Snippet]
This quick script will get the plans you've got in your cache to a .SQLPLAN file, complete with options for filters and customizations
Join the DZone community and get the full member experience.
Join For FreeI 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 Google search, I figured I’d quickly knock up an example.
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.
Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments