I need to see when a particular table is being blocked in my env. I also want to see how long it was blocked for, if possible. The reason why I need to know this is because there is an integration process that moves data from one database to another that is timing out and we think it's because another process has a lock on a certain table.

So far, I have this query to show me when anything is blocked at the moment, but I don't know if it's possible to filter on a particular table (since I get no results when I run this) and also not sure if it can tell me how long something is blocked for.

select 

    t.spid
    , t.[name]
    , t.hostname
    , t.[program_name]
    , 'Blocked By:' [Blocked By:]
    , t2.spid
    , t2.[name]
    , t2.hostname
    , t2.[program_name]
    
from 

    _Running_Processes t with(nolock)
    left join _Running_Processes t2 with(nolock) on t.[Blocking spid] = t2.spid 

where

    t.[Blocking spid] <> 0
    
order by 

    t.spid

I need to see when a particular table is being blocked in my env. I also want to see how long it was blocked for, if possible. The reason why I need to know this is because there is an integration process that moves data from one database to another that is timing out and we think it's because another process has a lock on a certain table.

So far, I have this query to show me when anything is blocked at the moment, but I don't know if it's possible to filter on a particular table (since I get no results when I run this) and also not sure if it can tell me how long something is blocked for.

select 

    t.spid
    , t.[name]
    , t.hostname
    , t.[program_name]
    , 'Blocked By:' [Blocked By:]
    , t2.spid
    , t2.[name]
    , t2.hostname
    , t2.[program_name]
    
from 

    _Running_Processes t with(nolock)
    left join _Running_Processes t2 with(nolock) on t.[Blocking spid] = t2.spid 

where

    t.[Blocking spid] <> 0
    
order by 

    t.spid

• sql
• sql-server
• t-sql
• sql-server-2019

Share Improve this question Follow edited Feb 4 at 18:08 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Feb 4 at 18:05 TheMortiestMortyTheMortiestMorty 70522 gold badges77 silver badges1616 bronze badges 1

• If you're using a reasonable isolation level, practically every query you run will have at least some locks on each table it uses... maybe not the whole table, but at least part of it. That is how safe concurrent access is maintained, and normally these locks are reasonably efficient so you don't notice. Getting a report for ALL of them for a certain table is likely to produce much more noise than signal. – Joel Coehoorn Commented Feb 4 at 19:50

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You are probably best off using XEvents for this. You can configure a trace on the Blocked Process Report event.

CREATE EVENT SESSION [BlockedProcesses] ON SERVER 
ADD EVENT sqlserver.blocked_process_report
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.query_hash,
        sqlserver.session_id,
        sqlserver.session_nt_username,
        sqlserver.sql_text
    )
    WHERE
    (
        object_id = 12345    -- put the object_id of your table here
        AND (database_name = N'YourDb'
    )
ADD TARGET package0.event_file
(
    SET filename = N'BlockedProcessesFileHere',
        max_file_size=(50)
)
WITH (STARTUP_STATE = ON);

Then you can read it like this:

DECLARE @xml xml;
SELECT TOP (1) @xml = CAST(st.target_data AS xml)
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets st ON st.event_session_address = s.address
WHERE s.name = 'BlockedProcesses'
OPTION (FORCE ORDER);

SELECT
  timestamp       = x.evt.value('@timestamp','datetimeoffset'),
  index_id        = x.evt.value('(data[@name="index_id"]/value/text())[1]','int'),
  blocked_process = x.evt.query('data[@name="blocked_process"]/value'),  -- the XML graph
  spid            = x.evt.value('(action[@name="session_id"]/value/text())[1]','int'),
  hostname        = x.evt.value('(action[@name="client_hostname"]/value/text())[1]','nvarchar(255)'),
  sql_text        = x.evt.value('(action[@name="sql_text"]/value/text())[1]','nvarchar(max)')
FROM @xml.nodes('*/event') x(evt);