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.
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!
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 methods
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
EXEC sp_configure 'hardware offload enabled', 1;
GO
RECONFIGURE
GO
-- 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 hardware
ALTER 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 values
SELECT [name], [value], value_in_use, minimum, maximum,
[description], is_dynamic, is_advanced
FROM 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 DMV
SELECT accelerator, accelerator_desc, config, config_in_use , mode, mode_desc,
mode_reason, mode_reason_desc, accelerator_hardware_detected,
accelerator_library_version, accelerator_driver_version
FROM 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 as GlennAlanBerry. Thanks for reading!
Hi Glenn,
thanks for this great blog post. You write for QAT Software Mode “Does not require any specialized hardware”. Does this mean I can also run QAT Software Mode on AMD processors? Do you see any major roadblocks for the usage on AMD processors?
Best regards
Martin
Yes, you can run QAT database backups on AMD processors. As my blog post discussed, you just have to install the Intel QAT drivers and then enable QAT in SQL Server, and then use the QAT_DEFLATE option in the backup command.
Using software mode will use your processor cores to do the backup compression (just like legacy Microsoft backup compression does). If you do a striped backup to many backup files, your CPU utilization will go up during the backup.
Hi Glenn,
Do you know if this works also with native SQL logshipping?
Regards,
Bastiaan van Utrecht
I believe (but have not confirmed yet) that if you set the instance-level default backup compression method to QAT and you set log shipping to use backup compression, that it will use QAT backup compression. You will need to have the QAT drivers installed and QAT backup compression enabled on the source instance and any secondary instances that are log shipping targets.
Hi Glenn, this is a great video. We have a 30TB database on an AWS EC2 instance and having issues with the long backup/restore times. Would Intel QAT help with this? It is scheduled to be migrated to SQL 2019 (on a faster EC2 instance), however was thinking it would be better to go to SQL 2022 to take advantage of QAT. Thanks, Scott
Intel QAT backup compression might help (potentially a lot), depending on how many data files you have, where they are located, whether you do a striped backup, where the backup files go, how compressible your data is, and how your EC2 storage is set up.
If you are on an older version of SQL Server (older than SQL Server 2019), I would definitely choose SQL Server 2022 rather than SQL Server 2019 at this point in time. QAT backup compression is one reason why, but SQL Server 2022 has a lot of other useful improvements (and it will be in Mainstream support for a longer time).
Your backup and restore times will be related to the read and write performance of your storage and EC2 instance. QAT might be able to reduce the duration and size of your backup through improved compression and CPU offload but it wont improve the performance of your storage. What is your current instance type and backup storage destination? What is your desired backup time or restore time.? There are some techniques and configurations to explore that can optimize this.
After setting up QAT software compression on my oldy, Latitude E6420 I found compressed backup performing 2 times faster:
BACKUP DATABASE [test_db] TO DISK = ‘test_db_no_compression.bkp’
–BACKUP DATABASE successfully processed 521 pages in 0.085 seconds (47.811 MB/sec).
BACKUP DATABASE [test_db]
TO DISK = ‘test_db_with_compression.bkp’
WITH COMPRESSION;
–BACKUP DATABASE successfully processed 521 pages in 0.050 seconds (81.279 MB/sec).
47.811 MB/sec against 81.279 MB/sec on the account of software speed – up is a remarkable improvement.
It would be truly interesting to see how this technique of moving backup I/O routines into isolated from engine compute compartment changes other I/O intensive activities, perhaps on a different database, sharing storage media with the one being baked up, for example, index rebuild / defragmentation. Once ago in our telecom application with 20 – 30 databases sharing storage media and having limited window of time to perform chores, it was one of the hot spots. Though with advent of enterprise SSD with tenth of thousands of I/O cycles / second improvement per disk it shouldn’t as big of a deal anymore. Still, it would be interesting to see.
Another question, can this be used for HADR log stream?
I thought there was an option, or is it now implied / default, got to go back to check on it.
If this improves streaming of HADR, it can be a huge win for companies with large network of POS ( Point of Sale ) to keep HARD copies synchronized, with sales traffic peaks.
Last, but not least, is this compression architecture being applied/can be configured to apply, to SQL Server table / column compression.
And if it is, it would be truly interesting to benchmark compressed data vs. uncompressed data with reasonably priced hardware compression module.
Speaking of which, any references would be greatly appreciated.
Intel QAT Backup compression in SQL Server 2022 has nothing (at all) to do with SQL Server Data Compression (PAGE, ROW, XML), Columnstore data compression, or compressing AG log streams. Depending on your storage performance, CPU performance and whether QAT hardware or software backup compression is being used, it can be useful for backup compression in some scenarios.
Unfortunately, both Intel and Microsoft have pretty much abandoned QAT Backup Compression. It is still in the product, but I don’t expect either company to make any further investments or improvements.
Great post Glenn!
We may have went too far with our setup…
We have a newer Intel server we use as a ESX vmware host. It has a Platinum processor with built in QAT tech. Recently we migrated a SQL server VM to this host to try out QAT. We were able to find the device IDS on the host for the built in QAT Co Processor on the server and allowed us to pass thru the QAT device ( on the CPU ) to the SQL server VM.
We installed the latest QAT driver on server 2019 and it picks up the QAT device in device manager.
Did we not need to do any of this?
Can we simply just install the QAT driver, disregard the warning about the device not being found and then configure SQL to use QAT? Any benefit to software vs hardware QAT?
Any benefit to doing it the way we did it? And do you actually need a QAT intel cpu to do this? I guess its a little confusing to me…whats actually doing what?
Also! Will this work with sql 2019 with Server OS 2019?
QAT backup compression only works on SQL Server 2022 and newer. The OS doesn’t matter (as long as it supports SQL Server 2022).
The only reason you need (or want) QAT Hardware compression is if you want significantly lower CPU utilization while a QAT compressed database backup is running (because the QAT co-processor is doing the backup compression instead of your regular CPU cores). Hardware QAT also requires SQL Server 2022 Enterprise Edition.
If the host CPU (whatever it is, Intel or AMD) has very fast single-threaded CPU performance, software QAT backup compression may be faster than hardware QAT backup compression (at the cost of higher CPU utilization). SQL Server 2022 Enterprise Edition
Software QAT backup compression only requires the Intel QAT driver (plus you have to go through the steps to enable it in SQL Server 2022). Software QAT backup compression will also work on SQL Server 2022 Standard Edition