SQL Server – Why a Session Becomes a Head Blocker

activity-monitorblockingsql serversql-server-2008-r2

I am troubleshooting a problem that seems to be random

The problem is sometimes an application (dynamics SL) is freezing

When the application is freezing , I notice there is a blocked by in SQL activity monitor
and a head blocker

in my limited understanding , head blocker means a session is currently running and is locking a resource and that resource is also needed by another session. so all sessions that need access to that resource cannot continue thus the "blocked by" that I see in activity monitor

my question is
in five cases that I have seen is that the head blocker task status is actually blank. usually a task status is "running" or "suspended"
now how can a session that is not running becomes the head blocker?

I am thinking my understanding of head blocker maybe wrong. but please enlightened me ELI5 style 🙂

each of the 5 cases. I just kill the process of the head blocker. then everyone is not freezing anymore.

I am hoping to identify the source of this head blocker and come up with a solution

When I click the details of the head blocker. all I see is 

select @@identity

please advise?

thank you

Best Answer

Actually in order to be blocking a session only has to hold a lock to a resource something else wants. It does not actually have to be doing anything. For example if you run the following

BEGIN TRANSACTION
    UPDATE MyTable SET Col1 = 'ABC'

And don't run a COMMIT then you are going to leave a transaction open. The session is not doing anything and in fact it will not show up in sys.dm_exec_requests. However the locks are going to continue to be held and continue to block other sessions. You can find the information on open transactions in sys.dm_tran_session_transactions.

Next point is the command you are seeing. That is just the last batch of the session. If you open a transaction and run several batches through it you may very well be holding locks from earlier commands that are not going to show up.

I wrote about both of these things in quite a bit more detail in the following links:

Transactions: Who What and Where

Using sys.dm_exec_sql_text to figure out blocking is sometimes flawed.

Also the first link will have information on how to find who is running/ran the offending transaction. I frequently find that if the login is a shared one, or a SQL ID I can track the individual down by using the information in the host_name column of sys.dm_exec_sessions.

Related Question