My SQL Server 2019 Checklist

My settings checklist for a basic SQL Server 2019 instance. These are the initial settings that can be adjusted based on the database workload.

• Set Windows server to High performance power plan
• Optimize Windows server for Background Services
• Turn on Instant File Initialization (if allowed)
• Set log file size to 20-30% of the database size
• Set datafile and log file autogrowth to the maximum a file can grow within a 5 second period. Usually, I settle for 1024 MB
• The total size of tempdb should be 25% of the total size of all databases within the instance
• Tempdb data files should be split between 4-8 data files
• Change default backup location to drives other than the C drive or where the data files are stored
• Set Default Index fill factor to 0 or 100%. Anything else will lead to internal fragmentation
• Turn on Backup Compression by default
• Set Cost Threshold for Parallelism to 50
• Set Maximum server memory to be 4 GB less than the total memory of the physical server (this is assuming that the database server is the only application on that machine)