Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Export All Plans From Cache to a .SQLPLAN File [Snippet]

DZone's Guide to

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

· Database Zone
Free Resource

Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate

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 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.

Align DevOps for your applications with DevOps for your SQL Server databases to increase speed of delivery and keep data safe. Discover true Database DevOps, brought to you in partnership with Redgate

Topics:
database ,sql server ,powershell

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}