Blocked / blocking sessions

• SFlucke

  SSC Veteran

  Points: 251

  More actions

  September 9, 2021 at 9:55 am

  #3928147

  Hi, guys!

  According to BOL I've used this code since more then 10 ago successfully, but rarely in the last years - to query which session is block by which session quite fast (as you can see in the ActivityMonitor as well):

  SELECT tl.resource_type, tl.resource_database_id,
         tl.resource_associated_entity_id, tl.request_mode,
         tl.request_session_id, wt.blocking_session_id, 
         wt.wait_type, wt.wait_duration_ms
  FROM sys.dm_tran_locks as tl
  INNER JOIN sys.dm_os_waiting_tasks as wt
  ON tl.lock_owner_address = wt.resource_address

  And for sad, using SQL 2017 (14.0.3356.20) the query returns no reasonable results, no matches according "tl.lock_owner_address = wt.resource_address", but the Avtivity Monitor shows blockings!

  And profiling the Activity Monitor I can see a quite different way to search for blocking sessions using sys.dm_os_tasks, sys.dm_exec_requests and sys.dm_exec_sessions.

  Any thought regarding this - is BOL not correct anymore regarding >>lock_owner_address - Memory address of the internal data structure that is used to track this request. This column can be joined the with resource_address column in sys.dm_os_waiting_tasks.<<

  https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?f1url=%3FappId%3DDev15IDEF1%26l%3DEN-US%26k%3Dk(sys.dm_tran_locks_TSQL)%3Bk(sql13.swb.tsqlresults.f1)%3Bk(sql13.swb.tsqlquery.f1)%3Bk(MiscellaneousFilesProject)%3Bk(DevLang-TSQL)%26rd%3Dtrue&view=sql-server-2017

  Kind regards! Sebastian

• September 10, 2021 at 8:09 am

  This was removed by the editor as SPAM

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  September 10, 2021 at 10:10 am

  #3928500

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.