1

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

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
BobNoobGuy
  • 413
  • 3
  • 7
  • 11

1 Answers1

4

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.

Kenneth Fisher
  • 24,127
  • 12
  • 61
  • 114
  • Thanks, Definitely will read in more details on that link. This clears up my understanding on head blocker Thank you – BobNoobGuy Apr 11 '14 at 17:55