SCCM Performance Tuning and Fix Slow SCCM – Part 2

In the first part of my SCCM Performance Tuning series we went through disk performance and SCCM collection evaluation updates. Part 2 of the series covers optimization of SQL database and antivirus exceptions for your SCCM server, which are two areas where the wrong configuration can cause huge loss in performance.

Previous chapters in this series:

Part 1 – Hardware, disk and collection performance.

SCCM Performance Tuning

Part 2 of the SCCM Performance Tuning series will go through the following topics:

• Tune and Optimize SQL Servers for SCCM
• Antivirus Exceptions

Tune and Optimize SQL Server for SCCM

SCCM SQL Database setup

The default settings for the SQL database created during installation of SCCM are horrible and you will experience performance issues unless you change these.
First, set your SQL database instance to use maximum 75% of the servers memory. If you do not limit this, the SQL Server will take as much as it can, which is everything. You need some memory left for the operating system and SCCM to perform its job.
Now lets take a look at the actual database for SCCM. By default there is one database file and one log file. The default size is very low and auto-growth is set at 2%. This is insane at so many levels and it is a big no-no. You don’t want your database or log files to to grow several times and by small amounts as this greatly increases fragmentation. Let’s do something about this.

1. Change the database initial size to at least 10 GB. Let it grow with 1 GB at the time. This reduces fragmentation significantly.
2. Add 3 more databases with the same settings. Now SQL have 4 threads to the database and not one.
3. Set log file to start at 1 GB. Let it autogrow with at least 200 MB at the time.

Defragment indexes on your SQL SCCM database every night

This point here is extremely important. It is very common to see big increase in performance after optimizing your SQL indexes. The indexes in your database are like the index of a book, you know exactly what chapter to go to find the information you are looking for. As information in the database is changed during the day, the indexes are no longer correct and SQL have to look through all the data to find the requested information. This is called fragmentation and cause performance to drop.
See my separate post on Optimize SQL database for how to implement Ola Hallengren’s SQL Maintenance solution to optimize your SQL indexes. This is a industry best practice standard for how to defragment your SCCM database.

WSUS Database

Don’t run a separate instance of SQL for your WSUS database, and DO NOT run it on a Windows Internal Database on the same server as your site server. That will eat unnecessary CPU and memory of your server.

Antivirus Exceptions for SCCM standalone server

One thing I have seen on several SCCM servers is an antivirus agent running real-time scanning on the entire server. That will cause a significant performance drop that you will notice in the sccm console.
Here are the anti-virus exceptions you want to add for your Configuration Manager Site Server
Directories

• %allusersprofile%\NTUser.pol
• %systemroot%\system32\GroupPolicy\registry.pol
• %windir%\Security\database\*.chk
• %windir%\Security\database\*.edb
• %windir%\Security\database\*.jrs
• %windir%\Security\database\*.log
• %windir%\Security\database\*.sdb
• %windir%\SoftwareDistribution\Datastore\Datastore.edb
• %windir%\SoftwareDistribution\Datastore\Logs\edb.chk
• %windir%\SoftwareDistribution\Datastore\Logs\edb*.log
• %windir%\SoftwareDistribution\Datastore\Logs\Edbres00001.jrs
• %windir%\SoftwareDistribution\Datastore\Logs\Edbres00002.jrs
• %windir%\SoftwareDistribution\Datastore\Logs\Res1.log
• %windir%\SoftwareDistribution\Datastore\Logs\Res2.log
• %windir%\SoftwareDistribution\Datastore\Logs\tmp.edb
• %programfiles%\Microsoft Configuration Manager\Inboxes\*.*
• %programfiles(x86)%\Microsoft Configuration Manager\Inboxes\*.*
• C:\Windows\TEMP\BootImages
• C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
• C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe

File exclusions

• *.mdf
• *.ldf
• *.ndf
• *.bak
• *.trn
• *.trc
• *.sqlaudit
• *.sql

The SCCM antivirus exceptions are available as a whitepaper at Microsoft Technet Gallery: https://gallery.technet.microsoft.com/Configuration-Manager-Anti-8c9f8f4c

Source LInk : https://www.andersrodland.com/sccm-performance-tuning-part-2/