Optimizing ConfigMgr databases

Installing and configuring the CMMonitor database

Background

The SCCM 2007 and Configuration Manager built-in site maintenance index rebuild task does not always run. The consequence is a ConfigMgr database with indexes that are fragmented and statistics that are out of date. The impact is a database that continues to run slower and slower.

The CMMonitor database is designed to; mitigate the out of date indexes and statistics, not make any changes to the existing ConfigMgr database and be configurable to address optimizing other databases as needed.

Installation scripts can be found here:

CMMonitor Demo (DB optimization)

On the Configuration Manager Database server, open SSMS (SQL Server Management Studio) and open the 3 scripts referenced above.

Step 1

Create the CMMonitor database – from SSMS reference script ‘5 -CMMonitorDB.sql’. You will need to change the location of the database data files to match your server directory structure. Paths highlighted below:

CREATE DATABASE [CMMonitor] ON PRIMARY

( NAME = N’CMMonitor’, FILENAME = N’I:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CMMonitor.mdf’ , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )

LOG ON

( NAME = N’CMMonitor_log’, FILENAME = N’T:\Log\CMMonitor_log.ldf’ , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 5120KB )

GO

Execute this script. Once run, you should receive the following message:

Command(s) completed successfully.

Step 2

Populate the CMMonitor database – use the following script:

Index and Statistics Maintenance: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

NOTE: make sure you change the statement on the first page of this script:

USE MASTER

to

USE CMMONITOR

GO

Execute this script. Once run, you should receive the following message:

Command(s) completed successfully.

Step 3

Create the SQL Server Agent task – from SSMS reference script ‘7-Create Agent task.sql’. This step will create the task to automate the index optimize and statistics update process.

Execute this script. Once run, you should receive the following message:

Command(s) completed successfully.

Using SSMS, browse to the SQL Server Agent node, expand that node and the Jobs node below that. You should see a minimum of these jobs defined.

Select job named ‘IndexOptimize – AME’, right click then properties. Rename job to suit your server, site code, etc.

Next, click Steps, then Edit:

Then, edit the names of the databases you want to optimize, adding a comma between each database. Normally you will want to include the SUSDB if that database is present with the site database.

Click OK, then OK again to save. Note the schedule is set to run 1x/week on Monday, started at 6:00pm. Alter this schedule as appropriate.

Final note

Consider adding a schedule to the SQL Server Agent task ‘CommandLog Cleanup’. This will clean out entries from the CMMonitor database, CommandLog table older than 30 days.