Recommendations for SQL Server Optimizations

Configure settings as follows to tune performance.

Operating System Settings

  • Set Power Mode (Control Panel) to "High Performance"
  • Set System Properties to "Adjust for Best Performance"
  • Configure PageFile.sys size should be 1.5 * Total Memory and located on SSD if possible
  • Add SQL User to Security for Perform Volume Maintenance Task

AntiVirus Software Exclusions

  • SQL Server Log Files: exclude the LOG folder for each SQL instance
  • SQL Server DB files: exclude *.MDF, *.LDF, *.NDF
  • SQL Trace Files: exclude *.TRC
  • Pagefile.sys
  • SQL Server Back-up Files (i.e., *.bak, *.trn)

Server level settings

NOTE: To access, right-click Server in SSMS > Properties.

  • Set min server memory to 8GB (8192 MB)
  • Set max server memory (based on total Operating System reported memory - 4GB
  • Set Cost Threshold to 75
  • Set Max Degree of Parallelism to 4
  • Set Backup Compression to On

Database Level Settings

Configure all user databases created by the SAP Advanced Data Migration installation to these settings:

  • Size Each Data File to 512 MB, 512 MB Growth
  • Size Log File to 64 MB, 256 MB Growth
  • Set Read Committed Snapshot Isolation to True
  • Set Recovery Mode to Simple

TempDB Optimization

Configure the TempDB system database to these settings, preferably on SSD:

  • 8 Files - Each sized at 8GB (8192 MB), 512 MB Growth
  • Log File set to 512 MB, 512 MB Growth

Other SQL Server settings

NOTE: Consult with the Database Administrator for further details.

  • Add Trace Flag 174
  • Add Trace Flag 1117 and 1118 if version lower than SQL Server 2016
  • Set all User Databases to AUTOGROW_ALL_FILES (Only for SQL 2016 +)
  • Set up Weekly Index Maintenance Job
  • Set up Weekly Statistics Rebuild Job (or more often as required)
  • Set up Nightly Database Backup Job