The SQL Server process may consume lots of CPU resources on the server that hosts the Operations Manager 2007 database after you make Operations Manager 2007 configuration changes

The Microsoft SQL Server process may consume lots of CPU resources on the server that hosts the Microsoft System Center Operations Manager 2007 database. This problem may occur after you make Operations Manager 2007 configuration changes by using the Operations console or by using the Operations Manager 2007 Software Development Kit (SDK) programmatically.

For example, this problem may occur after you make the following Operations Manager 2007 configuration changes:

• You install new management packs.
• You store the overrides for the different management packs within the default management pack.
• You configure automated deployment of new agents.

This problem occurs because some SQL queries that the Data Abstraction Layer (DAL) component of Operations Manager 2007 runs force SQL Server to build a new plan every time that the Configuration service runs an SQL statement.

Update Information

To resolve this problem, obtain the latest version of Microsoft System Center Operations Manager 2007. For more information, refer to this Microsoft Web site: http://www.microsoft.com/systemcenter/operationsmanager/en/us/how-to-buy.aspx (http://www.microsoft.com/systemcenter/operationsmanager/en/us/how-to-buy.aspx)

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site: Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

You must have Microsoft System Center Operations Manager 2007 Service Pack 1 (SP1) installed to apply this hotfix. This hotfix must be applied on each computer that meets the following criteria:

• The computer hosts an Operations Manager 2007 root management server.
• The computer hosts an Operations Manager 2007 management server.

Restart requirement

You do not have to restart the computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace any other hotfixes.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Operations Manager 2007, x64-based version

Operations Manager 2007, x86-based version

To extract the management pack files that are contained in this hotfix, follow these steps:

1. Copy the SystemCenterOperationsManager2007-SP1-KB956240-X86-X64-ENU.msi file to a local folder or to an available network shared folder.
2. Run the SystemCenterOperationsManager2007-SP1-KB956240-X86-X64-ENU.msi file locally on each computer that meets the predefined criteria.
   
   Note You can run the SystemCenterOperationsManager2007-SP1-KB956240-X86-X64-ENU.msi file in Windows Explorer or at a command prompt.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft System Center Operations Manager 2007 R2.

This hotfix optimizes the SQL queries to reduce the CPU usage of the SQL Server process when you make Operations Manager 2007 configuration changes.

After you apply this hotfix by using the Run Software Update option in the System Center Operations Manager 2007 Software Update dialog box, you must run a Transact-SQL statement against the Operations Manager operational database in SQL Server. To do this in SQL Server Management Studio, follow these steps:

1. In SQL Server Management Studio, connect to the server that hosts the Operations Manager operational database.
2. On the standard toolbar, click New Query.
3. On the SQL Editor toolbar, click the Operations Manager operational database. For example, click OperationsManager.
4. In the query window, type the following command:
   DBCC FREEPROCCACHE
5. On the SQL Editor toolbar, click Execute. You receive the following message:
   DBCC execution completed. If DBCC printed error messages, contact your system administrator.
6. Exit SQL Server Management Studio.

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

For a list of issues that are fixed in Microsoft System Center Operations Manager 2007 R2, refer to the following article in the Microsoft Knowledge base: