locked
Basic SQL Management Pack question RRS feed

  • Question

  • I have a question about the SQL Management Pack that should be easy enough for you SQL experts out there.  I'm not the SQL guy so I was a little confused about a statement that was in  the SQL Management Pack guide. It reads, "We recommend that you monitor no more than 50 databases and 150 database files per agent to avoid spikes in CPU usage that may affect the performance of monitored computers."

    If we have a centralized SQL server that houses some 80 databases then what are the implications of the above statement as it would apply to one of our main db servers?

    Sorry to ask such a basic question but that just stood out to me and I knew we had at least one server that had more than 50 databases on it.

    Thanks in advance

    SJJ

    Friday, October 29, 2010 3:16 PM

Answers

  • We're running the SQL agent OK on some very large boxes with only 1 minor issue that's easy to fix. The discoveries for Reporting Service, Analysis Service and DB Engine are all set to run every 14400 sec (4 hrs); this is true on both SQL2005 & 2008. The problem is that they can all fire at the same time and some will timeout. So, we've changed the interval for the RS to 13800 and AS to 15000 (10 min difference than the DB engine) so that they won't run at the same time. Note that our big SQL servers are fairly beefy, 8-16 cores, at least 32GB of RAM, etc.

    "Fear disturbs your concentration"
    • Proposed as answer by Nicholas Li Monday, November 1, 2010 9:39 AM
    • Marked as answer by Nicholas Li Wednesday, November 3, 2010 3:31 AM
    Friday, October 29, 2010 3:52 PM
  • This is just a guideline.  YMMV.  If you are seeing spikes that you don't like, consider tuning the discoveries to run at different intervals.
    Microsoft Corporation
    • Proposed as answer by Nicholas Li Monday, November 1, 2010 9:39 AM
    • Marked as answer by Nicholas Li Wednesday, November 3, 2010 3:31 AM
    Friday, October 29, 2010 4:29 PM

All replies

  • We're running the SQL agent OK on some very large boxes with only 1 minor issue that's easy to fix. The discoveries for Reporting Service, Analysis Service and DB Engine are all set to run every 14400 sec (4 hrs); this is true on both SQL2005 & 2008. The problem is that they can all fire at the same time and some will timeout. So, we've changed the interval for the RS to 13800 and AS to 15000 (10 min difference than the DB engine) so that they won't run at the same time. Note that our big SQL servers are fairly beefy, 8-16 cores, at least 32GB of RAM, etc.

    "Fear disturbs your concentration"
    • Proposed as answer by Nicholas Li Monday, November 1, 2010 9:39 AM
    • Marked as answer by Nicholas Li Wednesday, November 3, 2010 3:31 AM
    Friday, October 29, 2010 3:52 PM
  • This is just a guideline.  YMMV.  If you are seeing spikes that you don't like, consider tuning the discoveries to run at different intervals.
    Microsoft Corporation
    • Proposed as answer by Nicholas Li Monday, November 1, 2010 9:39 AM
    • Marked as answer by Nicholas Li Wednesday, November 3, 2010 3:31 AM
    Friday, October 29, 2010 4:29 PM