Part of the DBA-Tools Project.
Three Different Views of the Same Underlying Problem
Blocking Chains shows what’s blocked right now. These three scripts go wider and deeper: Get-LockEscalationStats finds which tables have converted row/page locks into full table locks (a common, self-inflicted cause of blocking), Get-ContentionAnalysis rolls up lock waits, latch waits, TempDB allocation contention, and spinlock collisions into one summary since the last restart, and Get-BlockingChainsWithPlan is Blocking Chains with the actual execution plan attached for every blocked or blocking session, so you can see not just who’s blocked but what query is doing it.
Together they cover three different angles on the same underlying question: is locking or latching actually costing this server anything, and if so, where.
Why Lock Escalation, Contention, and Blocking Plans Matter
- Lock escalation converts many fine-grained row/page locks into one table lock, which can block readers and writers that would have coexisted fine under row-level locking, a frequent, avoidable blocking cause
- Cumulative wait statistics (lock, latch, TempDB, spinlock) since the last restart tell you where contention has actually cost time, not just where it’s theoretically possible
- A blocking chain with the query plan attached turns “session 62 is blocking session 78” into “this specific query, with this specific plan, is the actual problem,” the difference between guessing and fixing
- TempDB allocation contention (
PAGELATCH_UP/PAGELATCH_EXon PFS/GAM/SGAM pages) has a well-known, direct fix, adding TempDB data files, but only if you know to look for it
When to Run These Scripts
- Get-LockEscalationStats — when investigating blocking that seems disproportionate to the actual data touched, escalation is a common hidden cause
- Get-ContentionAnalysis — as a general health check or when investigating a vague “performance feels off” report, it surfaces contention across four different resource types in one pass
- Get-BlockingChainsWithPlan — during an active blocking incident, when Get-BlockingChains has identified the sessions involved and you need the actual query and plan to fix the root cause
- Routine SQL Server health checks, alongside the rest of the Blocking and Locking cluster
The Scripts
1. Get-LockEscalationStats — Tables With the Most Lock Escalations
/*
Script Name : Get-LockEscalationStats
Category : performance
Purpose : Shows tables with the most lock escalations since last restart.
Lock escalation converts row/page locks to a table lock, increasing blocking.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-lock-contention-and-blocking-plans/)
Requires : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-lock-contention-and-blocking-plans/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
/*
DESIGN: sys.dm_db_index_operational_stats with NULL parameters covers all databases
and all indexes. Aggregated per table (summing across all indexes) since escalation
is a table-level event. Excludes system databases (database_id <= 4).
High escalation counts on a table indicate:
- Large batch operations scanning many rows (e.g. bulk updates, large deletes)
- Queries that acquire more than 5,000 row/page locks in a single statement
Remedies:
- ALTER TABLE t SET (LOCK_ESCALATION = DISABLE) — prevents escalation (use carefully)
- Batch large DML into smaller chunks to stay below the lock threshold
- Add covering indexes to reduce rows scanned per operation
- Use READ_COMMITTED_SNAPSHOT isolation to eliminate shared locks on reads
*/
SELECT TOP 30
DB_NAME(ios.database_id) AS database_name,
OBJECT_NAME(ios.object_id, ios.database_id) AS table_name,
SUM(ios.index_lock_promotion_count) AS lock_escalations,
SUM(ios.row_lock_count) AS row_lock_count,
SUM(ios.page_lock_count) AS page_lock_count,
SUM(ios.row_lock_wait_count) AS row_lock_wait_count,
SUM(ios.page_lock_wait_count) AS page_lock_wait_count,
CAST(SUM(ios.row_lock_wait_in_ms) / 1000.0 AS decimal(12,2))
AS row_lock_wait_sec,
CAST(SUM(ios.page_lock_wait_in_ms) / 1000.0 AS decimal(12,2))
AS page_lock_wait_sec
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) ios
WHERE ios.database_id > 4
AND ios.index_lock_promotion_count > 0
GROUP BY
ios.database_id,
ios.object_id
ORDER BY
lock_escalations DESC;
2. Get-ContentionAnalysis — Lock, Latch, TempDB, and Spinlock Contention in One Pass
/*
Script Name : Get-ContentionAnalysis
Category : performance
Purpose : Unified contention summary across lock waits, latch waits, TempDB allocation
pressure, and spinlock contention. All figures are cumulative since the last
SQL Server restart, high counts on a recently restarted instance are not
necessarily concerning.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-lock-contention-and-blocking-plans/)
Requires : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-lock-contention-and-blocking-plans/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @min_wait_count BIGINT = 100; -- suppress trivial entries with fewer wait occurrences
DECLARE @min_spinlock_collisions BIGINT = 50000; -- only flag high-volume spinlocks
SELECT
contention_type,
resource_name,
wait_count,
total_wait_ms,
avg_wait_ms,
days_since_restart,
note
FROM (
-- ── Lock waits ──────────────────────────────────────────────────────────────
SELECT
'LOCK' AS contention_type,
ws.wait_type AS resource_name,
ws.waiting_tasks_count AS wait_count,
ws.wait_time_ms AS total_wait_ms,
CAST(ws.wait_time_ms * 1.0
/ NULLIF(ws.waiting_tasks_count, 0) AS DECIMAL(12,2)) AS avg_wait_ms,
uptime.days_since_restart,
'Locking contention — run Get-BlockingChains for real-time chain analysis' AS note
FROM sys.dm_os_wait_stats ws
CROSS JOIN (
SELECT DATEDIFF(DAY, sqlserver_start_time, GETDATE()) AS days_since_restart
FROM sys.dm_os_sys_info
) uptime
WHERE ws.wait_type LIKE 'LCK_M_%'
AND ws.waiting_tasks_count >= @min_wait_count
UNION ALL
-- ── Latch waits ─────────────────────────────────────────────────────────────
SELECT
'LATCH',
ls.latch_class,
ls.waiting_requests_count,
ls.wait_time_ms,
CAST(ls.wait_time_ms * 1.0
/ NULLIF(ls.waiting_requests_count, 0) AS DECIMAL(12,2)),
uptime.days_since_restart,
CASE
WHEN ls.latch_class LIKE 'PAGEIOLATCH%'
THEN 'I/O latch — data page reads are slow; check disk latency'
WHEN ls.latch_class LIKE 'ACCESS_METHODS%'
THEN 'Scan/seek contention — high concurrent read/write activity'
WHEN ls.latch_class = 'BUFFER'
THEN 'Buffer pool contention — memory pressure or heavy page access'
WHEN ls.latch_class LIKE 'FGCB%'
THEN 'Filegroup cache contention — check filegroup configuration'
ELSE 'Internal latch contention — investigate if avg_wait_ms is high'
END
FROM sys.dm_os_latch_stats ls
CROSS JOIN (
SELECT DATEDIFF(DAY, sqlserver_start_time, GETDATE()) AS days_since_restart
FROM sys.dm_os_sys_info
) uptime
WHERE ls.waiting_requests_count >= @min_wait_count
AND ls.latch_class NOT LIKE 'LOG_%'
AND ls.latch_class NOT LIKE 'LOGGING_%'
UNION ALL
-- ── TempDB allocation page contention ────────────────────────────────────────
SELECT
'TEMPDB_ALLOC',
ws.wait_type,
ws.waiting_tasks_count,
ws.wait_time_ms,
CAST(ws.wait_time_ms * 1.0
/ NULLIF(ws.waiting_tasks_count, 0) AS DECIMAL(12,2)),
uptime.days_since_restart,
CASE ws.wait_type
WHEN 'PAGELATCH_UP' THEN 'TempDB PFS/GAM/SGAM allocation contention — add TempDB files (up to # of logical CPUs, max 8)'
WHEN 'PAGELATCH_EX' THEN 'TempDB exclusive page latch — likely allocation bitmap contention'
ELSE 'TempDB shared page latch — allocation page reads'
END
FROM sys.dm_os_wait_stats ws
CROSS JOIN (
SELECT DATEDIFF(DAY, sqlserver_start_time, GETDATE()) AS days_since_restart
FROM sys.dm_os_sys_info
) uptime
WHERE ws.wait_type IN ('PAGELATCH_UP', 'PAGELATCH_EX', 'PAGELATCH_SH')
AND ws.waiting_tasks_count >= @min_wait_count
UNION ALL
-- ── Spinlock contention ──────────────────────────────────────────────────────
SELECT
'SPINLOCK',
ss.name,
ss.collisions,
ss.spins / 1000,
CAST(ss.spins * 1.0 / NULLIF(ss.collisions, 0) AS DECIMAL(12,2)),
uptime.days_since_restart,
'Spin ratio: ' + CAST(CAST(ss.spins * 1.0 / NULLIF(ss.collisions, 0) AS DECIMAL(10,0)) AS VARCHAR)
+ ' spins/collision — avg_wait_ms column shows spin ratio, not ms'
FROM sys.dm_os_spinlock_stats ss
CROSS JOIN (
SELECT DATEDIFF(DAY, sqlserver_start_time, GETDATE()) AS days_since_restart
FROM sys.dm_os_sys_info
) uptime
WHERE ss.collisions >= @min_spinlock_collisions
AND ss.spins / NULLIF(ss.collisions, 0) > 1000
) contention
ORDER BY
CASE contention_type
WHEN 'LOCK' THEN 1
WHEN 'LATCH' THEN 2
WHEN 'TEMPDB_ALLOC' THEN 3
WHEN 'SPINLOCK' THEN 4
END,
total_wait_ms DESC;
3. Get-BlockingChainsWithPlan — Blocking Chains With the Query Plan Attached
/*
Script Name : Get-BlockingChainsWithPlan
Category : diagnostics
Purpose : Same as Get-BlockingChains.sql with the addition of query_plan for
every session that has a cached plan. Use the PowerShell wrapper to
extract plans to individual XML files. Plan will be NULL for idle
head blockers (no active request) and sessions still in parse/compile.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-lock-contention-and-blocking-plans/)
Requires : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-lock-contention-and-blocking-plans/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DROP TABLE IF EXISTS #ar;
SELECT
session_id, blocking_session_id, status, wait_type, wait_time,
cpu_time, logical_reads, writes, total_elapsed_time,
sql_handle, plan_handle, database_id,
statement_start_offset, statement_end_offset
INTO #ar
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID;
WITH
DownstreamCounts AS (
SELECT blocking_session_id,
COUNT(*) AS downstream_waiters
FROM #ar
WHERE blocking_session_id > 0
GROUP BY blocking_session_id
),
Chain AS (
-- Anchor: head blockers — block others but are not themselves blocked
SELECT
s.session_id AS chain_id,
0 AS chain_level,
CAST('head blocker' AS VARCHAR(16)) AS role,
CAST(0 AS SMALLINT) AS blocker_session_id,
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.open_transaction_count,
COALESCE(r.status, s.status) AS status,
r.wait_type,
r.wait_time,
r.cpu_time,
r.logical_reads,
r.writes,
r.total_elapsed_time,
COALESCE(r.database_id, s.database_id) AS database_id,
COALESCE(r.statement_start_offset, 0) AS statement_start_offset,
COALESCE(r.statement_end_offset, -1) AS statement_end_offset,
COALESCE(r.sql_handle, c.most_recent_sql_handle) AS sql_handle,
r.plan_handle
FROM sys.dm_exec_sessions AS s
LEFT JOIN #ar AS r ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
AND r.session_id IS NULL
WHERE s.is_user_process = 1
AND EXISTS (SELECT 1 FROM #ar WHERE blocking_session_id = s.session_id)
AND NOT EXISTS (SELECT 1 FROM #ar WHERE session_id = s.session_id AND blocking_session_id > 0)
UNION ALL
-- Recursive: victims blocked by the previous chain level
SELECT
ch.chain_id,
ch.chain_level + 1,
CAST('blocked' AS VARCHAR(16)),
r.blocking_session_id,
r.session_id,
s.login_name,
s.host_name,
s.program_name,
s.open_transaction_count,
r.status,
r.wait_type,
r.wait_time,
r.cpu_time,
r.logical_reads,
r.writes,
r.total_elapsed_time,
r.database_id,
r.statement_start_offset,
r.statement_end_offset,
r.sql_handle,
r.plan_handle
FROM #ar AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
JOIN Chain AS ch ON ch.session_id = r.blocking_session_id
WHERE r.blocking_session_id > 0
)
SELECT
ch.chain_id,
ch.chain_level,
ch.role,
ch.blocker_session_id,
ch.session_id,
ch.login_name,
ch.host_name,
ch.program_name,
DB_NAME(ch.database_id) AS database_name,
ch.open_transaction_count,
ch.status,
ch.wait_type,
ISNULL(ch.wait_time, 0) AS wait_time_ms,
ISNULL(ch.cpu_time, 0) AS cpu_time_ms,
ISNULL(ch.logical_reads, 0) AS logical_reads,
ISNULL(ch.writes, 0) AS writes,
ISNULL(ch.total_elapsed_time, 0) AS total_elapsed_time_ms,
ISNULL(dc.downstream_waiters, 0) AS downstream_waiters,
CAST(
(ISNULL(su.user_objects_alloc_page_count, 0) +
ISNULL(su.internal_objects_alloc_page_count, 0)) * 8
AS BIGINT) AS tempdb_allocations_kb,
CAST(
(ISNULL(su.user_objects_alloc_page_count, 0) - ISNULL(su.user_objects_dealloc_page_count, 0) +
ISNULL(su.internal_objects_alloc_page_count, 0) - ISNULL(su.internal_objects_dealloc_page_count, 0)) * 8
AS BIGINT) AS tempdb_current_kb,
SUBSTRING(
ISNULL(qt.text, ''),
(ch.statement_start_offset / 2) + 1,
CASE
WHEN ch.statement_end_offset = -1
THEN LEN(ISNULL(qt.text, ''))
ELSE (ch.statement_end_offset - ch.statement_start_offset) / 2 + 1
END
) AS sql_text,
CAST(qp.query_plan AS NVARCHAR(MAX)) AS query_plan
FROM Chain AS ch
LEFT JOIN DownstreamCounts AS dc ON dc.blocking_session_id = ch.session_id
LEFT JOIN sys.dm_db_session_space_usage AS su ON su.session_id = ch.session_id
OUTER APPLY sys.dm_exec_sql_text(ch.sql_handle) AS qt
OUTER APPLY sys.dm_exec_query_plan(
CASE WHEN ch.plan_handle <> 0x0000000000000000000000000000000000000000
THEN ch.plan_handle END
) AS qp
ORDER BY
ch.chain_id,
ch.chain_level,
ch.session_id
OPTION (MAXRECURSION 10);
How To Run From The Repo
Clone DBA Tools, initialize and run whichever script answers your question:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Tables with the most lock escalations:
.\run.ps1 Get-LockEscalationStats
# Lock/latch/TempDB/spinlock contention summary since last restart:
.\run.ps1 Get-ContentionAnalysis
# Blocking chains with the query plan attached (use during an active incident):
.\run.ps1 Get-BlockingChainsWithPlan
# Any of these against a remote sql server:
.\run.ps1 Get-ContentionAnalysis -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/performance/blocking-locking/Get-LockEscalationStats.sqlsql/performance/blocking-locking/Get-ContentionAnalysis.sqlsql/performance/blocking-locking/Get-BlockingChainsWithPlan.sql
Example Output
Real output from this lab instance, not staged.
Get-ContentionAnalysis (5 rows, a genuinely useful result on this small lab box, 4 days since last restart):
| contention_type | resource_name | wait_count | total_wait_ms | avg_wait_ms | note |
|---|---|---|---|---|---|
| LOCK | LCK_M_SCH_M | 512 | 7,988 | 15.60 | Locking contention, run Get-BlockingChains for real-time chain analysis |
| LATCH | BUFFER | 2,171,287 | 73,372,629 | 33.79 | Buffer pool contention, memory pressure or heavy page access |
| LATCH | ACCESS_METHODS_DATASET_PARENT | 2,746 | 36,125 | 13.16 | Scan/seek contention, high concurrent read/write activity |
| TEMPDB_ALLOC | PAGELATCH_SH | 46,954 | 1,440 | 0.03 | TempDB shared page latch, allocation page reads |
| TEMPDB_ALLOC | PAGELATCH_EX | 1,547 | 402 | 0.26 | TempDB exclusive page latch, likely allocation bitmap contention |
Get-LockEscalationStats and Get-BlockingChainsWithPlan both return 0 rows here, an honest, valid finding: no table on this instance has hit the escalation threshold, and there’s no active blocking at the moment this was run.
⚠ Found and fixed a real bug in Get-LockEscalationStats.sql while testing it: the script referenced ios.lock_escalation_count, a column that doesn’t exist on sys.dm_db_index_operational_stats. The correct column for this exact metric is index_lock_promotion_count. Fixed both the SUM() and the WHERE filter, re-verified clean against HPAI01 (0 rows, no escalations on this lab instance, a real and expected result for a small, low-concurrency box).
Understanding the Results
- contention_type = LOCK, high total_wait_ms — real blocking cost; cross-reference with Blocking Chains or this post’s own
Get-BlockingChainsWithPlanfor the specific sessions and queries involved - contention_type = LATCH (BUFFER) — buffer pool contention usually points at memory pressure or an unusually hot set of pages; check
max server memoryconfiguration and whether the working set fits in the buffer pool - contention_type = TEMPDB_ALLOC — PFS/GAM/SGAM allocation contention has a well-known standard fix: add TempDB data files up to the number of logical CPUs (capped at 8), each additional file spreads allocation page contention across more files
- lock_escalations > 0 on a specific table — that table is a real escalation candidate; consider
LOCK_ESCALATION = DISABLE(carefully), smaller batch sizes for large DML, orREAD_COMMITTED_SNAPSHOTto reduce shared-lock pressure on readers - query_plan is NULL for a head blocker — expected for an idle session holding an open transaction with no currently-active request; the plan only exists for a session actively running a statement
Best Practices
- Run
Get-ContentionAnalysisas a routine health check, it’s cumulative since restart so a recently-restarted instance will show low numbers regardless of underlying issues, factor indays_since_restartbefore drawing conclusions - Reach for
Get-BlockingChainsWithPlanspecifically during an active incident once you already know blocking is happening, use the lighter Blocking Chains for routine checks since plan capture adds overhead - Treat any table with real lock escalations as a candidate for either query-batching or a
READ_COMMITTED_SNAPSHOTreview, not just a data point to note - TempDB allocation contention has a standard, well-tested fix (more data files up to the CPU count), apply it directly rather than tuning around the symptom
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
Why is total_wait_ms so much higher for LATCH than LOCK here?
Latch waits (especially BUFFER) accumulate across every page access on the instance, a much higher-frequency event than lock waits, which only occur under genuine row/page/table contention. A high LATCH total is common and not automatically concerning, compare it against avg_wait_ms and days_since_restart before treating it as a problem.
Does 0 rows from Get-LockEscalationStats mean escalation can never happen here?
No, it means no table has crossed the escalation threshold (roughly 5,000 locks in a single statement) since the last restart. A large batch operation run tomorrow could trigger it; this script reflects history since restart, not a permanent guarantee.
Summary
Blocking and contention show up in different DMVs depending on what’s actually causing them, lock escalation converting row locks to table locks, cumulative wait statistics across four different resource types, or a live blocking chain with the actual query behind it. These three scripts cover that range in one cluster, from “has this ever been a problem” to “what exact query is the problem right now.”
Run Get-ContentionAnalysis as a routine check, and reach for Get-LockEscalationStats and Get-BlockingChainsWithPlan specifically once contention or blocking is already suspected, that’s when the extra detail earns its keep.
Leave a Reply