Identifying and Analyzing Blocking and Locking Queries in MS SQL

Introduction

In database management systems, blocking and locking issues are common performance bottlenecks that can lead to application response delays and resource contention. MS SQL Server offers various Dynamic Management Views (DMVs) to monitor and diagnose these problems. Based on real-world Q&A data, this article explores how to effectively identify and analyze blocking and locking queries to optimize database performance.

Initial Detection of Locking Issues

By querying sys.dm_os_wait_stats, system-level wait statistics can be obtained to help identify potential locking problems. For example, in the provided Q&A data, the user observed the following wait types:

  wait type    waittime  pct     running ptc
  LCK_M_RS_S   2238.54   22.14   22.14
  LCK_M_S      1980.59   19.59   41.73

Here, LCK_M_RS_S and LCK_M_S represent waits for row-level shared locks and shared locks, with high percentage values possibly indicating lock contention. This provides initial clues for deeper analysis.

Core Method for Identifying Blocking Queries

Based on the best answer (score 10.0), using sys.dm_exec_requests allows for quick identification of queries currently blocking other sessions. The following query returns requests with blocking sessions in a specified database:

SELECT * 
  FROM sys.dm_exec_requests
  WHERE DB_NAME(database_id) = 'YourDBName' 
    AND blocking_session_id <> 0

To retrieve the text of blocking queries, combine with sys.dm_exec_sql_text:

SELECT text,* 
  FROM sys.dm_exec_requests
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
  WHERE DB_NAME(database_id) = 'YourDBName' 
    AND blocking_session_id <> 0

This method directly associates blocking sessions with SQL statements, facilitating rapid diagnosis.

Supplementary Analysis Techniques

Referencing other answers (score 3.3), sys.dm_tran_locks provides more detailed locking information. The following query expands the analysis scope to include locked objects, session details, and transaction context:

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

This query is useful for scenarios requiring a comprehensive understanding of locking, such as identifying issues in specific tables or transactions.

Practical Recommendations and Optimization Strategies

In practice, it is advisable to combine multiple DMVs for integrated analysis. For instance, regularly monitor sys.dm_os_wait_stats to detect trends and use the above queries for real-time diagnosis of blocking. Optimization strategies may include adjusting indexes, modifying query logic, or setting appropriate transaction isolation levels. These techniques are applicable to SQL Server 2005 and later versions, but compatibility differences between versions should be noted.

Conclusion

By systematically utilizing MS SQL Server's Dynamic Management Views, blocking and locking queries can be efficiently identified and analyzed. The core method relies on sys.dm_exec_requests, while supplementary techniques like sys.dm_tran_locks offer deeper insights. Implementing these methods helps enhance database performance, reduce application latency, and lay the groundwork for query optimization.