How to Enable Intel QAT Backup Compression in SQL Server 2022 - Glenn's SQL Server Performance (2024)

Introduction

One very interesting new feature in SQL Server 2022 RC0 is Intel QAT Backup Compression. Despite what some people seem to think, this feature does not require any QAT capable hardware, and it is pretty easy to enable and use. This post will walk through how to enable Intel QAT backup compression in SQL Server 2022.

I have a video that shows how to do this.

What is Intel QAT Backup Compression?

This is the first in a series of methods that can use specialized hardware accelerators to offload specific tasks from the regular compute cores in a CPU to some other device in your system. This takes that workload off of your regular compute cores, which frees them up to do other tasks (like running database queries).

So far, Microsoft has only announced Intel QAT acceleration, but it seems extremely likely that other acceleration methods will be supported in the future. How far away that future may be is anybody’s guess.

There are two modes that you can use for QAT backup compression.

• QAT Software Mode
  • Only requires the Intel QAT Driver
  • Does not require any specialized hardware
  • Supported in SQL Server 2022 Standard Edition and Enterprise Edition
• QAT Hardware Mode
  • Still requires the Intel QAT Driver
  • Requires an Intel QAT capable hardware device
  • Requires SQL Server 2022 Enterprise Edition

If you have SQL Server 2022 Enterprise Edition and an Intel QAT capable hardware device, then you will be using QAT Hardware mode by default when you run a QAT backup command. You can override this default and force your system to use software mode with an ALTER SERVER CONFIGURATION command.

Right now, the easiest method to get an Intel QAT capable hardware device is to use an Intel QAT 8970 PCIe card. In early 2023, you (or your cloud provider) will be able to use Intel Sapphire Rapids processors that have a QAT hardware offload accelerator included as a separate part of the CPU.

How to Enable Intel QAT Backup Compression in SQL Server 2022

This requires three steps (after installing SQL Server 2022 RC0 or newer).

Step 1: Install the latest Intel QAT driver

These drivers are required, whether or not you have any Intel QAT capable hardware in your system. You can even install these drivers before you install SQL Server 2022, as part of your base OS build and configuration.

I have a detailed blog post about how to do this.

• How to Install the Intel QAT Driver

BTW, don’t let this warning dialog scare you off. It is normal if your system does not have any Intel QAT capable hardware. Most people will not have any Intel QAT accelerators in their system right now. That is fine! You can still test this feature out on whatever hardware you have, physical or virtual. It even works in cloud VMs!

See Also

Best CPU Processor For Laptop Ranking List March 2024 | Cashify Laptops Blog

After you install the Intel QAT drivers, restart the SQL Server Service.

Step 2: Enable hardware offload config with sp_configure

This step is also required, even if you don’t have any Intel QAT capable hardware. This configuration setting is like a master switch for any hardware offload methods. So far, Microsoft has only announced Intel QAT hardware offload, but it is pretty obvious that they are planning for additional hardware offload methods in the future.

Here is the code for this sp_configure option. You need to restart the SQL Server Service for the change to go into effect.

-- Step 2 - Enable hardware offload config with sp_configure-- This must be enabled in order to use any hardware offload methods-- Disabling it here will disable ALL hardware offload methodsEXEC sp_configure 'show advanced options', 1;GORECONFIGUREGOEXEC sp_configure 'hardware offload enabled', 1;GORECONFIGUREGO-- Restart the SQL Server Service (because it is not a dynamic option)

Step 3: Enable hardware offload for the QAT accelerator

The final step is to run an ALTER SERVER CONFIGURATION command to enable hardware offload for the QAT accelerator. Again, this step is required, even if you don’t have any Intel QAT capable hardware in your system. Here is the code for this step.

-- Step 3 - Enable hardware offload for the QAT accelerator-- This is required even if you don't have any Intel QAT hardwareALTER SERVER CONFIGURATION SET HARDWARE_OFFLOAD = ON (ACCELERATOR = QAT); -- You will see this message:-- Hardware offload configuration has changed. Current configuration (0x0) and mode (0x0). -- New configuration (0x1) and mode (0x0). Restart SQL Server for the new configuration to take effect.-- Restart the SQL Server Service to have it go into effect-- You are done!

You need to restart the SQL Server Service for the change to go into effect.

How Do I Know if it Worked?

You can check sp_configure and a new sys.dm_server_accelerator_status DMV to find out. Here are queries for that:

-- Checking your backup compression status-- Get backup related instance-level configuration valuesSELECT [name], [value], value_in_use, minimum, maximum, [description], is_dynamic, is_advancedFROM sys.configurations WITH (NOLOCK)WHERE [name] IN (N'backup compression default', N'backup compression algorithm', N'hardware offload config', N'hardware offload enabled', N'hardware offload mode')ORDER BY [name];-- backup compression algorithm values-- 0 = None-- 1 = MS_XPRESS (Microsoft native backup compression)-- 2 = QAT_DEFLATE (Intel QAT backup compression)-- Get detailed accelerator information using the new DMVSELECT accelerator, accelerator_desc, config, config_in_use , mode, mode_desc, mode_reason, mode_reason_desc, accelerator_hardware_detected, accelerator_library_version, accelerator_driver_versionFROM sys.dm_server_accelerator_status;

This is what it looks like on one of my systems (that does not have any Intel QAT capable hardware).

I also have a more complete script here.

Final Words

It is pretty easy to enable this new feature, and it does not require any special hardware for software mode. If you do have QAT capable hardware and SQL Server 2022 Enterprise Edition, you can use hardware mode or software mode. Which mode you choose depends on your goals.

You should be aware that if you have a QAT compressed backup (regardless of whether hardware or software mode was used for the backup), then you MUST have the Intel QAT driver installed on any system that you want to restore that backup to. You also need to follow the steps I’ve outlined in this post to enable QAT Backup Compression.

If you don’t, the restore will error out with a message like this:

Msg 17441, Level 16, State 1, Line 175 This operation requires Intel(R) QuickAssist Technology (QAT) libraries to be loaded.

I have been testing this feature (with both hardware mode and software mode) for quite some time, and I have been very impressed with the performance and reliability of this feature. I will have another post that talks about my testing experience in depth.

But don’t take my word for it. I’m just some guy on the internet. You should do your own testing of QAT backups and restores until you feel completely confident about the feature.

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter asGlennAlanBerry.Thanks for reading!

Related