locked
SQL MP Blocking SPIDs Monitor RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Anyone notice this is set to not generate an alert by default? Anyone have any idea why that setting is set like that? Bug?
    Wednesday, August 26, 2009 4:05 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    You're correct.  By default the management pack will alert when deadlocks have occurred, and blocking will reflect up on the state of the DB overall (allowing for reporting, etc.).  In this way the MP will alert on the most critical conditions and still give users the ability to report on chronic problems.  If you want to be more proactive about dealing with blocking then you can enable alerting on this monitor, but the assumption of the MP's design is that is something you will manage as problems (in the ITIL sense) as opposed to incidents.

    Feedback on the design is defintely welcome.
    This posting is provided "AS IS" with no warranties, and confers no rights. Use of attachments are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Thursday, August 27, 2009 5:51 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    You're correct.  By default the management pack will alert when deadlocks have occurred, and blocking will reflect up on the state of the DB overall (allowing for reporting, etc.).  In this way the MP will alert on the most critical conditions and still give users the ability to report on chronic problems.  If you want to be more proactive about dealing with blocking then you can enable alerting on this monitor, but the assumption of the MP's design is that is something you will manage as problems (in the ITIL sense) as opposed to incidents.

    Feedback on the design is defintely welcome.
    This posting is provided "AS IS" with no warranties, and confers no rights. Use of attachments are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Thursday, August 27, 2009 5:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Cory, I appreciate the reply. The guide hints or seems to indicate that you would expect an alert by default:

    Monitoring for blocked single server process IDs (SPIDs). A monitor periodically queries each database engine instance for a list of SPIDs and checks to see if any long-running blocking is occurring. If blocking is detected and it exceeds the given threshold, then the state is changed and an alert is raised.


    Personally I'm not a DBA so I'm not too particular about this monitor but I would like to see one created for SQL 2000. I find it somewhat odd there isn't a Blocking SPIDs monitor for SQL 2000.
    Monday, August 31, 2009 8:28 PM