CONFIGURE SCCM / SQL FOR
OPTIMAL PERFORMANCE
Benjamin Reynolds Steve Thompson
blogs.technet.microsoft.com/ www.stevethompsonmvp.wordpress.com
benjamin/
Senior Consultant
Microsoft
Softchoice
Benjamin Reynolds Steve Thompson
? @Steve_TSQL
SQL, SSRS, SCCM SQL / EMS MVP
15 years Many moons
Sunriver & Indian Blues & BBQ
AGENDA
Configure Windows Server
Best practices
Configure SQL Server
Best practices
Optimization and Tuning techniques
Syscommittab
Backup for performance
CONFIGURE WINDOWS SERVER
CONFIGURE WINDOWS SERVER
Key Topics
Provision Server
Recommended Platform
Memory
CPU
Disk
Best Practices
WINDOWS SERVER - PROVISION SERVER
Use Windows Server 2016
Memory
CPU
Disk
64KB NTFS format
IOPS
Drive layout
Considerations for Azure VMs
See HA session
Best Practices – capacity planning
https://docs.microsoft.com/en-us/sccm/core/plan-design/configs/recommended-hardware
CONFIGURE SQL SERVER
CONFIGURE SQL SERVER
Installation location
Disk Layout
Memory
CPU
Databases
SCCM
TempDB
SUSDB
DB Sizing, Autogrowth & VLF’s
Patching – SP & CU updates
Cautionary notes…
Best Practices / Lessons from the field!
SQL INSTALLATION LOCATION
Collocate with SCCM?
When possible, install SQL Server on same server as SCCM
Remote install of SQL Server
Exceptions: HA scenarios
Install location
Never C:
Disk Layout
Plan to separate Data from Logs, TempDB on dedicated drive
DISK LAYOUT
Plan to separate Data from Logs,
Place TempDB on dedicated drive
Include TempDB data and logs on same drive – SSD if possible
If SQL co‐located with SCCM sample drive layout:
Logical Drive Minimum Size Purpose
(GB)
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
DISK LAYOUT - WHAT MSIT DOES
SQL MEMORY CONFIGURATION
SQL Server loves memory. Maybe too much.
Always, always set a minimum and maximum memory
Recommended Settings:
Minimum SQL memory = 8GB
Maximum SQL memory
SCCM and SQL on same server: example 96GB total – 16GB = 80 GB for SQL
SQL on Remote server: memory setting should be about 80% of server memory
Always calculate memory to whole GB
Example: 8GB * 1024 = 8192MB
SQL MEMORY CONFIGURATION
Default (Screen shot) & modified
Remember - Monitor memory usage and adjust as needed. Page file usage = BAD
CPU
MAXDOP - MAX Degree Of Parallelism (MAXDOP)
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.
To suppress parallel plan generation, set max degree of parallelism to 1
Large sites may benefit
Very large site: SCCM Admin feedback: “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.”
Advice: Experiment and monitor
DATABASES
Estimate size(s)
SCCM
SUSDB
TempDB
Use SCCM DB Sizing estimator!
Multiple data files may be appropriate
Can migrate using Filegroups
Be cautious in a multi-site hierarchy!
Single LOG file
Autogrowth = BAD
TEMPDB
TempDB data files should be equally sized
Total TempDB should approximate 25-30% of the SCCM total size.
Place TempDB on a dedicated drive, with log file
Create
4 (or more) equally sized data files. This helps you avoid the
GAM/SGAM/PFS page contention issues described in Microsoft KB 2154845.
Turn off auto-growth
Create no more than 8 data files
DB FILE AUTOGROWTH
AUTOGROWTH IS TYPICALLY BAD
Autogrowth recommendations:
Use proper sizing estimates for sufficient free space
Monitor DB free space
SET Autogrowth to either 512 or 1024MB
NEVER, EVER USE % GROWTH!
Incorrect settings can cause VLFs
VLF – VIRTUAL LOG FILES
Can have a negative effect on database performance
Too many VLFs will slow down the startup, shutdown and read/write
performance of any database.
VLFs are created when a transaction log file is allowed to auto-grow.
Size transaction log file appropriately!
Depending on size of database/transaction log file, less than several hundred
Correct a high VLF count by:
SINGLE USER MODE
DBCC SHRINKFILE
MODIFY FILE
VLF – VIRTUAL LOG FILES
MS IT – recent foray into VLFs
SERVICE ACCOUNTS & INSTANT INITIALIZATION
Service Account versus System Account
Ensure the service account is added to “Perform volume maintenance tasks”
Ensure the service account is added to “Lock Pages in Memory”
DO WE WANT TO KEEP THIS AND TALK ABOUT THIS AT ALL?
PATCHING
Patching – SP & CU updates
Apply services packs and Cumulative updates on a regular basis
Contain bug fixes, security vulnerabilities and performance fixes
Cautionary notes…
SQL Server 2016 and SSMS have shared components
Best practice, do not install SSMS on server, install on a separate workstation
SQL Server review demo
OPTIMIZATION AND TUNING
OPTIMIZATION AND TUNING
Indexes and Statistics – oh my!
Ola Hallengren solution
How to properly implement
Proper SQL Agent task settings
Considerations
MS IT – lessons from the field
INDEX & STATISTICS MAINTENANCE
What is an index?
A “small pointer table” to help speed up data lookups
Scans vs Seeks
Why maintain indexes?
They get fragmented and need to be “defragged”
A highly fragmented index will impact performance
Overall data processing on the site
Replication processing
Querying/Reporting
What is a statistic?
Information about the distribution of values in a table or index
Used by the optimizer to determine an execution plan
Why maintain statistics?
It impacts performance!
INDEX & STATISTICS MAINTENANCE
ConfigMgr “Rebuild Indexes” Maintenance Task
Reorganize indexes with 10%-30% fragmentation
Rebuild indexes when fragmentation is greater than 30%
Enterprise Edition of SQL Server allows for an online rebuild operation
Disabled by default!
There is no “Statistics Maintenance Task”!
Custom Index & Statistics Maintenance
Define different fragmentation levels, time limits, or schedules
Define separate statistic update schedules
DO IT!
Index & Statistics maintenance is absolutely necessary!
Indexes weekly; Statistics daily
INDEX & STATISTICS MAINTENANCE
Reorganize vs Rebuild
Using Ola Hallengren’s index maintenance solution
Download & Install IndexOptimize.sql, CommandExecute.sql, and CommandLog.sql (from
https://ola.hallengren.com/downloads.html)
Create a SQL Agent Job (or jobs) which executes IndexOptimize and set a schedule
Recommendations?
Log to a table and review to determine if changes are necessary!
WHAT WE DO
Index weekly
Statistics daily
Log to the table!
Skip “DrsConflictInfo”, “HinvChangeLog”, and “CI_CurrentComplianceStatusDetails
What’s next…
INDEX INVESTIGATIONS
Implement Tuning Solution
BACKUP
BACKUP – SCCM AND SQL
Maintenance Plans
Custom Plans
Considerations
Performance
SCCM BACKUPS
Saves a copy of the mdf and ldf files
No compression
Saves configuration information
Information to pre-populate recovery values
Only backs up the CM database
CUSTOM BACKUP PLANS
SQL backups supported by SCCM
Smaller backup sizes possible – compression!
Greater control over the backups taken/created
All databases backed up
Does not backup configuration information
During recovery those values must be typed in – server/sitecode/etc
Backup additional items
Backup the Cd.latest folder after each build
https://stevethompsonmvp.wordpress.com/2017/01/17/sccm-file-backup-considerations/
CONSIDERATIONS/PERFORMANCE
Striping SQL backups
Number of backups to keep
Max Transfer Size
LOG BACKUPS! (for Availability Groups)
Backup ALL databases!
Test backups often! (You don’t have a backup until you’ve restored it!)
Backup cd.latest!
WHAT WE DO AT MSIT
CM DB striped backup – 16 files
Nightly Full backups
Log backups every 2 hours (if in FULL recovery mode)
Keep the last 2 Full backups (and any log backups after)
Max Transfer Size of 262144 for SAN connected drives (and Azure)
Separate DPM jobs for offsite and longer term storage
Restore CAS backup daily!
Backup Demo
SYSCOMMITTAB
SYSCOMMITTAB
Internal tables used with Change Tracking
“Sys.change_tracking_[ID]”
Sys.syscommittab
Requires a DAC connection to view
DMV = Sys.dm_tran_commit_table
Records the changes to these tables
DRS picks up the changes for replication
Old records cleaned up by a ‘ghost cleanup process’
The “magical tipping point”
SYSCOMMITTAB
Make sure you don’t have a huge “backlog” of records on the CT tables!
SQL 2014+ stored procedure: sp_flush_commit_table_on_demand
ConfigMgr added two stored procedures for taking care of “syscommittab”
spDiagChangeTracking – used to check or cleanup (@CleanupChangeTracking variable)
spCleanupChangeTracking – does not require DAC but SQL 2014+
Custom solution
Used as the core of the CM sprocs
Issues creating an Agent job due to DAC requirements
Write the info to a table!
THE “DBA” SOLUTION
THE “DBA” SOLUTION
A separate database named “DBA”
Logging and statistics gathering
Custom objects (stored procedures, functions, views, etc) stored here
Iterations in source control
A look at the DBA Solution
REFERENCES
Ola and SCCM
https://stevethompsonmvp.wordpress.com/2016/11/29/optimizing-sccm-databases-revisited/
Properly install and configure SQL for SCCM
https://stevethompsonmvp.wordpress.com/2016/07/25/installing-and-configuring-sql-server-
for-configuration-manager/
VLFs
https://stevethompsonmvp.wordpress.com/2013/05/14/virtual-log-files-and-impact-on-
configmgr-performance/
SECTION HEADER
This is the next section
TITLE
Line1
Bullet Level 1
Bullet Level 2
Bullet Level 3
TITLE
Code
Text Only with Border
Level 1
Level 2
Level 3
Text Only (Red)
Level 1
Level 2
Level 3
TITLE
Text 1 Text 2
Level 1 Level 1
Level 2 Level 2
Level 3 Level 3
TITLE
Section 1 Section 2
Text Text
Level 1 Level 1
Level 2 Level 2
Level 3 Level 3
Demo Title
