Microsoft SQL Server

Optimize SQL Database

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

  1. Download MaintenanceSolution.sql: https://ola.hallengren.com/downloads.html
  2. 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.
  3. 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.
    SQL Index Optimize
  4. Schedule the IndexOptimize – USERS_DATABASES job to run every night.
  5. 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

Anders Rødland

Anders Rødland started his IT career in 2006. My main focus is MS Configuration Manager and client management, and I currently hold active 15 Microsoft certifications. Certified on Windows Server, Windows Client, SQL, Exchange and System Center Configuration Manager. Anders Rødland also holds an ITIL Foundation certification. This is my private blog and do not represent my employer. I use this to share information that I find useful. Sharing is caring.