Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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, August 15, 2016 2:17 PM
Points: 608, Visits: 1,362
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: Sunday, January 17, 2016 1:26 AM
Points: 1,618, Visits: 21,012
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, August 15, 2016 2:17 PM
Points: 608, Visits: 1,362
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: Sunday, January 17, 2016 1:26 AM
Points: 1,618, Visits: 21,012
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 5,431, Visits: 7,876
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, June 1, 2016 10:47 AM
Points: 21, Visits: 347
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:07 PM
Points: 19,966, Visits: 18,199
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
Post #1538439
Posted Wednesday, February 5, 2014 10:07 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 41,257, Visits: 38,569
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."

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

Add to briefcase

Permissions Expand / Collapse