Blocking, DMVs, and Execution Plans
Identify and resolve session blocking, use dynamic management views for performance diagnostics, review execution plans, and monitor with Intelligent Insights.
Diagnosing performance problems
Blocking is like a traffic jam — one car (session) stops in the middle of the road, and everyone behind it waits. Find the car causing the jam, move it, and traffic flows again.
DMVs are your traffic cameras — they show you live data about what every session is doing, what it’s waiting for, and how much resource it’s using.
Execution plans are the GPS route the database chose for your query. Sometimes it picks a bad route (full table scan instead of an index seek). Reading the plan tells you why a query is slow.
Session blocking
Blocking occurs when one session holds a lock that another session needs. It’s normal and temporary in most cases — problematic when it lasts.
Finding blocking chains
-- Find blocked sessions and their blockers
SELECT
blocked.session_id AS blocked_session,
blocked.blocking_session_id AS blocker_session,
blocked.wait_type,
blocked.wait_time / 1000.0 AS wait_seconds,
blocker_text.text AS blocker_query,
blocked_text.text AS blocked_query
FROM sys.dm_exec_requests blocked
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
LEFT JOIN sys.dm_exec_requests blocker ON blocked.blocking_session_id = blocker.session_id
OUTER APPLY sys.dm_exec_sql_text(blocker.sql_handle) blocker_text
WHERE blocked.blocking_session_id <> 0;Resolving blocking
| Strategy | When to Use |
|---|---|
| Wait it out | Short blocks (under 10 seconds) during normal operations |
| Kill the blocker | The blocking session is stuck or abandoned (KILL session_id) |
| Optimise the blocker | The blocking query is inefficient — make it faster (better indexes, smaller transactions) |
| Use RCSI | Enable Read Committed Snapshot Isolation to reduce reader-writer blocking |
| Shorter transactions | Keep transactions as brief as possible — don’t hold locks while waiting for user input |
| Appropriate isolation level | Use READ COMMITTED (default) or SNAPSHOT instead of SERIALIZABLE unless needed |
RCSI — the blocking killer
Read Committed Snapshot Isolation (RCSI) changes how readers and writers interact:
- Without RCSI: Readers block on writers (wait for locks to release)
- With RCSI: Readers get a consistent snapshot from tempdb — no waiting
ALTER DATABASE NorthStarERP SET READ_COMMITTED_SNAPSHOT ON;RCSI is enabled by default on Azure SQL Database. On MI and SQL VMs, you enable it manually. The trade-off: increased tempdb usage for version store.
Essential DMVs
| DMV | What It Shows | Use Case |
|---|---|---|
sys.dm_exec_requests | Currently executing requests | Find active queries, blocking, wait types |
sys.dm_exec_sessions | All active sessions | Session count, login info, memory usage |
sys.dm_exec_query_stats | Aggregated query statistics | Top queries by CPU, reads, duration |
sys.dm_os_wait_stats | Server-wide wait statistics | Identify top waits (I/O, locks, CPU) |
sys.dm_db_index_usage_stats | Index read/write/seek counts | Find unused or missing indexes |
sys.dm_db_missing_index_details | Suggested missing indexes | Index recommendations with impact |
sys.dm_exec_cached_plans | Cached execution plans | Plan cache analysis, plan reuse |
sys.dm_db_resource_stats | Resource utilisation (SQL DB) | CPU, I/O, memory over time |
sys.dm_tran_locks | Current lock information | Lock analysis during blocking |
Practical DMV queries
-- Top 10 queries by average CPU time
SELECT TOP 10
qs.total_worker_time / qs.execution_count AS avg_cpu_us,
qs.execution_count,
qs.total_logical_reads / qs.execution_count AS avg_reads,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 200) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_cpu_us DESC;
-- Missing index recommendations
SELECT
mid.statement AS table_name,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.avg_user_impact AS estimated_improvement_pct,
migs.user_seeks
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact * migs.user_seeks DESC;Execution plans
An execution plan shows the exact steps the query optimizer chose to execute your query.
How to get a plan
| Method | T-SQL | Notes |
|---|---|---|
| Estimated plan | SET SHOWPLAN_XML ON | Shows what the optimizer PLANS to do (without executing) |
| Actual plan | SET STATISTICS XML ON | Shows what actually happened (with row counts, execution times) |
| SSMS | Ctrl+L (estimated), Ctrl+M (actual) | Visual plan diagram |
Key operators to know
| Operator | What It Means | Good or Bad? |
|---|---|---|
| Index Seek | Targeted lookup using an index | Good — efficient |
| Index Scan | Reads entire index | Bad if table is large |
| Clustered Index Scan | Full table scan | Usually bad — no useful index |
| Key Lookup | Fetches additional columns not in the index | OK if few rows, bad if many |
| Nested Loop | Row-by-row join | Good for small result sets |
| Hash Match | Hash-based join | Good for large unsorted sets |
| Sort | Sorts data in memory/tempdb | Expensive if unexpected or large |
| Table Spool | Caches intermediate results | Watch for unnecessary spools |
Reading plan properties
- Estimated vs Actual Rows: A big difference means stale statistics
- Cost %: Higher percentage = more expensive step
- Warnings: Yellow triangles indicate problems (missing indexes, implicit conversions)
Exam tip: implicit conversions
A common plan warning is implicit conversion — the optimizer converts a column’s data type to match a parameter. This can:
- Prevent index seeks (scan instead)
- Waste CPU on type conversion
- Cause wrong row estimates
Example: WHERE VarcharColumn = @NVarcharParam — the optimizer converts every row’s VarcharColumn to NVARCHAR, causing a scan.
Fix: Match parameter types to column types in application code.
Intelligent Insights
Intelligent Insights is an Azure SQL Database and MI feature that uses built-in intelligence to detect performance issues:
- Automatically detects: query regressions, timeout increases, excessive wait times, resource limits
- Provides root cause analysis and recommended actions
- Outputs diagnostics to a JSON log (send to Log Analytics for querying)
- Part of Azure SQL’s diagnostic settings (enable the “SQLInsights” category)
| Insight Type | What It Detects |
|---|---|
| Reaching resource limits | CPU, I/O, or storage hitting platform limits |
| Workload increase | More queries or heavier queries than baseline |
| Memory pressure | Insufficient memory for query execution |
| Locking | Excessive blocking affecting performance |
| Plan regression | Optimizer chose a worse plan (similar to Query Store) |
| Database-scoped config change | A config change impacted performance |
Tomas sees multiple sessions waiting with LCK_M_S waits for over 30 seconds. All are blocked by session 55, which is running an UPDATE inside an uncommitted transaction. What should he do first?
Kenji reviews an execution plan and sees a Clustered Index Scan with 5 million rows returned but the query only needs 10 rows. The WHERE clause uses an NVARCHAR parameter against a VARCHAR column. What is the likely cause?
🎬 Video coming soon
Next up: Index and Query Optimization — identify the right indexes and optimise query constructs.