Fix: SQL server hits 100% CPU utilization when there are configuration update requests in SCOM 2007

Article
02/03/2011

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.

Cause

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.

Resolution

This SQL issue was first fixed in SQL Server 2008 R2 Cumulative Update 5 (CU5): https://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.

More Information

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

Fix: SQL server hits 100% CPU utilization when there are configuration update requests in SCOM 2007

Cause

Resolution

More Information

Additional resources