Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Blocked Processes Expand / Collapse
Author
Message
Posted Saturday, January 22, 2011 4:54 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:24 PM
Points: 604, Visits: 1,306
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.
Post #1052004
Posted Saturday, January 22, 2011 10:18 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:55 AM
Points: 1,618, Visits: 20,978
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
Post #1052023
Posted Sunday, January 23, 2011 10:19 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:24 PM
Points: 604, Visits: 1,306
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 ?
Post #1052069
Posted Sunday, January 23, 2011 10:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:55 AM
Points: 1,618, Visits: 20,978
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
Post #1052071
Posted Monday, January 24, 2011 7:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1052342
Posted Wednesday, February 5, 2014 6:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 10:49 AM
Points: 21, Visits: 312
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.
Post #1538436
Posted Wednesday, February 5, 2014 6:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:00 PM
Points: 21,212, Visits: 14,910
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1538439
Posted Wednesday, February 5, 2014 10:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1538464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse