Home > Workload Solutions > SQL Server > Guides > Design Guide—Modernize Your Microsoft SQL Server Platform and Accelerate Deployments > SQL Server best practices
Database best practices include:
TempDB is a SQL Server system database. As it is a resource designed to support all user databases connected to the same instance, it is important that it is appropriately sized, ideally on dedicated volumes for TempDB data and log. We created separate volumes to host both of for our best practice.
Size TempDB logs appropriately and, if available, host them on dedicated volumes for TempDB data and log.
Use the steps outlined in Microsoft tempdb database to update properties for TempDB files.
User database files, including data and log files, can grow during the insert, delete, or update operations stages which are often performed during an OLTP workload. To limit the need for this autogrowth being triggered often, users can set the Autogrow value for the data and log files.
The autogrow default values for UserDB and TempDB files are 64 MB.
Modifying the default values for both UserDB and TempDB files helps optimize how the database adds space and minimizes the impact on transactions. By increasing the size of autogrowth database efficiency improves by avoiding excessive autogrowths events. Database administrators must determine the proper autogrow value for their databases, as each is different. To validate autogrow best practices, we implemented the following changes to our SQL Server databases:
We recommend assigning appropriate autogrowth values for database files.
Use the steps provided in Considerations for the autogrow and autoshrink settings in SQL Server to update properties for database files.
The default value for worker threads is zero. This setting directs the SQL Server to automatically configure the number of worker threads at database startup. In our baseline tests we configured max worker threads to 864, which is the recommended value for databases using eight CPUs and 64-bit system starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x). For more information about Microsoft’s configuration recommendations see, Configure the max worker threads Server Configuration Option.
After testing a few max worker thread configurations, we found that increasing the value to 864 provided a slight performance improvement.
It is recommended to determine the updated max worker thread value for your environment and workload.
For information about configuring max worker threads, see Configure the max worker threads Server Configuration Option.
Configure the max worker threads Server Configuration Option.
CPU affinity describes the ability to define which processor cores the SQL Server engine uses to multitask. By assigning processor cores, the SQL Server only uses the selected processors for multitasking database operations. This can improve performance by reducing processor reloads and thread migration across processors.
The virtual machine supporting the SQL Server has 6 vCPUs. For this reason, we implemented the following configuration changes in this best practice:
We recommend setting SQL Server Process Affinity.
To configure SQL Server Processor Affinity to use processors zero through seven, we performed the following command:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=0 TO 7
To configure SQL Server Processor Affinity to use NUMA NODE 0 we performed the following command:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE=0
Affinity mask Server Configuration Option
Database administrators can modify memory allocation for SQL Server using the minimum server memory and maximum server memory parameters.
Minimum server memory is the amount of allocated memory that the SQL Server reserves for the database. This setting is a memory allocation guarantee that the database administrator can use to optimize performance. Analysis of memory use from the operating system and other applications is required to accurately define the minimum server memory setting. Setting the minimum server memory to a high value can impact the operating system and lower overall performance.
We changed the minimum server memory from the default value to 360,448 MB (352GB).
Maximum server memory is the maximum amount of memory that the SQL Server can use. Setting the maximum server value limits the amount of memory that the database can use. The difference between minimum and maximum server values defines the amount of memory that the SQL Server can dynamically use. For example, if the minimum is set to 64 GB and the maximum is set to 128 GB, then the SQL Server can dynamically use 64 GB (128 – 64 = 64). We highly recommend analyzing your operating system and other application memory use to ensure that the database does not impact the overall system.
We used the default value for our baseline configuration tests. For additional tests, we changed the maximum server memory to a value of 360,448 MB (352 GB). By setting the maximum and minimum server memory values to 120 GB the database will allocate all the memory and not exceed the 352 GB memory configuration.
We recommend setting the minimum and maximum SQL Server memory to a value lower than that assigned to the operating system. These values should also be equal especially for predictable workloads.
In SQL the Server Management Studio we selected Properties > Memory, as shown in Figure 5. We set the minimum and maximum server memory values to 122880 MB (120 GB).
Figure 7. Memory page from SQL Server Management Studio
We also recommend applying this setting through the mssql config file. Run the following command to update the memory that is reserved for the SQL Server process to 120 GB:
/opt/mssql/bin/mssql-conf set memory.memorylimitmb 122880