Export All Plans From Cache to a .SQLPLAN File
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…