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:
- Has anyone else in the community seen this problem?
- If you have seen it, is it localized to this one particular stored procedure?
- What can I do to prevent this from happening in the future?
Thanks in advance for any tips.