For SQL Server 2019, adhere to these best practices:
- Set the min server memory and max server memory settings to the same value while leaving room for operating system overhead. For more information, see SQL Server Max Memory Best Practices.
- Change the maximum degree of parallelism (MAXDOP) configuration and cost threshold for parallelism options. Perform this step after validation because the query parallelism requirement changes according to the dataset and the nature of the queries. For more information, see Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server and Configure the cost threshold for parallelism Server Configuration Option. During our solution deployment, we set the MAXDOP value to 12 and kept the cost threshold for parallelism value at its default of 5.
- Set the max worker thread value according to the workload and processor assigned to the SQL Server instance. For more information, see Configure the max worker threads Server Configuration Option.
- Use multiple datafiles on different volumes in the same filegroup.
- Allocate multiple tempdb datafiles to address tempdb contention issues. For more information, see Recommendations to reduce allocation contention in SQL Server tempdb database. For our study, we allocated eight files on a separate drive dedicated for tempdb with 32 GB per file.
- Segregate database datafiles, database log files, and tempdb files on separate drives that are mapped to dedicated volumes. For our study, we created eight datafiles and one log file on dedicated drives.
- Trace flags that are available in the SQL Server change the default behavior. Our study showed that following trace flags provides better performance.
- Trace flag 834 causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool. For more information, see Large-Page Support.
- Trace flag 3895 supports the newly introduced Memory optimized tempdb metadata feature.
- Trace flag 3979 helps remove the forced flash mechanism (for data and log files) on SQL Server running on Linux. It causes SQL server to use writethrough and alternatewritethrough logic. For more details, see KB4131496 - Improvement: Enable "forced flush" mechanism in SQL Server 2017 on Linux.
- Trace flag 1810 helps print log messages related to database file mapping on DAX volumes. This information helps us understand and troubleshoot HBP or DAX mapping issues.
