In this best practice, we used processor affinity to optimize SQL Server performance. The goal is to define a range of processors for the SQL Server to use to reduce thread migration and improve performance. 

Overview

CPU affinity is the ability to define which processor cores the SQL Server engine will use to multitask. By assigning processor cores SQL Server only uses those processors for multitasking database operations. This can improve performance by reducing processor reloads and thread migration across processors.

The virtual machine supporting SQL Server has 8 vCPUs. For this reason, we implemented the following configuration changes in this best practice:

• We configured SQL Server Processor Affinity to use processors 0 through 7 for a total of 8 processors.
• We assigned 0 through 7 to belong to a single NUMA node. 

Recommendation

The use of CPU affinity did improve performance for the following metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• Batch Requests per Second (BRPS)
• PowerMax IOPS

Reducing processor reloads and thread migration across processors was beneficial and resulted in a moderate database performance increase.

NOPM, TPM, Batch Requests per Second, and PowerMax IOPS all showed significant increases. 

PowerMax average read and write response times did increase as the database system was operating at a greater efficiency level. Additionally, CPU utilization also increased with the additional workload. 

Implementing CPU Affinity boosted database performance, therefore we recommend it as a Day 2 Moderate Recommendation best practice. 

Implementation Steps

To configure SQL Server Processor Affinity to use processors 0 through 7 we executed 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 executed the following command:

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE=0

Additional Resources

Affinity mask Server Configuration Option

In this best practice we implemented Forced Unit Access (FUA) to improve write performance of the SQL Server database. 

 Overview

FUA is a bit that is sent with writes to storage improving performance and durability of the database. Prior to the Linux 4.18 kernel, FUA was not supported. This resulted in device level flushing that was inefficient and had the potential to impact storage I/O performance. Microsoft optimized SQL Server to minimize device level flushing thus improving performance.

In the Linux 4.18 and later versions, the Linux XFS file system supports the FUA bit. Use of the FUA bit eliminated the need for forced flushes which further improved database write performance. Using Linux XFS file system and the FUA bit can significantly improve the performance of write intensive workloads. 

Recommendation

Implementing FUA resulted in slight but consistent performance gains for the following metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• Batch Requests per Second (BRPS)
• PowerMax IOPS

There were no changes to the metrics below:

• PowerMax Average Read Response Time
• PowerMax Average Write Response Time
• Server CPU Utilization

The minimal performance improvement is the reason we suggest this is a Day 3, Fine Tuning recommendation. 

Implementation Steps

In our environment, the DpuFua parameter equaled 0. We set both the control.writethrough and control.alternatewritethrough value to 1 from the default of 0 using the mssql configuration file. 

We also enabled TF3982, which is recommended in the Safe Installation (Best Grouping Performance – Forced Flush from SQL Server and HE) subsection of SQL Server On Linux: Forced Unit Access (Fua) Internals.

To set the control.writethrough and control.alternatewritethrough values to 1, navigate to /opt/mssql/bin/mssql-conf and add the following:

control.writethrough = 1

control.alternatewritethrough =1

Additional Resources

SQL Server on Linux: Forced Unit Access (FUA) Internals

In this best practice we increased SQL Server max worker threads. By increasing max work threads, we hoped to provide the database with more worker threads to optimize system performance. 

Overview

The default value for worker threads is zero. A setting of zero 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, as this is the recommended value for databases using eight CPUs and 64-bit computer starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x). For more information about Microsoft’s recommendations see, Configure the max worker threads Server Configuration Option. 

After testing a few max worker thread configurations, we found increasing the value to 864 provided a slight performance improvement. 

Recommendation

Every database configuration is different. For this reason, we recommend following Microsoft’s recommendations for worker thread values. With additional testing, customers might be able to improve performance by adjusting the settings. In this best practice, increasing max worker threads to 864 provided for slight improvements to the following metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• Batch Requests per Second (BRPS)

There were no changes to the metrics below:

• PowerMax Average Read Response Time
• PowerMax Average Write Response Time
• PowerMax IOPS
• Server CPU Utilization

Because of the slight performance improvement, we recommend this practice as a Day 3 Fine Tuning recommendation. 

Implementation Steps

For information on configuring max worker threads, please refer to this link.

Additional Resources

Configure the max worker threads Server Configuration Option. 

In this best practice we increased the minimum and maximum server memory values for SQL Server. The goal was to validate if allocating additional memory to SQL Server will increase database performance. 

 Overview

Database administrators can modify memory allocation for SQL Server using the minimum server memory and maximum server memory parameters. 

Minimum Server Memory

Minimum server memory is the amount of allocated memory that the SQL Server will reserve 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. 

In this best practice the minimal server memory was changed from the default value to 122880 MB (120 GB).  

Maximum Server Memory

Maximum server memory is the maximum amount of memory that the SQL Server can use. Setting the maximum server value sets a limit to the amount of memory the database can use. The difference between minimum and maximum server values defines the amount of memory 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). Analysis of the operating system and other application memory use is highly recommended to ensure that the database does not impact the overall system. 

Prior to modifying the minimum server memory parameter, we used the default setting of 2,147,483,647 MB.

We changed the maximum server memory to a value of 122880 MB (120 GB).  By setting the maximum and minimum server memory values to 120 GB the database will allocate all the memory and not exceed the 120 GB memory configuration. 

Recommendation

Setting the minimal and maximum server memory values to 120 GB showed minor performance improvements for these metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• Batch Requests per Second (BRPS)
• PowerMax IOPS

The minor improvements in NOPM and the other metrics were not significant enough to categorize the performance gains as more than Day 3, Fine Tuning activity. SQL Server with the default memory settings does very well at optimizing memory allocation for the best possible performance. With detailed analysis there is the opportunity to improve database performance by setting minimum and maximum server memory parameters. The database administrator should evaluate and test the settings of these parameters based on the database workload. 

There was a significant CPU utilization savings, of approximately 12%, in setting the minimum and maximum server memory settings. We did not investigate the cause in the drop of CPU utilization however, the savings might be attributed to the database engine not having to dynamically manage memory allocation. 

PowerMax average read and write times did show minor increases. The minor increase in read and write times can be attributed to the slight increases in NOPM, TPM, BRPS, and storage IOPS. 

Implementation Steps

In SQL the Server Management Studio we clicked on the Properties tab and selected the Memory page shown in Figure 1. The Minimum server memory field enables the administrator to define the minimal amount of allocated memory to SQL Server. As demonstrated in Figure 1, the minimum and maximum server memory values were both set to 122880 MB (120 GB). 

Figure 1: 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 reserved for the SQL Server process to 120GB:

/opt/mssql/bin/mssql-conf set memory.memorylimitmb 122880 

Additional Resources

Server memory configuration options

In this best practice, we modified the autogrow setting from the default value for both UserDB and TempDB. The goal of this best practice is to tune autogrow to address database space requirements while minimizing the impact on transactions when the database increases the size of a UserDB or TempDB files.  

 Overview

The autogrow default values include the following:

• UserDB and TempDB files is 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 the database benefits by not having excessive autogrowths events. Thus, the impact to the database is minimized and operates more efficiently. Database administrators must determine the proper autogrow value for their databases, as each is different. For validating autogrow best practices the following changes were made to our SQL Server databases:

• Data and log files for UserDB set to FILEGROWTH = 1048576 MB (1 GB)
• Data and log files for TempDB set to FILEGROWTH = 1048576 MB (1 GB)

Recommendation

Increasing the data and log file growth for UserDB and TempDB did show minor performance improvements in the following metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• Batch Requests per Second (BRPS)

There was no significant improvements in the metrics below:

• PowerMax Average Read Response Time
• PowerMax Average Write Response Time
• PowerMax IOPS
• Server CPU Utilization

We are recommending database administrators review the autogrowth setting for databases as part of a Day 3, fine tuning exercise. 

Implementation Steps

The script we used to resize the data and log files, repeat the same for all files that need to be updated is provided below: 

USE [master]

GO

ALTER DATABASE [TPCC500GB] MODIFY FILE ( NAME = N'TPCC500GB', FILEGROWTH = 1048576KB )

GO

We can use the ALTER DATABASE command to resize the TempDB data/log files. Amend the script for each TempDB file.

USE master;  

GO  

ALTER DATABASE tempdb   

MODIFY FILE (NAME = tempdev, FILENAME = '/mnt/temp_data/tempdb.mdf', SIZE = 8192MB,  FILEGROWTH = 1048576KB);  

Additional Resources

Considerations for autogrow and autoshrink settings in SQL Server

TempDB Database