SQL Server table locks

Hello folks,

I’ve been working with AX/D365 for several years and there is one thing that happens in almost all projects with high data volume, at some point we start to get slow processes.

When I have to analyze very slow process, those that seems to be stuck, I like to begin by checking the SQL table locks and here are some useful queries I’ve been using for a long time, hope it helps you too!

sp_who2

The stored procedure sp_who2 lists all current processes connected to a SQL Server. The BlkBy column shows the spid of the process that is blocking the current process.

exec sp_who2

dm_tran_locks #1

The following query will list all lock requests. If the number of requests starts to grow exponentially and seems to never fall, some development probably have introduced a circular lock.

The query also shows the statement, so you can use it to check which tables are part of the circular lock.

USE MASTER
 GO    
     SELECT 
         SessionID = s.Session_id,
         resource_type,   
         DatabaseName = DB_NAME(resource_database_id),
         request_mode,
         request_type,
         login_time,
         host_name,
         program_name,
         client_interface_name,
         login_name,
         nt_domain,
         nt_user_name,
         s.status,
         last_request_start_time,
         last_request_end_time,
         s.logical_reads,
         s.reads,
         request_status,
         request_owner_type,
         objectid,
         dbid,
         a.number,
         a.encrypted ,
         a.blocking_session_id,
         a.text       
     FROM   
         sys.dm_tran_locks l
         JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
         LEFT JOIN   
         (
             SELECT  *
             FROM    sys.dm_exec_requests r
             CROSS APPLY sys.dm_exec_sql_text(sql_handle)
         ) a ON s.session_id = a.session_id
     WHERE  
         s.session_id > 1
         AND request_mode ='X'

dm_tran_locks #2

The following query is similar to the sp_who2, but it will return only the blocked processes, alongside with the blocking spid it will also give you the blocking statement so you can try to figure which process generated the block.

SELECT
  db.name DBName,
  tl.request_session_id,
  wt.blocking_session_id,
  OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
  tl.resource_type,
  h1.TEXT AS RequestingText,
  h2.TEXT AS BlockingTest,
  tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

Retrieve the blocking statement

Sometimes the aforementioned queries are not able to correctly retrieve the query statement related to the blocking spid, so here are two other queries I use to help with it:

dbcc inputbuffer (<spid>)

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (<spid>) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t