Commands / Get-sqmBlockingReport
Get-sqmBlockingReport
Blocking & Locking sqmSQLTool v1.8.2+ · Get 🔓 VIEW SERVER STATE required
Identifies active blocking chains on a SQL Server instance by querying sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_sql_text. Resolves the exact SQL statement for both the blocked and blocking session via statement_start/end_offset. Returns structured objects with BlockingChains, HeadBlockers, BlockedSessions, and a HasBlocking flag — suitable for Agent Job monitoring or pipeline-based alerting. Optionally writes a CSV snapshot per run.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredentialOptional—SQL or Windows credential for the connection.
-MinWaitSecondsintOptional0Only report sessions with a wait time ≥ this value. Applied in SQL as wait_time / 1000.0 ≥ N.
-OutputPathstringOptional—When provided AND blocking exists, writes a CSV snapshot. Directory is created automatically if missing.
-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 — blocking query (master) dm_exec_requests JOIN dm_exec_sessions APPLY dm_exec_sql_text WHERE blocking_session_id > 0 AND wait_time/1000.0 ≥ MinWaitSeconds · ORDER BY wait_time DESC For each row → PSCustomObject (blocked session) BlockedSpid, BlockingSpid, WaitType, WaitSeconds, WaitResource, DatabaseName Login / Host / Program · BlockedStatement & BlockingStatement (trimmed) Determine true head blockers headBlockerSpids (HashSet) = all BlockingSpid values collected above trueHeadBlockers = headBlockerSpids NOT IN BlockedSpids For each true head blocker → Get-sqmBlockingChain (recursive) Builds full chain: head → directly blocked → their blocked (depth-tracked) Per chain: HeadSpid · Login · Statement · BlockedSpids[] · MaxWaitSeconds -OutputPath AND HasBlocking? YES Export-Csv Blocking_Instance_timestamp.csv NO Return [PSCustomObject] HasBlocking · BlockedCount · BlockingChains · HeadBlockers BlockedSessions · CaptureTime · SqlInstance Error handling -EnableException: throw · else: Write-Error + return $null DONE

Return Object

PropertyTypeDescription
.HasBlockingbool$true if any blocking was found.
.BlockedCountintTotal number of blocked sessions.
.BlockingChainsListOne entry per true head blocker: HeadBlockerSpid, HeadLogin, HeadStatement, HeadStartTime, BlockedSpids[], BlockedCount, MaxWaitSeconds.
.HeadBlockersint[]SPIDs that are blocking others but are not themselves blocked.
.BlockedSessionsListAll blocked session records with full detail (login, host, statement, wait info, CPU, logical reads, ChainDepth).

Examples

Check for blocking on the local instance
Get-sqmBlockingReport
Only report sessions waiting longer than 30 seconds
Get-sqmBlockingReport -SqlInstance "SQL01" -MinWaitSeconds 30
Use HasBlocking flag for conditional alerting
if ((Get-sqmBlockingReport -SqlInstance "SQL01").HasBlocking) {
    Write-Warning "Active blocking detected!"
}
Agent Job: write CSV snapshot whenever blocking occurs
Get-sqmBlockingReport -SqlInstance "SQL01" -OutputPath "C:\System\WinSrvLog\MSSQL\Blocking"
⬆ Index
All Commands