Testing SQL Server 2022 QAT Backup Compression Software Mode on AMD

Testing SQL Server 2022 QAT Backup Compression Software Mode on AMD

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

DatabaseRuntime in minutesAlgorithmCompression rate
A 1 TB12MS_XPRESS47%
A 1 TB8QAT54%
B 2 TB24MS_XPRESS18,8%
B 2 TB20QAT18,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.

DatabaseRuntime in minutesAlgorithmCompression rate
A 1 TB                           19MS_XPRESS53%
A 1 TB18QAT55%
B 2 TB81MS_XPRESS10,7%
B 2 TB83QAT10,3%
B 2 TB unencrypted89MS_XPRESS18,8%
B 2 TB unencrypted120QAT38,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

DatabaseRuntime in minutesAlgorithm
A 1 TB                           22MS_XPRESS
A 1 TB21QAT
B 2 TB96MS_XPRESS
B 2 TB87QAT

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.

2 thoughts on “Testing SQL Server 2022 QAT Backup Compression Software Mode on AMD

  1. “In my environment with AMD hardware using software QAT compression did not show any significant improvement.”

    Your conclusion implies that AMD hardware is the root cause of the poor performance of QAT compression. However, I would suggest that you re-run your tests with a non-TDE encrypted database. The reason I say this is that I did a brief bit of QAT+TDE testing prior and found the results to be dismal. See my blog post for some detailed results. I would love to see results done on AMD hardware without TDE impacting the results.

    1. Hi Andy,

      Thanks for your advice. You are right: Without TDE I could get much better compression. However in my case this was not an option as encryption is a strict requirement.

      At the end of the day I had to come to a decision after 2 days of testing and as much as I would have loved to benefit from QAT the results in my environment especially with the LAN backup were not in favor of this additional layer of complexity.

      As I haven’t found any other blog posts on QAT with AMD at the time of my testing I decided to show my results. However it is important for every professional thinking about backup tuning to conduct his or her own tests as environments and also most importantly disk speeds will vary.

      Thanks

      Martin

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.