35- Optimizing SCCM Site Database

In time, SCCM Site database will have Fragmented Indexes and this will slow down your database's performance. To have a good performance on Configuration Manager, we have to maintain our indexes. As I read so far, most SCCM MVPs believe that Rebuild Indexes Site Maintenance Task in ConfigMgr 2012 still has some issues and should be kept "Disabled" and They recommend us to use Ola's Maintenance Solution. I followed this great article on the following link how to use Ola's script. All credits goes to firstly Olga, Steve Thompson and article author Stefan Schörling.

http://www.cmtrace.com/2014/05/14/guide-on-how-to-configure-sql-server-index-optimization-with-ola-hallengrens-maintenance-solution/

Download Olas IndexCheck from http://ola.hallengren.com/scripts/IndexCheck.sql

Download Olas Maintenance Solution from Http://ola.hallengren.com

Copy these files to your SQL Server

1. Check FragmentationPercentage: 

Start SQL Management Studio

File>Open IndexCheck script

Add USE CM_<SiteCode> to the beggining of the script (here we select the database to work on)

Add Order by AvgFragmentationInPercent desc to the end of the script (orders the result according to fragmentation percentage)  

Now Execute the script and see how bad the fragmentation percentages are.

2.Create Database for Maintenance:

In SQL Management Studio create a new Database. We will use this database for logging activities and saving our tasks.

Give a  name and set the initial size of your Database

Click Options and Set Recovery Model as Simple

Make sure SQL Server Agent has started.

Create Jobs for Maintenance:

Now open MaintenanceSolution.sql in SQL Server Management Studio (Open > File > MaintenanceSolution.sql)

Change the database for USE command, change it to the database we just created and Specify the backup directory for logs  

Execute the script. Script will create the necessary jobs.

By default these jobs are set to run on all databases. We will set the jobs to run on only CM_<SiteCode> and SUSDB. To change the databases that the IndexOptimize task is run on right click on the job and select properties. Once in the properties go to the Steps section and select edit on the Step.

change the USER_DATABASES value to the preferred databases you want to. So if I’d like the job to run against only CM_ISU and SUSDB I would change the value to CM_ISU, SUSDB.  

Click on Schedule Page, and click New

To maintain the solution itself there are some tasks to do cleanup so we don’t fill the CommandLog table with years of data and the disks with log files for years. So to cleanup all these activities, configure these additional jobs so that you cleanup after the solution. By default it keeps the data for 30 days. As a recommendation set them (the jobs marked below with red ) to run on a weekly basis as well.

You can check if the task failed or succeeded