September 27, 2011 at 4:54 am
We have a customer who is experiencing hanging in our APP. We have found that it is a process that is blocking and it seems to be the same time in the week and the same username NTAUTHORITY\system. Is there any way of tracking where this process / block is coming from? I.e. Free monitor apps and such like.
I am not overly experienced in SQL so please, no jargon 🙂
September 27, 2011 at 5:31 am
Not jargon, but unavoidable technical terms.
You can identify blocking processes by looking at dynamic management objects (DMO). The one that most concerns you is sys.dm_exec_requests. That shows active queries in the system. One of the columns is bloccking_session_id. That will tell you which session is blocking. Then you can use that id to look at what that session is doing by combining the sys.dm_exec_requests with sys.dm_exec_sql_text for the session_id that is blocking your process. You can see exactly what query is being run.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 27, 2011 at 5:43 am
Say if there was an outside source that was blocking the process and not a process in the APP that is causing the block. Is there a way of trapping and idendifying the source of that block?
September 27, 2011 at 5:45 am
Exactly the way Grant's just said. It doesn't matter where the SQL is coming from, blocking is detected the same way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 27, 2011 at 5:47 am
Cheers. I will have a look.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply