sqmSQLTool · Performance & Diagnostics · 2026

Performance &
Diagnostics

Find bottlenecks in minutes instead of hours — straight from PowerShell, no external tools.

Wait analysis DMV reports Query Store Extended Events Baseline Index diagnostics

Uwe Janke · Senior SQL Server DBA · dtcSoftware

sqmSQLTool — Wait Statistics Report

Server: SQL-PROD-01 | 2026-05-28 09:14

CXPACKET38.4%

PAGEIOLATCH_SH21.7%

LCK_M_X14.2%

SOS_SCHEDULER_YIELD8.9%

ASYNC_NETWORK_IO5.1%

WRITELOG3.8%

Duration > 500 ms

Queries > 500 ms47

Avg. duration (ms)1,243

Max. duration (ms)18,720

Missing indexes

Impact > 803

Impact 50-808

Fragmentation > 30%12

Why sqmSQLTool Performance & Diagnostics?

Everyday challenges

Without structured diagnostic tools, root-cause work stays slow and tedious.

🚫

No baseline comparison

Slow queries exist - but nobody knows if they were always slow, or got worse after a change.

🔍

Wait stats are opaque

sys.dm_os_wait_stats returns raw data. Without normalizing and filtering it, bottlenecks aren't visible at a glance.

⚠️

Query Store not enabled

In many environments Query Store is disabled or unconfigured - even though it's the single most effective tool against plan regressions.

📊

Index recommendations go nowhere

DMVs like sys.dm_db_missing_index_details deliver valuable hints, but they're rarely reviewed systematically.

sqmSQLTool brings every diagnostic step together in PowerShell cmdlets — reproducible, scriptable, and runnable in parallel across multiple instances.

Function overview

Four diagnostic categories

Every function is self-contained and can be used individually or combined into monitoring scripts.

            ⏳
            Wait analysis
            Get-sqmWaitStatistics
Get-sqmConnectionStats
Get-sqmPerfCounters

          

            🔎
            Query diagnostics
            Get-sqmLongRunningQueries
Invoke-sqmQueryStore
Get-sqmDeadlockReport

          

            🔧
            Index optimization
            Get-sqmMissingIndexes
Get-sqmIndexFragmentation

          

            📈
            Baseline & tracing
            Invoke-sqmPerfBaseline
Invoke-sqmExtendedEvents

          

PowerShell session

→

sqmSQLTool module

→

SQL Server DMVs / XE / Query Store

→

Structured report / PSObject

Return values are always PSCustomObject arrays — pipe straight into Export-Csv, ConvertTo-Html, or Send-MailMessage.

Wait analysis

Get-sqmWaitStatistics

Reads sys.dm_os_wait_stats, filters out irrelevant system waits, and calculates the percentage share of each wait type.

# Top waits for an instance
Get-sqmWaitStatistics -SqlInstance "SQL-PROD-01" -Top 10

# Critical wait types only (threshold > 5%)
Get-sqmWaitStatistics -SqlInstance "SQL-PROD-01" `
    -MinPercent 5 -ExcludeKnownIdle

# Multiple instances in parallel
$instances = "SQL-01", "SQL-02", "SQL-03"
$instances | ForEach-Object -Parallel {
    Get-sqmWaitStatistics -SqlInstance $_
} | Sort-Object WaitPercent -Descending

ExcludeKnownIdle automatically filters out Sleep, WAITFOR, BROKER_TO_FLUSH, and similar idle waits — the report shows only active bottlenecks.

Output mockup — top wait types (SQL-PROD-01)

Get-sqmWaitStatistics — SQL-PROD-01

WaitType	WaitMs	Pct	Category
CXPACKET	1,284,320	38.4	Parallelism
PAGEIOLATCH_SH	724,810	21.7	I/O
LCK_M_X	474,500	14.2	Locking
SOS_SCHEDULER_YIELD	297,140	8.9	CPU
ASYNC_NETWORK_IO	170,330	5.1	Network
WRITELOG	126,900	3.8	I/O

Total wait time: 3,343,900 ms | Measured since last restart

sys.dm_os_wait_stats-ExcludeKnownIdle-Top N-MinPercent

Query diagnostics

Get-sqmLongRunningQueries

Reads sys.dm_exec_requests combined with sys.dm_exec_sql_text and sys.dm_exec_query_plan — an instant snapshot of every active query above the threshold.

What gets captured?

Query text (truncated to 500 characters)
Plan handle for a direct call to sys.dm_exec_query_plan
Current wait type and wait duration
Login name, host name, database
CPU time, read/write counters, duration in ms
Blocking session ID (if any)

# Every query longer than 5 seconds
Get-sqmLongRunningQueries `
    -SqlInstance "SQL-PROD-01" `
    -ThresholdMs 5000

# With a blocking filter and export
Get-sqmLongRunningQueries `
    -SqlInstance "SQL-PROD-01" `
    -ThresholdMs 1000 `
    -IncludeBlocking |
  Export-Csv "C:\Reports\LongQueries.csv" `
    -NoTypeInformation

LongRunningQueries — Threshold: 5,000 ms

SQL-PROD-01 | 2026-05-28 09:22:14

SPID72

Loginsvc_etl@domain.local

DatabaseDWH_Production

Duration (ms)18,720

CPU (ms)14,440

Wait typePAGEIOLATCH_SH

Blocking SPID0

Logical reads4,821,332

Query (truncated)

SELECT f.*, d.Region FROM Fact_Sales f JOIN Dim_Region d ON f.RegionID = d.ID WHERE f.OrderDate >= '2026-01-01' ...

Note: for blocking chains, set the -IncludeBlocking parameter — the head blocker is then included in the output too.

Index optimization

Get-sqmMissingIndexes & Get-sqmIndexFragmentation

Get-sqmMissingIndexes

Reads sys.dm_db_missing_index_details + sys.dm_db_missing_index_group_stats. Returns an impact score, table, columns, and a ready-to-run CREATE INDEX statement.

# Missing indexes with impact > 60
Get-sqmMissingIndexes `
    -SqlInstance "SQL-PROD-01" `
    -Database "DWH_Production" `
    -MinImpact 60 |
  Format-Table Table, ImpactScore, `
    IndexStatement -AutoSize

Missing Indexes — DWH_Production

Fact_Sales (Impact 94)INCLUDE RegionID

Dim_Customer (Impact 81)ON CustomerKey

Fact_Orders (Impact 67)ON OrderDate, Status

Get-sqmIndexFragmentation

Reads sys.dm_db_index_physical_stats and classifies: REBUILD (frag > 30%), REORGANIZE (10–30%), OK (below 10%).

# Fragmentation of every index
Get-sqmIndexFragmentation `
    -SqlInstance "SQL-PROD-01" `
    -Database "DWH_Production" `
    -MinFragmentation 10 |
  Where-Object { $_.Recommendation -eq 'REBUILD' } |
  Export-Csv "Rebuild_List.csv"

Index Fragmentation — Selection

Fact_Sales.IX_Date67% → REBUILD

Fact_Orders.PK41% → REBUILD

Dim_Product.IX_Cat18% → REORG

Dim_Customer.PK4% → OK

Query diagnostics

Invoke-sqmQueryStore

Enables Query Store on one or more databases, configures the capture mode and retention, and reads the top regressed queries with a plan comparison.

Enable & configure Query Store

# Enable Query Store with default configuration
Invoke-sqmQueryStore `
    -SqlInstance "SQL-PROD-01" `
    -Database "AppDB" `
    -Action "Enable" `
    -CaptureMode "Auto" `
    -RetentionDays 30

# Top 5 plan-regressed queries in the last 7 days
Invoke-sqmQueryStore `
    -SqlInstance "SQL-PROD-01" `
    -Database "AppDB" `
    -Action "GetTopRegressed" `
    -Days 7 -Top 5

Force Plan: -Action ForcePlan -QueryID 142 forces a stable plan — an instant return to prior performance with no code change.

Query Store — Top Regressed Queries (AppDB, last 7 days)

QueryID 142 — usp_GetOrderSummary

Avg. duration before (ms)87

Avg. duration now (ms)3,240

Plan changed on2026-05-25

Old plan IDPlanID 88

New plan IDPlanID 201 (Scan!)

QueryID 87 — fn_CalcDiscount

Avg. duration before (ms)12

Avg. duration now (ms)890

RecommendationUpdate statistics

sys.query_store_querysys.query_store_planForce PlanCaptureMode

Tracing & diagnostics

Invoke-sqmExtendedEvents

Creates, starts, and stops Extended Events sessions for deadlock tracing and long-query monitoring — no Profiler, no trace flags.

Create & start a deadlock session

# Create the deadlock XE session
Invoke-sqmExtendedEvents `
    -SqlInstance "SQL-PROD-01" `
    -Action "CreateDeadlockSession" `
    -SessionName "sqm_deadlock_trace" `
    -TargetPath "D:\XE\deadlocks"

# Long-query session (from 3 seconds)
Invoke-sqmExtendedEvents `
    -SqlInstance "SQL-PROD-01" `
    -Action "CreateLongQuerySession" `
    -ThresholdMs 3000 `
    -SessionName "sqm_longquery"

# Stop the session and read the events
Invoke-sqmExtendedEvents `
    -SqlInstance "SQL-PROD-01" `
    -Action "ReadAndStop" `
    -SessionName "sqm_longquery"

Supported actions

CreateDeadlockSession

An XE session for xml_deadlock_report — writes XEL files to disk. The basis for Get-sqmDeadlockReport.

CreateLongQuerySession

Tracks sql_statement_completed, filtered on duration > threshold. Captures query text, plan handle, wait info.

Start / Stop / Drop

Lifecycle management for the session, no manual T-SQL.

ReadAndStop

Reads events from the ring buffer or the XEL file, stops the session, returns a PSObject array.

XEL files are archived to -TargetPath automatically. If no path is given, the session falls back to the ring buffer.

Baseline & monitoring

Invoke-sqmPerfBaseline

Takes a snapshot of key performance metrics before a change and compares it after — an objective answer on whether things improved or got worse.

# Save a snapshot BEFORE the change
Invoke-sqmPerfBaseline `
    -SqlInstance "SQL-PROD-01" `
    -Action "Snapshot" `
    -Label "before_index_change" `
    -OutputPath "D:\Baseline"

# ... make the change ...

# Snapshot AFTER the change
Invoke-sqmPerfBaseline `
    -SqlInstance "SQL-PROD-01" `
    -Action "Snapshot" `
    -Label "after_index_change" `
    -OutputPath "D:\Baseline"

# Compare the two snapshots
Invoke-sqmPerfBaseline `
    -Action "Compare" `
    -BaselinePath "D:\Baseline\before_index_change.json" `
    -ComparePath "D:\Baseline\after_index_change.json"

Comparison report (excerpt)

Baseline Compare — SQL-PROD-01

Before: before_index_change | After: after_index_change

Metric	Before	After	Delta
Avg Query Duration (ms)	1,243	312	-74.9%
Logical Reads / Batch	4,821,332	287,440	-94.0%
PAGEIOLATCH_SH Wait (ms)	724,810	48,220	-93.3%
CPU Time / Batch (ms)	14,440	3,120	-78.4%
LCK_M_X Wait (ms)	474,500	471,900	-0.5%

Snapshots are saved as JSON and are audit-safe. Ideal for change documentation in a CR process.

Resource monitoring

Get-sqmConnectionStats & Get-sqmPerfCounters

Get-sqmConnectionStats

Reads sys.dm_exec_sessions and sys.dm_exec_connections — shows active connections grouped by login, host, and status.

# Get connection statistics
Get-sqmConnectionStats `
    -SqlInstance "SQL-PROD-01" `
    -GroupBy "LoginName"

# Sleeping sessions older than 10 minutes
Get-sqmConnectionStats `
    -SqlInstance "SQL-PROD-01" `
    -Status "sleeping" `
    -MinIdleMinutes 10

Connection Stats — by Login

svc_app@domain84 active

svc_etl@domain23 (14 sleeping)

SSRS_Service12 active

sa2 (check this!)

Get-sqmPerfCounters

Reads Windows PerfMon counters via sys.dm_os_performance_counters or WMI — no remote connection to the OS needed.

# Key SQL PerfMon counters
Get-sqmPerfCounters `
    -SqlInstance "SQL-PROD-01" `
    -Category "SQLServer:Buffer Manager", `
              "SQLServer:SQL Statistics", `
              "SQLServer:Memory Manager"

# As continuous sampling (10s interval)
Get-sqmPerfCounters `
    -SqlInstance "SQL-PROD-01" `
    -SampleCount 6 -IntervalSec 10

PerfCounters — SQL-PROD-01

Buffer Cache Hit Ratio98.7%

Page Life Expectancy284 s

Batch Requests/sec1,420

SQL Compilations/sec88 (high!)

Memory Grants Pending4

Deadlock analysis

Get-sqmDeadlockReport

Parses deadlock XML from the System Health XE target, or from your own XEL files, and outputs a readable, structured summary per deadlock event.

# Deadlocks from System Health (last 7 days)
Get-sqmDeadlockReport `
    -SqlInstance "SQL-PROD-01" `
    -Source "SystemHealth" `
    -Days 7

# Read from your own XEL file
Get-sqmDeadlockReport `
    -SqlInstance "SQL-PROD-01" `
    -Source "XelFile" `
    -XelPath "D:\XE\deadlocks\sqm_deadlock*.xel"

# Export as an HTML report
Get-sqmDeadlockReport `
    -SqlInstance "SQL-PROD-01" `
    -Source "SystemHealth" `
    -Days 30 |
  ConvertTo-Html -Title "Deadlock Report" |
  Out-File "DeadlockReport.html"

The SQL deadlock XML can be over 100 KB. sqmSQLTool extracts only the relevant nodes: the processes involved, resources, victim SPID, and SQL text.

Deadlock Report — SQL-PROD-01 (last 7 days)

Deadlock #1 | 2026-05-26 22:14:08

Victim SPIDSPID 114

Victim loginsvc_etl@domain.local

Victim queryUPDATE Fact_Sales SET ...

Winner SPIDSPID 87

Winner loginsvc_app@domain.local

ResourceFact_Sales.IX_Date (RID Lock)

Deadlock #2 | 2026-05-27 03:42:51

Victim SPIDSPID 203

ResourceDim_Customer.PK (Key Lock)

RecommendationEnable READ_COMMITTED_SNAPSHOT

xml_deadlock_reportSystem HealthXEL fileRCSI

Quick reference

All 10 diagnostic functions at a glance

Function	Category	Main parameters	Returns	DMV / source
Get-sqmWaitStatistics	Wait	-Top, -MinPercent, -ExcludeKnownIdle	PSObject[]	dm_os_wait_stats
Get-sqmLongRunningQueries	Query	-ThresholdMs, -IncludeBlocking	PSObject[]	dm_exec_requests
Get-sqmMissingIndexes	Index	-Database, -MinImpact	PSObject[]	dm_db_missing_index_*
Get-sqmIndexFragmentation	Index	-Database, -MinFragmentation	PSObject[]	dm_db_index_physical_stats
Invoke-sqmQueryStore	Query	-Action, -Days, -Top	PSObject[] / void	query_store_*
Invoke-sqmExtendedEvents	Tracing	-Action, -SessionName, -ThresholdMs	PSObject[] / void	XE Engine
Invoke-sqmPerfBaseline	Baseline	-Action, -Label, -OutputPath	PSObject[] / JSON	DMVs / JSON file
Get-sqmConnectionStats	Resource	-GroupBy, -Status, -MinIdleMinutes	PSObject[]	dm_exec_sessions
Get-sqmPerfCounters	Resource	-Category, -SampleCount, -IntervalSec	PSObject[]	dm_os_performance_counters
Get-sqmDeadlockReport	Deadlock	-Source, -Days, -XelPath	PSObject[]	System Health / XEL

Every function supports -SqlInstance as its first parameter and accepts credential objects via -Credential. Pipeline input is supported.

Conclusion

What the team gains with sqmSQLTool Performance & Diagnostics

diagnostic functions

From wait analysis to deadlock reports - all in one module, no tool switching.

extra tools

No Profiler, no SSMS wizard, no separate monitoring agent - just PowerShell.

instances in parallel

ForEach-Object -Parallel enables diagnostics across the entire instance estate at once.

Reproducibility

Script-based diagnostics are repeatable, versionable, and can be embedded in CI/CD pipelines or monitoring jobs. No more "I quickly did that in SSMS."

Ready to use immediately

No schema changes, no extra database. The module only reads DMVs, system catalogs, and optional XEL files.

Uwe Janke · Senior SQL Server DBA · dtcSoftware · 2026

GitHub → sqmSQLTool

Performance &Diagnostics

Everyday challenges

No baseline comparison

Wait stats are opaque

Query Store not enabled

Index recommendations go nowhere

Four diagnostic categories

Wait analysis

Query diagnostics

Index optimization

Baseline & tracing

Get-sqmWaitStatistics

Get-sqmLongRunningQueries

What gets captured?

Get-sqmMissingIndexes & Get-sqmIndexFragmentation

Get-sqmMissingIndexes

Get-sqmIndexFragmentation

Invoke-sqmQueryStore

Enable & configure Query Store

Invoke-sqmExtendedEvents

Create & start a deadlock session

Supported actions

Invoke-sqmPerfBaseline

Get-sqmConnectionStats & Get-sqmPerfCounters

Get-sqmConnectionStats

Get-sqmPerfCounters

Get-sqmDeadlockReport

All 10 diagnostic functions at a glance

What the team gains with sqmSQLTool Performance & Diagnostics

Reproducibility

Ready to use immediately

Performance &
Diagnostics