ExamFinch.com My Study

Study guide

9.2 Session Blocking and Dynamic Management Views

Practice this exam

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

9.2 Session Blocking and Dynamic Management Views

Key Takeaways Explain This Topic AI

Blocking is one session holding a lock another session needs; deadlocking is a circular dependency where both sessions block each other and one is chosen as the victim

A blocking chain has a head blocker at its root; resolving the head blocker resolves the whole chain, so the first diagnostic step is always identifying the head blocker

LCK_M_* waits indicate lock waits on specific resources; PAGEIOLATCH_* waits indicate buffer pool I/O pressure, not lock contention - confusing the two leads to wrong remedies

sys.dm_tran_locks, sys.dm_os_waiting_tasks, sys.dm_exec_requests, and sys.dm_exec_sessions joined with sys.dm_exec_sql_text expose blockers, waiters, wait types, and the SQL being run

RCSI, snapshot isolation, index tuning, and shorter transactions are the durable fixes for blocking; killing the head blocker is a temporary release

Last updated: August 2026

Blocking vs Deadlocking

Blocking is a normal consequence of locking: session A holds a lock on a resource (row, page, key, table) that session B needs, so B waits. Blocking becomes a problem only when wait times grow. Deadlocking is a special case: two (or more) sessions each hold a lock the other needs, forming a cycle. SQL Server detects the cycle and picks a deadlock victim - the cheaper session to roll back - and raises error 1205 to that session. The key exam distinction: blocking is a wait, deadlocking is an error; blocking can resolve itself when the blocker commits, deadlocking never resolves without intervention.

A blocking chain forms when session A blocks B, B blocks C, C blocks D, and so on. The session at the root (A) is the head blocker . The single most important diagnostic principle: always find and address the head blocker first. Killing or rolling back a non-head session only shortens the chain by one; resolving the head blocker clears the entire chain.

Wait Types That Signal Blocking and I/O Pressure

The wait_type column in sys.dm_os_waiting_tasks and sys.dm_exec_requests tells you why a session is waiting. Two families are easily confused:

Wait family Meaning Typical cause Remedy

LCK_M_S, LCK_M_X, LCK_M_IS, LCK_M_IX, LCK_M_U Lock waits on a specific resource type (shared, exclusive, intent, update) Blocking by another session Find head blocker; tune indexes/isolation

PAGEIOLATCH_SH, PAGEIOLATCH_EX Latch on a buffer while the page is read from disk into the buffer pool Buffer pool I/O pressure, large scans, memory pressure Add indexes, increase memory, tune queries

PAGELATCH_EX Latch contention on a hot page (e.g., end-of-insert on a clustered index) Hotspot on a single page Partition, use a non-sequential leading key

WRITELOG Waiting on log flush Transaction log I/O bottleneck Faster log disk, batch transactions

The trap: PAGEIOLATCH is a latch wait, not a lock wait. A scenario that says "PAGEIOLATCH waits are high, so I will switch to snapshot isolation" is wrong - snapshot isolation addresses lock waits (LCK_M_*), not buffer-pool I/O. The correct remedy for PAGEIOLATCH is to reduce I/O (indexes, covering indexes, more memory) or speed up the storage.

  • The Core DMVs for Blocking Investigation
  • The diagnostic query pattern joins five DMVs: SELECT
  • s.session_id, s.host_name, s.program_name, s.status AS session_status,
  • r.status AS request_status, r.wait_type, r.wait_resource, r.wait_time_ms,
  • r.blocking_session_id, r.command,
  • SUBSTRING(t.text, (r.statement_start_offset/2)+1,
  • CASE r.statement_end_offset
  • WHEN -1 THEN DATALENGTH(t.text)
  • ELSE (r.statement_end_offset - r.statement_start_offset)/2 + 1
  • END) AS statement_text,
  • DB_NAME(r.database_id) AS db_name
  • FROM sys.dm_exec_requests r
  • JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
  • OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
  • WHERE r.session_id <> @@SPID
  • ORDER BY r.blocking_session_id, r.wait_time_ms DESC;

The DMVs and what each contributes:

sys.dm_exec_sessions : one row per session. Host, program, login, status (sleeping vs running), and is_user_process to filter system sessions.

sys.dm_exec_requests : one row per active request. blocking_session_id is nonzero when the request is blocked; wait_type , wait_resource , and wait_time_ms describe the wait. statement_start_offset / statement_end_offset let you extract the exact statement from a multi-statement batch.

sys.dm_tran_locks : one row per active lock. resource_associated_entity_id joins to a hobt_id or object_id; request_status is GRANT or WAIT. Use this to see exactly what resource a blocker holds.

sys.dm_os_waiting_tasks : one row per waiting task. blocking_session_id here also identifies the blocker; wait_duration_ms and resource_description give detail.

sys.dm_exec_sql_text : returns the full text of a batch given a sql_handle ; cross-applied against the request or session.

To find the head blocker: query sys.dm_exec_requests where blocking_session_id <> 0 , then identify sessions whose session_id appears as a blocker but whose own blocking_session_id is 0 - those are head blockers. Alternatively, the blocking_chain pattern: walk blocking_session_id until you reach a session that blocks nothing.

Blocked Process Report and Deadlock Graphs

SQL Server can capture blocking and deadlocks as events. Trace flags 1222 and 1204 write deadlock information to the SQL Server error log (1222 is the modern default; 1204 is the legacy format). The blocked process report is an XML report raised when a session is blocked longer than the blocked process threshold (configured via sp_configure 'blocked process threshold' , in seconds); it fires a BlockedProcessReport event consumed by Extended Events.

The richest deadlock source is the system_health Extended Event session , which is on by default on SQL Server and captures xml_deadlock_report . To read recent deadlocks: SELECT XEvent.query('(event/data[@name="deadlock"]/value/deadlock)[1]') AS deadlock_graph,

  • XEvent.value('(event/@timestamp)[1]', 'datetime2') AS event_time
  • FROM (SELECT CAST(target_data AS xml) AS target_data
  • FROM sys.dm_xe_session_targets st
  • JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
  • WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer') AS t
  • CROSS APPLY target_data.nodes('//event[@name="xml_deadlock_report"]') AS XEvent(x);

On Azure SQL Database, system_health is not available; you instead capture deadlocks with a custom Extended Event session targeting the sqlserver.xml_deadlock_report event, stream the SQLInsights and Deadlocks diagnostic-log categories to a Log Analytics workspace, or read the deadlock platform metric in Azure Monitor. The deadlock graph shows the victim, the resources, the locks, and the statements from each session - read it to find the cheapest fix.

Resolving Blocking Durably

Releasing a chain by killing the head blocker ( KILL <session_id> ) is a temporary measure. The durable fixes:

Enable Read Committed Snapshot Isolation (RCSI) or snapshot isolation : readers get a row-versioned view and do not need shared locks, eliminating most reader/writer blocking. RCSI is enabled at the database level ( ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON ) and is the default on Azure SQL Database - a common reason workloads stop blocking when migrated to Azure. Snapshot isolation requires code changes to use SET TRANSACTION ISOLATION LEVEL SNAPSHOT and careful handling of update conflicts.

Tune indexes so queries touch fewer rows and hold locks for shorter durations; a missing index often manifests as blocking on a large scan.

Shorten transactions : commit in the smallest meaningful unit; do not hold transactions open across user input or network calls.

Use a lower isolation level when correctness permits - but only after RCSI, which gives you read consistency without locks, is ruled out.

Batch large updates to reduce lock duration and lock escalation.

A recurring exam scenario: an OLTP workload migrated from on-premises SQL Server to Azure SQL Database suddenly has much less blocking - the answer is RCSI, which is on by default for Azure SQL Database and off by default for SQL Server.