Configure integrated acceleration and offloading
Applies to: SQL Server 2022 (16.x)
This article demonstrates how to configure an integrated acceleration and offloading with Intel® QuickAssist Technology (QAT) for SQL Server. Intel® QAT is an integrated acceleration and offloading solution. For more background, see Integrated acceleration and offloading.
Download the drivers.
The minimum QATzip accelerator library version supported is 1.8.0-0010, but you should always install the latest version from the vendor. Drivers are available at Intel® Quick Assist Technology landing page.
Follow the instructions from the vendor to install the drivers on your server.
Restart the server after you install the drivers.
If the drivers are installed, the following files are available:
- The QATzip library is available at
C:\Windows\system32\
. - The ISA-L library installed with QATzip is available at
C:\Program Files\Intel\ISAL\*
.
The paths above apply to both hardware and software-only deployment.
After the drivers are installed, configure the server instance.
Set the server configuration option
hardware offload enabled
to1
to enable all SQL Server accelerators. By default, this setting is0
. This setting is an advanced configuration option. To set this setting, run the following commands:sp_configure 'show advanced options', 1; GO RECONFIGURE GO sp_configure 'hardware offload enabled', 1; GO RECONFIGURE GO
Stop and restart the SQL Server service.
Note
If
hardware offload enabled
option equals0
, all offloading and acceleration is disabled, however the accelerator-specific configurations will persist.Configure the server to use hardware offload for a specific accelerator. Run ALTER SERVER CONFIGURATION to enable hardware acceleration. The following examples set this configuration for Intel® QAT.
Choose one of the following examples, a. enable hardware-offloading with software fallback or b. software support.
a. Enable accelerator hardware offloading
Hardware compression configuration protects the host CPU - Intel® QAT hardware mode is designed to protect the underlying host system CPU. This method performs best when the underlying system is under higher workloads.
ALTER SERVER CONFIGURATION SET HARDWARE_OFFLOAD = ON (ACCELERATOR = QAT);
Tip
If the hardware device fails for any reason, the accelerator can gracefully fall back to software mode.
b. Force enable accelerator software mode
ALTER SERVER CONFIGURATION SET HARDWARE_OFFLOAD = ON (ACCELERATOR = QAT, MODE = SOFTWARE)
Important
The performance of the QAT_DEFLATE algorithm in terms of SOFTWARE vs. HARDWARE mode compared to MS_XPRESS varies based on several factors. The workload pressure the host system may be under during backup execution and the available memory and processing power of the Intel® QuickAssist Technology (QAT) hardware device are all factors that could impact the performance of the leveraged compression algorithm.
Restart the SQL Server instance. You need to restart the SQL Server instance after you run a command to
SET HARDWARE_OFFLOAD = ...
.To verify the configuration, run:
SELECT * FROM sys.dm_server_accelerator_status; GO
The query results identify:
mode_desc
- NONE, SOFTWARE, or HARDWARE modemode_reason_desc
- Reason for the modeaccelerator_library_version
- User mode accelerator versionaccelerator_driver_version
- Kernel mode accelerator version
The accelerator is enabled if the mode description is either SOFTWARE or HARDWARE. The mode_reason_desc
explains why the result is either SOFTWARE or HARDWARE mode.
If other results are found, refer to the sys.dm_server_accelerator_status (Transact-SQL) for troubleshooting.
The following example disables hardware offloading and acceleration for an Intel® QAT accelerator.
ALTER SERVER CONFIGURATION
SET HARDWARE_OFFLOAD = OFF (ACCELERATOR = QAT);
SQL Server 2022 (16.x) introduces an ALGORITHM
extension for backup compression for BACKUP (Transact-SQL).
The T-SQL BACKUP command WITH COMPRESSION has been extended to allow for a specified backup compression algorithm. For backup compression acceleration, Intel® QAT uses an algorithm called QAT_DEFLATE. If the drivers are available and the SQL Server configuration has been completed successfully as illustrated in the previously documented steps, WITH COMPRESSION initiates an Intel® QAT compressed backup.
Note
The standard compression algorithm is MS_XPRESS and is default compression option.
Use the ALGORITHM command to specify either of these two algorithms (MS_XPRESS
, QAT_DEFLATE
) for backup compression.
The example below performs backup compression using Intel® QAT hardware acceleration.
BACKUP DATABASE <database> TO DISK = '<path>\<file>.bak'
WITH COMPRESSION (ALGORITHM = QAT_DEFLATE);
Either of the following statements use the default MS_XPRESS compression option:
BACKUP DATABASE <database> TO DISK = '<path>\<file>.bak'
WITH COMPRESSION (ALGORITHM = MS_XPRESS);
BACKUP DATABASE <database> TO DISK = '<path>\<file>.bak'
WITH COMPRESSION;
The table below gives a summary of the BACKUP DATABASE with COMPRESSION options beginning with SQL Server 2022 (16.x).
Backup command | Description |
---|---|
BACKUP DATABASE <database_name> TO DISK |
Backup with no compression or with compression depending on default setting. |
BACKUP DATABASE <database_name> TO DISK WITH COMPRESSION |
Backup using the default setting in sp_configure . |
BACKUP DATABASE <database_name> TO DISK WITH COMPRESSION (ALGORITHM = MS_XPRESS) |
Backup with compression using the MS_XPRESS algorithm. |
BACKUP DATABASE <database_name> TO DISK WITH COMPRESSION (ALGORITHM = QAT_DEFLATE) |
Backup with compression using the QATzip library. |
Note
The examples in the table above specify DISK as destination. The actual destination may be DISK, TAPE, or URL.
The SQL Server backup compression default behavior can be adjusted. You can change the server default configuration and other options. You can enable or disable hardware acceleration, you can enable backup compression as a default, and you can also change the default compression algorithm as by using sp_configure
.
The status of these options is reflected in the sys.configurations (Transact-SQL). View the configuration of offload and acceleration configuration with the sys.dm_server_accelerator_status (Transact-SQL) dynamic management view.
The backup compression algorithm
configuration changes the backup compression algorithm default for backup compression. Changing this option will change the default algorithm when the algorithm isn't specified on the BACKUP ... WITH COMPRESSION
command.
You can view the current default settings for the backup compression in sys.configurations (Transact-SQL), for example:
SELECT * FROM sys.configurations
WHERE name = 'backup compression algorithm';
SELECT * FROM sys.configurations
WHERE name = 'backup compression default';
To change these configuration settings, use sp_configure (Transact-SQL) system stored procedure. For example:
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;
No restart of SQL Server is required for this change to take effect.
The backup compression algorithm
configuration sets the default compression algorithm. To set Intel® QAT as the default compression algorithm for SQL Server, use the following script:
EXEC sp_configure 'backup compression algorithm', 2;
RECONFIGURE;
To change the default compression algorithm back to the default, use the following script:
EXEC sp_configure 'backup compression algorithm', 1;
RECONFIGURE;
No restart of SQL Server is required for this change to take effect.
The backup file metadata identifies if the database backup is compressed and what algorithm was used to compress the backup.
Use RESTORE HEADERONLY to view the compression algorithm. See RESTORE Statements - HEADERONLY (Transact-SQL).
Note
If the server-scope configuration HARDWARE_OFFLOAD
option is not enabled, and/or the Intel® QAT drivers have not been installed, SQL Server returns error 17441, (Msg 17441, Level 16, State 1, Line 175 This operation requires Intel(R) QuickAssist Technology (QAT) libraries to be loaded.
)
To restore an Intel® QAT compressed backup, the correct assemblies must be loaded on the SQL Server instance initiating the restore operation. It's not required to have QAT hardware to restore QAT compressed backups. However, to restore QAT backups requires the following:
- QAT driver needs to be installed on the machine
- Hardware offloading needs to be enabled (
sp_configure 'hardware offload enabled', 1;
) - The SQL Server instance configuration has to have
ALTER SERVER CONFIGURATION SET HARDWARE_OFFLOAD ON (ACCELERATOR = QAT)
set as described previously.
QAT backups performed in HARDWARE mode can be restored in SOFTWARE mode and vice-versa.
You can view the compression algorithm and history of all SQL Server backup and restore operations on an instance in backupset (Transact-SQL) system table. A new column was added to this system table for SQL Server 2022 (16.x), compression_algorithm
, which indicates MS_EXPRESS
or QAT_DEFLATE
, for example.
After you configure integrated acceleration and offloading, every time the SQL Server service starts, the SQL Server process looks for the required user space software library that interfaces with the hardware acceleration device driver API and loads the software assemblies if they're available. For the Intel® QAT accelerator, the user space library is QATzip. This library provides many features. The QATzip software library is a user space software API that can interface with the QAT kernel driver API. It's used primarily by applications that are looking to accelerate the compression and decompression of files using one or more Intel® QAT devices.
In the case of the Windows operating system, there's a complimentary software library to QATzip, the Intel Intelligent Storage Library (ISA-L). This serves as a software fallback mechanism for QATzip in the case of hardware failure, and a software-based option when the hardware isn't available.
Note
The unavailability of an Intel® QAT hardware device doesn't prevent instances from performing backup or restore operations using the QAT_DEFLATE algorithm. If the physical device is not available, the software algorithm will be leveraged as a fallback solution.
- Integrated acceleration and offloading
- Hardware offload enabled configuration option
- ALTER SERVER CONFIGURATION (Transact-SQL)
- BACKUP COMPRESSION (Transact-SQL)
- RESTORE Statements (Transact-SQL)
- View or configure the backup compression algorithm Server Configuration Option
- View or Configure the backup compression default Server Configuration Option