MMS2017 SCCM SQL Optimal Performance v05

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!
 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
 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
TITLE
Section 1 Section 2
 Text  Text
Demo Title

MMS2017 SCCM SQL Optimal Performance v05

Uploaded by

Copyright:

Available Formats

MMS2017 SCCM SQL Optimal Performance v05

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

MMS2017 SCCM SQL Optimal Performance v05

Uploaded by

Copyright:

Available Formats

CONFIGURE SCCM / SQL FOR

SQL, SSRS, SCCM SQL / EMS MVP

15 years Many moons

Sunriver & Indian Blues & BBQ

 TempDB data files should be equally sized

 AUTOGROWTH IS TYPICALLY BAD

 DO WE WANT TO KEEP THIS AND TALK ABOUT THIS AT ALL?

 Records the changes to these tables

Properly install and configure SQL for SCCM

You might also like