2.5 — Evaluate Query Performance and Resolve Blocking and Deadlocks
DP-800 Domain 2 · Skill 2.5
What You Need to Know
You need to know which tool shows what, how to read an execution plan, and how to tell blocking from deadlocks.
The toolbox
| Tool | Best for |
|---|---|
| Execution plans (estimated or actual) | Why one query is slow: scans compared with seeks, key lookups, spills, bad estimates, implicit conversions |
| DMVs | What's happening now: sys.dm_exec_requests (running and blocked), sys.dm_exec_sessions, sys.dm_os_wait_stats (waits), sys.dm_exec_query_stats (cached statistics), sys.dm_db_missing_index_details |
| Query Store | History: top resource consumers, regressed queries, plan changes, wait statistics per query, and forcing a good plan |
| Query Performance Insight (Azure portal, Azure SQL Database) | A visual view over Query Store: top CPU, duration and execution count queries |
| Extended Events | Detailed tracing, such as deadlock graphs and long waits |
Reading a plan: common culprits
| Symptom in the plan | Typical fix |
|---|---|
| Index scan where few rows are needed | Add or adjust a nonclustered index on the predicate |
| Key lookup repeated many times | Make the index covering with INCLUDE |
| Implicit conversion warning (for example nvarchar compared with varchar) | Match parameter and column types |
| Spill to tempdb (sort or hash) | Fix cardinality estimates (statistics), reduce the rows, or rely on memory grant feedback |
| Estimated rows far from actual rows | Update statistics; check for parameter sniffing |
Non-SARGable predicate (WHERE YEAR(OrderDate) = 2026) |
Rewrite as a range (OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01') |
Parameter sniffing: a plan compiled for one parameter value performs badly for others. Options include Query Store plan forcing or hints, OPTION (RECOMPILE) for rare queries, and parameter-sensitive plan optimization (compatibility level 160 and later).
Blocking
- Blocking happens when one session waits for a lock held by another. It resolves when the holder commits or rolls back.
- Find it with
sys.dm_exec_requests.blocking_session_id, and follow the chain to the head blocker (often an open transaction left idle). - Fixes: short transactions, the right indexes (so fewer rows get locked), RCSI or SNAPSHOT for readers, batching large updates, and never waiting for user input inside a transaction.
Deadlocks
- A deadlock is a cycle: A waits for B while B waits for A. The engine picks a victim and raises error 1205. Nothing resolves it by waiting.
- Capture deadlock graphs with the system_health Extended Events session (SQL Server) or the database's deadlock events in Azure SQL.
- Fixes: access objects in the same order everywhere, keep transactions short, add indexes so that fewer locks are taken, use row versioning for reads, and retry the victim's transaction in the application.
Key Concept: Plans explain one query. DMVs show now. Query Store shows history and regressions (and can force plans). Blocking is a wait that ends on its own; a deadlock is a cycle that the engine breaks with error 1205, so fix the access order and retry.
Exam Traps
- Looking for last week's regression in DMVs (cached statistics reset; use Query Store)
- Treating blocking as a deadlock, or the reverse
- Fixing a key lookup by adding a new single-column index instead of
INCLUDEcolumns - Wrapping a column in a function in
WHEREand wondering why the index isn't used
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Monitor performance by using the Query Store, Microsoft Learn
- Query Performance Insight for Azure SQL Database, Microsoft Learn
- Understand and resolve blocking problems, Microsoft Learn
- Analyze and prevent deadlocks in Azure SQL Database, Microsoft Learn
- Display an actual execution plan, Microsoft Learn