Microsoft Operations Manager 2005 reports run slowly or never finish

When you run reports in Microsoft Operations Manager (MOM) 2005, you may experience one or more of the following problems:

• Reports run slowly.
• Reports run for hours before finishing.
• Reports run for hours, and then they never finish.
• Reports run for a long time, and then they return time-out errors.
• Reports run for a long time, and then they return query execution errors.

This problem occurs because statistics and index information may become outdated. This condition occurs because modifications (INSERT, UPDATE, and DELETE statements) are made to the data in the SystemCenterReporting database nightly. This condition may increase the compilation cost factor in query execution plans. Additionally, the condition may cause a gradual, continuous decline in MOM reporting performance over time.

To resolve this problem, use SQL Query Analyzer to run the Microsoft SQL Server query for the report in question directly against the SystemCenterReporting database. To do this, follow these steps:

1. Follow the steps in the following Microsoft Knowledge Base article to extract the SQL statement from the report:
   947678  (http://support.microsoft.com/kb/947678/ ) How to extract an SQL statement from a Microsoft Operations Manager 2005 report
2. Adjust parameters as necessary in the Transact-SQL statement.
3. Run the Transact-SQL statement in SQL Query Analyzer.

If the same performance problems occur when you run the query directly in SQL Query Analyzer, run the UPDATE STATISTICS statement against the SystemCenterReporting database. SQL Server keeps statistics about the distribution of the key values in each index. SQL Server uses these statistics to determine which indexes to use in query processing. The UPDATE STATISTICS statement updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or in the indexed view.

To run the UPDATE STATISTICS statement, follow these steps:

1. Open SQL Query Analyzer, and then connect to the computer that is running SQL Server.
2. Run the following query:

   USE SystemCenterReporting
   EXEC sp_updatestats 
   

   Note Depending how outdated the information is, the query may take a while to finish.

You can also run the DBCC SHOWCONTIG statement to check the fragmentation in the tables. The DBCC SHOWCONTIG statement determines whether a table and its indexes are heavily fragmented. Table fragmentation occurs when data modifications (INSERT, UPDATE, and DELETE statements) are made against the table. Because these modifications are not usually distributed equally among the rows of the table, the fullness of each page can vary over time. For queries that scan part or all the table, such table fragmentation can cause additional page reads. These page reads hinder parallel scanning of data.

To run the DBCC SHOWCONTIG statement, follow these steps:

1. Open SQL Query Analyzer, and then connect to the server.
2. Run the following SQL query:

   USE SystemCenterReporting
   DBCC SHOWCONTIG
   

   Note This step runs the DBCC SHOWCONTIG statement against all tables in the database. To run this against one specific table, add ('<table>') at the end of the DBCC SHOWCONTIG statement.

Tables that have a low scan density are highly fragmented. If there are lots of these tables, run a reindexing operation for the database. For more information, see the "How to create a weekly SQL Server job to check the reindexing operation of the SystemCenterReporting database" section.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

To make sure that reports run correctly, create performance optimization jobs for the SystemCenterReporting database. At a minimum, create three jobs and two maintenance plans.

How to create a daily SQL Server job to run the UPDATE STATISTICS statement

1. Open SQL Server Enterprise Manager.
2. Expand Microsoft SQL Servers, expand SQL Server Group, expand (SQL Server Name), expand Management, and then expand SQL Server Agent.
3. Right-click Jobs, and then select New Job.
4. Provide a name for the job, such as SystemCenterReporting - Update Statistics.
5. On the Steps tab, click New, and then enter the following on the General tab:
   Step name: Update Usage on all tables
   Database: SystemCenterReporting
   Command: DBCC UpdateUsage('SystemCenterReporting')
6. On the Advanced tab, enter the following options, and then click OK:
   On success action: Goto the next step
   On failure action: Goto the next step
7. On the Steps tab, click New, and then enter the following on the General tab:
   Step name: Update statistics on all tables
   Database: SystemCenterReporting
   Command: EXEC sp_updatestats
8. On the Advanced tab, enter the following options, and then click OK:
   On success action: Quit the job reporting success
   On failure action: Quit the job reporting failure
9. On the Schedules tab, click New Schedule, type a name in the Name field, and then set the job to occur daily, during a time that does not conflict with other SQL Server jobs.

How to create a weekly SQL Server job to check the reindexing operation of the SystemCenterReporting database

To create this job, you must first create a database maintenance plan.

Create the maintenance plan

1. Open SQL Server Enterprise Manager.
2. Expand Microsoft SQL Servers, expand SQL Server Group, expand (SQL Server Name), and then expand Management.
3. Right-click Database Maintenance Plans, and then select New Maintenance Plan.
4. On the Welcome to the Database Maintenance Plan Wizard page, click Next.
5. On the Select Databases page, select the SystemCenterReporting database, and then click Next.
6. On the Update Data Optimization Information page, select the Reorganize data and index pages check box, select the Reorganize pages together with the original amount of free space check box, and then click Next.
7. On the Database Integrity Check page, click Next.
8. On the Specify the Database Backup Plan page, clear the Back up the database as part of the maintenance plan check box, and then click Next.
9. On the Specify the Transaction Log Backup Plan page, click Next, and then click Next three more times.
10. On the Completing the Database Maintenance Plan Wizard page, type a plan name in the Plan name field, such as SystemCenterReportingReindex, and then click Finish.

Note This procedure also creates a job that is called "Optimizations Job for DB Maintenance Plan 'SystemCenterReportingReindex'" in SQL Server Agent. Under Management, delete this job from Jobs.

Create the SQL Server job

1. Open SQL Server Enterprise Manager.
2. Expand Microsoft SQL Servers, expand SQL Server Group, expand (SQL Server Name), expand Management, and then expand SQL Server Agent.
3. Right-click Jobs, and then select New Job.
4. Provide a name for the job, such as SystemCenterReporting - Reindex.
5. On the Steps tab, click New, and then enter the following on the General tab:
   
   Step name: Reindex SystemCenterReporting tables
   Database: SystemCenterReporting
   Command: EXECUTE master.dbo.xp_sqlmaint '-PlanName Maintenance_Plan_Name -RebldIdx 100 -WriteHistory’
   
   Note The Maintenance_Plan_Name placeholder represents that name of the maintenance plan, such as SystemCenterReportingReindex.
6. Enter the following on the Advanced tab, and then click OK:
   
   On success action: Quit the job reporting success
   On failure action: Quit the job reporting failure
7. On the Schedules tab, click New Schedule, and then set the job to occur weekly, during a time that does not conflict with other SQL Server jobs.

How to create a weekly SQL Server job to check the integrity of the database

To create this job, you must first create a database maintenance plan.

Create the maintenance plan

1. Open SQL Server Enterprise Manager.
2. Expand Microsoft SQL Servers, expand SQL Server Group, expand (SQL Server Name), and then expand Management.
3. Right-click Database Maintenance Plans, and then select New Maintenance Plan.
4. On the Welcome to the Database Maintenance Plan Wizard page, click Next.
5. On the Select Databases page, select the SystemCenterReporting database, and then click Next.
6. On the Update Data Optimization Information page, click Next.
7. On the Database Integrity Check page, select Check database integrity, and then make sure that the Include indexes check box is selected.
8. Click Change to set the schedule for a time that does not conflict with other SQL Server jobs, a weekend day or time is recommended.
9. After you set the schedule, click OK, and then click Next.
10. On the Specify the Database Backup Plan page, clear the Back up the database as part of the maintenance plan check box, and then click Next.
11. Click Next on the Specify the Transaction Log Backup Plan page, click Next on the Reports to Generate page, and then click Next on the Maintenance Plan History page.
12. On the Completing the Database Maintenance Plan Wizard page, type a plan name in the Plan name field, such as SystemCenterReportingCheckIntegrity, and then click Finish.

Note This job also creates a job that is called "Integrity Checks Job for DB Maintenance Plan 'SystemCenterReportingCheckIntegrity'" in SQL Server Agent. Under Management, delete this job from Jobs.

Create the SQL Server job

1. Open SQL Server Enterprise Manager.
2. Expand Microsoft SQL Servers, expand SQL Server Group, expand (SQL Server Name), expand Management, and then expand SQL Server Agent.
3. Right-click Jobs, and then click New Job.
4. Type a name for the job in the Name field, such as SystemCenterReporting - Check Integrity.
5. Click the Steps tab, click New, and then enter the following information on the General tab:
   
   Step name: Check SystemCenterReporting Integrity
   Database: SystemCenterReporting
   Command: EXECUTE master.dbo.xp_sqlmaint '-PlanName Maintenance_Plan_Name -CkDB -CkAl -WriteHistory'
   
   Note The Maintenance_Plan_Name placeholder represents that name of the maintenance plan, such as SystemCenterReportingCheckIntegrity.
6. Enter the following information on the Advanced tab, and then click OK:
   On success action: Quit the job reporting success
   On failure action: Quit the job reporting failure
7. On the Schedules tab, click New Schedule, type a name in the Name field, and then set the job to occur weekly, during a time that does not conflict with other SQL Server jobs.