Fixing slow SCCM console performance


If your SCCM console has ground to a halt then it’s more than likely that you need to run indexing on the SQL DB to sort your problem out.

The SCCM Site Maintenance Tasks have a built-in task you can enable to do this but you can also download Ola Hallengren’s SQL Server Maintenance Solution which is a cool SQL script that you can use to perform this and the script also comes with some pretty handy solutions for DB backup and integrity checking.

You can download the solution from here

Once downloaded, open up the .sql file in Notepad and edit the backup location in the line ‘SET @BackupDirectory = N’C:\Backup’ — Specify the backup root directory.’ and choose a backup location that you prefer. You won’t need the backup directory for indexing but it’s worth setting as you can then use the cool DB backup scripts at your leisure.

Open SQL Management Studio and create a New Query. Copy and paste the .SQL file into the query and then click the Execute button.

index-001

Once the query has completed the ‘Query executed successfully’ message will appear.

index-002

Now under the SQL Server Agent node in the Object Explorer drill down to the Jobs section and the script will have inserted a bunch of new jobs. We are interested in the ‘IndexOptimize – USER_DATABASES’ job

index-003

Right click and choose ‘Start Job at Step…’

index-004

The script will begin to run.

index-005

and eventually complete. Click Close.

index-006

Hopefully you will see a very responsive SCCM console now that the indexing script has run.

Oh and you can schedule this task to ensure your ConfigMgr DB is in tip top condition!

8 comments

  1. Hi, great but what’s the difference with SCCM, Site Maintenance, Rebuild Indexes please.
    If Rebuild indexes is enabled every day, would i use this sql script please?

    1. David – I don’t know the exact differences except the community is in agreement that the index in this script is a lot better than the indexing that is built in to the maintenance tasks.

    2. The SCCM site maintenance task takes more of a brute force approach to index rebuilds. When it works. I have collected evidence that it does not always work.
      https://stevethompsonmvp.wordpress.com/2013/04/19/how-to-determine-if-the-configmgr-rebuild-indexes-site-maintenance-task-is-running/
      SCCMMentor has the right idea, however, Ola’s script can be made to run even better with a custom SQL Agent task, that you can find in this blog: https://stevethompsonmvp.wordpress.com/2016/11/29/optimizing-sccm-databases-revisited/

  2. Glad to help, as I recall, the issue with Ola’s default Optimize task, is the statistics do not get updated.

  3. Hello Paul Winstanley,
    I see that MaintenanceSolution.sql in the below URL is updated on 2020-12-31
    https://github.com/olahallengren/sql-server-maintenance-solution/blob/master/MaintenanceSolution.sql
    i do not see the SET @BackupDirectory. So I need to download MaintenanceSolution.sql and execute it? or is there any procedure to be followed before that. Please advise.

    Also Do i need to mention SCCM database in the USE [master] — Specify the database in which the objects will be created.
    Please advise.

Leave a Reply to Boopathi Cancel 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 )

Google photo

You are commenting using your Google 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