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

Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.

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.

Databases should be easy to deploy, easy to use, and easy to scale. If you agree, you should check out CockroachDB, a scalable SQL database built for businesses of every size. Check it out here. 

Topics:
database ,sql server ,powershell

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}