How to find blocking session ID for my SP

Question

I have a SP that runs at night and sometimes it does not finish. The tool I automate the runs with has an option about that can kill the job after some time if it does not finish, i.e. it kills the job e.g. after one hour.

Anyway I think the reason it sometimes does not finish in the maximum allotted time is because it is being blocked by another session ID. How can I query the DMV's for the text of the query and find out exactly what is in the blocking session.

I have this query and I know the blocking session ID and my session ID.

SELECT TOP 100 w.session_id, w.wait_duration_ms, w.blocking_session_id, w.wait_type, e.database_id, D.name
FROM sys.dm_os_waiting_tasks w
LEFT JOIN sys.dm_exec_sessions e ON w.session_id = e.session_id
LEFT JOIN sys.databases d ON e.database_id = d.database_id
where w.session_id = x and w.blocking_session_id = y
order by w.wait_duration_ms desc

How can I get the content (e.g. name of the SP) of the blocking session ID?

Answer 1

You can download and create sp_whoisactive routine. It will give you a clear details of what's going on right now.

For example, create a table:

DROP TABLE IF EXISTS dbo.TEST;

CREATE TABLE dbo.TEST
(
    [Column] INT 
);

In one session execute the code below:

BEGIN TRAN;

INSERT INTO dbo.TEST
SELECT 1

-- commit tran

Then in second:

SELECT *
FROM dbo.TEST;

In third one, execute the routine:

EXEC sp_Whoisactive

It will give you something like the below:

You can clearly see the SELECT is blocked by the session with open transaction.

As the routine is returning the activity for particular moment, you may want to record the details in a table and analyze them later.

If you are doubting that the process is blocked or a deadlock victim, it will be more appropriate to create extended event session which is collecting only these events. There are many examples of how this is done and it's easy. It's good, because you can analyze the deadlock graph and fix the issue easier.