When using SQL Server 2008 R2 as the database server for the System Center Operations Manager 2007 operationsmanager database, the SQL server service may use 100% CPU when SCOM 2007 triggers configuration update requests. For example, when we make a change to a rule/monitor, or modify some override values, the SQL server service will use 100% CPU utilization. This high CPU usage will persist for several minutes. The affecting time is based on how many configuration updates will be raised after the console changes. During the period that the SQL server service is hitting 100% CPU, the SCOM administration console will also be unresponsive.
While the Operations Manager database is preparing for requested configuration updates, there will be a lot of accesses to the tempdb database. If you used a Run As account that is not in sysadmin group, a known issue with SQL 2008 R2 will be encountered. From the SQL server side, this issue occurs because of high spinlock contentions on security caches when a non-sysadmin user creates a heavy OLTP workload on the tempdb database.
This SQL issue was first fixed in SQL Server 2008 R2 Cumulative Update 5 (CU5): http://support.microsoft.com/default.aspx?scid=kb;en-US;2438347
The SQL hotfix avoids regenerating a security token when a temporary object is either explicitly or implicitly dropped by a non-sysadmin user, therefore the high spinlock contentions do not occur again.
As a best practice, we need to apply SQL 2008 R2 CU5 before we upgrade the SCOM operations manager database to SQL 2008 R2.
Simon Xin | Support Escalation Engineer
The App-V Team blog: http://blogs.technet.com/appv/ The WSUS Support Team blog: http://blogs.technet.com/sus/ The SCMDM Support Team blog: http://blogs.technet.com/mdm/ The ConfigMgr Support Team blog: http://blogs.technet.com/configurationmgr/ The SCOM 2007 Support Team blog: http://blogs.technet.com/operationsmgr/ The SCVMM Team blog: http://blogs.technet.com/scvmm/ The MED-V Team blog: http://blogs.technet.com/medv/ The DPM Team blog: http://blogs.technet.com/dpm/ The OOB Support Team blog: http://blogs.technet.com/oob/ The Opalis Team blog: http://blogs.technet.com/opalis The Service Manager Team blog: http: http://blogs.technet.com/b/servicemanager The AVIcode Team blog: http: http://blogs.technet.com/b/avicode
Is there a way that we can demonstrate this issue so that once the CU has been applied, the same test will not show high cpu usage?
Hi, i also found that setting SQL Max Worker Threads to 576 (for my case 8cpu 64 bits) solved the problem.