MOM2005: Slow performance for MOM 2005 reports

  • Article

Here's a tip on an issue we seem to be seeing every so often.  We've had a few calls lately where when running reports for Microsoft Operations Manager 2005, you may experience some of the following symptoms:

- Reports run slow.
- Reports run for hours before completing.
- Reports run for hours and never complete.
- Reports run for a long time and return timeout errors.
- Reports run for a long time and return query execution errors.

Cause:
As data is modified in the SystemCenterReporting database nightly (INSERTS, UPDATES, DELETES), this causes statistics/index information to become outdated. In turn, this increases the compilation cost factor in query execution plans, and subsequently causes a gradual, continuous decline in MOM Reporting performance over time.

Resolution:
Test running the SQL query for the report in question directly against the database using SQL Server Query Analyzer. To do this:

1. Extract the SQL statement from the report.  Below are the instructions for doing this via Visual Studio .NET 2003:

  • Select the report you would like to work with and click the Properties tab.
  • Click Edit and select a location to save the .rdl file.
  • Open Visual Studio .NET 2003, and from the File menu, point to New and select Project.
  • Under 'Business Intelligence Products', select Report Project, and click OK.
  • To create a connection to the database, right-click Shared Data Sources and select Add New Data Source.
  • On the Data Link Properties dialog box, enter the instance name where the SystemCenterReporting database resides, select to use Windows NT integrated security, and select the SystemCenterReporting database.
  • Click Test Connection to validate your connection to the database, and then click OK.
  • Right-click the new connection and rename it to SCDW.rds.
  • To add the report, right-click Reports, point to Add and select Add Existing Item.
  • Select the .rdl file you saved in the 2nd step.
  • Double-click the report to open it in the VS.NET 2003 Design Environment.
  • Select Preview, enter the appropriate criteria, and verify the report is returning the data you expect.
  • Select the Data tab, and then select the appropriate dataset from the Dataset drop-down list, this will display the Transact-SQL for the selected dataset of the report.

NOTE: When pasting this into Query Analyzer to extract the data from the database, you will most likely need to edit the statement accordingly (i.e. supply literal values for all declared parameters).

2. Adjust parameters as necessary in the T-SQL statement.
3. Run the T-SQL statement in Query Analyzer.

If the same performance problems occur running the query directly in Query Analyzer, run Update Statistics on the SystemCenterReporting database. SQL Server keeps statistics about the distribution of the key values in each index and uses
these statistics to determine which index(es) to use in query processing. Update Statistics updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

To run Update Statistics, complete the following:

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

USE SystemCenterReporting
EXEC sp_updatestats

Depending how outdated this information is, this may take a while to complete.

You can also run DBCC SHOWCONTIG to check the fragmentation in the tables. DBCC SHOWCONTIG determines whether a table and its indexes are heavily fragmented. Table fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) made against the table(s). 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 of a table, such table fragmentation can cause additional page reads, which hinders parallel scanning of data.

To run DBCC SHOWCONTIG:

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

USE SystemCenterReporting
DBCC SHOWCONTIG

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

Tables with a low Scan Density are highly fragmented. If there are a considerable number of these, a reindex operation should be run for the database. Details on creating a weekly reindexing job/maintenance plan are listed in the More Information - Reindexing section below.

More Information:

Performance optimization jobs should be added for the SystemCenterReporting database. At a minimum, three jobs (and two maintenance plans) should be created.

UPDATE STATISTICS: Daily

Create a SQL Server job to run Update Statistics daily. To create this job:

1. Open SQL Server Enterprise Manager.
2. Expand Management -> SQL Server Agent -> Jobs
3. Right-click Jobs and select New Job.
4. Provide a name for the job, such as 'SystemCenterReporting - Update Statistics'.
5. On the Steps tab, click New and enter the following on the General tab:

Step name: Update Usage on all tables
Database: SystemCenterReporting
Command: DBCC UpdateUsage('SystemCenterReporting')

6. Enter the following on the Advanced tab, 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 enter the following on the General tab:

Step name: Update statistics on all tables
Database: SystemCenterReporting
Command: EXEC sp_updatestats

8. 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

9. On the Schedules tab, click New Schedule and set the job to occur daily, during a time that does not conflict with other SQL Server jobs.

------------------------------------

REINDEXING: Weekly

Create a SQL Server job to check the perform reindexing of the SystemCenterReporting database weekly. To create this job, you must first have a Database Maintenance Plan. To create the Maintenance Plan, complete the following:

1. Open SQL Server Enterprise Manager.
2. Expand Management and select Database Maintenance Plans.
3. Right-click Database Maintenance Plans and select New Maintenance Plan.
4. On the 'Welcome to the Database Maintenance Plan Wizard' screen, click Next.
5. On the 'Select Databases' screen, select the SystemCenterReporting database,
and then click Next.
6. On the 'Update Data Optimization Information' screen, select 'Reorganize data
and index pages', select 'Reorganize pages with the original amount of free space',
and then click Next.
7. On the 'Database Integrity Check' page, click Next.
8. On the 'Specify the Database Backup Plan', uncheck 'Backup the database as part
of the maintenance plan', and then click Next.
9. Click Next on the 'Specify the Transaction Log Backup Plan' screen, click Next
on the 'Reports to Generate' screen, click Next on the 'Maintenance Plan History'
screen and then click Next.
10. On the 'Completing the Database Maintenance Plan Wizard' screen, provide a
Plan name such as 'SystemCenterReportingReindex', and then click Finish.

This will also create a job called "Optimizations Job for DB Maintenance Plan 'SystemCenterReportingReindex'". Delete this job from Jobs under Management -> SQL Server Agent.

To create the SQL Server job, complete the following:

1. Open SQL Server Enterprise Manager.
2. Expand Management -> SQL Server Agent -> Jobs
3. Right-click Jobs and select New Job.
4. Provide a name for the job, such as 'SystemCenterReporting - Reindex'.
5. On the Steps tab, click New and enter the following on the General tab:

Step name: Reindex SystemCenterReporting tables
Database: SystemCenterReporting
Command: EXECUTE master.dbo.xp_sqlmaint '-PlanName SystemCenterReportingReindex
-RebldIdx 100 -WriteHistory’

*NOTE: If you named the Maintenance Plan differently than SystemCenterReportingReindex, change the name accordingly after the -PlanName switch above.

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 set the job to occur weekly, during a time that does not conflict with other SQL Server jobs.

------------------------------------

DATABASE INTEGRITY CHECK: Weekly

Create a SQL Server job to check the integrity of the database weekly. To create this job, you must first have a Database Maintenance Plan. To create the Maintenance Plan, complete the following:

1. Open SQL Server Enterprise Manager.
2. Expand Management and select Database Maintenance Plans.
3. Right-click Database Maintenance Plans and select New Maintenance Plan.
4. On the 'Welcome to the Database Maintenance Plan Wizard' screen, click Next.
5. On the 'Select Databases' screen, select the SystemCenterReporting database, and then click Next.
6. On the 'Update Data Optimization Information' screen, click Next.
7. On the 'Database Integrity Check' page, select 'Check database integrity' and make sure 'Include indexes' is selected.
8. Click Change to set the schedule for a time that does not conflict with other SQL Server jobs, a weekend day/time is recommended.
9. After setting the schedule, click Next.
10. On the 'Specify the Database Backup Plan', uncheck 'Backup the database as part of the maintenance plan', and then click Next.
11. Click Next on the 'Specify the Transaction Log Backup Plan' screen, click Next on the 'Reports to Generate' screen, click Next on the 'Maintenance Plan History' screen and then click Next.
12. On the 'Completing the Database Maintenance Plan Wizard' screen, provide a Plan name such as 'SystemCenterReportingCheckIntegrity', and then click Finish.

This will also create a job called "Integrity Checks Job for DB Maintenance Plan 'SystemCenterReportingCheckIntegrity'". Delete this job from Jobs under Management -> SQL Server Agent.

To create the SQL Server job, complete the following:

1. Open SQL Server Enterprise Manager.
2. Expand Management -> SQL Server Agent -> Jobs
3. Right-click Jobs and select New Job.
4. Provide a name for the job, such as 'SystemCenterReporting - Check Integrity'.
5. On the Steps tab, click New and enter the following on the General tab:

Step name: Check SystemCenterReporting Integrity
Database: SystemCenterReporting
Command: EXECUTE master.dbo.xp_sqlmaint '-PlanName
SystemCenterReportingCheckIntegrity -CkDB -CkAl -WriteHistory'

*NOTE: If you named the Maintenance Plan differently than SystemCenterReportingCheckIntegrity, change the name accordingly after the -PlanName switch above.

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 set the job to occur weekly, during a time that does not conflict with other SQL Server jobs.

J.C. Hornbeck