Fragmented indexes in your SQL database can make your application slow down your significantly. And the indexes on SCCM database get fragmented very quickly. Ola Hallengren developed a maintenance solution for SQL that I use on every SQL server I manage, there is a huge performance gain in the SCCM console after optimizing sql database with this solution. This tutorial will go through how to implement his SQL Maintenance Solution and schedule it to run every night.
Optimize SQL database by defragmenting your indexes
- Download MaintenanceSolution.sql: https://ola.hallengren.com/downloads.html
- Open MaintenanceSolution.sql in SQL Server Management Studio on your SQL Server and execute it. This will create the maintenance jobs for your SQL Server Agent.
- Optimize SQL database by running the “IndexOptimize – USER_DATABASES” job. It can take a long time for this job to complete the first time depending on how fragmented your indexes are.
- Schedule the IndexOptimize – USERS_DATABASES job to run every night.
- Enjoy a much faster SCCM console.
This maintenance solution can be run, and it is recommended to run this on every SQL database on your server. If your company have dedicated staff administering your SQL servers, there is a good chance they already implemented this as it has become an industry standard. If so, simply ask them to implement this on your Configuration Manager database as well.
Ola Hallengren’s SQL Server IndexOptimize: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
