SQL Server 2022: Intel® QuickAssist Technology overview
Part of the SQL Server 2022 blog series.
In SQL Server 2008 Enterprise Edition, backup compression was introduced adding WITH COMPRESSION as an option in the T-SQL backup command. SQL Server backup compression provides the compressed backup option for all streaming SQL Server backup types—full, differential, and transaction log. Using the metadata in the backup file header, the RESTORE command decompresses compressed backup files. Compressing backup data benefits customers in two ways: first, the backup files will consume less storage, and second, since there are fewer bytes being transferred, the backups run faster than uncompressed backups.
The default configuration in SQL Server is to not compress backups, but many users change this default globally using sp_configure.
While customers use the current compression standard, the challenge many customers have with the current compression implementation is that the compression ratios are frequently too low to justify the additional compute overhead required to compress the backup files.
Additionally, customers cannot offload the compute overhead of the compression task. Many customers in high-volume online transaction processing (OLTP) environments put their business continuity and disaster recovery (BCDR) strategy at risk by delaying their regular backup schedules in order to remain operational for customer workloads.
Intel® QuickAssist (Intel QAT) technology in SQL Server 2022, solves these challenges by providing hardware acceleration and CPU offload capabilities for enhanced compression and decompression functions. This feature is designed to give tangible improvements both in terms of reduced CPU usage and backup completion times, as well as a reduction in storage consumption.
The interface for Intel® QAT devices uses a software library called QATzip where the QATzip API maintains a built-in software fallback mechanism. This fallback mechanism allows administrators to hot-add/remove devices, better resist hardware failure scenarios, and provide software-based compression where needed.
The SQL Server 2022 feature provides integrated compression/decompression capabilities by using the QATzip library and the associated Intel® Intelligent Storage Acceleration Library (ISA-L). Intel® ISA-L is installed alongside QATzip where the presence and enablement of both the QATzip and Intel® ISA-L assemblies is a prerequisite for enabling instance-level server configuration for Intel® QAT hardware acceleration.
QATzip currently supports offloading backups to hardware devices, either through a peripheral component interconnect express (PCIe) adapter or as part of the host server chipset, and also supplying a software-based compression capability that can be used if there isn’t hardware available.
The SQL Server 2022 server-scope T-SQL syntax extends the current BACKUP DATABASE command to make it easy for database administrators to choose the desired algorithm to compress backups.
Additionally, the sp_configure configuration option is available to adjust backup compression defaults while introducing a new dynamic management view in order to query the configuration state.
The implementation of this feature surfaces appropriate error log and informational messaging to troubleshoot the Intel® QAT configuration as well as being able to track the benefits of the Intel® QAT compression feature over time by surfacing the compression algorithm history in the msdb database.
SQL Server 2022
Learn about the new features on security, platform, management, and more.
Business cases for backup compression with Intel® QAT
The organizations that will find the most benefit from the Intel® QAT are those that have high needs around maintaining customer workloads, such as mission critical OLTP and online analytical processing (OLAP) environments, while also ensuring their organization’s data recovery strategy maintains the organizations service level agreements (SLAs). These organizations must maintain application workloads to meet customer expectations while ensuring the organization’s data recovery strategy is never put at risk.
Often these organizations are highly virtualized and are running SQL Server instances at, or very close to, 100 percent processing time to maximize hardware and software licensing investments.
Reference:
Online transaction processing (OLTP) – Microsoft Docs
Online analytical processing (OLAP) – Microsoft Docs
Intel® QAT driver overview
Customers should download the Intel drivers themselves directly from the Intel QuickAssist supportability page. The supportability page will give an overview of the Intel® QAT, list driver versions per operating system, and maintain the release notes for the Intel® QAT drivers.
Note: The official Intel® QAT Driver for Windows, which for the writing of this documentation is version 1.8.0-0010, is required to be installed separately from the SQL Server 2022 installation.
For the initial release of QAT-enabled backups for SQL Server 2022, the feature will be available for SQL Server 2022 on Windows Server only. As a host operating system, Windows Server 2016, Windows Server 2019, and Windows Server 2022 are supported.
When the drivers are installed and server-scope configuration option is enabled for SQL Server, it is expected that the QATzip library will be available in the C:\Windows\ system32\ path for QATzip and the Intel® ISA-L will be available in C:\Program Files\Intel\ISAL\* path.
This path is true for both hardware and software-only deployment scenarios.
Customers can install the drivers before the SQL Server 2022 installation in preparation for a disaster recovery site, for system standardization, or after the SQL Server 2022 installation, even without QAT hardware. In either case, it is required for customers to maintain the driver version.
Preparing a disaster recovery server site
It’s possible to install the QATzip library on a server with no QAT hardware acceleration devices available (virtual or physical). In such cases, SQL Server will still load the QATZip.dll assembly provided the ALTER SERVER CONFIGURATION option for HARDWARE_OFFLOAD option is enabled for QAT.
In this scenario, users can specify the ALGORITHM for COMPRESSION as QAT_DEFLATE and rely on the software fallback provided by the Intel® ISA-L to perform the compression operation using the same format and encoding as provided by QAT devices.
Below are links to the currently supported Windows Server driver version, the Intel® QAT release notes, and the Intel® QAT landing page, which will be used as the primary location for Intel® QAT support.
Intel® QuickAssist Technology landing page
Intel® QuickAssist Technology Driver for Windows* (1.8.0-0010)
Intel® QuickAssist Technology Driver for Windows release notes
SQL Server 2022 Edition support for Intel® QAT
SQL Server 2022 implements different levels of Intel® QAT support based on the SQL Server edition. The SQL Server 2022 Enterprise edition will use hardware offloading if an Intel QAT device is available, otherwise, it will fall back to software-based compression if the Intel QAT device is not available for any reason.
SQL Server 2022 Standard Edition will only allow Intel® QAT backups with software-only compression, even if an Intel QAT device is available.
SQL Server 2022 Express Edition will allow Intel® QAT backups to be restored but will only allow backups to be performed with the default MS_XPRESS algorithm.
Note: The Intel® QAT drivers are required to be installed in order to perform backups and restores using the Intel® QAT feature.
SQL Server 2022 configuration for Intel® QAT for backup compression
In order to back up databases with the Intel® QAT compression algorithm, it is required to have the drivers installed, and the SQL Server must then be permitted to load the drivers into the SQL Server 2022 process space.
To complete this task, a new option has been added to ALTER SERVER CONFIGURATION in SQL Server 2022 with the HARDWARE_OFFLOAD syntax to enable or disable the use of hardware acceleration for a SQL Server instance. With this configuration being applied, after a restart of the SQL Server service, the Intel® QAT hardware acceleration technology can be used to provide a new optional backup compression algorithm.
Note: The HARDWARE_OFFLOAD option requires a restart of the SQL Server instance to take effect.
After the feature is enabled, every time the SQL Server service starts, the SQL Server process will look for the required user space software library that interfaces with the hardware acceleration device driver API and will load the software assemblies if they are available. The Intel® QAT user space library is QATzip, which provides a number of features. The QATzip software library is a user space software API that can interface with the QAT kernel driver API. It is used primarily by applications that are looking to accelerate the compression and decompression of files using one or more QAT devices.
In the case of the Windows operating system, there is a complimentary software library to QATzip, the Intel® Intelligent Storage Acceleration Library (ISA-L). This serves as a software fallback mechanism for QATzip in the case of hardware failure as well as a software-based option when the hardware is not available.
Note: The unavailability of a QAT hardware device will not prevent SQL instances from performing backup or restore operations using the new QAT_DEFLATE algorithm.
Software compression or decompression using Intel® ISA-L still requires that the QATzip software library is installed and that HARDWARE_OFFLOAD server configuration has been enabled for QAT. This is because SQL Server loads the QATzip.dll assembly provided by QATzip and it handles the fallback to Intel® ISA-L.
Additionally, software-based compression will be available for SQL Server 2022 Standard Edition allowing Standard Edition customers the ability to take advantage of software-based accelerators where SQL Server Enterprise Edition 2022 customers will have the extended capability of offloading backups to an external device.
Enabling Intel® QAT backup compression
The ALTER SERVER CONFIGURATION statement will be used to enable or disable access to QAT devices. Using the HARDWARE_OFFLOAD statement, as shown below, will persist a bit on the SQL Server boot page to load QAT binaries at startup.
ALTER SERVER CONFIGURATION
SET HARDWARE_OFFLOAD = ON (ACCELERATOR = QAT)
After executing the statement, users would then restart the SQL Server service to have the QAT binaries loaded into the SQL Server process space.
Note: The default configuration will be to use the Microsoft default MS_XPRESS compression capabilities.
The ALTER SERVER CONFIGURATION statement below will disable the Intel® QAT feature for SQL Server 2022.
ALTER SERVER CONFIGURATION
SET HARDWARE_OFFLOAD = OFF (ACCELERATOR = QAT)
Hardware acceleration configuration and availability group replicas
The HARDWARE_OFFLOAD server-scope configuration option will be compatible with Availability Groups. If the setting is enabled on a primary replica, the configuration option will be a persistent server-scope property such that upon failover from a primary to a secondary replica, an attempt will be made to load the correct assemblies on the new primary replica. The recommendation will be to enable the server-scope setting on all replicas after installing the latest Intel® QAT drivers on each replica.
Verifying Intel® QAT accelerator configuration
In order to verify the Intel® QAT accelerator configuration, a number of methods can be used including a new dynamic management view (DMV), sp_configure and the sys.configurations catalog view, and the SQL Server 2022 error log.
sys.dm_server_hardware_offload_config
The SQL Server sys.dm_server_hardware_offload_config dynamic management view can be used to verify the Intel® QAT configuration state.
The addition of this dynamic management view will make it much easier to verify the SQL Server accelerators that are configured for use, and those that have been confirmed to be loaded. This dynamic management view (DMV) should be the primary method to verify the configuration state of the available accelerators.
Example:
SELECT * FROM sys.dm_server_hardware_offload_config
GO
sp_configure and the sys.configurations catalog view
The SQL Server backup compression default behavior can be adjusted. You can change the server default configuration as well as 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 table.
As mentioned previously, you can discover the hardware offload configuration by querying the sys.dm_server_hardware_offload_config dynamic management view.
Backup compression default
To discover the backup compression default state, you can use the query below:
SELECT * FROM sys.configurations
WHERE name = 'backup compression default';
Changing this configuration is permitted through the sys.sp_configure 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. When this default option is changed to 1 (i.e., WITH COMPRESSION), MS_XPRESS will continue to be the default compression option, provided the backup compression algorithm has not been changed.
Backup compression algorithm
To discover the backup compression algorithm being used, you can use the query below:
SELECT * FROM sys.configurations
WHERE name = 'backup compression algorithm';
The “configure backup compression algorithm” configuration allows customers to change the default compression algorithm so they can determine if they want Intel® QAT to be the default compression algorithm for SQL Server.
Backup compression enabled
There will be a global sp_configure option to enable/disable hardware offloading.
Note: The value will be either 0 or 1 and will require a reboot to take effect.
sp_configure ‘hardware offload enabled’, 0
GO
Creating a backup using QAT compression
The T-SQL BACKUP command WITH COMPRESSION has been extended to allow for a specified backup compression algorithm. When using Intel® QAT for backup compression acceleration, the algorithm QAT_DEFLATE will initiate an Intel® QAT compressed backup if the drivers are available and the SQL Server configuration has been completed.
Note: The standard compression algorithm will be referred to as MS_EXPRESS and will remain the default compression option.
The ALGORITHM command will be used to specify either of these two algorithms (QAT_DEFLATE, MS_EXPRESS) for backup compression.
The example below will perform backup compression using Intel® QAT hardware acceleration.
BACKUP DATABASE testdb TO DISK='D:\Backups\testdb.bak'
WITH COMPRESSION (ALGORITHM = QAT_DEFLATE);
Either of these statements will use the default MS_XPRESS compression engine.
BACKUP DATABASE testdb TO DISK='D:\Backups\testdb.bak'
WITH COMPRESSION (ALGORITHM = MS_XPRESS);
BACKUP DATABASE testdb TO DISK='D:\Backups\testdb.bak'
WITH COMPRESSION;
The extension to the T-SQL syntax for backup compression provides the addition of the ALGORITHM option, allowing the options MS_XPRESS (default) or QAT_DEFLATE (Intel® QAT-based compression) in SQL Server 2022.
The table below gives a summary of the BACKUP DATABASE with COMPRESSION options in SQL Server 2022.
| BACKUP STATEMENT | OVERVIEW |
BACKUP DATABASE <database_name> TO {DISK|TAPE|URL} | Backup with no compression or with compression depending on default setting |
BACKUP DATABASE <database_name> TO {DISK|TAPE|URL} WITH COMPRESSION | Backup using XPRESS compression algorithm |
BACKUP DATABASE <database_name> TO {DISK|TAPE|URL} WITH COMPRESSION (ALGORITHM = MS_XPRESS) | Backup with compression using XPRESS algorithm. There is an argument for permitting use of DEFAULT or NATIVE as permitted options. |
BACKUP DATABASE <database_name> TO {DISK|TAPE|URL} WITH COMPRESSION (ALGORITHM=QAT_DEFLATE) | Backup with compression using the QATZip library using QZ_DEFLATE_GZIP_EXT with compression level 1. |
SQL Server 2022 RESTORE process
The RESTORE command does not include a COMPRESSION option as the backup header metadata specifies if the database is compressed and, therefore, the storage engine can restore from the backup file(s) accordingly. The backup metadata will be extended to include the compression type.
Running RESTORE HEADERONLY on a backup without compression and a backup compressed with the default MS_XPRESS algorithm will return result sets similar to the commands below:
RESTORE HEADERONLY
FROM DISK=’C:\temp\QAT-DEFLATE.bak’
GO RESTORE HEADERONLY
FROM DISK=’C:\temp\MS-XPRESS.bak’
GO
The RESTORE HEADERONLY command will display the compression algorithm along with other information such as the backup name, backup description, backup type, compression state, device type, and other columns. In SQL Server 2022, we will be adding the compression algorithm column.
Backups that have been compressed with either QAT_DEFLATE or MS_XPRESS, and uncompressed backups will all use the same T-SQL syntax RESTORE DATABASE DDL commands.
In the example below, the T-SQL restore syntax is valid for restoring from the testdb.bak backup file, regardless of whether it is an uncompressed backup, or a compressed backup created using QAT_DEFLATE or MS_XPRESS.
RESTORE DATABASE testdb FROM DISK=’E:\Backups\testdb.bak’
WITH RECOVERY;
SQL Server backups compressed using QAT_DEFLATE will support all T-SQL RESTORE operations. The RESTORE {DATABASE|LOG} statements for restoring and recovering databases from backups will support all recovery arguments, such as WITH MOVE, PARTIAL, STOPAT, KEEP REPLICATION, KEEP CDC, and RESTRICTED_USER.
Auxiliary RESTORE commands will also be supported for all backup compression algorithms. Auxiliary RESTORE commands include RESTORE FILELISTONLY, RESTORE HEADERONLY, RESTORE VERIFYONLY, and more.
Note: If the server-scope configuration HARDWARE_OFFLOAD option is not enabled, and/or the Intel® QAT drivers have not been installed, an error will be surfaced instead of attempting to perform the restore.
To restore an Intel® QAT compressed backup, the correct assemblies must be loaded on the SQL Server instance initiating the restore operation.
Best practices
The use of a BLOCKSIZE of 65536 (64KB) will be recommended, but options such as BUFFERCOUNT and MAXTRANSFERSIZE will continue to remain dependent on the user’s environment if changes from the default are being made.
Learn more
For more information, and to get started with SQL Server 2022, check out the following references:
Read What’s new in SQL Server 2022 for all the new features on security, platform, management, and more.
Read the Microsoft Integrated acceleration and offloading guidance to get started with Intel® QuickAssist technology in SQL Server 2022.
Go to the Intel® QAT portal for the latest Intel information on Intel® QAT device drivers and application support along with technical documentation and whitepapers.
