Home > Workload Solutions > SQL Server > Best Practices > AMD-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Database Best Practices > Performance Best Practices > SQL Server 2019: Forced Unit Access
In this best practice we implemented Forced Unit Access (FUA) to improve write performance of the SQL Server database.
Category | SQL Server 2019 Enterprise |
Product | SQL Server |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine Tuning |
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:
There were no changes to the metrics below:
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