Find the root cause of SQL Server blocking and deadlocks in minutes. The Locking and Blocking Analysis goal in AI SQL Tuner Studio takes a multi-signal snapshot of the current concurrency state of a user database, correlates ten evidence sections, and asks the AI to produce a prioritized, evidence-cited fix list with confidence levels and rollback plans.
Table of contents
What is the Locking and Blocking Analysis goal?
It is an analysis goal inside AI SQL Tuner Studio that answers the question “why is the database blocking right now, and what should I change first?” for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Fabric SQL DB.
Instead of switching between sp_who2, blocked process reports, the system_health Extended Events session, Query Store, and DMV queries, you point the goal at a user database and receive one HTML report that correlates ten data sources, classifies the root cause, and lists prioritized fixes with confidence levels and estimated reduction in blocking.
How it works
- Connect to a user database. The goal rejects
master,tempdb,model, andmsdbin both the UI and core validation. - Collect. Ten non-blocking collectors run against DMVs, Query Store, system_health Extended Events, and TempDB usage views. Lock inventory is summarized to keep the AI request size reasonable; per-section character counts are logged for diagnostics.
- Analyze. The combined snapshot is sent to the AI with a structured system prompt for root-cause classification, multi-section evidence correlation, and per-recommendation confidence scoring.
- Report. A prioritized HTML report is returned with executive summary KPIs, blocking tree, root cause classification, impact metrics, detailed fixes, code rewrite guidance, historical patterns, and monitoring quality notes.
The ten evidence sections
Each section is collected with non-blocking queries and gracefully skips features that are unavailable. When something like Query Store or the system_health Extended Events session is off, the report includes an enablement advisory instead of failing.
| # | Section | Impact | Source | What it surfaces |
|---|---|---|---|---|
| 1 | Active Blocking Chains | High | sys.dm_os_waiting_tasks, sys.dm_exec_requests, sys.dm_exec_sessions |
Lead blocker to waiter tree, statement-level SQL, batch context, wait type, and wait duration. |
| 2 | Lock Inventory | High | sys.dm_tran_locks |
Held and requested locks for the target database plus tempdb, summarized by resource, object, index, mode, status, session, login, and count. |
| 3 | Long-Running Transactions | High | sys.dm_tran_active_transactions joined to session and request DMVs |
Open read and write transactions ordered by duration descending. |
| 4 | Index Usage Statistics | High | sys.dm_db_index_usage_stats, sys.indexes, sys.objects, sys.dm_db_partition_stats |
Top 50 indexes by combined seek + scan + lookup activity, with update counts and row-count estimates – hotspot indexes and high-scan / low-seek patterns that amplify lock coverage. |
| 5 | Missing Index Candidates | High | sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats |
Top 20 candidates by estimated impact score – scan-causing gaps that directly increase lock scope. |
| 6 | Blocker Query Plans | High | sys.dm_exec_sql_text, sys.dm_exec_query_plan |
SQL text and plan XML for every session that is blocking or being blocked, so the AI can identify scan operators that hold excessive locks. |
| 7 | Deadlock History | High | system_health Extended Events session |
Up to 10 most-recent deadlock XML events. File target on-premises via sys.fn_xe_file_target_read_file; ring buffer on Azure SQL MI via sys.dm_xe_session_targets. |
| 8 | Query Store Top Queries | Medium | sys.query_store_query, sys.query_store_plan, sys.query_store_runtime_stats |
Top 20 queries by total CPU time. Includes an ALTER DATABASE enablement advisory when Query Store is disabled. |
| 9 | Database Settings | Medium | sys.databases, sys.tables |
RCSI, snapshot isolation, auto-update statistics, and per-table lock-escalation setting (TABLE / AUTO / DISABLE). |
| 10 | TempDB Contention | Medium | sys.dm_os_wait_stats, sys.dm_db_file_space_usage, sys.dm_exec_query_stats |
PAGELATCH and LATCH waits, version store and free space, internal and user objects, and the top 10 spilling queries by total spills. |
What the AI produces
The AI receives all ten sections and produces a prioritized HTML report with these components:
- Blocking Summary – whether blocking is active, number of sessions blocked, longest and cumulative wait times.
- Blocking Tree – lead blocker, immediate waiters, and downstream waiters.
- Root Cause Classification – one or more categories from a fixed taxonomy (long-running transaction, missing index, lock escalation, hotspot table or index, parameter sniffing, TempDB contention) with explicit evidence references from the collected sections.
- Prioritized Fixes – each fix includes rationale, evidence references, estimated risk, rollback plan, confidence level (High, Medium, Low), and estimated reduction in blocking.
- SQL and Code Recommendations – when the blocker or waiter SQL suggests scans, wide updates or deletes, broad transaction scope, or non-sargable predicates, the AI recommends targeted query and code changes, often with before-and-after T-SQL examples.
- Long-Term Mitigations – patterns identified from deadlock history or recurring Query Store top queries.
- Enablement Advisories – when Query Store or system_health XE capture is missing, the report explains what evidence they add and the steps to enable them.
Interpretation guide
| Section | What to look for |
|---|---|
| Active Blocking Chains | Lead blocker session ID, wait type, and total waiter count. A deep chain of three or more waiters signals a critical blocker. |
| Lock Inventory | OBJECT-level locks indicate lock escalation. Many KEY or PAGE locks on a single table indicate hotspot access. |
| Long-Running Transactions | Transactions open for more than ~30 seconds without an active request indicate an orphaned or idle-open transaction. |
| Index Usage | High user_scans with low user_seeks on a large table means queries are scanning rather than seeking, holding more locks for longer. High user_updates versus reads can indicate maintenance overhead. |
| Missing Indexes | High-impact missing indexes suggest queries are performing full or range scans instead of seeks. Creating the suggested index converts scans to seeks and dramatically reduces lock coverage. |
| Blocker Plans | Look for Clustered Index Scan or Table Scan operators on large tables in the plan XML. These operators touch and lock every row they read. |
| Deadlock History | Recurring deadlocks on the same table or index combination suggest a consistent access-order issue or a missing covering index. Check the victim and owner sessions for symmetry. |
| Query Store Top Queries | High total CPU queries executing frequently can hold locks for extended periods. Plan regressions for the same query may indicate parameter sniffing causing unpredictable scan depth. |
| Database Settings | If RCSI is OFF and READ COMMITTED is the workload isolation level, enabling RCSI can eliminate reader-writer blocking with minimal risk. If lock escalation is set to TABLE on hot tables, consider AUTO or DISABLE. |
| TempDB Contention | High PAGELATCH_UP or PAGELATCH_EX on TempDB pages combined with a large version store indicates row-versioning pressure causing secondary TempDB contention. Spilling queries should be tuned to grant adequate memory. |
Enable full historical capture
Enable Query Store
Query Store is enabled per-database. Run the following while connected to the target database:
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;
ALTER DATABASE [YourDatabase] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
MAX_STORAGE_SIZE_MB = 1024,
INTERVAL_LENGTH_MINUTES = 60,
QUERY_CAPTURE_MODE = AUTO
);With Query Store enabled, the tool surfaces the top resource-consuming queries and lets the AI correlate recurring high-CPU patterns with blocking activity.
Verify the system_health Extended Events session
The system_health session is enabled by default on SQL Server 2008 and later. To verify it is running:
SELECT name, state_desc
FROM sys.dm_xe_sessions
WHERE name = 'system_health';If it is stopped, restart it:
ALTER EVENT SESSION [system_health] ON SERVER STATE = START;If the session has been dropped entirely, restart the SQL Server service to recreate the default system_health session, or re-create it manually from the SQL Server source scripts. To capture deadlocks in a custom XE session instead, add the xml_deadlock_report event and configure a file target.
Sample report
This is a real report generated against a TPC-H 10 GB workload on SQL Server 2022 Developer Edition. It identifies a synchronous AFTER trigger that runs a MERGE back against the same hot table inside the user transaction as the lead blocker, ranks fixes by estimated reduction in blocking with confidence levels, and includes T-SQL rewrite examples.
View the full sample Locking and Blocking Analysis report →
Supported platforms
- SQL Server 2008 and later (on-premises, including Developer, Standard, and Enterprise editions). Deadlock history is read from the
system_healthfile target. - Azure SQL Database. Database-scoped DMVs and Query Store are supported.
- Azure SQL Managed Instance. Deadlock history is read from the
system_healthring buffer viasys.dm_xe_session_targets. - Fabric SQL Database.
Safety guardrails
- The goal requires a user database. The UI and core validation reject
master,tempdb,model, andmsdb. - All ten collectors are non-blocking and use read-only DMV and system catalog queries.
- Lock Inventory output is summarized (resource type, object, index, mode, status, session, login, count, short SQL snippet) to keep the AI request payload small.
- Per-section character counts are logged for diagnostics, mirroring Index Tuning telemetry.
- If Query Store or system_health XE data is unavailable, the report includes an advisory rather than failing.
Frequently asked questions
What is SQL Server locking and blocking analysis?
It is the process of identifying which sessions are holding locks, which sessions are waiting on those locks, what statements they are running, and which root cause – hotspot index, long-running transaction, missing index, lock escalation, isolation level, or TempDB pressure – is producing the blocking. AI SQL Tuner Studio automates that correlation across blocking chains, lock inventory, blocker query plans, Query Store, deadlock history, database settings, and TempDB.
Does Locking and Blocking Analysis work on Azure SQL Database and Managed Instance?
Yes. On Azure SQL Managed Instance, deadlock history is read from the system_health ring buffer instead of the file target. Sections that depend on optional features include an enablement advisory in the report when those features are off.
Can I run this against master or tempdb?
No. The goal requires a user database. Both the UI and the core validation reject master, tempdb, model, and msdb.
Will collection itself cause blocking?
No. All ten collectors use read-only DMV and system catalog queries that do not take user-locks on application data. They are designed to be safe to run during active blocking incidents.
What if Query Store or the system_health Extended Events session is disabled?
The corresponding section is skipped and replaced with an enablement advisory in the report. Query Store advisories include the ALTER DATABASE statement to turn it on. The system_health session is enabled by default on SQL Server 2008 and later; the advisory shows how to verify and restart it.
Does the AI provide rewrite examples for the offending SQL?
When the blocker or waiter SQL suggests scans, wide updates or deletes, broad transaction scope, or non-sargable predicates, the AI returns targeted query and code recommendations and may include before-and-after T-SQL examples.
What AI model powers the recommendations?
You can choose which model to use among OpenAI GPT-5.4 and Anthropic Claude Sonnet 4.6. Opus 4.6 and 4.7 are also available for Corporate Edition. Collected SQL Server evidence is sent with a structured system prompt and the model returns a prioritized HTML report with confidence levels, evidence citations, and rollback plans.
Get started
Download AI SQL Tuner Studio and run the Locking and Blocking Analysis goal against any user database on SQL Server, Azure SQL DB, Azure SQL MI, or Fabric SQL DB.
