For me as a DBA one of the interesting new features in SQL Server 2022 is the support for Intel QAT compression with backups with compression algorithm QAT Deflate.

Backup and Restore are bread and butter things for us DBAs. Unfortunately as time goes by and database size increases backup processes tend to run longer and longer. As these are small changes over longer time frames it typically is difficult to observe till you wake up one morning….production is not performing happy and you discover that the nightly full now stretches to finish within business hours. Long story short…every little improvement can help a lot and therefore I got excited about QAT Compression as soon as I heard it.

I have read some promising testing results from Andy Yun and Glenn Berry  and wanted to give it myself a test run as well. Glenn also has two nicely written blog posts about the setup process: How to Enable Intel QAT Backup Compression in SQL Server 2022 and How to Install the Intel QAT Driver

My Environment

My database environment however is not ideal as I am running on AMD CPUs. The system configuration is: One AMD EPYC 7313 16-Core Processor 3.00 GHz with 512 GB of RAM.

With Enterprise Edition I could leverage hardware QAT compression by adding some QAT cards to the server. Unfortunately this is not an option as the hardware is provided by a cloud provider who doesn’t offer neither QAT cards nor Intel processors with the rest of the config (most importantly NVMe storage).

Possible Legal Issues

The license conditions state

“This Software is licensed for use only in conjunction with (a) physical Intel component products, and (b) virtual (“emulated”) devices designed to appear as Intel component products to a Guest operating system running within the context of a virtual machine. Any other use of the Software, including but not limited to use with non-Intel component products, is not licensed hereunder.”

This seems logical so why should Intel produce any drivers for AMD users to profit from. However the “physical Intel component products” leaves room for interpretation as the machine has Intel Ethernet Controllers in my case,

Testing

In my scenario the backup and restore tests unfortunately did not show any significant performance or even file size gains.

The tests were performed at a time when no other significant traffic occurred at the site.

Storage Location 1: local NVMe storage
Storage Location 2: spinning disks on another server (accessed via Windows Share) over a 2,5 Gbps network connection

Test Database A: Size 1 TB
Test Database B: Size 2 TB, 80% blob content stored within SQL Server (no filestream)

Software QAT compression with SQL Server Enterprise Edition

Databases are encrypted with TDE

Results are as follows:

Local Backups

Database	Runtime in minutes	Algorithm	Compression rate
A 1 TB	12	MS_XPRESS	47%
A 1 TB	8	QAT	54%
B 2 TB	24	MS_XPRESS	18,8%
B 2 TB	20	QAT	18,4%

Local backups look decent…QAT compression saves 4 minutes of time (23% faster). However the compression rate remains pretty much the same…so no big savings here.

LAN Backups

This is the realistic scenario…backing up to a Windows Share on a different server.  As this server is provisioned with lots of storage we don’t get the NVMe luxury here…in fact these are spinning disks. Crystal Disk Mark shows sequential read and write number of roughly 1 GB/sec.

Database	Runtime in minutes	Algorithm	Compression rate
A 1 TB	19	MS_XPRESS	53%
A 1 TB	18	QAT	55%
B 2 TB	81	MS_XPRESS	10,7%
B 2 TB	83	QAT	10,3%
B 2 TB unencrypted	89	MS_XPRESS	18,8%
B 2 TB unencrypted	120	QAT	38,8%

The interesting finding was that the numbers are now much closer. The backup for database A had an acceptable throughput of 900 MB/sec. Database B with all the BLOBs got a significantly worse time and also mediocre throughput of 450 MB/sec.

For fun I also did some testing with the database unencrypted. Here both algorithms showed higher compression rates and QAT did even offer more than 20% compression gain. However in my environment this QAT Backup also took 31 minutes longer.

LAN restores

Database	Runtime in minutes	Algorithm
A 1 TB	22	MS_XPRESS
A 1 TB	21	QAT
B 2 TB	96	MS_XPRESS
B 2 TB	87	QAT

The results for restores show a slight performance gain for database B: 9 Minutes or 10% faster. Database A again is pretty much the same.

Conclusion

In my environment with AMD hardware using software QAT compression did not show any significant improvement. If you are running with AMD please don’t stop here…this is my environment and chances are that yours is different and QAT might be worth a wile.

I would have considered QAT under the following circumstances:

• Results for LAN backups show a speed or compression gain of more than 25%.
• Having an Intel NIC card qualifies as “physical Intel component” regarding the license terms

Using QAT also comes with another burden (especially in a desaster): Each and every server restoring QAT backups naturally has to have the QAT driver installed. So if Intel will adapt the QAT driver to verify Intel components in the future and you are running just on AMD….you will be screwed. There it will seem much more logical to invest in an additional QAT card if you have the chance to do so and are not blocked by your cloud vendor.