Configuration Manager – SQL Performance Tuning

Up until late last year I had always used the Rebuild Indexes task built into the SCCM Task Maintenance options to “optimise” the back-end SQL database. The would occasionally fire up SQL Management Studio and also run manual scripts to shrink logs and other tasks, however the SCCM console in particular never appeared to be as quick as I believed it should be.

Then I came across the holy grail of SQL maintenance scripts in a tweet and blog post by SQL MVP Steve Thompson and after the initial lengthy run of the script I was blown away by the performance improvements.

So if you are still running the inbuilt task in SCCM I urge you to keep reading..

The Script – SQL Maintenance for the non SQL Admin

The underlying script contents were developed by SQL MVP Ola Hallengren and automate best practices for re-indexing, de-fragmenting and updating SQL database statistics to ensure that your databases run quickly and efficiently.

To set up your SQL server to run these processes, download the SQL maintenance script from Ola’s website – https://ola.hallengren.com/scripts/MaintenanceSolution.sql

Now run the SQL script in SQL Management Studio and it will create all of the required objects and jobs required.

SQL Jobs;

Steve Thompson has specific instructions for creating a sorting database for SCCM jobs on his own blog – https://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/ but you can also use the default tempdb if you wish.

Running the SQL Maintenance Task

To run the task you must first of all select the type of optimisation you want to run. Ola has these listed out on his site (https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html), the one I use on my own environment is the following;

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’,
@SortInTempdb = ‘Y’,
@MaxDOP = 0

Script in action;

Obviously the initial run can be very CPU intensive, especially if you specify the option to use all available CPU’s so you might want to ensure that your server is running this during a quiet time to avoid floods of user complaints. You’ll be glad to hear though that after the initial optimisation processes have been completed, subsequent job runs should be significantly quicker.

So its time to disable the inbuilt task and return performance to your SCCM environment..