Launch Deal · 50% off 1st month ·

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 INCLUDE columns
  • Wrapping a column in a function in WHERE and 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.

See plans

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.