Any ConfigMgr environment will start to lose database performance over time. The may manifest to you as a general slowing of the ConfigMgr Console. This degradation of performance is likely due the database not being maintained properly. This post will show you how to implement a widely accepted community tool for optimizing your ConfigMgr SQL database. This is the same tool that Microsoft uses to maintain their ConfigMgr database. It’s also worth pointing out that this tool can be used on any SQL database, not just ConfigMgr. This solution rebuilds the indexes and statistics for whatever SQL database you run it against.
I recommend running this against your ConfigMgr and SUSDB databases nightly, if possible.
I personally swear by this process. I had a slow console. My environment had been in place for five years with no SQL maintenance being done to it. I ran this process once, and it was like a had a brand new environment. The console was much more responsive.
A lot of the real technical credit for the items in the post goes to Ola Hallengren and Steve Thompson, both Microsoft SQL MVPs. Ola wrote the solution, and Steve made it even easier to implement.
Environment Changes
There are two likely changes we need to make to your environment before proceeding. First, the SQL Server Agent service must be set to automatic. To configure this, open the Services MMC, select “SQL Server Agent <instance name>”, and set it to automatic (or automatic delayed start). The jobs that are executed require this service to set to automatic.
Next, disable the built-in “Rebuild Indexes” ConfigMgr Site Maintenance task. This task cannot be used when using this maintenance solution. Don’t worry, this solution is a lot better than the build in task. To disable this task, go to the Administration node of your ConfigMgr console. Expand Site Configuration, the click Sites. Next, select your site, and click Site Maintenance in the ribbon. Find the Rebuild Indexes task and disable it.
SQL Maintenance Script
The first item you need is the SQL maintenance script. This is available from Ola’s website here: https://ola.hallengren.com/. The download is right under the “Getting Started” heading (MaintenanceSolution.sql). You can download it directly from his website, or from GitHub (both links available on the page).
Once you download the SQL file, open up SQL Server Management Studio.
The first thing we must do is create the CMMonitor database. This is a small database that sits next to your ConfigMgr database and records what the maintenance solution does. Ola’s script, by default, places all of this information into the master database, but it’s best to separate it. You can create the CMMonitor database yourself, or use this SQL script (courtesy of Steve) to create it. Change the file paths in both lines that begin with FILENAME to the location where you want the database files stored. I recommend the same path as your ConfigMgr database.
USE MASTER
GO
CREATE DATABASE [CMMonitor] ON PRIMARY
( NAME = N’CMMonitor’,
FILENAME = N’D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CMMonitor.mdf’ ,
SIZE = 10240KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 10240KB )
LOG ON
( NAME = N’CMMonitor_log’,
FILENAME = N’D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CMMonitor_log.ldf’ ,
SIZE = 1024KB , MAXSIZE = 2048GB ,
FILEGROWTH = 5120KB )
GO
ALTER DATABASE [CMMonitor] SET RECOVERY SIMPLE
GO
Next, copy the contents of the MaintenanceSolution.sql into a new query. On line 22 of the query, change [master] to [CMMonitor].
Execute this query. It will build the solution, almost start to finish.
Configure the SQL Job
After the script finishes, you will see a number of jobs at SQL Server Agent > Jobs in the SSMS.
We need to build the job that’s named “IndexOptimize – ConfigMgr”. This defines the schedule and options that we need. I’m again going to borrow the command portion of this from Steve. This SQL script will build the ConfigMgr job and run it daily at 12:01am. Simply copy it into SSMS and execute it.
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’IndexOptimize – ConfigMgr’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’,
@notify_email_operator_name=N’Index Optimization Job Notification’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Optimize’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’EXECUTE dbo.IndexOptimize
@Databases = ”USER_DATABASES”,
@FragmentationLow = NULL,
@FragmentationMedium = ”INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE”,
@FragmentationHigh = ”INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE”,
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 40,
@UpdateStatistics = ”ALL”,
@OnlyModifiedStatistics = ”Y”,
–@MaxDOP=2,
–@SortInTempdb = ”Y”,
@LogToTable = ”Y”
‘,
@database_name=N’CMMonitor’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Run Optimization’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20161128,
@active_end_date=99991231,
@active_start_time=100,
@active_end_time=235959,
@schedule_uid=N’72893373-3c7b-4073-8b17-54dcaa9634bd’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Once this executes, you’ll have the same “IndexOptimize – ConfigMgr” job that’s in my screenshot above. This job is customized for ConfigMgr (and WSUS’s SUSDB). A full list of parameters available is on Ola’s website.
I recommend running this daily during off hours. If that’s not possible and you want to change the schedule, right-click on the job and select Properties. Select “Schedule” from left pane, and edit the one schedule that is listed.
Email Alerts
After running this for a few days, I decided that I wanted email alerts so that I knew it was running correctly. Setting up email in SSMS is actually not an easy thing to do, and involves several steps.
First, we must configure database mail by creating a mail profile. To do that, expand the Management node of SSMS, right-click on Database Mail, and select Configure Database Mail.
Keep the defaults (“Set up Database Mail by performing the following tasks:” and selecting yes to configuring database mail) until you get to the screen asking you specify a profile name. Give your profile a name and click Add.
Fill in the boxes, at least the account name, email address, display name, server name , and port. If you have to authenticate to your SMTP server, specify those credentials as well.
Click OK, then Next.
Check the box under Public.
Click Next until the wizard completes and builds the mail profile. Now we have the email settings for SQL configured.
Next, we need to configure notifications for the SQL Server Agent. To do this, right-click on SQL Server Agent in SSMS and select Properties.
Select “Alert System” from the left pane. Check the “Enable mail profile” checkbox and select the mail profile we created earlier.
Now we have to create a new operator. Under SQL Server Agent, right click on Operators and select “New Operator”.
Finally, open the SQL job (IndexOptimize – ConfigMgr) and select Notifications from the left pane. Check the email box, select the operator we just created, and when you want to be alerted.
Now you’re done. You have a start-to-finish SQL maintenance solution.
Disclaimer
All content provided on this blog is for information purposes only. Windows Management Experts, Inc makes no representation as to accuracy or completeness of any information on this site. Windows Management Experts, Inc will not be liable for any errors or omission in this information nor for the availability of this information. It is highly recommended that you consult one of our technical consultants, should you need any further assistance.
