none
SCSM database has high CPU and RAM usage

    Question

  • I am running my Service Manager environment across 4 virtual (VMWare 4.0) servers:

    • Server 1: Service Manager Management Server
    • Server 2: Service Manager Data Warehouse Management Server
    • Server 3: Database Server - SQL 2008 R2
    • Server 4: Web Server

    All servers are running Windows Server 2008 R2. VMs are running on an IBM blade with dual quad-core Intel Xeon E5530 2.4GHz processors and 48GB of DDR3 RAM. Hard disks are running on a fibre channel SAN with 15K SAS drives. The database VM has been given 2 CPUs and 8GB of RAM and 120GB of hard drive space.

    There have not been any complaints about performance and there are no visible errors that I can find.  However, the database server is consistently running between 50% and 80% CPU utilization and using nearly all 8GB of RAM. This is a very small environment with only 6 helpdesk users. There has been less than 400 tickets created in the system, total (since we brought the server online a month ago), and we're not doing anything fancier than creating tickets in the system at the moment (and querying AD for user and computer accounts). I started looking at the Activity Monitor on the SQL server and found that there are several extremely expensive processes running on the Service Manager databases (one of the worst offenders is a process called [CreateWorkItem] running on the DWStagingAndConfig database).

    This database server is slated to be our primary centralized SQL server for most if not all of our SQL databases in the future. So I can't leave this process the way it is. I couldn't possibly imagine that this is normal behavior for our environment, so I'm hoping someone can tell me what I can do to troubleshoot and/or tweak some settings to keep this process under control. Any ideas?

     

    Thursday, July 29, 2010 6:36 PM

Answers

  • Hi, As this thread has been quiet for a while, we assume that the issue has been resolved. At this time, we will mark it as "Answered". Either the previous steps should be helpful for many similar scenarios and will be marked as answer, or this post will be marked as answer in order to close the thread. Feel free to re-open the thread if you have additional information about this specific case or to open a new thread for a new case. In addition, we’d love to hear your feedback about the solution. By sharing your experience you can help other community members facing similar problems. Thanks,


    Bob Cornelissen - BICTT (My Blog about SCOM) - MVP 2012 and Microsoft Community Contributor 2011 + 2012 Recipient

    Monday, November 19, 2012 7:01 AM
    Moderator

All replies

  • We had a similar issue that was resolved by increasing the CPUs to 4
    _______________________________ james.richardson@adept4.co.uk www.adept4.co.uk
    Friday, July 30, 2010 7:32 PM
  • I don't mind increasing the resources if needed. My concern is that the server is spinning it's wheels when it shouldn't be. We have such a small environment that I can't imagine that the server needs to be utilizing 50% of a dual CPU VM and 8GB of RAM. I have 18 other VMs running on 2 hosts, and this machine is the biggest resource hog by far.

    I've heard that the initial install/sync with the Data Warehouse is supposed to be a huge resource hog, but then it dies down once the initial synchronization is complete. Is it possible that my initial synchronization didn't complete properly? The Data Warehouse and Reporting tabs show up in my Service Manager console, and I can run reports without error. Is there some way to check and verify that the initial synchronization happened properly?

     

    Friday, July 30, 2010 10:54 PM
  • how many custom views and folders have you set up as this too leads to increased CPU / MEM usage
    _______________________________ james.richardson@adept4.co.uk www.adept4.co.uk
    Friday, August 06, 2010 9:50 PM
  • There have been 19 additional views created under Incident Management since we installed the server. No folders. There are 455 tickets in the system, total (active and closed).

    I think I found a bandaid for now. I found this article: vNext: Service Manager 2010 – DW Registration. Sure it's talking about the Beta version, but the PowerShell commands at the bottom of the article are still useful. It showed me how to bring up the schedules for the Data Warehouse Jobs and how to modify them. I have (greatly) extended out the frequency of how often those jobs run, and that seems to have calmed the server down. Now of course that means that our reports will be much more out of sync with our production data, but at least now I'm not being held up in implementing some other projects of mine that involve adding more databases to that SQL server.

    So I guess my next question is, is this normal behavior for Service Manager? The CPU still utilizes 50-60% CPU whenever those Data Warehouse jobs run, the only difference is now they don't run nearly as frequently. I would like to tighten those schedules back down so we don't have such a difference between production data and reporting data. Are there some indexes I can create on the DB that might help speed up those processes? I only know enough about SQL to be dangerous with it. Has anyone done any kind of performance tweaking on their Service Manager database that might help us?

     

    Friday, August 06, 2010 10:27 PM
  • Hi, As this thread has been quiet for a while, we assume that the issue has been resolved. At this time, we will mark it as "Answered". Either the previous steps should be helpful for many similar scenarios and will be marked as answer, or this post will be marked as answer in order to close the thread. Feel free to re-open the thread if you have additional information about this specific case or to open a new thread for a new case. In addition, we’d love to hear your feedback about the solution. By sharing your experience you can help other community members facing similar problems. Thanks,


    Bob Cornelissen - BICTT (My Blog about SCOM) - MVP 2012 and Microsoft Community Contributor 2011 + 2012 Recipient

    Monday, November 19, 2012 7:01 AM
    Moderator