Optimizing SCCM Databases Revisited

Since Optimizing ConfigMgr databases was published in May 2013, I have received some tremendous feedback, all positive.

The basics of that original post have not changed, Ola Hallengrens Index Optimization scripts, hands down, is the best solution for 99% of ALL SCCM implementations. This post is about making it easier to implement for the non DBA, and ensuring that the proper options are implemented for the Index Optimization job.

Pre-requisites:

Download Ola Hallengrens MaintenanceSolution.sql script, here

Steps

The following steps are required to implement this solution. Details on each step, follow.

1) Create the CMMonitor database

2) Install MaintenanceSolution.sql

3) Configure and Schedule the Index Optimization SQL Agent job task

4) Verify the Index Optimization SQL Agent job task

Create the CMMonitor database

Open SQL Server Management Studio (SSMS), connect to the SQL instance where the SCCM database is located.

Copy and paste the following into SSMS, change the file paths as appropriate and Execute.

USE MASTER

GO

IF EXISTS (SELECT name FROM sys.databases 
WHERE name = N'CMMonitor')

DROP DATABASE [CMMonitor]

GO

CREATE DATABASE [CMMonitor] ON PRIMARY 

( NAME = N'CMMonitor', 
FILENAME = N'C:\MSSQL\DATA\CMMonitor.mdf' , 
SIZE = 10240KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10240KB )

LOG ON 

( NAME = N'CMMonitor_log', 
FILENAME = N'C:\MSSQL\Log\CMMonitor_log.ldf' , 
SIZE = 1024KB , MAXSIZE = 2048GB , 
FILEGROWTH = 5120KB )

GO

ALTER DATABASE [CMMonitor] SET RECOVERY SIMPLE 

GO

clip_image002[5]

Install MaintenanceSolution.sql

Using SSMS (or your favorite editor) edit the MaintenanceSolution.sql (downloaded as part of pre-requisites) to point to your newly created CMMonitor database.

Note: SQL Server Agent should be running. If it is not, please start this service and set start mode to Auto.

Near the beginning of the MaintenanceSolution.sql, locate the USE [Master] entry and modify as follows:

-- USE [master] -- Specify the database in which the objects will be created.

USE [CMMonitor]

Then Execute the SQL query.

clip_image004[4]

Configure the Index Optimization SQL Agent job task

To perform this manually, Using SSMS, expand the SQL Server Agent node, then Jobs.

Note: Otherwise skip to end of this section to copy / run the SQL Script…

Right click Jobs, then New Job…

On the General page:

  • Enter the Name: IndexOptimize – Custom
  • Change the owner to sa

clip_image006[4]

Click Steps, then New…

On the New Job Step – General page

Step Name: Optimize

  • Type: Transact-SQL script (T-SQL) – should be default
  • Database: CMMonitor

Command (copy / paste the following)

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'

Then click OK

clip_image008[4]

New Job Step

On the New Job – Schedules page, click New…

On the New Job Schedule page:

Name: Run Optimization

Frequency: Weekly, select Wednesday (and Sunday)

Click OK

clip_image010[4]

Click OK to Save New Job.

SQL Script to create IndexOptimization – Custom job

On the instance where you have installed CMMonitor and the MaintenanceSolution, Copy / paste the following SQL syntax into SSMS and execute.

Note: do not run this step if you have manually configured this process!

USE [msdb]

GO

/****** Object: Job [IndexOptimize - Custom] Script Date: 11/28/2016 2:35:32 PM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 11/28/2016 2:35:32 PM ******/

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 - Custom',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@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', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Optimize] Script Date: 11/28/2016 2:35:32 PM ******/

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=8,

@freq_interval=9,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=1,

@active_start_date=20161128,

@active_end_date=99991231,

@active_start_time=0,

@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

Verify the Index Optimization SQL Agent job task

You can wait until after the scheduled date/time to observe results. (Default schedule is 2x/week. On a smaller site, you may want to test the IndexOptimization – Custom job immediately. To do so, locate the new SQL Agent job, right click and select Start Job at Step…

clip_image012

When complete, it should indicate that it was a success.

clip_image014

To review the results that were logged to the CommandLog table in the CMMonitor database, use the following SQL query:

SELECT TOP (1000) [ID]

,[DatabaseName]

,[SchemaName]

,[ObjectName]

,[ObjectType]

,[IndexName]

,[IndexType]

,[StatisticsName]

,[PartitionNumber]

,[ExtendedInfo]

,[Command]

,[CommandType]

,[StartTime]

,[EndTime]

,[ErrorNumber]

,[ErrorMessage]

FROM [CMMonitor].[dbo].[CommandLog]

ORDER BY ID DESC

Summary (other things to think about)

Disable the built in SCCM maintenance to re-index tables, you won’t need this anymore.

You may have noticed that a number of new SQL Server Agent jobs were created. These may be useful to you at some point, but are beyond the scope of this post.

I do recommend enabling the CommandLog Cleanup Agent job, as this will flush commandlog entries older than 30 days.

You may have noticed two options that were commented out in the IndexOptimize – Custom job:

–@MaxDOP=2,

–@SortInTempdb = ‘Y’,

Depending on your scenario, you may find that it may speed up the Optimize process. Be aware a very large database may use significant TempDB with SortInTempdb – monitor and adjust as needed.

All USER Databases will be optimized on the SQL Server where this job is run. That includes SUSDB, ReportServer and CM databases.

Check Ola’s site as updates are released on a fairly regular basis. Updating the Maintenance solution is very straight forward, from Ola’s FAQ.

I’m using an old version of the SQL Server Maintenance Solution. How do I upgrade to the most recent version?

To upgrade to the most recent version of the SQL Server Maintenance Solution, follow these steps.

1. Download MaintenanceSolution.sql.

2. Change this line in the script so as not to create jobs:
SET @CreateJobs = ‘N’

3. Execute MaintenanceSolution.sql. The script drops the old versions of the stored procedures and creates new versions.

4. Start the jobs and verify that they are completing successfully.

From <https://ola.hallengren.com/frequently-asked-questions.html>

Advertisements
This entry was posted in ConfigMgr, SQL Server. Bookmark the permalink.

7 Responses to Optimizing SCCM Databases Revisited

  1. Pingback: Optimizing SCCM Databases Revisited | Skatterbrainz Blog

  2. Jonathan says:

    Hi,
    Thanks a lot! couldn’t be more appropriate timing. I was trying to find why my SCCM was soooo slow. That did the trick.

    Still don’t understand why I had to do this for the first time.
    Site is pretty small (500clients) , and freshly installed to CB 1606. we migrated data from an old SCCM 2012 R2 SP1.

    can migration cause that kind of issue with indexes?
    Jonathan

  3. ConfigMgrMVP says:

    Migration will likely cause out of date indexes and statistics…

  4. ShawnBeau says:

    What is the rationale for changing the fragmentation levels from the default of 5 and 30 to 10 and 40 for SCCM? Also, Microsoft recommends doing Update Statistics with FULLSCAN, should then @StatisticsSample=100 be used?

    • ConfigMgrMVP says:

      The defaults were changed because on a very large site, more indexing was done than necessary and it took a LONG time. Modified the levels of fragmentation to be a little less aggressive. FULLSCAN will also add to the amount of time that this process will take. Feel free to modify and report back! Update – did some checking and FULLSCAN with a 100% sample is NOT a best practice. Most recommend letting the update stats figure out the sample size.

  5. Pingback: CMWT 2016.12.29 | Skatterbrainz Blog

  6. Pingback: SCCM / Windows Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s