{"id":73,"date":"2019-09-25T11:59:01","date_gmt":"2019-09-25T14:59:01","guid":{"rendered":"https:\/\/pedrotornich.com\/a\/?p=73"},"modified":"2019-09-25T11:59:03","modified_gmt":"2019-09-25T14:59:03","slug":"sql-server-table-locks","status":"publish","type":"post","link":"https:\/\/pedrotornich.com\/a\/2019\/09\/25\/sql-server-table-locks\/","title":{"rendered":"SQL Server table locks"},"content":{"rendered":"\n

Hello folks,<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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!<\/p>\n\n\n\n

sp_who2<\/h4>\n\n\n\n

The stored procedure sp_who2<\/strong> lists all current processes connected to a SQL Server. The BlkBy<\/strong> column shows the spid<\/strong> of the process that is blocking the current process.<\/p>\n\n\n\n

exec sp_who2<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
dm_tran_locks #1<\/h4>\n\n\n\n
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.<\/p>\n\n\n\n
The query also shows the statement, so you can use it to check which tables are part of the circular lock.<\/p>\n\n\n\n
USE MASTER\n GO    \n     SELECT \n         SessionID = s.Session_id,\n         resource_type,   \n         DatabaseName = DB_NAME(resource_database_id),\n         request_mode,\n         request_type,\n         login_time,\n         host_name,\n         program_name,\n         client_interface_name,\n         login_name,\n         nt_domain,\n         nt_user_name,\n         s.status,\n         last_request_start_time,\n         last_request_end_time,\n         s.logical_reads,\n         s.reads,\n         request_status,\n         request_owner_type,\n         objectid,\n         dbid,\n         a.number,\n         a.encrypted ,\n         a.blocking_session_id,\n         a.text       \n     FROM   \n         sys.dm_tran_locks l\n         JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id\n         LEFT JOIN   \n         (\n             SELECT  *\n             FROM    sys.dm_exec_requests r\n             CROSS APPLY sys.dm_exec_sql_text(sql_handle)\n         ) a ON s.session_id = a.session_id\n     WHERE  \n         s.session_id > 1\n         AND request_mode ='X'<\/code><\/pre>\n\n\n\n
dm_tran_locks #2<\/h4>\n\n\n\n
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.<\/p>\n\n\n\n
SELECT\n  db.name DBName,\n  tl.request_session_id,\n  wt.blocking_session_id,\n  OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,\n  tl.resource_type,\n  h1.TEXT AS RequestingText,\n  h2.TEXT AS BlockingTest,\n  tl.request_mode\nFROM sys.dm_tran_locks AS tl\nINNER JOIN sys.databases db ON db.database_id = tl.resource_database_id\nINNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address\nINNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id\nINNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id\nINNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id\nCROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1\nCROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2\nGO<\/code><\/pre>\n\n\n\n
Retrieve the blocking statement<\/h4>\n\n\n\n
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:<\/p>\n\n\n\n
dbcc inputbuffer (<spid>)<\/code><\/pre>\n\n\n\n
SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text\nFROM sys.dm_exec_cursors (<spid>) c\nCROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-73","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/posts\/73"}],"collection":[{"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/comments?post=73"}],"version-history":[{"count":5,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/posts\/73\/revisions"}],"predecessor-version":[{"id":80,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/posts\/73\/revisions\/80"}],"wp:attachment":[{"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/media?parent=73"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/categories?post=73"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pedrotornich.com\/a\/wp-json\/wp\/v2\/tags?post=73"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}