Commands / Get-sqmLongRunningQueries
Get-sqmLongRunningQueries
Performance sqmSQLTool v1.8.2+ · Get 🔓 VIEW SERVER STATE required
Queries sys.dm_exec_requests for active sessions exceeding a duration or CPU threshold. Resolves the exact current statement (not just the batch) via statement_start/end_offset, and enriches each result with wait type, isolation level, query hash, estimated completion time, and blocking information. System sessions (SPID ≤ 50) and the caller's own SPID are always excluded. The -ExcludeWaitType filter (applied in PowerShell, not SQL) removes idle wait types from the result. -IncludeQueryPlan adds the XML execution plan per row — useful interactively, but too expensive for Agent Jobs.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredentialOptional—SQL or Windows credential.
-MinDurationSecondsintOptional30Minimum elapsed time in seconds. Applied in SQL via DATEDIFF(s, start_time, GETDATE()).
-MinCpuMsintOptional0Minimum CPU time (ms). Only added to the WHERE clause when > 0.
-ExcludeWaitTypestring[]Optional~30 idle typesWait types to exclude. Filter applied in PowerShell per row, not in SQL. Default list covers SLEEP_*, XE, HADR background waits, etc.
-IncludeSystemSessionsswitchSwitch$falseInclude SPIDs ≤ 50 (background tasks). Default: excluded via AND session_id > 50.
-IncludeQueryPlanswitchSwitch$falseFetch XML execution plan from sys.dm_exec_query_plan per row. Expensive — use interactively only, not in Agent Jobs.
-OutputPathstringOptional—Write a CSV snapshot (QueryPlanXml and FullBatch excluded). Directory created automatically.
-EnableExceptionswitchSwitch$falseThrow on error instead of Write-Error.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME Invoke-DbaQuery — active requests (master) dm_exec_requests JOIN dm_exec_sessions APPLY dm_exec_sql_text WHERE DurationSeconds ≥ MinDurationSeconds · session_id ≠ @@SPID · ORDER BY DurationSeconds DESC -IncludeSystemSessions: NO → AND session_id > 50 · MinCpuMs > 0 → AND cpu_time ≥ MinCpuMs For each result row — ExcludeWaitType filter (PowerShell) Skip row if WaitType matches -ExcludeWaitType (default: ~30 idle wait types) Build PSCustomObject: SessionId · Database · Login · Host · Program · DurationSeconds · CpuMs -IncludeQueryPlan set? YES Fetch dm_exec_query_plan per row only if PlanHandle is byte[] · expensive NO -OutputPath provided? YES Export-Csv (excl. QueryPlanXml & FullBatch) LongRunning_Instance_timestamp.csv NO Return List[PSCustomObject] SessionId · BlockingSpid · Database · Login · Host · DurationSeconds · StartTime CpuMs · WaitType · IsolationLevel · CurrentStatement · QueryHash · IsBlocked Error handling -EnableException: throw · else: Write-Error + return $null DONE

Examples

Queries running longer than 30 seconds on the local instance
Get-sqmLongRunningQueries
Queries running longer than 60 seconds on a named instance
Get-sqmLongRunningQueries -SqlInstance "SQL01" -MinDurationSeconds 60
Top 10 by duration, minimum threshold lowered
Get-sqmLongRunningQueries -MinDurationSeconds 10 |
    Sort-Object DurationSeconds -Descending |
    Select-Object -First 10
Agent Job snapshot — save CSV when queries exceed 2 minutes
Get-sqmLongRunningQueries -MinDurationSeconds 120 -OutputPath "C:\System\WinSrvLog\MSSQL\LongRunning"
Include XML execution plan (interactive use only)
Get-sqmLongRunningQueries -SqlInstance "SQL01" -IncludeQueryPlan |
    Select-Object SessionId, DurationSeconds, QueryPlanXml
⬆ Index
All Commands