sqmSQLTool

1.8.4.0

bin/Public/Invoke-sqmExtendedEvents.ps1

<#
.SYNOPSIS
    Verwaltet Extended Events Sessions fuer die Performance-Analyse auf SQL Server.

.DESCRIPTION
    Erstellt, startet, stoppt, liest und wertet Extended Events Sessions aus.

    Betriebsarten (Switches, kombinierbar):
      -Create Legt eine neue XEvent-Session anhand eines Templates an.
      -Start Startet eine vorhandene (oder neu erstellte) Session.
      -Stop Stoppt eine laufende Session.
      -Read Liest Ereignisse aus dem XEL-Ringpuffer oder einer Datei aus.
      -Diagnose Aggregiert Ereignisse und erkennt Muster (Top Waits, Blocking-Ketten,
                 Slow Queries, Deadlocks).
      -Drop Entfernt eine Session vollstaendig (inkl. XEL-Dateien).

    Wenn kein Switch angegeben wird, werden -Read und -Diagnose ausgefuehrt.

    Verfuegbare Session-Templates:
      SlowQueries sql_statement_completed > Schwellwert (Standard: 1000 ms)
      Blocking blocked_process_report
      Waits wait_info mit konfigurierbarer Warteliste
      Deadlocks xml_deadlock_report
      AllInOne Kombiniert alle vier Templates in einer Session

.PARAMETER SqlInstance
    SQL Server-Instanz. Standard: aktueller Computername.

.PARAMETER SqlCredential
    PSCredential fuer die Verbindung.

.PARAMETER SessionName
    Name der XEvent-Session. Standard: 'sqmPerformance'.

.PARAMETER Template
    Session-Template beim Erstellen. Werte: SlowQueries, Blocking, Waits, Deadlocks, AllInOne.
    Standard: AllInOne.

.PARAMETER SlowQueryThresholdMs
    Minimale Ausfuehrungsdauer in Millisekunden fuer SlowQueries-Capture. Standard: 1000.

.PARAMETER WaitTypes
    Komma-getrennte Liste von Wait Types fuer das Waits-Template.
    Standard: LCK_M_X,LCK_M_S,LCK_M_U,PAGEIOLATCH_SH,PAGEIOLATCH_EX,CXPACKET,SOS_SCHEDULER_YIELD

.PARAMETER TargetType
    Zieltyp fuer die Ereignisspeicherung: RingBuffer oder File. Standard: RingBuffer.

.PARAMETER TargetFilePath
    Verzeichnis fuer XEL-Dateien (nur bei TargetType = File).
    Standard: aus Modulkonfiguration OutputPath + \XEvents.

.PARAMETER MaxFileSizeMB
    Maximale Groesse einer XEL-Datei (MB). Standard: 100.

.PARAMETER MaxRolloverFiles
    Anzahl der XEL-Rollover-Dateien. Standard: 5.

.PARAMETER RingBufferMaxMB
    Maximale Groesse des RingBuffers (MB). Standard: 50.

.PARAMETER MaxEventsRead
    Maximale Anzahl Ereignisse beim Lesen. Standard: 10000.

.PARAMETER LookbackMinutes
    Zeitfenster fuer Diagnose-Aggregation in Minuten. Standard: 60.

.PARAMETER TopN
    Anzahl der Top-Eintraege in Diagnose-Tabellen. Standard: 25.

.PARAMETER OutputPath
    Verzeichnis fuer gespeicherte Berichte. Standard: aus Modulkonfiguration + \XEvents.

.PARAMETER Create
    Session anlegen.

.PARAMETER Start
    Session starten.

.PARAMETER Stop
    Session stoppen.

.PARAMETER Read
    Ereignisse lesen.

.PARAMETER Diagnose
    Ereignisse aggregieren und Probleme erkennen.

.PARAMETER Drop
    Session entfernen.

.PARAMETER EnableException
    Ausnahmen sofort ausloesen.

.EXAMPLE
    # AllInOne-Session erstellen und sofort starten
    Invoke-sqmExtendedEvents -SqlInstance SQL01 -Create -Start

.EXAMPLE
    # Slow Queries > 2 Sekunden aufzeichnen, in Datei speichern
    Invoke-sqmExtendedEvents -SqlInstance SQL01 -Template SlowQueries -SlowQueryThresholdMs 2000 -TargetType File -Create -Start

.EXAMPLE
    # Laufende Session auslesen und Bericht erstellen
    Invoke-sqmExtendedEvents -SqlInstance SQL01 -Read -Diagnose

.EXAMPLE
    # Session stoppen und entfernen
    Invoke-sqmExtendedEvents -SqlInstance SQL01 -Stop -Drop

.NOTES
    Erfordert: dbatools, Invoke-sqmLogging, Get-sqmDefaultOutputPath
    Benoetigt VIEW SERVER STATE auf der Instanz.
    XEL-Datei-Lesen erfordert direkten Zugriff auf den SQL Server-Pfad.
#>
function Invoke-sqmExtendedEvents
{
    [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
    [OutputType([PSCustomObject])]
    param (
        [Parameter(Mandatory = $false)]
        [string]$SqlInstance,
        [Parameter(Mandatory = $false)]
        [System.Management.Automation.PSCredential]$SqlCredential,
        [Parameter(Mandatory = $false)]
        [string]$SessionName = 'sqmPerformance',
        [Parameter(Mandatory = $false)]
        [ValidateSet('SlowQueries', 'Blocking', 'Waits', 'Deadlocks', 'AllInOne')]
        [string]$Template = 'AllInOne',
        [Parameter(Mandatory = $false)]
        [ValidateRange(100, 3600000)]
        [int]$SlowQueryThresholdMs = 1000,
        [Parameter(Mandatory = $false)]
        [string]$WaitTypes = 'LCK_M_X,LCK_M_S,LCK_M_U,PAGEIOLATCH_SH,PAGEIOLATCH_EX,CXPACKET,SOS_SCHEDULER_YIELD',
        [Parameter(Mandatory = $false)]
        [ValidateSet('RingBuffer', 'File')]
        [string]$TargetType = 'RingBuffer',
        [Parameter(Mandatory = $false)]
        [string]$TargetFilePath,
        [Parameter(Mandatory = $false)]
        [ValidateRange(10, 2048)]
        [int]$MaxFileSizeMB = 100,
        [Parameter(Mandatory = $false)]
        [ValidateRange(1, 100)]
        [int]$MaxRolloverFiles = 5,
        [Parameter(Mandatory = $false)]
        [ValidateRange(1, 500)]
        [int]$RingBufferMaxMB = 50,
        [Parameter(Mandatory = $false)]
        [ValidateRange(100, 1000000)]
        [int]$MaxEventsRead = 10000,
        [Parameter(Mandatory = $false)]
        [ValidateRange(1, 525600)]
        [int]$LookbackMinutes = 60,
        [Parameter(Mandatory = $false)]
        [ValidateRange(1, 500)]
        [int]$TopN = 25,
        [Parameter(Mandatory = $false)]
        [string]$OutputPath,

        # --- Aktions-Switches ---
        [Parameter(Mandatory = $false)]
        [switch]$Create,
        [Parameter(Mandatory = $false)]
        [switch]$Start,
        [Parameter(Mandatory = $false)]
        [switch]$Stop,
        [Parameter(Mandatory = $false)]
        [switch]$Read,
        [Parameter(Mandatory = $false)]
        [switch]$Diagnose,
        [Parameter(Mandatory = $false)]
        [switch]$Drop,

        [Parameter(Mandatory = $false)]
        [switch]$EnableException
    )

    begin
    {
        $functionName = $MyInvocation.MyCommand.Name

        if (-not $PSBoundParameters.ContainsKey('SqlInstance') -or [string]::IsNullOrWhiteSpace($SqlInstance))
        {
            $SqlInstance = $env:COMPUTERNAME
        }

        if (-not $script:dbatoolsAvailable)
        {
            $errMsg = "dbatools-Modul nicht gefunden. Bitte installieren: Install-Module dbatools"
            Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR"
            throw $errMsg
        }

        # Wenn kein Aktions-Switch gesetzt: Read + Diagnose
        $noActionSwitch = -not ($Create -or $Start -or $Stop -or $Read -or $Diagnose -or $Drop)
        $runRead    = $Read    -or $noActionSwitch
        $runDiagnose = $Diagnose -or $noActionSwitch

        # OutputPath / TargetFilePath aufloesen
        if (-not $OutputPath)    { $OutputPath     = Join-Path (Get-sqmDefaultOutputPath) 'XEvents' }
        if (-not $TargetFilePath){ $TargetFilePath  = Join-Path (Get-sqmDefaultOutputPath) 'XEvents' }

        $connParams = @{ SqlInstance = $SqlInstance }
        if ($SqlCredential) { $connParams['SqlCredential'] = $SqlCredential }

        $result = [PSCustomObject]@{
            SqlInstance    = $SqlInstance
            SessionName    = $SessionName
            Template       = $Template
            SessionStatus  = $null
            CreateStatus   = $null
            StartStatus    = $null
            StopStatus     = $null
            DropStatus     = $null
            Events         = @()
            EventCount     = 0
            Diagnose       = $null
            ReportFile     = $null
        }

        Invoke-sqmLogging -Message "Starte $functionName auf $SqlInstance - Session '$SessionName' (Template=$Template, TargetType=$TargetType)" -FunctionName $functionName -Level "INFO"
    }

    process
    {
        try
        {
            # =================================================================
            # Hilfsfunktion: Wait-Type-Filter-SQL bauen
            # =================================================================
            $waitFilter = ($WaitTypes -split ',' | ForEach-Object {
                    "N'$($_.Trim())'"
                }) -join ','

            # =================================================================
            # CREATE
            # =================================================================
            if ($Create -and $PSCmdlet.ShouldProcess($SqlInstance, "XEvent-Session '$SessionName' erstellen (Template: $Template)"))
            {
                Invoke-sqmLogging -Message "Erstelle XEvent-Session '$SessionName' (Template=$Template, Target=$TargetType)..." -FunctionName $functionName -Level "INFO"

                # --- Event-Definitionen je Template ---
                $eventBlock = switch ($Template)
                {
                    'SlowQueries' {
                        $thresholdMicro = $SlowQueryThresholdMs * 1000
                        @"
    ADD EVENT sqlserver.sql_statement_completed (
        ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.username,
                sqlserver.client_app_name, sqlserver.client_hostname,
                sqlserver.request_id, sqlserver.session_id, sqlserver.plan_handle)
        WHERE (duration >= $thresholdMicro
           AND sqlserver.is_system = 0)
    ),
    ADD EVENT sqlserver.rpc_completed (
        ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.username,
                sqlserver.client_app_name, sqlserver.client_hostname,
                sqlserver.session_id)
        WHERE (duration >= $thresholdMicro
           AND sqlserver.is_system = 0)
    )
"@
                    }
                    'Blocking'   {
                        @"
    ADD EVENT sqlserver.blocked_process_report (
        ACTION (sqlserver.sql_text, sqlserver.database_name,
                sqlserver.session_id, sqlserver.username)
    )
"@
                    }
                    'Waits'      {
                        @"
    ADD EVENT sqlos.wait_info (
        ACTION (sqlserver.sql_text, sqlserver.session_id,
                sqlserver.database_name, sqlserver.username)
        WHERE (wait_type IN ($waitFilter)
           AND duration > 0
           AND opcode = 1)
    )
"@
                    }
                    'Deadlocks'  {
                        @"
    ADD EVENT sqlserver.xml_deadlock_report (
        ACTION (sqlserver.database_name)
    )
"@
                    }
                    'AllInOne'   {
                        $thresholdMicro = $SlowQueryThresholdMs * 1000
                        @"
    ADD EVENT sqlserver.sql_statement_completed (
        ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.username,
                sqlserver.client_app_name, sqlserver.client_hostname,
                sqlserver.session_id, sqlserver.plan_handle)
        WHERE (duration >= $thresholdMicro
           AND sqlserver.is_system = 0)
    ),
    ADD EVENT sqlserver.rpc_completed (
        ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.username,
                sqlserver.client_app_name, sqlserver.session_id)
        WHERE (duration >= $thresholdMicro
           AND sqlserver.is_system = 0)
    ),
    ADD EVENT sqlserver.blocked_process_report (
        ACTION (sqlserver.sql_text, sqlserver.database_name,
                sqlserver.session_id, sqlserver.username)
    ),
    ADD EVENT sqlos.wait_info (
        ACTION (sqlserver.sql_text, sqlserver.session_id, sqlserver.database_name)
        WHERE (wait_type IN ($waitFilter)
           AND duration > 0
           AND opcode = 1)
    ),
    ADD EVENT sqlserver.xml_deadlock_report (
        ACTION (sqlserver.database_name)
    )
"@
                    }
                }

                # --- Target-Definition ---
                $targetBlock = if ($TargetType -eq 'File')
                {
                    $xelPattern = "$($TargetFilePath.TrimEnd('\'))\$SessionName"
                    $maxBytes   = $MaxFileSizeMB * 1024 * 1024
                    @"
    ADD TARGET package0.event_file (
        SET filename = N'$xelPattern.xel',
            max_file_size = $MaxFileSizeMB,
            max_rollover_files = $MaxRolloverFiles
    )
"@
                }
                else
                {
                    $maxBytes = $RingBufferMaxMB * 1024 * 1024
                    @"
    ADD TARGET package0.ring_buffer (
        SET max_memory = $maxBytes
    )
"@
                }

                $createSql = @"
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'$SessionName')
    DROP EVENT SESSION [$SessionName] ON SERVER;

CREATE EVENT SESSION [$SessionName] ON SERVER
$eventBlock
$targetBlock
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = OFF
);
"@

                try
                {
                    Invoke-DbaQuery @connParams -Database master -Query $createSql -ErrorAction Stop
                    $result.CreateStatus = 'Success'
                    Invoke-sqmLogging -Message "Session '$SessionName' erfolgreich erstellt." -FunctionName $functionName -Level "INFO"
                }
                catch
                {
                    $result.CreateStatus = "Failed: $($_.Exception.Message)"
                    Invoke-sqmLogging -Message "Session '$SessionName' erstellen fehlgeschlagen: $($_.Exception.Message)" -FunctionName $functionName -Level "ERROR"
                    if ($EnableException) { throw }
                }
            }

            # =================================================================
            # START
            # =================================================================
            if ($Start -and $PSCmdlet.ShouldProcess($SqlInstance, "XEvent-Session '$SessionName' starten"))
            {
                try
                {
                    $startSql = "ALTER EVENT SESSION [$SessionName] ON SERVER STATE = START;"
                    Invoke-DbaQuery @connParams -Database master -Query $startSql -ErrorAction Stop
                    $result.StartStatus = 'Running'
                    Invoke-sqmLogging -Message "Session '$SessionName' gestartet." -FunctionName $functionName -Level "INFO"
                }
                catch
                {
                    $result.StartStatus = "Failed: $($_.Exception.Message)"
                    Invoke-sqmLogging -Message "Session '$SessionName' starten fehlgeschlagen: $($_.Exception.Message)" -FunctionName $functionName -Level "ERROR"
                    if ($EnableException) { throw }
                }
            }

            # =================================================================
            # STOP
            # =================================================================
            if ($Stop -and $PSCmdlet.ShouldProcess($SqlInstance, "XEvent-Session '$SessionName' stoppen"))
            {
                try
                {
                    $stopSql = "ALTER EVENT SESSION [$SessionName] ON SERVER STATE = STOP;"
                    Invoke-DbaQuery @connParams -Database master -Query $stopSql -ErrorAction Stop
                    $result.StopStatus = 'Stopped'
                    Invoke-sqmLogging -Message "Session '$SessionName' gestoppt." -FunctionName $functionName -Level "INFO"
                }
                catch
                {
                    $result.StopStatus = "Failed: $($_.Exception.Message)"
                    Invoke-sqmLogging -Message "Session '$SessionName' stoppen fehlgeschlagen: $($_.Exception.Message)" -FunctionName $functionName -Level "ERROR"
                    if ($EnableException) { throw }
                }
            }

            # =================================================================
            # SESSION-STATUS abfragen (immer)
            # =================================================================
            try
            {
                $statusSql = @"
SELECT
    s.name AS SessionName,
    s.create_time AS CreateTime,
    CASE ds.create_time WHEN NULL THEN 'Stopped' ELSE 'Running' END AS SessionState,
    ds.create_time AS StartedAt,
    t.target_name AS TargetType,
    CAST(t.target_data AS XML) AS TargetDataXml,
    (SELECT SUM(CAST(f.column_value AS BIGINT))
     FROM sys.dm_xe_session_object_columns f
     WHERE f.event_session_address = ds.address
       AND f.column_name = 'event_count') AS ApproxEventCount
FROM sys.server_event_sessions s
LEFT JOIN sys.dm_xe_sessions ds
    ON s.name = ds.name
LEFT JOIN sys.dm_xe_session_targets t
    ON ds.address = t.event_session_address
WHERE s.name = N'$SessionName'
"@
                $statusRows = Invoke-DbaQuery @connParams -Database master -Query $statusSql -ErrorAction Stop
                if ($statusRows)
                {
                    $result.SessionStatus = $statusRows | Select-Object -First 1 SessionName, SessionState, StartedAt, TargetType, ApproxEventCount
                }
                else
                {
                    $result.SessionStatus = [PSCustomObject]@{ SessionName = $SessionName; SessionState = 'NotFound' }
                }
            }
            catch
            {
                Invoke-sqmLogging -Message "Session-Status konnte nicht abgefragt werden: $($_.Exception.Message)" -FunctionName $functionName -Level "WARNING"
            }

            # =================================================================
            # READ - Ereignisse aus RingBuffer oder XEL-Datei lesen
            # =================================================================
            if ($runRead)
            {
                Invoke-sqmLogging -Message "Lese Ereignisse aus Session '$SessionName' (max $MaxEventsRead)..." -FunctionName $functionName -Level "INFO"

                $sessionState = if ($result.SessionStatus) { $result.SessionStatus.SessionState } else { $null }
                if ($sessionState -notin @('Running', 'Stopped') -and $sessionState -ne $null)
                {
                    Invoke-sqmLogging -Message "Session '$SessionName' nicht gefunden - kein Read moeglich." -FunctionName $functionName -Level "WARNING"
                }
                else
                {
                    try
                    {
                        # Ziel-Typ der laufenden Session ermitteln
                        $targetTypeSql = @"
SELECT t.target_name, t.target_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
WHERE s.name = N'$SessionName'
"@
                        $targetInfo = Invoke-DbaQuery @connParams -Database master -Query $targetTypeSql -ErrorAction SilentlyContinue

                        $parsedEvents = [System.Collections.Generic.List[PSCustomObject]]::new()

                        if ($targetInfo -and $targetInfo.target_name -eq 'ring_buffer')
                        {
                            # RingBuffer-XML parsen
                            $rbReadSql = @"
SELECT CAST(t.target_data AS XML) AS RingBufferXml
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
WHERE s.name = N'$SessionName'
  AND t.target_name = 'ring_buffer'
"@
                            $rbRow = Invoke-DbaQuery @connParams -Database master -Query $rbReadSql -ErrorAction Stop
                            if ($rbRow -and $rbRow.RingBufferXml)
                            {
                                [xml]$rbXml = $rbRow.RingBufferXml
                                $eventNodes = $rbXml.RingBufferTarget.event | Select-Object -First $MaxEventsRead
                                foreach ($evNode in $eventNodes)
                                {
                                    $ev = [PSCustomObject]@{
                                        EventName    = $evNode.name
                                        Timestamp    = $evNode.timestamp
                                        DatabaseName = ($evNode.action | Where-Object { $_.name -eq 'database_name' }).value
                                        Username     = ($evNode.action | Where-Object { $_.name -eq 'username' }).value
                                        SessionId    = ($evNode.action | Where-Object { $_.name -eq 'session_id' }).value
                                        ClientApp    = ($evNode.action | Where-Object { $_.name -eq 'client_app_name' }).value
                                        ClientHost   = ($evNode.action | Where-Object { $_.name -eq 'client_hostname' }).value
                                        DurationMs   = [math]::Round(
                                            [double](($evNode.data | Where-Object { $_.name -eq 'duration' }).value) / 1000.0, 2)
                                        WaitType     = ($evNode.data | Where-Object { $_.name -eq 'wait_type' }).text
                                        SqlText      = ($evNode.action | Where-Object { $_.name -eq 'sql_text' }).value
                                        DeadlockXml  = ($evNode.data | Where-Object { $_.name -eq 'xml_report' }).value
                                        RowsAffected = ($evNode.data | Where-Object { $_.name -eq 'row_count' }).value
                                        PhysicalReads = ($evNode.data | Where-Object { $_.name -eq 'physical_reads' }).value
                                        LogicalReads  = ($evNode.data | Where-Object { $_.name -eq 'logical_reads' }).value
                                        Writes        = ($evNode.data | Where-Object { $_.name -eq 'writes' }).value
                                        CpuTime       = ($evNode.data | Where-Object { $_.name -eq 'cpu_time' }).value
                                    }
                                    $parsedEvents.Add($ev)
                                }
                            }
                        }
                        elseif ($targetInfo -and $targetInfo.target_name -eq 'event_file')
                        {
                            # XEL-Datei-Pfad aus sys.server_event_sessions lesen
                            $xelPathSql = @"
SELECT f.value AS XelPath
FROM sys.server_event_sessions s
JOIN sys.server_event_session_targets t ON s.event_session_id = t.event_session_id
CROSS APPLY (
    SELECT c.value
    FROM sys.server_event_session_fields c
    WHERE c.event_session_id = s.event_session_id
      AND c.object_id = t.target_id
      AND c.name = 'filename'
) f
WHERE s.name = N'$SessionName'
"@
                            $xelPathRow = Invoke-DbaQuery @connParams -Database master -Query $xelPathSql -ErrorAction SilentlyContinue
                            if ($xelPathRow -and $xelPathRow.XelPath)
                            {
                                $xelPattern = $xelPathRow.XelPath -replace '\.xel$', '*.xel'
                                $readXelSql = @"
SELECT TOP ($MaxEventsRead)
    object_name AS EventName,
    CAST(event_data AS XML) AS EventXml,
    file_name AS SourceFile,
    file_offset AS FileOffset
FROM sys.fn_xe_file_target_read_file(N'$xelPattern', NULL, NULL, NULL)
ORDER BY file_offset DESC
"@
                                $xelRows = Invoke-DbaQuery @connParams -Database master -Query $readXelSql -ErrorAction Stop
                                foreach ($xelRow in $xelRows)
                                {
                                    try
                                    {
                                        [xml]$evXml = $xelRow.EventXml
                                        $evData = $evXml.event
                                        $getVal = { param($name)
                                            $node = $evData.data | Where-Object { $_.name -eq $name }
                                            if ($node) { $node.value } else { $null }
                                        }
                                        $getAct = { param($name)
                                            $node = $evData.action | Where-Object { $_.name -eq $name }
                                            if ($node) { $node.value } else { $null }
                                        }
                                        $dur = & $getVal 'duration'
                                        $parsedEvents.Add([PSCustomObject]@{
                                            EventName     = $xelRow.EventName
                                            Timestamp     = $evData.timestamp
                                            DatabaseName  = & $getAct 'database_name'
                                            Username      = & $getAct 'username'
                                            SessionId     = & $getAct 'session_id'
                                            ClientApp     = & $getAct 'client_app_name'
                                            ClientHost    = & $getAct 'client_hostname'
                                            DurationMs    = if ($dur) { [math]::Round([double]$dur / 1000.0, 2) } else { $null }
                                            WaitType      = ($evData.data | Where-Object { $_.name -eq 'wait_type' }).text
                                            SqlText       = & $getAct 'sql_text'
                                            DeadlockXml   = & $getVal 'xml_report'
                                            RowsAffected  = & $getVal 'row_count'
                                            PhysicalReads = & $getVal 'physical_reads'
                                            LogicalReads  = & $getVal 'logical_reads'
                                            Writes        = & $getVal 'writes'
                                            CpuTime       = & $getVal 'cpu_time'
                                            SourceFile    = $xelRow.SourceFile
                                        })
                                    }
                                    catch { }
                                }
                            }
                        }

                        $result.Events     = $parsedEvents.ToArray()
                        $result.EventCount = $parsedEvents.Count
                        Invoke-sqmLogging -Message "$($parsedEvents.Count) Ereignisse gelesen." -FunctionName $functionName -Level "INFO"
                    }
                    catch
                    {
                        Invoke-sqmLogging -Message "Fehler beim Lesen der Ereignisse: $($_.Exception.Message)" -FunctionName $functionName -Level "ERROR"
                        if ($EnableException) { throw }
                    }
                }
            }

            # =================================================================
            # DIAGNOSE - Ereignisse aggregieren und Muster erkennen
            # =================================================================
            if ($runDiagnose -and $result.Events.Count -gt 0)
            {
                Invoke-sqmLogging -Message "Starte Diagnose ($($result.Events.Count) Ereignisse, Fenster: ${LookbackMinutes} Min)..." -FunctionName $functionName -Level "INFO"

                $cutoff = (Get-Date).AddMinutes(-$LookbackMinutes)
                $window = $result.Events | Where-Object {
                    $ts = $null
                    if ($_.Timestamp -and [datetime]::TryParse($_.Timestamp, [ref]$ts)) { $ts -ge $cutoff } else { $true }
                }

                $diagnoseResult = [PSCustomObject]@{
                    TopSlowQueries    = @()
                    TopWaits          = @()
                    BlockingEvents    = @()
                    DeadlockEvents    = @()
                    IssueCount        = 0
                    Issues            = @()
                }

                $issues = [System.Collections.Generic.List[PSCustomObject]]::new()

                # D1: Top Slow Queries
                $slowEvts = $window | Where-Object { $_.EventName -in @('sql_statement_completed','rpc_completed') -and $_.DurationMs -gt 0 }
                if ($slowEvts)
                {
                    $topSlow = $slowEvts |
                        Group-Object SqlText |
                        ForEach-Object {
                            $durs = $_.Group | ForEach-Object { [double]$_.DurationMs }
                            [PSCustomObject]@{
                                SqlText       = ($_.Name -replace '\s+', ' ').Trim() | ForEach-Object { $_.Substring(0, [Math]::Min(300, $_.Length)) }
                                ExecCount     = $_.Count
                                AvgDurationMs = [math]::Round(($durs | Measure-Object -Average).Average, 2)
                                MaxDurationMs = [math]::Round(($durs | Measure-Object -Maximum).Maximum, 2)
                                TotalDurationMs = [math]::Round(($durs | Measure-Object -Sum).Sum, 2)
                                DatabaseName  = ($_.Group | Select-Object -First 1).DatabaseName
                            }
                        } |
                        Sort-Object AvgDurationMs -Descending |
                        Select-Object -First $TopN
                    $diagnoseResult.TopSlowQueries = @($topSlow)

                    $verySlowCount = @($slowEvts | Where-Object { $_.DurationMs -ge ($SlowQueryThresholdMs * 10) }).Count
                    if ($verySlowCount -gt 0)
                    {
                        $issues.Add([PSCustomObject]@{
                            Severity    = 'Warning'
                            Category    = 'VerySlowQueries'
                            Description = "$verySlowCount Queries mit Dauer >= $($SlowQueryThresholdMs * 10) ms im Auswertungsfenster"
                            Detail      = "Top: $($topSlow[0].SqlText.Substring(0, [Math]::Min(200, $topSlow[0].SqlText.Length))) [Avg: $($topSlow[0].AvgDurationMs) ms]"
                        })
                    }
                }

                # D2: Top Wait Types
                $waitEvts = $window | Where-Object { $_.EventName -eq 'wait_info' -and $_.WaitType }
                if ($waitEvts)
                {
                    $topWaits = $waitEvts |
                        Group-Object WaitType |
                        ForEach-Object {
                            $durs = $_.Group | ForEach-Object { [double]$_.DurationMs }
                            [PSCustomObject]@{
                                WaitType       = $_.Name
                                WaitCount      = $_.Count
                                TotalWaitMs    = [math]::Round(($durs | Measure-Object -Sum).Sum, 2)
                                AvgWaitMs      = [math]::Round(($durs | Measure-Object -Average).Average, 2)
                                MaxWaitMs      = [math]::Round(($durs | Measure-Object -Maximum).Maximum, 2)
                            }
                        } |
                        Sort-Object TotalWaitMs -Descending |
                        Select-Object -First $TopN
                    $diagnoseResult.TopWaits = @($topWaits)

                    # Lock-dominanz erkennen
                    $lockWaits = $topWaits | Where-Object { $_.WaitType -like 'LCK_*' }
                    if ($lockWaits)
                    {
                        $lockTotal = ($lockWaits | Measure-Object TotalWaitMs -Sum).Sum
                        if ($lockTotal -gt 5000)
                        {
                            $issues.Add([PSCustomObject]@{
                                Severity    = if ($lockTotal -gt 60000) { 'Critical' } else { 'Warning' }
                                Category    = 'LockContention'
                                Description = "Lock-Waits: $([math]::Round($lockTotal/1000,1)) Sek gesamt im Auswertungsfenster"
                                Detail      = ($lockWaits | ForEach-Object { "$($_.WaitType): $($_.WaitCount)x / $($_.TotalWaitMs) ms" }) -join '; '
                            })
                        }
                    }

                    # CXPACKET / Parallelismus
                    $cxp = $topWaits | Where-Object { $_.WaitType -eq 'CXPACKET' }
                    if ($cxp -and $cxp.WaitCount -gt 100)
                    {
                        $issues.Add([PSCustomObject]@{
                            Severity    = 'Warning'
                            Category    = 'ParallelismPressure'
                            Description = "CXPACKET: $($cxp.WaitCount) Waits / $([math]::Round($cxp.TotalWaitMs/1000,1)) Sek gesamt - moeglicher uebermassiger Parallelismus"
                            Detail      = "MAXDOP oder Cost Threshold for Parallelism pruefen."
                        })
                    }
                }

                # D3: Blocking-Ereignisse
                $blockEvts = $window | Where-Object { $_.EventName -eq 'blocked_process_report' }
                if ($blockEvts)
                {
                    $diagnoseResult.BlockingEvents = @($blockEvts | Select-Object Timestamp, DatabaseName, DurationMs, SqlText, Username | Sort-Object DurationMs -Descending | Select-Object -First $TopN)
                    $issues.Add([PSCustomObject]@{
                        Severity    = if ($blockEvts.Count -gt 10) { 'Critical' } else { 'Warning' }
                        Category    = 'Blocking'
                        Description = "$($blockEvts.Count) Blocking-Ereignis(se) erfasst"
                        Detail      = "Maximale Blockierungsdauer: $([math]::Round(($blockEvts | Measure-Object DurationMs -Maximum).Maximum, 2)) ms"
                    })
                }

                # D4: Deadlocks
                $deadlockEvts = $window | Where-Object { $_.EventName -eq 'xml_deadlock_report' }
                if ($deadlockEvts)
                {
                    $diagnoseResult.DeadlockEvents = @($deadlockEvts | Select-Object Timestamp, DatabaseName, DeadlockXml)
                    $issues.Add([PSCustomObject]@{
                        Severity    = 'Critical'
                        Category    = 'Deadlock'
                        Description = "$($deadlockEvts.Count) Deadlock(s) erfasst"
                        Detail      = "Timestamps: $(($deadlockEvts | ForEach-Object { $_.Timestamp }) -join ', ')"
                    })
                }

                $diagnoseResult.Issues     = $issues.ToArray()
                $diagnoseResult.IssueCount = $issues.Count
                $result.Diagnose = $diagnoseResult

                $critCount = @($issues | Where-Object { $_.Severity -eq 'Critical' }).Count
                $warnCount = @($issues | Where-Object { $_.Severity -eq 'Warning' }).Count
                Invoke-sqmLogging -Message "Diagnose abgeschlossen: $critCount Critical, $warnCount Warning." -FunctionName $functionName -Level "INFO"
            }

            # =================================================================
            # BERICHT SPEICHERN
            # =================================================================
            if ($result.EventCount -gt 0 -or ($result.Diagnose -and $result.Diagnose.IssueCount -gt 0))
            {
                if (-not (Test-Path $OutputPath)) { New-Item -ItemType Directory -Path $OutputPath -Force | Out-Null }

                $safeInst  = $SqlInstance -replace '[\\/:<>|]', '_'
                $safeSess  = $SessionName -replace '[\\/:<>|]', '_'
                $timestamp = Get-Date -Format 'yyyyMMdd_HHmsqm'
                $baseFile  = Join-Path $OutputPath "XE_${safeInst}_${safeSess}_${timestamp}"

                # Rohdaten (Ereignisse) als CSV
                if ($result.EventCount -gt 0)
                {
                    $csvFile = "${baseFile}_Events.csv"
                    $result.Events | Select-Object EventName, Timestamp, DatabaseName, Username,
                        SessionId, ClientApp, ClientHost, DurationMs, WaitType,
                        LogicalReads, PhysicalReads, Writes, CpuTime, RowsAffected,
                        @{ N = 'SqlText'; E = { if ($_.SqlText) { $_.SqlText.ToString().Substring(0, [Math]::Min(500, $_.SqlText.ToString().Length)) } } } |
                        Export-Csv -Path $csvFile -NoTypeInformation -Encoding UTF8 -Force
                    Invoke-sqmLogging -Message "Ereignisse gespeichert: $csvFile" -FunctionName $functionName -Level "INFO"
                }

                # Diagnose-Bericht als TXT
                if ($result.Diagnose)
                {
                    $lines = [System.Collections.Generic.List[string]]::new()
                    $lines.Add("=" * 70)
                    $lines.Add(" EXTENDED EVENTS DIAGNOSE - $SqlInstance | Session: $SessionName")
                    $lines.Add(" Erstellt: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')")
                    $lines.Add(" Erfasste Ereignisse: $($result.EventCount) | Auswertungsfenster: ${LookbackMinutes} Min")
                    $lines.Add("=" * 70)
                    $lines.Add("")

                    if ($result.Diagnose.Issues.Count -gt 0)
                    {
                        $lines.Add("ERKANNTE PROBLEME ($($result.Diagnose.IssueCount)):")
                        $lines.Add("-" * 50)
                        $grouped = $result.Diagnose.Issues | Group-Object Severity | Sort-Object { if ($_.Name -eq 'Critical') { 0 } else { 1 } }
                        foreach ($grp in $grouped)
                        {
                            foreach ($issue in $grp.Group)
                            {
                                $lines.Add(" [$($grp.Name.ToUpper())] $($issue.Category): $($issue.Description)")
                                if ($issue.Detail) { $lines.Add(" $($issue.Detail.Substring(0, [Math]::Min(200, $issue.Detail.Length)))") }
                            }
                        }
                        $lines.Add("")
                    }

                    if ($result.Diagnose.TopSlowQueries.Count -gt 0)
                    {
                        $lines.Add("TOP $TopN SLOW QUERIES (nach Avg-Dauer):")
                        $lines.Add("-" * 50)
                        $rank = 1
                        foreach ($sq in $result.Diagnose.TopSlowQueries)
                        {
                            $txt = if ($sq.SqlText) { $sq.SqlText.Substring(0, [Math]::Min(120, $sq.SqlText.Length)) } else { '(n/a)' }
                            $lines.Add(" $rank. Avg: $($sq.AvgDurationMs) ms | Max: $($sq.MaxDurationMs) ms | Count: $($sq.ExecCount) | DB: $($sq.DatabaseName)")
                            $lines.Add(" $txt")
                            $rank++
                        }
                        $lines.Add("")
                    }

                    if ($result.Diagnose.TopWaits.Count -gt 0)
                    {
                        $lines.Add("TOP $TopN WAIT TYPES (nach Total-Wartezeit):")
                        $lines.Add("-" * 50)
                        foreach ($wt in $result.Diagnose.TopWaits)
                        {
                            $lines.Add(" $($wt.WaitType.PadRight(30)) Count: $($wt.WaitCount.ToString().PadLeft(6)) Total: $([math]::Round($wt.TotalWaitMs/1000,2)) Sek Avg: $($wt.AvgWaitMs) ms Max: $($wt.MaxWaitMs) ms")
                        }
                        $lines.Add("")
                    }

                    if ($result.Diagnose.BlockingEvents.Count -gt 0)
                    {
                        $lines.Add("BLOCKING-EREIGNISSE ($($result.Diagnose.BlockingEvents.Count)):")
                        $lines.Add("-" * 50)
                        foreach ($bl in $result.Diagnose.BlockingEvents | Select-Object -First 10)
                        {
                            $lines.Add(" [$($bl.Timestamp)] $($bl.DatabaseName) | $($bl.DurationMs) ms | $($bl.Username)")
                        }
                        $lines.Add("")
                    }

                    if ($result.Diagnose.DeadlockEvents.Count -gt 0)
                    {
                        $lines.Add("DEADLOCK-EREIGNISSE ($($result.Diagnose.DeadlockEvents.Count)):")
                        $lines.Add("-" * 50)
                        foreach ($dl in $result.Diagnose.DeadlockEvents)
                        {
                            $lines.Add(" [$($dl.Timestamp)] $($dl.DatabaseName)")
                            if ($dl.DeadlockXml)
                            {
                                $dlFile = "${baseFile}_Deadlock_$($dl.Timestamp -replace '[:\s]', '_').xml"
                                $dl.DeadlockXml | Out-File -FilePath $dlFile -Encoding UTF8 -Force
                                $lines.Add(" Deadlock-XML gespeichert: $dlFile")
                            }
                        }
                        $lines.Add("")
                    }

                    $txtFile = "${baseFile}_Report.txt"
                    $lines | Out-File -FilePath $txtFile -Encoding UTF8 -Force
                    $result.ReportFile = $txtFile
                    Invoke-sqmLogging -Message "Diagnose-Bericht gespeichert: $txtFile" -FunctionName $functionName -Level "INFO"

                    # Top-Slow-Queries als eigene CSV
                    if ($result.Diagnose.TopSlowQueries.Count -gt 0)
                    {
                        $slowCsv = "${baseFile}_TopSlowQueries.csv"
                        $result.Diagnose.TopSlowQueries | Export-Csv -Path $slowCsv -NoTypeInformation -Encoding UTF8 -Force
                    }

                    # Top-Waits als eigene CSV
                    if ($result.Diagnose.TopWaits.Count -gt 0)
                    {
                        $waitCsv = "${baseFile}_TopWaits.csv"
                        $result.Diagnose.TopWaits | Export-Csv -Path $waitCsv -NoTypeInformation -Encoding UTF8 -Force
                    }
                }
            }

            # =================================================================
            # DROP
            # =================================================================
            if ($Drop -and $PSCmdlet.ShouldProcess($SqlInstance, "XEvent-Session '$SessionName' entfernen"))
            {
                try
                {
                    $dropSql = @"
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'$SessionName')
BEGIN
    ALTER EVENT SESSION [$SessionName] ON SERVER STATE = STOP;
    DROP EVENT SESSION [$SessionName] ON SERVER;
END
"@
                    Invoke-DbaQuery @connParams -Database master -Query $dropSql -ErrorAction Stop
                    $result.DropStatus = 'Dropped'
                    Invoke-sqmLogging -Message "Session '$SessionName' entfernt." -FunctionName $functionName -Level "INFO"
                }
                catch
                {
                    $result.DropStatus = "Failed: $($_.Exception.Message)"
                    Invoke-sqmLogging -Message "Session '$SessionName' entfernen fehlgeschlagen: $($_.Exception.Message)" -FunctionName $functionName -Level "ERROR"
                    if ($EnableException) { throw }
                }
            }
        }
        catch
        {
            $errMsg = "Schwerwiegender Fehler in $functionName : $($_.Exception.Message)"
            Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR"
            if ($EnableException) { throw }
            Write-Error $errMsg
        }

        return $result
    }

    end
    {
        Invoke-sqmLogging -Message "$functionName abgeschlossen." -FunctionName $functionName -Level "INFO"
    }
}