Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Blocked Processes


Blocked Processes

Author
Message
sudhakara
sudhakara
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 1383
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.
Adiga
Adiga
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1634 Visits: 21012
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
sudhakara
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 1383
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
Adiga
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1634 Visits: 21012
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
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5937 Visits: 8298
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
ngreene
ngreene
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
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.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21071 Visits: 18259
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

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44974 Visits: 39866
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. :-P

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search