Scribd
Upload
219 views

MMS2017 SCCM SQL Optimal Performance v05

This document provides an overview of how to configure SQL Server and System Center Configuration Manager (SCCM) for optimal performance. It discusses best practices for configuring Windows Server, SQL Server installation and settings like memory, disks and databases. The document also covers optimization techniques like index and statistics maintenance using tools like Ola Hallengren's scripts. It concludes with discussions on SQL Agent jobs, backup strategies and the syscommittab table used for change tracking in SCCM.

Uploaded by

Venkatesh Konada
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
219 views

MMS2017 SCCM SQL Optimal Performance v05

This document provides an overview of how to configure SQL Server and System Center Configuration Manager (SCCM) for optimal performance. It discusses best practices for configuring Windows Server, SQL Server installation and settings like memory, disks and databases. The document also covers optimization techniques like index and statistics maintenance using tools like Ola Hallengren's scripts. It concludes with discussions on SQL Agent jobs, backup strategies and the syscommittab table used for change tracking in SCCM.

Uploaded by

Venkatesh Konada
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 64

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

You might also like