Blocked Processes

Question

Blocked Processes

sudhakara

SSCrazy Eights

Points: 9425
More actions
January 22, 2011 at 4:54 pm

#149796

Hi,
Am Getting alert message from third party monitoring tool saying that blocked process reaches 99% on the ms sql server 2005. but there is no blocking on the server and found there too many sessions (>200) on the suspended status with awaiting command mode. Only few shared locks are there for each session.
Can any one tell me how to find the blocking process percentage and give me link where i can find more about that ?
Thanks in Advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

I am not sure how the monitoring tool that you are using the Blocking Process %. You may query sysprocesses, sys.dm_exec_requests to find more information about the blocking processes. This article is also a good read.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 2

Adiga (1/22/2011)
I am not sure how the monitoring tool that you are using the Blocking Process %. You may query sysprocesses, sys.dm_exec_requests to find more information about the blocking processes. This article is also a good read.

Thanks for your reply.

My guess is it showing the lock manager occupied percentage. Could you share the document or guide me how to view the lock manager process on sql server 2005 ?

Adiga One Orange Chip Points: 27284 More actions · Answer 3

You can make use of sys.dm_tran_locks DMV to get that information. Here is an example.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

sudhakara (1/22/2011)
Hi,
Am Getting alert message from third party monitoring tool saying that blocked process reaches 99% on the ms sql server 2005. but there is no blocking on the server and found there too many sessions (>200) on the suspended status with awaiting command mode. Only few shared locks are there for each session.
Can any one tell me how to find the blocking process percentage and give me link where i can find more about that ?
Thanks in Advance.

1) why is 200 spids waiting for commands "too many"?

2) blocked process percentage is pretty useless as a metric IMHO. use sp_whoisactive to find live blocking and start fixing the causes.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ngreene SSC-Addicted Points: 461 More actions · Answer 5

This article may be helpful for your problem.

http://sqlprosperity.com/post/SQL-Server-quasi-real-time-performance-monitoring

It has helped me a lot to identify what processes are causing blocks without having to be on the server querying it at the time of the block. Take a look and hopefully it is helpful.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

ngreene (2/5/2014)
This article may be helpful for your problem.
http://sqlprosperity.com/post/SQL-Server-quasi-real-time-performance-monitoring
It has helped me a lot to identify what processes are causing blocks without having to be on the server querying it at the time of the block. Take a look and hopefully it is helpful.

This thread was from January 2011

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 7

SQLRNNR (2/5/2014)
ngreene (2/5/2014)
This article may be helpful for your problem.
http://sqlprosperity.com/post/SQL-Server-quasi-real-time-performance-monitoring
It has helped me a lot to identify what processes are causing blocks without having to be on the server querying it at the time of the block. Take a look and hopefully it is helpful.
This thread was from January 2011

Just in time, then. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)