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!