Blocked Processes

  • 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.

  • 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

  • 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 ?

  • 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

  • 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

  • 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.

  • 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

  • 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)

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

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