Monitor and Server Health Group

 View Only

  • 1.  Frequently corrupted execution plan for spMonitor_GetAvailableMetricsOnComputer

    Posted Feb 23, 2011 09:57 AM

    DISCLAIMER: I AM NOT A DBA, NOR DO I CLAIM TO KNOW ANYTHING ABOUT DATABASES

     

    We periodically have a situation where access to the Resource Manager for systems under management times out.  About 6 month ago, when this occurred, we were fortunate enough to have some bright individuals from Symantec on site and they were able to determine by looking at the activity monitor on our SQL Server that the offending stored procedure (spMonitor_GetAvailableMetricsOnComputer) was taking waaay too long to execute, and causing the timeout.  They had a hunch that it was a corrupted execution plan.  They recompiled the SP by modifying (and not making any changes to it), and re-executing it and that resolved the issue. 

    We had not noticed any occurrences of this issue again until two weeks ago, and since then we've seen it twice.  I've been able to alleviate the problem by following those steps.
     

    Questions for the community:

    1. Has anyone else in the community seen this problem?
    2. If you have seen it, is it localized to this one particular stored procedure?
    3. What can I do to prevent this from happening in the future?

     

    Thanks in advance for any tips.



  • 2.  RE: Frequently corrupted execution plan for spMonitor_GetAvailableMetricsOnComputer

    Posted Feb 23, 2011 11:11 AM

    Hi Scott,

    Passing this information to dev and they will be looking into it further.

    Kind Regards,



  • 3.  RE: Frequently corrupted execution plan for spMonitor_GetAvailableMetricsOnComputer

    Posted Feb 28, 2011 05:09 AM

    Hi Scott,

    Just received some information on this.  This particular stored proc has been re-written for the upcoming 7.1 release with improvements.  If this is frequently occuring i would suggest we work thru support to review if this is resolved with the updated SP. 

    Kind Regards,