SQLEventAnalysis.psm1
|
# SQLEventAnalysis.psm1 using namespace System.Collections.Generic using namespace System.Management.Automation #region Classes class SQLActivityGroup { [int]$SessionId [string]$ActivityId [datetime]$FirstTimestamp [int]$EventCount hidden [System.Collections.ArrayList]$Events hidden [hashtable]$_eventsByType = @{} SQLActivityGroup([int]$sessionId, [string]$activityId, [System.Collections.ArrayList]$events) { $this.SessionId = $sessionId $this.ActivityId = $activityId $this.Events = $events $this.EventCount = $events.Count $this.FirstTimestamp = $events.Count -gt 0 ? $events[0].timestamp : [datetime]::MinValue # Pre-group events by type foreach ($e in $events) { $eventType = $e.name if (-not $this._eventsByType.ContainsKey($eventType)) { $this._eventsByType[$eventType] = [System.Collections.ArrayList]::new() } $this._eventsByType[$eventType].Add($e) | Out-Null } } [object[]] GetEventsByType([string]$eventType) { return $this._eventsByType[$eventType] ?? @() } [string[]] GetEventTypes() { return $this._eventsByType.Keys } } #endregion #region Public Functions function Add-DaxUsernameFromSqlContext { <# .SYNOPSIS Extracts Dynamics AX username from SQL Server context_info field. .DESCRIPTION Decodes the context_info binary field to extract the Dynamics AX username. This is specific to Dynamics AX implementations where username is stored in context_info. .PARAMETER InputObject The XE event object to process. .EXAMPLE Read-DbaXEFile -Path "C:\XEvents\*.xel" | Add-DaxUsernameFromSqlContext #> [CmdletBinding()] param( [Parameter(ValueFromPipeline = $true, Mandatory = $true)] [object]$InputObject ) process { $contextInfo = $InputObject.context_info if ($contextInfo) { $contextString = [Text.Encoding]::ASCII.GetString($contextInfo) $username = $contextString.Trim().Split(' ') | Select-Object -First 1 } else { $contextString = "" $username = "" } # Add properties to the object $InputObject | Add-Member -NotePropertyName "context_info_string" -NotePropertyValue $contextString -PassThru | Add-Member -NotePropertyName "usernameFromContext" -NotePropertyValue $username -PassThru } } function Group-DbaXEEventByActivity { <# .SYNOPSIS Groups Extended Events by session and activity ID. .DESCRIPTION Accumulates events by session_id and attach_activity_id. When an activity changes for a session, outputs the completed activity group with all its events. .PARAMETER InputObject The XE event object to process. .EXAMPLE $activities = Read-DbaXEFile -Path "*.xel" | Group-DbaXEEventByActivity #> [OutputType([PSCustomObject])] [CmdletBinding()] param( [Parameter(ValueFromPipeline = $true, Mandatory = $true)] [object]$InputObject ) begin { $sessionActivities = [Dictionary[int, Dictionary[string, System.Collections.ArrayList]]]::new() $currentActivityIds = [Dictionary[int, string]]::new() $processedCount = 0 } process { $processedCount++ # Extract session and activity IDs $sessionId = $InputObject.session_id ?? 0 $activityIdGuid = $null if ($null -ne $InputObject.attach_activity_id) { $activityIdParts = $InputObject.attach_activity_id.ToString().Split(':') $activityIdGuid = $activityIdParts[0] } # Skip events without session or activity if ($sessionId -eq 0 -or [string]::IsNullOrEmpty($activityIdGuid)) { Write-Verbose "Skipping event without session/activity ID" return } # Initialize tracking for new sessions if (-not $sessionActivities.ContainsKey($sessionId)) { $sessionActivities[$sessionId] = [Dictionary[string, System.Collections.ArrayList]]::new() $currentActivityIds[$sessionId] = $null } # Check if activity changed if ($currentActivityIds[$sessionId] -and $currentActivityIds[$sessionId] -ne $activityIdGuid) { # Output previous activity $previousActivityId = $currentActivityIds[$sessionId] $activityEvents = $sessionActivities[$sessionId][$previousActivityId] Write-Verbose "Outputting activity $previousActivityId with $($activityEvents.Count) events" # Create activity group $activityGroup = [SQLActivityGroup]::new($sessionId, $previousActivityId, $activityEvents) # Create output object with dynamic properties $output = [PSCustomObject]@{ PSTypeName = 'SQLActivityGroup' SessionId = $activityGroup.SessionId ActivityId = $activityGroup.ActivityId FirstTimestamp = $activityGroup.FirstTimestamp EventCount = $activityGroup.EventCount Events = $activityEvents } # Add dynamic properties for each event type foreach ($eventType in $activityGroup.GetEventTypes()) { $output | Add-Member -MemberType NoteProperty -Name $eventType -Value ($activityGroup.GetEventsByType($eventType)) } # Add common calculated properties $output | Add-Member -MemberType ScriptProperty -Name 'LastTimestamp' -Value { $this.Events[-1].timestamp } $output | Add-Member -MemberType ScriptProperty -Name 'Duration' -Value { ($this.LastTimestamp - $this.FirstTimestamp).TotalMilliseconds } $output | Add-Member -MemberType ScriptProperty -Name 'Username' -Value { # Try DAX username first, then regular username $daxUser = $this.Events | Where-Object usernameFromContext | Select-Object -First 1 -ExpandProperty usernameFromContext if ($daxUser) { $daxUser } else { $this.Events[0].username } } $output # Clean up $sessionActivities[$sessionId].Remove($previousActivityId) } # Update current activity $currentActivityIds[$sessionId] = $activityIdGuid # Initialize activity if needed if (-not $sessionActivities[$sessionId].ContainsKey($activityIdGuid)) { $sessionActivities[$sessionId][$activityIdGuid] = [System.Collections.ArrayList]::new() } # Add event to activity $sessionActivities[$sessionId][$activityIdGuid].Add($InputObject) | Out-Null } end { Write-Verbose "Outputting remaining activities" # Output remaining activities foreach ($sessionId in $sessionActivities.Keys) { foreach ($activityId in $sessionActivities[$sessionId].Keys) { $activityEvents = $sessionActivities[$sessionId][$activityId] # Create activity group $activityGroup = [SQLActivityGroup]::new($sessionId, $activityId, $activityEvents) # Create output object $output = [PSCustomObject]@{ PSTypeName = 'SQLActivityGroup' SessionId = $activityGroup.SessionId ActivityId = $activityGroup.ActivityId FirstTimestamp = $activityGroup.FirstTimestamp EventCount = $activityGroup.EventCount Events = $activityEvents } # Add dynamic properties for each event type foreach ($eventType in $activityGroup.GetEventTypes()) { $output | Add-Member -MemberType NoteProperty -Name $eventType -Value ($activityGroup.GetEventsByType($eventType)) } # Add calculated properties $output | Add-Member -MemberType ScriptProperty -Name 'LastTimestamp' -Value { $this.Events[-1].timestamp } $output | Add-Member -MemberType ScriptProperty -Name 'Duration' -Value { ($this.LastTimestamp - $this.FirstTimestamp).TotalMilliseconds } $output | Add-Member -MemberType ScriptProperty -Name 'Username' -Value { $daxUser = $this.Events | Where-Object usernameFromContext | Select-Object -First 1 -ExpandProperty usernameFromContext if ($daxUser) { $daxUser } else { $this.Events[0].username } } $output } } } } function Measure-DbaXEActivity { <# .SYNOPSIS Calculates performance metrics for SQL activity groups. .DESCRIPTION Adds calculated performance metrics like TotalDuration, TotalCPU, TotalReads, etc. to activity group objects. .PARAMETER InputObject The activity group object to measure. .EXAMPLE $activities | Measure-DbaXEActivity #> [CmdletBinding()] param( [Parameter(ValueFromPipeline = $true, Mandatory = $true)] [PSCustomObject]$InputObject ) process { # Calculate metrics from all events $durationSum = ($InputObject.Events | Where-Object duration | Measure-Object -Property duration -Sum).Sum $cpuSum = ($InputObject.Events | Where-Object cpu_time | Measure-Object -Property cpu_time -Sum).Sum $physicalReadsSum = ($InputObject.Events | Where-Object physical_reads | Measure-Object -Property physical_reads -Sum).Sum $logicalReadsSum = ($InputObject.Events | Where-Object logical_reads | Measure-Object -Property logical_reads -Sum).Sum $writesSum = ($InputObject.Events | Where-Object writes | Measure-Object -Property writes -Sum).Sum # Add metric properties $InputObject | Add-Member -NotePropertyName 'TotalDuration' -NotePropertyValue $durationSum -PassThru | Add-Member -NotePropertyName 'TotalCPUTime' -NotePropertyValue $cpuSum -PassThru | Add-Member -NotePropertyName 'TotalPhysicalReads' -NotePropertyValue $physicalReadsSum -PassThru | Add-Member -NotePropertyName 'TotalLogicalReads' -NotePropertyValue $logicalReadsSum -PassThru | Add-Member -NotePropertyName 'TotalWrites' -NotePropertyValue $writesSum -PassThru } } function Group-DbaXEEventByQuery { <# .SYNOPSIS Groups events by normalized SQL query text. .DESCRIPTION Groups events by SQL text, normalizing queries by removing parameters and standardizing format for aggregation purposes. .PARAMETER InputObject Activity groups or events to group by query. .PARAMETER GroupByUser If specified, groups by username and query combination. .EXAMPLE $activities | Group-DbaXEEventByQuery | Sort-Object TotalDurationMS -Descending #> [CmdletBinding()] param( [Parameter(ValueFromPipeline = $true, Mandatory = $true)] [PSCustomObject]$InputObject, [switch]$GroupByUser ) begin { $queryGroups = @{} } process { # Extract events from activity or use directly $events = if ($InputObject.PSObject.Properties['Events']) { $InputObject.Events } else { @($InputObject) } foreach ($e in $events) { if (-not $e.sql_text) { continue } # Normalize SQL text $normalizedSql = Get-NormalizedSqlText -SqlText $e.sql_text # Create group key $groupKey = if ($GroupByUser) { $username = $e.usernameFromContext ?? $e.username ?? 'Unknown' "$username|$normalizedSql" } else { $normalizedSql } # Initialize group if needed if (-not $queryGroups.ContainsKey($groupKey)) { $queryGroups[$groupKey] = [System.Collections.ArrayList]::new() } $queryGroups[$groupKey].Add($e) | Out-Null } } end { foreach ($groupKey in $queryGroups.Keys) { $events = $queryGroups[$groupKey] # Parse group key if ($GroupByUser -and $groupKey.Contains('|')) { $parts = $groupKey.Split('|', 2) $username = $parts[0] $sqlText = $parts[1] } else { $username = $null $sqlText = $groupKey } # Calculate statistics $stats = Get-EventStatistics -Events $events # Create output object $output = [PSCustomObject]@{ PSTypeName = 'SQLQueryGroup' SqlText = $sqlText Count = $events.Count TotalDurationMS = $stats.Duration.Sum / 1000 AvgDurationMS = $stats.Duration.Average / 1000 TotalCPUMS = $stats.CPU.Sum / 1000 AvgCPUMS = $stats.CPU.Average / 1000 TotalPhysicalReads = $stats.PhysicalReads.Sum AvgPhysicalReads = $stats.PhysicalReads.Average TotalLogicalReads = $stats.LogicalReads.Sum AvgLogicalReads = $stats.LogicalReads.Average TotalWrites = $stats.Writes.Sum AvgWrites = $stats.Writes.Average FirstExecution = $stats.FirstTimestamp LastExecution = $stats.LastTimestamp Events = $events } if ($username) { $output | Add-Member -NotePropertyName 'Username' -NotePropertyValue $username } $output } } } function Export-DbaXEQueryText { <# .SYNOPSIS Exports SQL query text with performance metadata to files. .DESCRIPTION Exports SQL queries to .sql files with performance statistics as comments. .PARAMETER InputObject Activity or query group objects to export. .PARAMETER OutputPath Directory to save the SQL files. .EXAMPLE $topQueries | Export-DbaXEQueryText -OutputPath "C:\Analysis\Queries" #> [CmdletBinding()] param( [Parameter(ValueFromPipeline = $true, Mandatory = $true)] [PSCustomObject]$InputObject, [Parameter(Mandatory = $true)] [string]$OutputPath ) begin { # Ensure output directory exists if (-not (Test-Path $OutputPath)) { New-Item -Path $OutputPath -ItemType Directory | Out-Null } } process { # Extract key information $username = $InputObject.Username ?? 'Unknown' $activityId = $InputObject.ActivityId ?? 'NoActivity' $duration = $InputObject.TotalDuration ?? $InputObject.TotalDurationMS ?? 0 # Find RPC completed event $rpcEvent = if ($InputObject.rpc_completed) { $InputObject.rpc_completed | Select-Object -First 1 } else { $InputObject.Events | Where-Object name -eq 'rpc_completed' | Select-Object -First 1 } if (-not $rpcEvent) { Write-Warning "No rpc_completed event found for activity $activityId" return } # Build content $content = @" -- Username: $username -- Activity: $activityId -- Duration: $($rpcEvent.duration) microseconds -- CPU Time: $($rpcEvent.cpu_time) microseconds -- Physical Reads: $($rpcEvent.physical_reads) -- Logical Reads: $($rpcEvent.logical_reads) -- Writes: $($rpcEvent.writes) -- Row Count: $($rpcEvent.row_count) $($rpcEvent.statement) GO $($rpcEvent.sql_text) "@ # Add stored procedure statements if available if ($InputObject.sp_statement_completed) { foreach ($spStmt in $InputObject.sp_statement_completed) { $dmvHash = Convert-XEHashToDMVHash -XEQueryHash $spStmt.query_hash $hexHash = "0x{0:X}" -f $dmvHash $content += @" GO -- Query Hash: $hexHash -- Line: $($spStmt.line_number), Statement: $($spStmt.statement_number) $($spStmt.statement) "@ } } # Generate filename $filename = "$username.$activityId.$([int]$duration).sql" $filepath = Join-Path $OutputPath $filename # Export $content | Out-File -FilePath $filepath -Encoding UTF8 Write-Verbose "Exported query to: $filepath" } } function Export-DbaXEQueryPlan { <# .SYNOPSIS Exports SQL execution plans from showplan events. .DESCRIPTION Extracts and saves execution plans from query_post_execution_showplan events. .PARAMETER InputObject Activity objects containing showplan events. .PARAMETER OutputPath Directory to save the .sqlplan files. .EXAMPLE $activities | Export-DbaXEQueryPlan -OutputPath "C:\Analysis\Plans" #> [CmdletBinding()] param( [Parameter(ValueFromPipeline = $true, Mandatory = $true)] [PSCustomObject]$InputObject, [Parameter(Mandatory = $true)] [string]$OutputPath ) begin { if (-not (Test-Path $OutputPath)) { New-Item -Path $OutputPath -ItemType Directory | Out-Null } } process { # Find showplan events $showplanEvents = if ($InputObject.query_post_execution_showplan) { $InputObject.query_post_execution_showplan } else { $InputObject.Events | Where-Object name -eq 'query_post_execution_showplan' } if (-not $showplanEvents) { Write-Verbose "No execution plan found for activity $($InputObject.ActivityId)" return } foreach ($showplan in $showplanEvents) { $username = $InputObject.Username ?? 'Unknown' $activityId = $InputObject.ActivityId ?? 'NoActivity' $duration = $InputObject.TotalDuration ?? 0 $filename = "$username.$activityId.$([int]$duration).sqlplan" $filepath = Join-Path $OutputPath $filename $showplan.showplan_xml | Out-File -FilePath $filepath -Encoding UTF8 Write-Verbose "Exported execution plan to: $filepath" } } } function Export-DbaXEAnalysisReport { <# .SYNOPSIS Exports XE analysis results to Excel report. .DESCRIPTION Creates formatted Excel reports with query statistics and analysis results. .PARAMETER InputObject Query group objects to export. .PARAMETER Path Path for the Excel file. .PARAMETER WorksheetName Name of the worksheet. Defaults to 'QueryAnalysis'. .EXAMPLE $queryGroups | Export-DbaXEAnalysisReport -Path "C:\Reports\Analysis.xlsx" #> [CmdletBinding()] param( [Parameter(ValueFromPipeline = $true, Mandatory = $true)] [PSCustomObject[]]$InputObject, [Parameter(Mandatory = $true)] [string]$Path, [string]$WorksheetName = 'QueryAnalysis' ) begin { $allData = [System.Collections.ArrayList]::new() } process { foreach ($item in $InputObject) { $allData.Add($item) | Out-Null } } end { # Select and order columns for export $exportData = $allData | Select-Object @( @{Name='Username'; Expression={$_.Username ?? 'All Users'}} 'SqlText' 'Count' 'TotalDurationMS' 'AvgDurationMS' 'TotalCPUMS' 'AvgCPUMS' 'TotalPhysicalReads' 'AvgPhysicalReads' 'TotalLogicalReads' 'AvgLogicalReads' 'TotalWrites' 'AvgWrites' 'FirstExecution' 'LastExecution' ) # Export to Excel $exportData | Export-Excel -Path $Path -WorksheetName $WorksheetName -TableName $WorksheetName -AutoSize -FreezeTopRow Write-Verbose "Exported $($exportData.Count) query groups to: $Path" } } function Convert-XEHashToDMVHash { <# .SYNOPSIS Converts Extended Events query hash to DMV query hash format. .DESCRIPTION Converts unsigned 64-bit XE query hash to signed bigint for DMV compatibility. .PARAMETER XEQueryHash The unsigned query hash from Extended Events. .EXAMPLE $dmvHash = Convert-XEHashToDMVHash -XEQueryHash $e.query_hash #> [CmdletBinding()] param( [Parameter(ValueFromPipeline = $true, Mandatory = $true)] [uint64]$XEQueryHash ) process { if ($XEQueryHash -gt 9223372036854775807) { [bigint]($XEQueryHash - 9223372036854775808) } else { [bigint]$XEQueryHash } } } #endregion #region Private Functions function Get-NormalizedSqlText { <# .SYNOPSIS Normalizes SQL text for grouping similar queries. #> [CmdletBinding()] param( [string]$SqlText ) if ([string]::IsNullOrWhiteSpace($SqlText)) { return $SqlText } # For SELECT queries, extract from SELECT onwards $upperSql = $SqlText.ToUpper() $selectIndex = $upperSql.IndexOf('SELECT') if ($selectIndex -ge 0) { return $SqlText.Substring($selectIndex) } # For non-SELECT queries, truncate if too long if ($SqlText.Length -gt 300) { return $SqlText.Substring(0, 300) } return $SqlText } function Get-EventStatistics { <# .SYNOPSIS Calculates statistics for a collection of events. #> [CmdletBinding()] param( [System.Collections.ArrayList]$Events ) $stats = @{ Duration = $Events | Where-Object duration | Measure-Object -Property duration -Sum -Average CPU = $Events | Where-Object cpu_time | Measure-Object -Property cpu_time -Sum -Average PhysicalReads = $Events | Where-Object physical_reads | Measure-Object -Property physical_reads -Sum -Average LogicalReads = $Events | Where-Object logical_reads | Measure-Object -Property logical_reads -Sum -Average Writes = $Events | Where-Object writes | Measure-Object -Property writes -Sum -Average FirstTimestamp = ($Events | Sort-Object timestamp | Select-Object -First 1).timestamp LastTimestamp = ($Events | Sort-Object timestamp | Select-Object -Last 1).timestamp } return $stats } #endregion # Export module members Export-ModuleMember -Function @( 'Add-DaxUsernameFromSqlContext', 'Group-DbaXEEventByActivity', 'Measure-DbaXEActivity', 'Group-DbaXEEventByQuery', 'Export-DbaXEQueryText', 'Export-DbaXEQueryPlan', 'Export-DbaXEAnalysisReport', 'Convert-XEHashToDMVHash' ) |