Cool SQL Locking detection queries

-- Detaction Queries Locking

 

-- this one uses the dm_tran_locks, just converts some information of that

SELECT
   L.resource_type,
   DB_NAME(L.resource_database_id) as DatabaseName,
   L.resource_associated_entity_id,
   L.request_session_id,
   L.request_mode,
   L.request_status
FROM sys.dm_tran_locks AS L
WHERE L.request_status = 'WAIT'
ORDER BY DatabaseName, L.request_session_id;

 

-- more descriptive

 

SELECT
   w.session_id AS waiting_session_id,
   DB_NAME(w.resource_database_id) AS DatabaseName,
   w.wait_duration_ms,
   w.waiting_task_address,
   t.request_mode,
   t.resource_type,
   t.resource_associated_entity_id,
  w.resource_description AS lock_resource_description,
   w.wait_type,
   w.blocking_session_id,
   w.resource_description AS blocking_resource_description
FROM sys.dm_os_waiting_tasks AS w
   JOIN sys.dm_tran_locks AS t
   ON w.resource_address = t.lock_owner_address
WHERE w.wait_duration_ms > 5000
   AND w.session_id > 50;
  

-- full descriptive with the locked query

 

SELECT
   W.session_id AS waiting_session_id,
   DB_NAME(T.resource_database_id) AS DatabaseName,
   W.wait_duration_ms,
   W.waiting_task_address,
   t.request_mode,
   (SELECT SUBSTRING(ST.text, (req.statement_start_offset/2) + 1,
      ((CASE req.statement_end_offset
         WHEN -1 THEN DATALENGTH(ST.text)
         ELSE req.statement_end_offset
        END - req.statement_start_offset)/2) + 1)
   FROM sys.dm_exec_requests AS req
      CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st
   WHERE req.session_id = T.request_session_id)
      AS waiting_query_text,
   t.resource_type,
   t.resource_associated_entity_id,
   w.wait_type,
   w.blocking_session_id,
   w.resource_description AS blocking_resource_description,
   CASE WHEN W.blocking_session_id > 0 THEN
      (SELECT ST2.text FROM sys.sysprocesses AS Pr
             CROSS APPLY sys.dm_exec_sql_text(pr.sql_handle) AS ST2
      WHERE pr.spid = w.blocking_session_id)
      ELSE NULL
   END AS blocking_query_text
FROM sys.dm_os_waiting_tasks AS w
   JOIN sys.dm_tran_locks AS t
   ON w.resource_address = t.lock_owner_address
WHERE w.wait_duration_ms > 5000
   AND w.session_id > 50;

 

Remember that only works on sql server 2005 or above.