Appendix A: Best practice guidance
For SQL Server 2017:
- Set min server memory and max server memory to the same value while leaving room for operating system overhead. For more information, see SQL Server Max Memory Best Practices.
- Change the max degree of parallelism (MAXDOP) configuration option and cost threshold for parallelism option after proper validation because the query parallelism requirement changes according to the dataset and 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 study, we set the MAXDOP value to 1 and kept the cost threshold for parallelism value at its default of 5.
- Enable instant file initialization by granting perform volume maintenance tasks permissions to the SQL Server service account. For more information, see Database File Initialization.
- Set the max worker thread value according to the workload and processor that are assigned to the SQL Server instance. For more information, see Configure the max worker threads Server Configuration Option. During our study, we set max worker thread to 704.
- Use multiple data files on different virtual disks and LUNs within the same filegroup.
- Allocate multiple tempdb data files 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 that was dedicated for tempdb with 8 GB size per file.
- Segregate database data files, database log files, and tempdb files on separate drives that are mapped to dedicated virtual disks and volumes. For our study, we created two data files and one log file on dedicated drives.