Commands / Invoke-sqmExtendedEvents
Invoke-sqmExtendedEvents
PerformancesqmSQLTool v1.8.2+ · Performance Diagnosticsdbatools required  •  ShouldProcess Medium  •  5 Templates
Comprehensive Extended Events lifecycle management. Six combinable operations: Create (template-based session with RingBuffer or File target), Start, Stop, Read (parse RingBuffer XML or XEL files), Diagnose (aggregate events: top slow queries, top waits, blocking chains, deadlocks — saves CSV + TXT report), Drop. Default when no switch given: Read + Diagnose. Templates: SlowQueries, Blocking, Waits, Deadlocks, AllInOne.

Execution Flow

START dbatools available? NO throw: dbatools not found YES ▼ No action switch? → default: -Read + -Diagnose Resolve OutputPath + TargetFilePath from config if -Create ShouldProcess Medium DROP old + CREATE EVENT SESSION Build template SQL: SlowQueries | Blocking Waits | Deadlocks | AllInOne + Target (RingBuffer/File) if -Start Invoke-DbaQuery: ALTER EVENT SESSION ON SERVER STATE = START if -Stop Invoke-DbaQuery: ALTER EVENT SESSION ON SERVER STATE = STOP Query session status (always) sys.server_event_sessions + sys.dm_xe_sessions if -Read (or default) Target type? RingBuffer or File RingBuffer Parse ring_buffer XML → events[] File fn_xe_file_target _read_file *.xel EventCount stored in result if -Diagnose (or default) && EventCount > 0 D1: Top Slow Queries Group by SqlText · Avg/Max duration D2: Top Wait Types Group by WaitType · TotalWait/Avg D3: Blocking Events blocked_process_report events D4: Deadlock Events xml_deadlock_report · save XML Save Events CSV + Diagnose TXT report TopSlowQueries.csv · TopWaits.csv · *_Report.txt if -Drop ShouldProcess Medium STATE=STOP + DROP EVENT SESSION Return [PSCustomObject] SessionStatus · Events[] · EventCount · Diagnose · ReportFile DONE

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredentialOptional—Credentials for the connection.
-SessionNamestringOptionalsqmPerformanceName of the XEvent session.
-TemplatestringOptionalAllInOneSlowQueries | Blocking | Waits | Deadlocks | AllInOne.
-SlowQueryThresholdMsintOptional1000Min duration (ms) for slow query capture.
-WaitTypesstringOptionalLCK_M_X,LCK_M_S,...Comma-separated wait types for Waits template.
-TargetTypestringOptionalRingBufferRingBuffer or File target.
-TargetFilePathstringOptionalOutputPath\XEventsDirectory for XEL files (File target only).
-MaxFileSizeMBintOptional100Max XEL file size in MB.
-RingBufferMaxMBintOptional50Max ring buffer size in MB.
-MaxEventsReadintOptional10000Maximum events read per call.
-LookbackMinutesintOptional60Time window for diagnostic aggregation.
-TopNintOptional25Top entries in diagnostic tables.
-CreateswitchSwitch—Create session (drops existing first).
-StartswitchSwitch—Start existing session.
-StopswitchSwitch—Stop running session.
-ReadswitchSwitch—Read events from ring buffer or XEL file.
-DiagnoseswitchSwitch—Aggregate events and detect patterns.
-DropswitchSwitch—Remove session including XEL files.
-EnableExceptionswitchSwitch$falseRe-throw exceptions immediately.

Examples

Create AllInOne session and start — default Read+Diagnose on next call
Invoke-sqmExtendedEvents -SqlInstance "SQL01" -Create -Start
Capture slow queries > 2s to XEL file
Invoke-sqmExtendedEvents -SqlInstance "SQL01" -Template SlowQueries -SlowQueryThresholdMs 2000 -TargetType File -Create -Start
Read running session and diagnose (default behavior)
Invoke-sqmExtendedEvents -SqlInstance "SQL01"
Stop and remove session
Invoke-sqmExtendedEvents -SqlInstance "SQL01" -Stop -Drop
← Back
Command Index