bin/Public/Get-sqmWaitStatistics.ps1
|
<# .SYNOPSIS Liest und analysiert SQL Server Wait Statistics aus sys.dm_os_wait_stats. .DESCRIPTION Liest die kumulierten Wait Statistics der Instanz, filtert bekannte idle Waits heraus und gibt die Top-N Waits mit Kategorie und Handlungsempfehlung zurueck. Optional: Snapshot-Vergleich (vorher/nachher) ueber -SnapshotBefore/-SnapshotAfter. .PARAMETER SqlInstance SQL Server-Instanz. Standard: lokaler Computername. .PARAMETER SqlCredential PSCredential fuer die Verbindung. .PARAMETER TopN Anzahl der zurueckgegebenen Top-Wait-Types. Standard: 25. .PARAMETER IncludeIdle Idle-Waits (SLEEP_*, WAITFOR etc.) einschliessen. Standard: aus. .PARAMETER SnapshotBefore PSCustomObject-Array eines frueheren Snapshots (Ausgabe von -SaveSnapshot). Wenn angegeben, wird nur das Delta berechnet. .PARAMETER SaveSnapshot Gibt einen Snapshot-Array zurueck, der spaeter als SnapshotBefore verwendet werden kann. .PARAMETER OutputPath Wenn angegeben, wird ein CSV-Bericht gespeichert. .PARAMETER EnableException Ausnahmen sofort ausloesen. .EXAMPLE Get-sqmWaitStatistics -SqlInstance "SQL01" -TopN 20 .EXAMPLE # Snapshot-Vergleich (Delta-Messung) $before = Get-sqmWaitStatistics -SqlInstance "SQL01" -SaveSnapshot # ... workload laufen lassen ... Get-sqmWaitStatistics -SqlInstance "SQL01" -SnapshotBefore $before .NOTES Erfordert: dbatools, Invoke-sqmLogging Benoetigt VIEW SERVER STATE. #> function Get-sqmWaitStatistics { [CmdletBinding()] [OutputType([PSCustomObject])] param ( [Parameter(Mandatory = $false)] [string]$SqlInstance, [Parameter(Mandatory = $false)] [System.Management.Automation.PSCredential]$SqlCredential, [Parameter(Mandatory = $false)] [ValidateRange(1, 500)] [int]$TopN = 25, [Parameter(Mandatory = $false)] [switch]$IncludeIdle, [Parameter(Mandatory = $false)] [object[]]$SnapshotBefore, [Parameter(Mandatory = $false)] [switch]$SaveSnapshot, [Parameter(Mandatory = $false)] [string]$OutputPath, [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." Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" throw $errMsg } # Bekannte idle Waits ausschliessen $idleWaits = @( 'SLEEP_TASK','SLEEP_SYSTEMTASK','SLEEP_DBSTARTUP','SLEEP_DBTASK', 'SLEEP_TEMPDBSTARTUP','SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY', 'SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP','SLEEP_TEMPDBSTARTUP', 'SLEEP_WORKER_THREAD','WAITFOR','WAITFOR_TASKSHUTDOWN', 'BROKER_TO_FLUSH','BROKER_SLEEP','BROKER_EVENTHANDLER', 'SQLTRACE_BUFFER_FLUSH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','CLR_SEMAPHORE', 'DISPATCHER_QUEUE_SEMAPHORE','XE_DISPATCHER_WAIT','XE_TIMER_EVENT', 'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','HADR_WORK_QUEUE','HADR_SLEEP_TASK', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION','LAZYWRITER_SLEEP', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH', 'RESOURCE_QUEUE','SERVER_IDLE_CHECK','SLEEP_DBSTARTUP', 'SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP','ONDEMAND_TASK_QUEUE', 'WAIT_XTP_HOST_WAIT','WAIT_XTP_ONLINE_INDEX_BUILD', 'FT_IFTS_SCHEDULER_IDLE_WAIT','FT_IFTSHC_MUTEX','DIRTY_PAGE_POLL' ) # Wait-Kategorien fuer Empfehlungen $waitCategories = @{ 'PAGEIOLATCH_SH' = @{ Category = 'I/O'; Recommendation = 'Disk I/O Engpass - Storage Performance pruefen, fehlende Indizes analysieren.' } 'PAGEIOLATCH_EX' = @{ Category = 'I/O'; Recommendation = 'Disk I/O Engpass beim Schreiben - Storage und Index-Fragmentierung pruefen.' } 'PAGEIOLATCH_UP' = @{ Category = 'I/O'; Recommendation = 'Disk I/O beim Aktualisieren - Storage Performance pruefen.' } 'WRITELOG' = @{ Category = 'I/O'; Recommendation = 'Log-I/O Engpass - schnelleres Storage fuer Log-Dateien, kein RAID5.' } 'IO_COMPLETION' = @{ Category = 'I/O'; Recommendation = 'Allgemeine I/O-Verzoegerungen - Storage Performance pruefen.' } 'ASYNC_IO_COMPLETION'= @{ Category = 'I/O'; Recommendation = 'Asynchrone I/O Verzoegerung - Backup/Restore/DBCC auf langsamem Storage.' } 'LCK_M_X' = @{ Category = 'Locking'; Recommendation = 'Exclusive Lock-Waits - Transaktionsdesign, Index-Abdeckung, Isolation Level pruefen.' } 'LCK_M_S' = @{ Category = 'Locking'; Recommendation = 'Shared Lock-Waits - Read Committed Snapshot (RCSI) erwaegen.' } 'LCK_M_U' = @{ Category = 'Locking'; Recommendation = 'Update Lock-Waits - Abfragen optimieren, Index-Abdeckung verbessern.' } 'LCK_M_IX' = @{ Category = 'Locking'; Recommendation = 'Intent Exclusive Lock-Waits - Blocking analysieren, Indizes optimieren.' } 'LCK_M_IS' = @{ Category = 'Locking'; Recommendation = 'Intent Shared Lock-Waits - Transaktionsdauer reduzieren.' } 'CXPACKET' = @{ Category = 'Parallelism'; Recommendation = 'Parallelismus-Waits - MAXDOP und Cost Threshold for Parallelism anpassen.' } 'CXCONSUMER' = @{ Category = 'Parallelism'; Recommendation = 'Parallele Consumer warten - MAXDOP Einstellung pruefen.' } 'RESOURCE_SEMAPHORE' = @{ Category = 'Memory'; Recommendation = 'Memory Grant Wartezeit - Abfragen mit zu grossem Memory Grant, Index-Fragmentierung.' } 'RESOURCE_SEMAPHORE_QUERY_COMPILE' = @{ Category = 'Memory'; Recommendation = 'Kompilierungs-Speichermangel - zu viele gleichzeitige Kompilierungen.' } 'CMEMTHREAD' = @{ Category = 'Memory'; Recommendation = 'Speicher-Contention - evtl. NUMA-Ungleichgewicht oder max server memory zu niedrig.' } 'SOS_SCHEDULER_YIELD'= @{ Category = 'CPU'; Recommendation = 'CPU-Druck - langlaufende Abfragen optimieren, CPU-Ressourcen erhoehen.' } 'THREADPOOL' = @{ Category = 'CPU'; Recommendation = 'Worker Thread Mangel - max worker threads oder Hardware-Kapazitaet pruefen.' } 'PAGELATCH_EX' = @{ Category = 'Latch'; Recommendation = 'Page-Latch Contention - oft tempdb-Engpass oder Hot Pages (GUID-basierte Indizes).' } 'PAGELATCH_SH' = @{ Category = 'Latch'; Recommendation = 'Shared Page Latch Contention - tempdb-Dateien erhoehen (8 empfohlen).' } 'PAGELATCH_UP' = @{ Category = 'Latch'; Recommendation = 'Update Page Latch - Last Page Insert Contention, Sequential Keys vermeiden.' } 'LATCH_EX' = @{ Category = 'Latch'; Recommendation = 'Interne Latch-Contention - dbatools Get-DbaLatch fuer Details verwenden.' } 'LATCH_SH' = @{ Category = 'Latch'; Recommendation = 'Interne Shared Latch - Statistiken und Plandaten koennen betroffen sein.' } 'DBMIRROR_EVENTS_QUEUE' = @{ Category = 'Network'; Recommendation = 'Database Mirroring Queue - Netzwerklatenz zwischen Partnern pruefen.' } 'DBMIRRORING_CMD' = @{ Category = 'Network'; Recommendation = 'Mirroring-Synchronisation - Netzwerkdurchsatz pruefen.' } 'ASYNC_NETWORK_IO' = @{ Category = 'Network'; Recommendation = 'Client verarbeitet Daten zu langsam - Result Sets verkleinern, Paginierung erwaegen.' } } Invoke-sqmLogging -Message "Starte $functionName auf $SqlInstance (TopN=$TopN, IncludeIdle=$IncludeIdle)" -FunctionName $functionName -Level "INFO" } process { try { $connParams = @{ SqlInstance = $SqlInstance } if ($SqlCredential) { $connParams['SqlCredential'] = $SqlCredential } $waitSql = @" SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms, CASE WHEN waiting_tasks_count > 0 THEN CAST(wait_time_ms * 1.0 / waiting_tasks_count AS DECIMAL(18,2)) ELSE 0 END AS avg_wait_ms FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0 ORDER BY wait_time_ms DESC "@ $rawWaits = Invoke-DbaQuery @connParams -Database master -Query $waitSql -ErrorAction Stop if (-not $rawWaits) { Invoke-sqmLogging -Message "Keine Wait Statistics gelesen." -FunctionName $functionName -Level "WARNING" return } # Snapshot-Modus: Rohdaten zurueckgeben if ($SaveSnapshot) { Invoke-sqmLogging -Message "Snapshot erstellt: $($rawWaits.Count) Wait Types." -FunctionName $functionName -Level "INFO" return $rawWaits } # Delta berechnen wenn SnapshotBefore vorhanden $workingSet = if ($SnapshotBefore) { $beforeHash = @{} foreach ($row in $SnapshotBefore) { $beforeHash[$row.wait_type] = $row } $rawWaits | ForEach-Object { $prev = $beforeHash[$_.wait_type] [PSCustomObject]@{ wait_type = $_.wait_type waiting_tasks_count = $_.waiting_tasks_count - [long]($prev.waiting_tasks_count) wait_time_ms = $_.wait_time_ms - [long]($prev.wait_time_ms) max_wait_time_ms = $_.max_wait_time_ms signal_wait_time_ms = $_.signal_wait_time_ms - [long]($prev.signal_wait_time_ms) resource_wait_time_ms = ($_.wait_time_ms - $_.signal_wait_time_ms) - ([long]($prev.wait_time_ms) - [long]($prev.signal_wait_time_ms)) avg_wait_ms = if (($_.waiting_tasks_count - [long]($prev.waiting_tasks_count)) -gt 0) { [math]::Round(($_.wait_time_ms - [long]($prev.wait_time_ms)) * 1.0 / ($_.waiting_tasks_count - [long]($prev.waiting_tasks_count)), 2) } else { 0 } IsDelta = $true } } | Where-Object { $_.wait_time_ms -gt 0 } } else { $rawWaits } # Idle Waits filtern $filtered = if ($IncludeIdle) { $workingSet } else { $workingSet | Where-Object { $_.wait_type -notin $idleWaits } } # Gesamtwartezeit fuer Prozentberechnung $totalWaitMs = ($filtered | Measure-Object wait_time_ms -Sum).Sum if ($totalWaitMs -eq 0) { $totalWaitMs = 1 } # Top-N aufbauen $results = $filtered | Sort-Object wait_time_ms -Descending | Select-Object -First $TopN | ForEach-Object { $cat = if ($waitCategories.ContainsKey($_.wait_type)) { $waitCategories[$_.wait_type].Category } else { 'Other' } $rec = if ($waitCategories.ContainsKey($_.wait_type)) { $waitCategories[$_.wait_type].Recommendation } else { '' } $pct = [math]::Round($_.wait_time_ms * 100.0 / $totalWaitMs, 1) [PSCustomObject]@{ WaitType = $_.wait_type Category = $cat WaitTimeSec = [math]::Round($_.wait_time_ms / 1000.0, 1) WaitTimePct = $pct WaitingTasksCount = $_.waiting_tasks_count AvgWaitMs = $_.avg_wait_ms MaxWaitMs = $_.max_wait_time_ms SignalWaitMs = $_.signal_wait_time_ms ResourceWaitMs = $_.resource_wait_time_ms IsDelta = [bool]$SnapshotBefore Recommendation = $rec } } # CSV-Export if ($OutputPath) { if (-not (Test-Path $OutputPath)) { New-Item -ItemType Directory -Path $OutputPath -Force | Out-Null } $safeInst = $SqlInstance -replace '[\\/:<>|]', '_' $ts = Get-Date -Format 'yyyyMMdd_HHmsqm' $csvFile = Join-Path $OutputPath "WaitStats_${safeInst}_${ts}.csv" $results | Export-Csv -Path $csvFile -NoTypeInformation -Encoding UTF8 -Force Invoke-sqmLogging -Message "Wait Statistics gespeichert: $csvFile" -FunctionName $functionName -Level "INFO" } Invoke-sqmLogging -Message "$functionName abgeschlossen: $($results.Count) Wait Types, Total $([math]::Round($totalWaitMs/1000,1)) Sek." -FunctionName $functionName -Level "INFO" return $results } catch { $errMsg = "Fehler in $functionName : $($_.Exception.Message)" Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw } Write-Error $errMsg } } } |