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:
  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:

Anders Rødland

Anders Rødland started his IT career in 2006. My main focus is MS Configuration Manager and client management, and I have passed 17 Microsoft certifications since then. My main expertise is on client management with Microsoft Endpoint Manager: Intune and Configuration Manager. I also do a lot of work on the security side with Microsoft Defender for Endpoint. In addition to my Microsoft certification, I also have an ITIL v3 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.