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

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover 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.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce 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.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}