Bewerken

Delen via

Configure integrated acceleration and offloading

  • Artikel

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.

Install drivers

  1. 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.

  2. Follow the instructions from the vendor to install the drivers on your server.

  3. Restart the server after you install the drivers.

Verify installed components

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.

Enable hardware offloading

After the drivers are installed, configure the server instance.

  1. Set the server configuration option hardware offload enabled to 1 to enable all SQL Server accelerators. By default, this setting is 0. 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

  2. Stop and restart the SQL Server service.

    Note

    If hardware offload enabled option equals 0, all offloading and acceleration is disabled, however the accelerator-specific configurations will persist.

  3. 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.

  4. Restart the SQL Server instance. You need to restart the SQL Server instance after you run a command to SET HARDWARE_OFFLOAD = ....

  5. To verify the configuration, run:

    SELECT * FROM sys.dm_server_accelerator_status;
GO

    The query results identify:

    • mode_desc - NONE, SOFTWARE, or HARDWARE mode
    • mode_reason_desc - Reason for the mode
    • accelerator_library_version - User mode accelerator version
    • accelerator_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.

Disable offloading and acceleration

The following example disables hardware offloading and acceleration for an Intel® QAT accelerator.

ALTER SERVER CONFIGURATION   
SET HARDWARE_OFFLOAD = OFF (ACCELERATOR = QAT);

Backup operation

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.

Default configurations

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.

Restore operations

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.

Backup history

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.

Service start - after configuration

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.

Next steps