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.
-MaxRolloverFilesintOptional5Number of XEL rollover files. Default: 5.
-NoOpenswitchSwitch$falseDo not open the HTML report after creation.
-OutputPathstringOptional, Directory for saved reports (CSV + TXT + HTML). Default: from module configuration + \XEvents.

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