High CPU Usage on SCCM's SQL Instance

[Aurock]

I recently logged on to my SQL server and found the CPU was constantly running at ~80-90% on all cores. Looking in task manager and perfmon, I see it is the SQL Instanced used by Configmgr. Periodically, the usage will drop back down to the normal ~0-5% utilization, but then it jumps back up again later.

Is there something I might have mis-configured in SCCM that would cause such high CPU usage?

 

(As I wrote this post, I watched the CPU Utilization drop off again. It's sitting at about 0.9% now.)

 

SCCM 2012 SP1

SQL 2008 R2 SP1 CU9

Approx 300 SCCM managed clients

1 Primary site server

2 management points

2 distribution points

 

 

[hutchnet]

I have a very similar setup and have just noticed the same, sqlservr.exe is sitting around 90% constantly, I was considering added a few more cores to the VM...

[Aurock]

In my case, the sql server is a physical server, with dual quad core xeons. I wouldn't expect that the configmgr db would require more processing power for such a relatively small site. Of course, I'm new to configmgr, so I could be wrong.

 

I think the most likely cause is operator error. . . that I've somehow configured configmgr to run some db intensive task or tasks more often than I should. I'm just not sure what it would have been.

[anyweb]

do your component logs reveal any problems ?

[hutchnet]

common.log reveals the below, which repeats every 5 seconds

 

Can not get the current execution state for component Windows_Intune_Service since it is not installed or completed installing. SMS_COMPONENT_MONITOR 21/02/2013 12:08:31 4172 (0x104C)
Waiting until the next polling cycle in 5 seconds from now. SMS_COMPONENT_MONITOR 21/02/2013 12:08:31 4172 (0x104C)

[hutchnet]

The high CPU does seem to be for around 20 minutes after a reboot...

[Aurock]

 

I do have an error state in "Component Status" for a new MP I installed a couple of days ago, which failed to install due to an SSL config issue. I need to look into that, but this high cpu usage has been going on since before I tried adding the additional MP.

 

The attached file shows cpu usage overnight last night. I don't see a clear pattern to the times the CPU usage is spiking up, it looks like it happens maybe every 90 minutes to 2 hours in this graph, but not at an exact interval. And overnight, from approxiamtely 2:30am-6:30am it only spiked up for brief moments, instead of running at full throttle for an hour or so as it seems to have done otherwise.

 

 

 

Blue = Process % User Time

Red = Process % Privilaged Time

 

Lt Purple= Processor % User Time

Dk Purple = Processor % Processor Time

[cgbolton1]

I am working on a lab installation of SCCM 2012 on Server 2012 with SQL 2012 and I am experiencing similar issues with high CPU utilization on my Primary Site server. The servers all live within an ESXi 5.1 environment. Here is the basics:

 

SCCM-CAS - 2CPU, 8GB RAM, thick provisioned hard drives - Server 2012, SQL 2012 (with memory max and min set to 8192MB)

SCCM-P01 - 2CPU, 8GB RAM, thick provisioned hard drives - Server 2012, SQL 2012 (with memory max and min set to 8192MB)

 

Both of the servers were originally configured following the lab recommendations on this web site when configured with SQL 2008 however I was able to get SCCM 2012 SP1 and SQL 2012 with CU 2 installed and functional (except for this problem). I was initially experiencing high CPU utilization on both of the servers so I started experimenting. The first thing I did was to change hard drive provisioning on SCCM-CAS from thin to thick (no discernable impact on performance). Then I added 2GB of RAM on to of the origionally configured 4GB for a total of 6GB and there was a small improvement. Finally I added a second CPU (for a total of 2) and things started to improve. The impact wasn't immediate but after about an hour SCCM-CAS improved to the point where CPU utilization runs between 10-20% when I am not doing anthing and bumps to about 50% when I start working in Configuration Manager Console. With the improvements to SCCM-CAS I thought it would make a difference on SCCM-P01 if I implemented the same changes. It has been a day since I changed SCCM-P01 to match SCCM-CAS and there hasn't been an improvement in performance. As a result of the high utilization SCCM-CAS reports degredation with regard to replication. It appears that the SQL instance on SCCM-P01 is what is causing the high CPU utilization but I am not quite sure what I should do about it. My testing and deployment has ground to a stand-still because of this high CPU utilization issue. I could add yet another CPU to the SCCM-P01 but I am thinking there is some other issue. Any insight into this would be greatly appreciated.

 

Thanks,

Chris Bolton

 

Edit: I went ahead and added 2 CPUs to my SCCM-P01 and now it is running like a champ. I am still not sure why it requires so much horsepower but replication is working properly now and all seems well. Now I have to figure out why I can't get the CAS to download updated. I get "Access denied" on the download attempts but that is a discussion for a different forum.

[Aurock]

I found a couple of threads in the MS forums discussing this issue. It seems that the High CPU usage occurs with SCCM 2012 SP1 when it runs the software update summarization, which is scheduled by default to run every hour. I changed the update summarization schedule to run every 8 hours instead, and how the cpu usage reflects the new schedule, only maxing out every 8 hours or so.

 

So far the responses in the thread seem to indicate that this summarization SHOULDN'T be taking so much CPU time, and that it should not be more resource intensive after sp1 than it was before, but that anyone experiencing the issue should open a support case.

[mauriciolazo]

Replying weeks later after last message, but wanted to share my experiences.

 

I had exactly the same issue with a different setup:

 

- 1 Central Administration Site

- 2 Primary Sites that are also Distribution Points.

- 1 Primary Site dedicated to Internet Based Client Management

 

Total amount of clients to be managed are around 4000.

 

The issue we had is that, I was preparing SCCM to install SP1, so I performed all the task for this update, and managed to install it on CAS server, but didn't run these other tasks on Primary Sites.

 

Then, 2 weeks later, we have a spike in SQL instance, RAM and CPU consumption, between 80% and 90% all the time.

 

Reading through the documentation we found 2 things:

 

- What Aurock stated previously, that Update summarization was set to every minute (in his server was at every hour, in mine it was at every minute!). It shouldn't have taken that much amount of resources, since a summarization and consolidation of updates is sent to the DB, and not the updates per se, nevertheless, it was taking a bit of resources.

 

- In Microsoft Documentation, it states that for a CAS server, it needs to have a MINIMUM of 8GB of RAM reserved just for that instance and its processes, and since I'm sharing that instance with another platform, I had just 6 GB of RAM reserved for CAS and also, the instance did not had a limit on memory usage (one of those -prerequisite check- warnings when you are installing SCCM).

 

SQL server requirements for SCCM 2012: http://technet.microsoft.com/en-us/library/gg682077.aspx#SQLDBconfig

 

 

All this combined, gave the SQL server a huge consumption on RAM and CPU resources.

 

Hope this helps somehow.