Loading...
Loading...
Use when the user asks what caused blocking that already ended (yesterday's incident, intermittent lock waits) or who is behind a supplied blocking chain. For blocking happening now, use `check-live-blocking`.
npx skill4agent add microsoft/microsoft-sql blocking-root-cause-analysisquery-store-volatile-historical-slow-queries-comparisonwait-category-analysischeck-live-blockingcreate-xe-sessioncreate-xe-sessioncheck-live-blockingcreate-xe-sessionmicrosoft-sql-adminmssql_list_database_connectionsmssql_connect_databasemssql_execute_querysqlcmdsql-tools-mcp-setupVIEW SERVER PERFORMANCE STATEVIEW SERVER STATEVIEW DATABASE PERFORMANCE STATE##MS_ServerStateReader##SELECT
@@SERVERNAME AS [Server],
DB_NAME() AS [Database],
SUSER_SNAME() AS [Login],
CAST(SERVERPROPERTY('EngineEdition') AS INT) AS [Engine Edition],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [Product Version];;WITH XEData AS (
SELECT
CAST(xet.target_data AS XML) AS target_data
FROM sys.dm_xe_database_sessions AS xes
INNER JOIN sys.dm_xe_database_session_targets AS xet
ON xes.address = xet.event_session_address
WHERE xes.name = 'BlockingInvestigation'
AND xet.target_name = 'ring_buffer'
),
RawEvents AS (
SELECT
evt.value('(@name)', 'nvarchar(100)') AS event_name,
evt.value('(@timestamp)', 'datetime2') AS event_time_utc,
evt.value('(data[@name="duration"]/value)[1]', 'bigint') AS duration_us,
evt.value('(data[@name="cpu_time"]/value)[1]', 'bigint') AS cpu_time_us,
evt.value('(data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
evt.value('(data[@name="row_count"]/value)[1]', 'bigint') AS row_count,
evt.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') AS sql_statement,
evt.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS client_app_name,
evt.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(256)') AS client_hostname,
evt.value('(action[@name="server_principal_name"]/value)[1]', 'nvarchar(256)') AS login_name,
evt.value('(action[@name="session_id"]/value)[1]', 'int') AS session_id
FROM XEData
CROSS APPLY target_data.nodes('RingBufferTarget/event') AS t(evt)
)
SELECT
event_time_utc AS [Event Time (UTC)],
event_name AS [Event],
duration_us / 1000 AS [Duration (ms)],
cpu_time_us / 1000 AS [CPU Time (ms)],
logical_reads AS [Logical Reads],
row_count AS [Row Count],
session_id AS [Session ID],
client_app_name AS [Application Name],
client_hostname AS [Host Name],
login_name AS [Login Name],
LEFT(sql_statement, 200) AS [SQL Text (truncated)]
FROM RawEvents
ORDER BY event_time_utc DESC;;WITH XEData AS (
SELECT
CAST(xet.target_data AS XML) AS target_data
FROM sys.dm_xe_sessions AS xes
INNER JOIN sys.dm_xe_session_targets AS xet
ON xes.address = xet.event_session_address
WHERE xes.name = 'BlockingInvestigation'
AND xet.target_name = 'ring_buffer'
),
RawEvents AS (
SELECT
evt.value('(@name)', 'nvarchar(100)') AS event_name,
evt.value('(@timestamp)', 'datetime2') AS event_time_utc,
evt.value('(data[@name="duration"]/value)[1]', 'bigint') AS duration_us,
evt.value('(data[@name="cpu_time"]/value)[1]', 'bigint') AS cpu_time_us,
evt.value('(data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
evt.value('(data[@name="row_count"]/value)[1]', 'bigint') AS row_count,
evt.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') AS sql_statement,
evt.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS client_app_name,
evt.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(256)') AS client_hostname,
evt.value('(action[@name="server_principal_name"]/value)[1]', 'nvarchar(256)') AS login_name,
evt.value('(action[@name="session_id"]/value)[1]', 'int') AS session_id
FROM XEData
CROSS APPLY target_data.nodes('RingBufferTarget/event') AS t(evt)
)
SELECT
event_time_utc AS [Event Time (UTC)],
event_name AS [Event],
duration_us / 1000 AS [Duration (ms)],
cpu_time_us / 1000 AS [CPU Time (ms)],
logical_reads AS [Logical Reads],
row_count AS [Row Count],
session_id AS [Session ID],
client_app_name AS [Application Name],
client_hostname AS [Host Name],
login_name AS [Login Name],
LEFT(sql_statement, 200) AS [SQL Text (truncated)]
FROM RawEvents
ORDER BY event_time_utc DESC;sys.dm_xe_sessionssys.dm_xe_session_targetssys.dm_xe_database_sessionssys.dm_xe_database_session_targetsSELECT TOP (20)
q.query_id AS [Query ID], q.query_hash AS [Query Hash],
ws.wait_category_desc AS [Wait Category],
SUM(ws.total_query_wait_time_ms) AS [Total Wait Time (ms)],
SUM(ws.avg_query_wait_time_ms * ws.total_query_wait_time_ms)
/ NULLIF(SUM(ws.total_query_wait_time_ms), 0) AS [Weighted Avg Wait (ms)],
SUM(ws.total_query_wait_time_ms) * 100.0
/ NULLIF(SUM(SUM(ws.total_query_wait_time_ms)) OVER (PARTITION BY q.query_id), 0)
AS [Wait Category % of Query Total],
COUNT(DISTINCT p.plan_id) AS [Plan Count]
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_wait_stats ws
ON p.plan_id = ws.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rsi
ON ws.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(MINUTE, -60, GETUTCDATE())
AND q.is_internal_query = 0
GROUP BY q.query_id, ws.wait_category_desc
ORDER BY [Total Wait Time (ms)] DESC;SELECT
r.session_id AS [Session ID],
r.blocking_session_id AS [Blocking Session ID],
DB_NAME(r.database_id) AS [Database],
s.program_name AS [Application Name],
s.host_name AS [Host Name],
s.login_name AS [Login Name],
r.status AS [Request Status],
r.command AS [Command],
r.wait_type AS [Wait Type],
r.wait_time AS [Wait Time (ms)],
r.wait_resource AS [Wait Resource],
r.start_time AS [Request Start Time],
DATEDIFF(SECOND, r.start_time, GETDATE()) AS [Elapsed Seconds],
r.open_transaction_count AS [Open Transactions], r.query_hash AS [Query Hash],
r.plan_handle AS [Plan Handle]
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
AND (
r.connection_id IS NULL
OR r.connection_id = c.connection_id
)
WHERE s.is_user_process = 1
AND r.blocking_session_id <> 0
OPTION (MAXDOP 1, USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION','QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160','ENABLE_QUERY_OPTIMIZER_HOTFIXES'))## Query Blocking Root Cause Report
Database: {{database name}}
Date: {{current date in YYYY-MM-DD format}}
Data Sources: BlockingInvestigation XE Session + Query Store + DMVs
---
### Prerequisites Check
{{If Query 1 (XE data) returns no rows, display this and STOP:}}
> **The BlockingInvestigation XE session has not captured any data yet.** This could mean:
> 1. The XE session has not been created. Use the `create-xe-session` skill to set it up.
> 2. The suspect query has not executed since the session was started. Wait for the workload to run and try again.
> 3. The query text pattern used when creating the session does not match the suspect query.
{{Skip the remaining sections if no XE data.}}
---
### XE Captured Executions
{{Display all captured query executions from Query 1:}}
| Time (UTC) | Event | Duration (ms) | CPU (ms) | Logical Reads | Session ID | Application | Host | Login | Query Hash |
|------------|-------|---------------|----------|---------------|------------|-------------|------|-------|---------------------|
{{one row per captured event, truncate SQL to 100 characters}}
---
### Source Identification
{{This is the CRITICAL section. Analyze the XE data to identify the blocking source:}}
#### Sessions Running the Suspect Query
| Application Name | Host Name | Login | # Executions | Avg Duration (ms) | Max Duration (ms) |
|------------------|-----------|-------|--------------|--------------------|-------------------|
{{Group XE data by application, host, login — these are the sessions running the query that gets blocked}}
#### Currently Active Blocking (if any)
{{If Query 3 returned results, display a summary table.}}
{{If no active blocking: "No active blocking detected at the time of this report."}}
---
### Query Store Wait Correlation
{{Show the wait categories from Query 2 for the queries that match the XE-captured SQL text:}}
| Wait Category | Total Wait Time (ms) | Interpretation |
|---------------|---------------------|----------------|
{{If Lock waits are present: "CONFIRMED — Lock waits detected, confirming blocking as the root cause."}}
{{Note any other significant wait categories that may be contributing.}}
---
### Root Cause Assessment
{{Provide a clear 2-3 sentence assessment:}}
1. **Source**: Identify the application(s) and host(s) running the suspect query based on XE data.
2. **Confirmation**: State whether blocking was confirmed via Lock waits in Query Store.
3. **Pattern**: If the XE captured both high-duration and low-duration executions of the same query,
the blocking is intermittent. If ALL executions are high-duration, blocking may be persistent.
4. **Blocker Identity**: Note that the XE session captures the VICTIM's identity (the session running
the suspect query). The BLOCKER is the other session holding the lock. If active blocking is
detected (Query 3), the blocker's identity is known. If not, the blocker has already released
the locks.
---
### Recommendations
{{Based on the root cause, provide 3-5 specific, actionable recommendations:}}
1. **Investigate the blocking application**: "The application '{{app name}}' on host '{{host}}'
running as login '{{login}}' is the source. Review its transaction management — look for
long-running transactions, missing COMMIT/ROLLBACK, or excessive lock scope."
2. **Reduce lock duration**: "If the blocking query performs large data modifications,
consider breaking it into smaller batches (e.g., process 1,000 rows per transaction
instead of the entire dataset)."
3. **Add retry logic**: "Add retry logic with exponential backoff in the affected application
for transient blocking. This improves resilience without changing the database."
4. **Improve indexing**: "Review indexes on the contended tables. More selective indexes
reduce the number of rows locked per operation, reducing lock conflicts."
5. **Schedule off-peak**: "If the blocking comes from a batch job or maintenance operation,
consider scheduling it during off-peak hours when contention is lower."
6. **Consider isolation level changes**: "If the workload supports it, enabling READ COMMITTED
SNAPSHOT (RCSI) allows readers to proceed without being blocked by writers."
7. **Live monitoring**: "To see the blocking in real-time next time it occurs, use
the `check-live-blocking` skill when users report slowness."