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.
Once the query has completed the ‘Query executed successfully’ message will appear.
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
Right click and choose ‘Start Job at Step…’
The script will begin to run.
and eventually complete. Click Close.
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!
SCCMentor - Paul Winstanley 
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?
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.
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/
Thanks Steve, that’s worth knowing. I wasn’t aware of your blog piece on this subject.
Glad to help, as I recall, the issue with Ola’s default Optimize task, is the statistics do not get updated.
Reblogged this on automatizeblog and commented:
Not tested yet, but interesting …
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.