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.


Download Ola Hallengrens MaintenanceSolution.sql script, here


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.



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




( NAME = N'CMMonitor', 
FILENAME = N'C:\MSSQL\DATA\CMMonitor.mdf' , 


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





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.


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


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,



@FragmentationLevel1 = 10,

@FragmentationLevel2 = 40,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y',


--@SortInTempdb = 'Y',

@LogToTable = 'Y'

Then click OK


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


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]


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



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)


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



EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'IndexOptimize - Custom',







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









@os_run_priority=0, @subsystem=N'TSQL',

@command=N'EXECUTE dbo.IndexOptimize

@Databases = ''USER_DATABASES'',

@FragmentationLow = NULL,



@FragmentationLevel1 = 10,

@FragmentationLevel2 = 40,

@UpdateStatistics = ''ALL'',

@OnlyModifiedStatistics = ''Y'',


--@SortInTempdb = ''Y'',

@LogToTable = ''Y''




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













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


GOTO EndSave





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…


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


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

SELECT TOP (1000) [ID]
















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


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:


–@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>

Posted in ConfigMgr, SQL Server | 10 Comments

Why you should not use the ConfigMgr rebuild index task

Rebuilding the database indexes is a good thing, yes? Absolutely.

Why not use the ConfigMgr rebuild index maintenance task?

When the maintenance task works, and I have proof that some of the time it fails to run on ConfigMgr:

  • It REINDEXES all indexes with more than 10% fragmentation
  • It does not update statistics

Let’s break these two items down…

Rebuild Indexes

By updating all indexes, the SQL Engine has to plow through EVERY index and REINDEX the index, REGARDLESS if it really needs the update! This makes the process very inefficient. You would not notice this in a lab, or a small environment. However, you WILL notice it on a very large site. To review the gory details, history time here.

It is far more efficient to Reorganize indexes that are slightly fragmented and Rebuild indexes that are very fragmented.


Database statistics are interesting to me… if you have ever studied statistics, you’ll know that the sampling method of any data will lead to really good results or perhaps, poor results. SQL statistics are used to map the distribution of values in a given column. The SQL Server query optimizer uses statistics to determine which indexes (if any) will be used to generate the query plan, then subsequently execute the query based on that plan.

If the statistics are up-to-date, then, most of the time the proper indexes will be selected for the query plan, leading to an efficient query plan execution. However, if the statistics are NOT up-to-date, expect that the WRONG query plan will be selected and a poorly executed query will result.

The analogy I like to use here, lets say you travel to an unfamiliar city. You pick up your rental car, and you are handed a street map for that city (this is pre GPS). If that street map is 10 years old (or older), you may have trouble finding your destination due to the changes in the road systems! You need an up-to-date map to determine an efficient route.

Key takeaways

Updating indexes without updating statistics is a disaster waiting to happen.

Do use Ola Hallengrens solution to INTELLIGENTLY update the indexes and modified statistics. Your database will be happier, your users will be happier.

Posted in ConfigMgr, SQL Server | 2 Comments

SQL Server MAXDOP and effect on ConfigMgr

What is MAXDOP?

SQL Server MAX Degree Of Parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing and thread resources that are used for the query plan operators that perform the work in parallel.

Sounds cool, what does that mean?

It means that SQL Server allows you to control the number of processors that are available to process a single statement. Statements include things such as: queries, DDL (data definition language) operations, bulk loads using parallel inserts, and a few other scenarios.

As a consultant, I’ve used and tuned MAXDOP setting for large data warehouse data loading operations. The improvement in tuning the parallelism can be quite dramatic.

By default, MAXDOP is set to 0. This means that all available processors are available to be deployed to process statements. That is good, right? Not necessarily. It depends.

Here are the current (somewhat generic) selected recommendations:

· To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.

· Index operations that create or rebuild an index, or that drop a clustered index, can be resource intensive. You can override the max degree of parallelism value for index operations by specifying the MAXDOP index option in the index statement. The MAXDOP value is applied to the statement at execution time and is not stored in the index metadata.

The effect on ConfigMgr?

For smaller sites, you may not need to change the default setting. When you scale out too much larger environments, particularly with the dreaded CAS in place, it is worth setting MAXDOP and monitoring.

Recently, I asked for feedback from the community on those that have set MAXDOP for ConfigMgr environments. Here is what I received (slightly edited for clarity):

Very large site: With a CAS, MAXDOP with a higher value makes collection queries faster, lower setting makes inbox processing faster, not a lot of room for compromise. We use 2.

Large site: With a CAS scenario with MaxDOP of 6 we’ve seen replication blocking itself. Set it to one and it hasn’t reoccurred. (Note: MAXDOP was disabled)


MAXDOP can be useful for certain scenarios. Modify and monitor for performance gains, or possible performance penalties. Adjust as needed.


Configure the max degree of parallelism Server Configuration Option


How It Works: Maximizing Max Degree Of Parallelism (MAXDOP)

From <https://blogs.msdn.microsoft.com/psssql/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop/>

TIP: Setting SQL Max Degree of Parallelism Achieve Big System Center SQL Performance Gains

(note: interesting, as always your mileage may vary)

From <http://www.systemcentercentral.com/tip-setting-sql-max-degree-of-parallelism-achieve-big-database-performance-gains/>

Posted in ConfigMgr, SQL, SQL Server 2016 | 1 Comment

SQL Server 2005 End of Life resources

Received an email today from Microsoft with some useful links and resources.


Extended support for SQL Server 2005 ended on April 12, 2016, which means you’ll no longer get security updates and hotfixes and could be at risk of data vulnerabilities.
Upgrade now to get the speed, productivity, and cost benefits of SQL Server 2014. Using familiar, trusted tools you can:

Achieve mission-critical performance.

Help maintain security and compliance.

Gain data insights faster.

Optimize your data infrastructure.

If you haven’t yet made the move, get started today—our resources will help you identify older versions of SQL Server running in your datacenter, review your upgrade options, and upgrade to a modern database that works for you.

Discover—Review your current IT infrastructure using the Microsoft Assessment and Planning Toolkit.

Target—Learn more about your upgrade options with our decision tool.

Upgrade—Use these resources to begin your upgrade:

SQL Server 2014 Upgrade Advisor

SQL Server 2014 Upgrade Technical Guide

Read more about our commitment to industry leading technology—see what’s coming up in SQL Server 2016.
Contact Microsoft or a partner for help today.

Posted in SQL Server, SQL Server 2014, SQL Server 2016 | Leave a comment

Upgrade from Oracle to SQL Server – free licenses

Just learned about an attractive offer from Microsoft to migrate Oracle, DB2 and a few others to SQL Server 2016 and get free licenses. You’ll need to purchase SA (Software Assurance), however, the cost savings can be fairly substantial!

SQL Server license migration


Posted in SQL Server, SQL Server 2016 | Leave a comment

SQL Server – Availability Group Issue

Spent part of a day chasing down an SQL Server Always On Availability Group manual failover, failure issue.

When running the failover wizard, the failure looks like this:


Some of the error description: Manual failover failed. Failed to perform a manual failover of the availability group ‘x’ to server instance ‘y’. Error code 5015.


The Availability Group and the Listener Name were named identically. That was causing the failure, to Windows Cluster, it was a name conflict as both resources could not be shared.

Renamed the AG name, problem solved!

Posted in High Availability, SQL Server | Leave a comment

Installing and configuring SQL Server for Configuration Manager

This guide is intended to assist administration in properly configuring SQL Server prior to installing Configuration Manager.

Too many make the mistake of miss-configuring SQL Server, then need to spend a LOT of time later, correcting it.

Article Contents

Installing and configuring SQL Server for Configuration Manager.


ConfigMgr 2012 R2 Database Sizing Estimator.

How are we going to configure SQL.

Cap log files.

Other databases.


SQL Server Installation.

Install SQL Server

Feature selection.

Instance Configuration.

Server Configuration Collation.

Install SQL Server Service Pack and latest Cumulative Update.

Install SQL Server 2012 Sp2.

Install SQL Server 2012 Sp2 CU4 (latest cumulative update).

Configure SQL Server.

Max memory setting.

Change location of TempDB.

Pre-create the ConfigMgr 2012 R2 database.

Post ConfigMgr 2012 R2 Installation Tasks.



You will want to install SQL Server on the same server that you plan on installing ConfigMgr 2012. If you need some background on why installing ConfigMgr 2012 on a remote SQL Server is a really bad idea, please read the following.

For a smaller ConfigMgr 2012 site, with perhaps less than 5,000 clients, the following server Physical or Virtual platform specifications will perform well.

ConfigMgr Site Server Specifications

Ø Windows Server 2012 R2

Ø SQL Server 2014

Ø 4 x CPU Cores

Ø 32GB memory (minimum)

Site Server Drive configuration guidelines:
Logical Drive Minimum Size


C: 100 OS + *Page file
E: 100 Applications + Database
F: 50 Log Files
G: 500 SCCM Content OS images, app packages, etc.
*P: 50 Page File
*T: 50 Temp DB
*U: 50 Backups

*Note: Optional configuration for higher performance and/or higher client counts

It is recommended that a separate server be installed to provide the Distribution Point and Management Point roles. The following server Physical or Virtual platform specifications are indicated.

ConfigMgr Distribution Point Specifications

Ø Windows Server 2012 R2

Ø 2 x CPU Cores

Ø 8GB memory (minimum)

Distribution Point Drive configuration guidelines:
Logical Drive Minimum Size


C: 100 OS + Page File + Binaries
E: 500 SCCM Content

ConfigMgr 2012 R2 Database Sizing Estimator

Prior to installing ConfigMgr 2012, It is recommended to pre-create the ConfigMgr 2012 database and pre-size the TempDB databases. One challenge, determining the correct size for the databases.

Shown is an example of the Database sizing worksheet, based on estimated client counts:


Anthony has documented this approach as follows:


Using the estimator to calculate the number and size of the data files, begin the SQL Server Installation process.

SQL Server Installation

Prior to SQL Server installation:

Drives dedicated for SQL Server data files or log files, such as TempDB should be formatted to NTFS 64KB block size.

Reference: https://stevethompsonmvp.wordpress.com/2014/07/25/sql-server-ntfs-cluster-size/

Server 2012 platform – Install File Services & .NET 3.5 framework (required for SQL install)

Install SQL Server 2012 or SQL Server 2014

Note: example is from SQL Server 2012, however, SQL Server 2014 is very similar.

Browse to SQL Server 2012 Sp1 install media, right click setup.exe, run as administrator. Only key dialogs will be shown for this installation.

Feature selection clip_image003

Feature selection – choose the above options.


Instance Configuration


Instance Configuration – take the Default Instance option.

Change the Instance Root directory location (assuming you’ve followed the drive suggestions) to:

E:\Microsoft SQL Server


Server Configuration Collation


Collation – default should be correct, however, verify that ‘SQL_Latin1_General_CP1_CI_AS’ is selected.


Default security – Windows Authentication, add <SERVER Name>\Administrators & <DOMAIN Name>\Domain Admins for Sysadmin permissions. Note: there may be some variation for groups based on client requirements.


Set SQL Server Agent service startup type to Automatic.


Install SQL Server Service Pack and latest Cumulative Update

Note: example, use the latest supported SP and CU!


Install SQL Server 2012 Sp2

Recommend downloading SQL Service pack to a folder on the server to be updated. Browse to SQL Server 2012 Service Pack, right click setup.exe, run as administrator


Click Next>


Click “I accept the license terms” and Click Next >


Accept defaults, and Click Next >


When checking files is complete, and Click Next >


Ready to Update, and click Update


Completion Dialog, all features should show a status of Succeeded


To verify installation results, review SQL Server log directory, navigate to:

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

Within that root folder, locate the Summary.txt file, review this file for component success status and proper SQL version.

Exit code = 0

SQL Server 2012 Sp2 build level = 11.2.5058.0


Install SQL Server 2012 Sp2 CU4 (latest cumulative update)

Note: Install latest SQL CU (reference link at end of this chapter)

Recommend downloading SQL Service pack to a folder on the server to be updated. Browse to the folde containing the hotfix, Right click setup.exe, run as administrator.


Setup Support Rules; and Click OK


And, Click Next >


Click “I accept the license terms” and Click Next >


Accept defaults, and click Next >


When checking files is complete, and Click Next >


Ready to Update, and click Update

(Note: highlight below – shows current patch level of SP2)


Completion Dialog, all features should show a status of Succeeded


Review Summary.txt

Exit code should read 0


Restart Server.

Verify that MSSQLServer service and SQL Server Agent services are running.



Always check for the latest updates!

Reference: https://technet.microsoft.com/en-us/sqlserver/ff803383.aspx


Configure SQL Server

Now that SQL Server is installed, it is time to configure SQL for optimal performance. In this section we will:

Ø Cap maximum memory

Ø Increased default size of TempDB and modified default file growth

Ø Give server administrators and domain admins Sysadmin equivalence

Ø Optionally, Install and configure SSRS (SQL Server Reporting Services)

o Increase default size of ReportingServer database and modified default file growth

o Change recovery mode to simple

Max memory setting
SQL Server Properties – Memory


Server properties > set min memory usage (8GB total)

Server properties > set max memory usage(32GB total – 8 GB = 26 GB for SQL)

Note: 26 GB * 1024 = 26624 MB

SQL Server Properties – Database Settings


SET Database Default Data, Log & Backup locations (as appropriate)

Change location of TempDB

From SSMS (SQL Server Management Studio), Start a New Query.

1. To get current location and names of tempdb files

Copy and Paste the following:

— To get current location and names of tempdb files

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N’tempdb’);


2. Change the location of each file by using ALTER DATABASE. Modify file paths as appropriate.

Copy and Paste the following:

USE Master;



MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);



MODIFY FILE (NAME = templog, FILENAME = ‘F:\MSSQL\LOGS\templog.ldf’);


Rerun query from step 1 to validate modified location:

— To get current location and names of tempdb files

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N’tempdb’);


3. Stopping and restarting MSSQLServer will recreate the TempDB files in their new locations.

4. To set initial size of TempDB from 8MB to the estimated size of tempdb, from prior step. From SSMS, expand System Databases node, right click TempDB database and select Properties


Files Option: Set TEMPDB initial size (based on estimated size – this should be approximately 25-30% of the size of the estimated ConfigMgr database) and autogrowth file setting. Autogrowth should be set to either 1024MB, or a minimum of 512MB.

TempDB Data files: optionally, create a minimum of 4 to a maximum of 8 data files (see references)

Reference: https://stevethompsonmvp.wordpress.com/2014/12/09/why-default-database-autogrowth-settings-are-bad/


Pre-create the ConfigMgr 2012 R2 database

Based on the previous size estimation, create a database to the total estimated size.

From SSMS, right click databases > New Database…

New Database properties


Database name: CM_<Site Code>

Owner: sa

Database Files: For both Data & Log files – Enter initial size, autogrowth, Path (as appropriate) & file name


New Database Properties



Select Options (on left)

Change recovery model to ‘Simple’

Then, click OK

Firewall ports

Windows server – if you are using a firewall, be aware that the following ports are required:

  • Intersite communications use:
    • SQL Server Service Broker, which defaults to port TCP 4022.
    • SQL Server Service, which defaults to port TCP 1433
  • Intrasite communication between the SQL Server database engine and various Configuration Manager site system roles default to port TCP 1433.


Install Configuration Manager

Now that all of the preliminary work is in place, now install Configuration Manager.

When installing Configuration Manager, use the option of installing to an existing SQL Server instance and database. Choose the pre-created database during the installation phase.

Post ConfigMgr 2012 R2 Installation Tasks

1. Setup database backup plan – backs up to <drive>:\BACKUP with a 1 week retention

2. Database Optimization and Tuning

Posted in ConfigMgr, SQL Server, SQL Server 2012, SQL Server 2014 | 4 Comments