Optimizing SCCM Databases Revisited

Since Optimizing ConfigMgr databases was published in May 2013, I have received some tremendous feedback, all positive.

Note [9-3-2021]: This post has good background on the manual process, to automate in about 5 lines of PowerShell code read this...

The basics of that original post have not changed, Ola Hallengrens Index Optimization scripts, hands down, is the best solution for 99% of ALL SCCM implementations. This post is about making it easier to implement for the non DBA, and ensuring that the proper options are implemented for the Index Optimization job.


Download Ola Hallengrens MaintenanceSolution.sql script, here


The following steps are required to implement this solution. Details on each step, follow.

1) Create the CMMonitor database

2) Install MaintenanceSolution.sql

3) Configure and Schedule the Index Optimization SQL Agent job task

4) Verify the Index Optimization SQL Agent job task

Create the CMMonitor database

Open SQL Server Management Studio (SSMS), connect to the SQL instance where the SCCM database is located.

Copy and paste the following into SSMS, change the file paths as appropriate and Execute.



IF EXISTS (SELECT name FROM sys.databases 
WHERE name = N'CMMonitor')




( NAME = N'CMMonitor', 
FILENAME = N'C:\MSSQL\DATA\CMMonitor.mdf' , 


( NAME = N'CMMonitor_log', 
FILENAME = N'C:\MSSQL\Log\CMMonitor_log.ldf' , 
SIZE = 1024KB , MAXSIZE = 2048GB , 





Install MaintenanceSolution.sql

Using SSMS (or your favorite editor) edit the MaintenanceSolution.sql (downloaded as part of pre-requisites) to point to your newly created CMMonitor database.

Note: SQL Server Agent should be running. If it is not, please start this service and set start mode to Auto.

Near the beginning of the MaintenanceSolution.sql, locate the USE [Master] entry and modify as follows:

-- USE [master] -- Specify the database in which the objects will be created.

USE [CMMonitor]

Then Execute the SQL query.


Configure the Index Optimization SQL Agent job task

To perform this manually, Using SSMS, expand the SQL Server Agent node, then Jobs.

Note: Otherwise skip to end of this section to copy / run the SQL Script…

Right click Jobs, then New Job…

On the General page:

  • Enter the Name: IndexOptimize – Custom
  • Change the owner to sa


Click Steps, then New…

On the New Job Step – General page

Step Name: Optimize

  • Type: Transact-SQL script (T-SQL) – should be default
  • Database: CMMonitor

Command (copy / paste the following)

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,



@FragmentationLevel1 = 10,

@FragmentationLevel2 = 40,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y',


--@SortInTempdb = 'Y',

@LogToTable = 'Y'

Then click OK


New Job Step

On the New Job – Schedules page, click New…

On the New Job Schedule page:

Name: Run Optimization

Frequency: Weekly, select Wednesday (and Sunday)

Click OK


Click OK to Save New Job.

SQL Script to create IndexOptimization – Custom job

On the instance where you have installed CMMonitor and the MaintenanceSolution, Copy / paste the following SQL syntax into SSMS and execute.

Note: do not run this step if you have manually configured this process!

USE [msdb]


/****** Object: Job [IndexOptimize - Custom] Script Date: 11/28/2016 2:35:32 PM ******/



SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 11/28/2016 2:35:32 PM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)


EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback



EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'IndexOptimize - Custom',







@description=N'No description available.',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Optimize] Script Date: 11/28/2016 2:35:32 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Optimize',









@os_run_priority=0, @subsystem=N'TSQL',

@command=N'EXECUTE dbo.IndexOptimize

@Databases = ''USER_DATABASES'',

@FragmentationLow = NULL,



@FragmentationLevel1 = 10,

@FragmentationLevel2 = 40,

@UpdateStatistics = ''ALL'',

@OnlyModifiedStatistics = ''Y'',


--@SortInTempdb = ''Y'',

@LogToTable = ''Y''




IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Optimization',













IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


GOTO EndSave





Verify the Index Optimization SQL Agent job task

You can wait until after the scheduled date/time to observe results. (Default schedule is 2x/week. On a smaller site, you may want to test the IndexOptimization – Custom job immediately. To do so, locate the new SQL Agent job, right click and select Start Job at Step…


When complete, it should indicate that it was a success.


To review the results that were logged to the CommandLog table in the CMMonitor database, use the following SQL query:

SELECT TOP (1000) [ID]
















FROM [CMMonitor].[dbo].[CommandLog]


Summary (other things to think about)

Disable the built in SCCM maintenance to re-index tables, you won’t need this anymore.

You may have noticed that a number of new SQL Server Agent jobs were created. These may be useful to you at some point, but are beyond the scope of this post.

I do recommend enabling the CommandLog Cleanup Agent job, as this will flush commandlog entries older than 30 days.

You may have noticed two options that were commented out in the IndexOptimize – Custom job:


–@SortInTempdb = ‘Y’,

Depending on your scenario, you may find that it may speed up the Optimize process. Be aware a very large database may use significant TempDB with SortInTempdb – monitor and adjust as needed.

All USER Databases will be optimized on the SQL Server where this job is run. That includes SUSDB, ReportServer and CM databases.

Check Ola’s site as updates are released on a fairly regular basis. Updating the Maintenance solution is very straight forward, from Ola’s FAQ.

I’m using an old version of the SQL Server Maintenance Solution. How do I upgrade to the most recent version?

To upgrade to the most recent version of the SQL Server Maintenance Solution, follow these steps.

1. Download MaintenanceSolution.sql.

2. Change this line in the script so as not to create jobs:
SET @CreateJobs = ‘N’

3. Execute MaintenanceSolution.sql. The script drops the old versions of the stored procedures and creates new versions.

4. Start the jobs and verify that they are completing successfully.

From <https://ola.hallengren.com/frequently-asked-questions.html>

This entry was posted in ConfigMgr, SQL Server. Bookmark the permalink.

35 Responses to Optimizing SCCM Databases Revisited

  1. Pingback: Optimizing SCCM Databases Revisited | Skatterbrainz Blog

  2. Jonathan says:

    Thanks a lot! couldn’t be more appropriate timing. I was trying to find why my SCCM was soooo slow. That did the trick.

    Still don’t understand why I had to do this for the first time.
    Site is pretty small (500clients) , and freshly installed to CB 1606. we migrated data from an old SCCM 2012 R2 SP1.

    can migration cause that kind of issue with indexes?

  3. ConfigMgrMVP says:

    Migration will likely cause out of date indexes and statistics…

  4. ShawnBeau says:

    What is the rationale for changing the fragmentation levels from the default of 5 and 30 to 10 and 40 for SCCM? Also, Microsoft recommends doing Update Statistics with FULLSCAN, should then @StatisticsSample=100 be used?

    • ConfigMgrMVP says:

      The defaults were changed because on a very large site, more indexing was done than necessary and it took a LONG time. Modified the levels of fragmentation to be a little less aggressive. FULLSCAN will also add to the amount of time that this process will take. Feel free to modify and report back! Update – did some checking and FULLSCAN with a 100% sample is NOT a best practice. Most recommend letting the update stats figure out the sample size.

  5. Pingback: CMWT 2016.12.29 | Skatterbrainz Blog

  6. Pingback: SCCM / Windows Server

  7. Bryan says:

    Steve, at MMS you and Ben recommended updating statistics daily and indexes weekly. Above it would seem we’re running this twice a week. Is that just a compromise because Ola’s scripts handle both or can we schedule those separately for even better maintenance?

    • ConfigMgrMVP says:

      Hi Bryan, Thanks for attending our session. I’m not sure I recommended updating statistics daily, that might have been Benjamin. First of all, there is no hard and fast rule that you have to do this a certain way. If you have a VERY large database it might make sense to split those two operations. I actually prefer using the optimize index task, AND update only modified statistics as part of the same process. The explanation would require a much longer answer. You can run this several times a week, or daily even. The more frequently you run it, the less it has to do.

  8. Jean-Sebastien Frenette says:


    I ran these script before running the script to see current database health

    Then I installed and ran IndexOptimize – Custom. It completed in less then 5 minutes (SCCM CB 1702, migrated from 2012 R2 2 years ago to 1511, never indexed since except SCCM).

    Then, I ran the health check again and found no difference in fragmentation count (and some are over 75%). The script to check the log says it ran alter index and update statistics on the tables, but it really seems to have done nothing.

    Right now, I’m running IndexOptimize – USER_DATABASE beacause I think it will help the SQL itself.

    Any idea why nothing has changed when ran IndexOptimize – custom?

  9. ConfigMgrMVP says:

    You have to modify the IndexOptimize – Custom sql agent task to reflect the name of your databases.

    • Jean-Sebastien Frenette says:

      I followed the guide from this page. I haven’t seen anywhere where it says to customize the script. I’ve used the script procedure to create the task. The IndexOptimize – Custom doesn’t have any database name except “USER_DATABASES”. cmmonitor log does show it has run on my database, but not much was done. It even fragmented like hell another database that had only 10%, now it’s at 85%

      • Another ConfigMgr Admin says:

        Does this mean @Databases = ‘USER_DATABASES’, needs to be replaced with “CM_{sitecode},ReportServer,SUSDB”

      • ConfigMgrMVP says:

        No, USER_DATABASES cover all user databases, just not SYSTEM databases. That setting will include the CM database as well, so you don’t have to specify a name.
        Jean-Sebastien – what you are stating does not match with what we’ve seen. I’ve never, seen Ola’s scripts make things worse. Double check that everything is set up correctly.

  10. Pingback: The very best ConfigMgr database maintenance plan in the entire world* – The EmptyGarden

  11. Pingback: The ConfigMgr Accidental DBA | Steve Thompson [MVP]

  12. Alessandro Zola says:

    Hi if I have a cas and two primary do I have to run the job on every sql instance?

  13. John P. says:

    Should only the custom optimization job be used or the should the IndexOptimize – USER_DATABASES be used as well

    • ConfigMgrMVP says:

      You can use either… USER_DATABASES will include everything.

      • John P. says:

        I’m confused. Shouldn’t I use the IndexOptimize – ConfigMgr job since it’s the custom one specifically for SCCM. Don’t both IndexOptimize – USER_DATABASES and IndexOptimize – ConfigMgr include everything?

      • ConfigMgrMVP says:

        Not following you here. My recommendation is to use the custom Agent Job outlined in this blog: Configure the Index Optimization SQL Agent job task

  14. Russell Johnson says:

    Just started using this with the new maintainance.sql script. It uses stored procedures now. User_Databases only optimized our CM DB, not CMMonitor nor ReportServer. Confusing.

    • ConfigMgrMVP says:

      How do you know it did not optimize those DB’s? Just FYI, Ola’s solution will only optimize those indexes that exceed the fragmentation set, AND the table size > 1500 pages. Maybe they did not need any attention.

  15. Pingback: Cool SQL Tricks with DbaTools and MEM ConfigMgr | Skatterbrainz Blog

  16. Pingback: ConfigMgr Database Maintenance – 2020 edition – Chad's Tech

  17. Pingback: ConfigMgr Database Maintenance - New 2020 edition

  18. Pingback: ConfigMgr Database Maintenance - New 2020 edition

  19. Pingback: SQL Server recommendations for Microsoft Endpoint Configuration Manager environment Whitepaper Review | Steve Thompson [MVP]

  20. Steve Freeman says:

    Hi Steve, I just wanted to confirm before implementing your solution that everything in this post is still accurate as of 2021? Just asking because it was posted in 2016, so I’m trying to cross all my T’s and dot all my I’s before making these changes. Thanks in advance!

  21. Boopati says:

    Hello Team,
    What are the pre-requesite before I start the Indexing
    How do I duplicate the database to perform the test before doing the indexing in actual database
    How to check the health of the SQL before start the indexing and after complete of the indexing

    • ConfigMgrMVP says:

      Pre-requisites are outlined in this blog. Not really necessary to duplicate DB, however if you are inclined, use a backup and restore to a different name. Check health of SQL is a broad topic, if you want to check the degree of index fragmentation, I have a blog article with a script to do that.

  22. Pingback: Create the Optimize Database Solution using PowerShell and dbatools | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.