# encoding: ascii
# api: powershell
# title: Export top n SQLPlans
# description: Export top n consuming sqlplans via avg_worker_time (=cpu) for all databases of a given SQLServer (SQL2005+) Instance
# version: 1.00
# type: script
# author: alzdba
# license: CC0
# x-poshcode-id: 3730
# x-archived: 2013-05-09T06:03:26
# x-published: 2013-10-31T05:53:00
#
# results in a number of .SQLPlan files and the consumption overview .CSV file
#
<#
ALZDBA SQLServer_Export_SQLPlans_SMO.ps1
Export top n consuming sqlplans (avg_worker_time=cpu) for a given SQLServer (SQL2005+) Instance
#>
#requires -version 2
#SQLServer instance 
$SQLInstance = 'yourserver\yourinstance'
#What number of plans to export per db ?
[int]$nTop = 50

trap {
  # Handle all errors not handled by try\catch
  $err = $_.Exception
  write-verbose "Trapped error: $err.Message" 
  while( $err.InnerException ) {
	   $err = $err.InnerException
	   write-host $err.Message -ForegroundColor Black -BackgroundColor Red 
	   };
  # End the script.
  break
  }
  
Clear-Host


# databases of which we do not want sqlplans 
[string[]]$ExcludedDb = 'tempdb' , 'model'

#recipient
$AllDbSQLPlan = $null 

$SampleTime = Get-Date -Format 'yyyyMMdd_HHmm'
#Load SQLServer SMO libraries
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 

$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstance
#Oh please put some application info in your connection metadata !
$serverInstance.ConnectionContext.ApplicationName = "DBA_Export_SQLPlans"
$serverInstance.ConnectionContext.WorkstationId = $env:COMPUTERNAME 
# connecting should take less than 5 seconds !
$serverInstance.ConnectionContext.ConnectTimeout = 5
#connect before processing
$serverInstance.ConnectionContext.Connect()
$Query = ''
if ( $serverInstance.VersionMajor -lt 9 ) {
	write-host $('{0} is of a Non-supported SQLServer version [{1}].' -f $SQLInstance, $serverInstance.VersionString) -ForegroundColor Black -BackgroundColor Red 
	# End the script.
	break

	}
elseif ( $serverInstance.VersionMajor -lt 10 ) {
	# SQL2005
	$Query = $('WITH XMLNAMESPACES ( ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'' AS PLN )
			SELECT TOP ( {0} ) db_name() as Db_Name
		  , Object_schema_name(qp.objectid ) as Schema
	      , Object_name(qp.objectid ) AS [Object_Name]
	      , ISNULL(qs.total_elapsed_time / qs.execution_count, 0) AS [Avg_Elapsed_Time]
	      , qs.execution_count AS [Execution_Count]
	      , qs.total_worker_time AS [Total_Worker_Time]
	      , qs.total_worker_time / qs.execution_count AS [Avg_Worker_Time]
	      , ISNULL(qs.execution_count / DATEDIFF(SS, qs.creation_time, GETDATE()), 0) AS [Calls_per_Second]
	      , qs.max_logical_reads
	      , qs.max_logical_writes
		  , qp.query_plan.exist(''/PLN:ShowPlanXML//PLN:MissingIndex'') as Missing_Indexes
	      , DATEDIFF( SS, qs.cached_time, getdate()) as Time_In_Cache_SS
		  , row_number() over ( order by qs.total_elapsed_time / qs.execution_count DESC ) [Row_Number]
	      , qp.query_plan
	FROM    sys.dm_exec_query_stats AS qs WITH ( NOLOCK )
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
	WHERE   qp.dbid = DB_ID()
	        and qs.execution_count > 5
			and Object_name(qp.objectid ) not like  ''spc_DBA%''
			and qs.total_worker_time / qs.execution_count > 50000  /* in microseconds */
	ORDER BY Avg_Elapsed_Time DESC
	OPTION  ( RECOMPILE ) ; ' -f $nTop )
	}
elseif ( $serverInstance.VersionMajor -eq 10 -and $serverInstance.VersionMinor -lt 50 ) {
	# SQL2008
	$Query = $('WITH XMLNAMESPACES ( ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'' AS PLN )
			SELECT TOP ( {0} ) db_name() as Db_Name
		  , Object_schema_name(qp.objectid ) as Schema
	      , Object_name(qp.objectid ) AS [Object_Name]
	      , ISNULL(qs.total_elapsed_time / qs.execution_count, 0) AS [Avg_Elapsed_Time]
	      , qs.execution_count AS [Execution_Count]
	      , qs.total_worker_time AS [Total_Worker_Time]
	      , qs.total_worker_time / qs.execution_count AS [Avg_Worker_Time]
	      , ISNULL(qs.execution_count / DATEDIFF(SS, qs.creation_time, GETDATE()), 0) AS [Calls_per_Second]
	      , qs.max_logical_reads
	      , qs.max_logical_writes
		  , qp.query_plan.exist(''/PLN:ShowPlanXML//PLN:MissingIndex'') as Missing_Indexes
	      , DATEDIFF( SS, qs.cached_time, getdate()) as Time_In_Cache_SS
		  , row_number() over ( order by qs.total_elapsed_time / qs.execution_count DESC ) [Row_Number]
	      , qp.query_plan
	FROM    sys.dm_exec_query_stats AS qs WITH ( NOLOCK )
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
	WHERE   qp.dbid = DB_ID()
	        and qs.execution_count > 5
			and Object_name(qp.objectid ) not like  ''spc_DBA%''
	and qs.total_worker_time / qs.execution_count > 50000 /* in microseconds */
	ORDER BY Avg_Elapsed_Time DESC
	OPTION  ( RECOMPILE ) ; ' -f $nTop )
	}
else {
	# SQL2008R2 and up
	$Query = $('WITH XMLNAMESPACES ( ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'' AS PLN )
			SELECT TOP ( {0} ) db_name() as Db_Name
		  , Object_schema_name(p.object_id ) as [Schema]
	      , p.name AS [Object_Name]
	      , qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
	      , qs.total_elapsed_time
	      , qs.execution_count
	      , cast(ISNULL(qs.execution_count * 1.00 / DATEDIFF(SS, qs.cached_time, GETDATE()), 0) as decimal(9,3)) AS [CallsPerSecond]
	      , qs.total_worker_time / qs.execution_count AS [Avg_Worker_Time]
	      , qs.total_worker_time AS [Total_Worker_Time]
		  , qp.query_plan.exist(''/PLN:ShowPlanXML//PLN:MissingIndex'') as Missing_Indexes
	      , qs.cached_time
	      , DATEDIFF( SS, qs.cached_time, getdate()) as Time_In_Cache_SS
		  , row_number() over ( order by qs.total_elapsed_time / qs.execution_count DESC ) [Row_Number]
	      , qp.query_plan
	FROM    sys.procedures AS p WITH ( NOLOCK )
	INNER JOIN sys.dm_exec_procedure_stats AS qs WITH ( NOLOCK )
	        ON p.[object_id] = qs.[object_id]
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
	WHERE   qs.database_id = DB_ID()
	        and qs.execution_count > 5
			/* only non-ms sprocs */
	        and p.is_ms_shipped = 0
			and p.name not like  ''spc_DBA%''
			and qs.total_worker_time / qs.execution_count > 50000  /* in microseconds */

	ORDER BY avg_elapsed_time DESC
	OPTION  ( RECOMPILE ) ;' -f $nTop )
	}

$i = 0
foreach($db in $serverInstance.Databases){
	if ( $ExcludedDb -notcontains $db.Name ) {
		$i += 1
		$pct = 100 * $i / $serverInstance.Databases.Count 
		Write-progress -Status "Processing DBs - $($db.Name)"  -Activity "Collection SQLPlans $SQLInstance" -PercentComplete $pct
		try {
			$DbSQLPlans = $db.ExecuteWithResults("$Query").Tables[0] 
		
			if ( !( $AllDbSQLPlan )) {
				$AllDbSQLPlan = $DbSQLPlans.clone()
				}
				
			if ( $DbSQLPlans.rows.count -gt 0 ) {
				$AllDbSQLPlan += $DbSQLPlans
				}
			}
		catch {
			#just neglect this error
			Write-Verbose $_.Exception.Message 
			}
		}
	else {
		Write-Verbose "Excluded Db $db.name"
		}
	}
#Take control: do it yourself !
$serverInstance.ConnectionContext.Disconnect()

	
if ( $AllDbSQLPlan -and $AllDbSQLPlan.Count -gt 0 ) {
	$TargetPath = "c:\tempo\Powershell"
	if ( !(Test-Path $TargetPath) ) {
		md $TargetPath | Out-Null 
		}
	Write-progress -Status "Exporting Consumption Data"  -Activity "Exporting SQLPlans $SQLInstance" -PercentComplete 15

	$TargetFile = $('{0}-{1}_AllDbSQLPlan.csv' -f $SampleTime, $($SQLInstance -replace '\\', '_') )
	$AllDbSQLPlan | Select Db_Name, Row_Number, Schema, Object_Name, avg_elapsed_time, total_elapsed_time, execution_count, Calls_Per_Second, Avg_Worker_Time, Total_Worker_Time, Missing_Indexes, cached_time, Time_In_Cache_SS | sort Db_Name, Row_Number | Export-Csv -Path $( Join-Path -Path $TargetPath -ChildPath $TargetFile ) -Delimiter ';' -NoTypeInformation 

	$i = 0
	foreach ( $p in $AllDbSQLPlan ) {
		$i += 1
		$pct = 100 * $i / $AllDbSQLPlan.Count 
		Write-progress -Status "Exporting SQLPlan - $($p.Object_Name)"  -Activity "Exporting SQLPlans $SQLInstance" -PercentComplete $pct

		$TargetFileName = $('{0}-{1}-{2}-{3}-{4}-{5}.SQLPlan' -f $SampleTime, $($SQLInstance.Replace('\','_')) , $p.Db_Name, $p.Row_Number, $p.Schema , $p.Object_Name )
		Write-verbose $TargetFileName
		
		Out-File -FilePath $( Join-Path -Path $TargetPath -ChildPath $TargetFileName ) -InputObject $p.query_plan		
		}

	#open explorer at target path
	Invoke-Item  "$TargetPath"
	}
else {
	Write-Host "No SQLPlans to be exported ! " -ForegroundColor Black -BackgroundColor Yellow
	}
