Introduction

I was asked by one of my customers to have a look at the SQL Server 2022 Integrated acceleration & offloading feature. The goal of it is to provide a framework for offloading specific SQL Server workload compute to hardware devices.

 At first it seems very interresting to speed up for instance backup tasks especially for big databases.

But reading the documentation, specifically the Intel drivers information, I was disappointed, because it was specified that the CPU must support the QuickAssist Technology (IntelQAT) and for virtualized Windows Server only Hyper-V was supported.

My customer environment was not at all in this situation, running the SQL Server on VMWare and the host having CPU without this QAT feature available.

I thought than that it was not possible to use this new feature, but my customer found some blog explaining that the CPU must not specifically have this QAT feature. The acceleration can be done only on software level and not hardware.

Well I was curious than to test this solution and see if it really can save time for the backup tasks.

Let’s have a look first, what we need to install, how to install the needed drivers and how to  configure SQL Server to be able to use this  Integrated acceleration & offloading feature.

Installation of the latest IntelQAT driver 

You wiil be able to find and download the latest IntelQAT driver from  the following link:

Intel® QuickAssist Technology Driver for Windows* – HW Version 2.0

Save the IntelQAT drvier in c:\temp and unzip the QAT2.0.W.2.0.5-0014.zip file in C:\temp\IntelQAT\QAT2.0.W.2.0.5-0014 folder

To install the IntelQAT driver start the QatSetup.exe located in C:\temp\IntelQAT\QAT2.0.W.2.0.5-0014\QuickAssist\Setup 

Click [yes] to the warning, to use the software compression option. You can see that in this version it is only a warning asking if you want to use software compression because the QuickAssist Technology Accelerator option is not available on processor hardware on the system. In older driver installation wizard it was an error message, that was a bit scaring, but the warning message reassure me that it can be used even if the Host CPU have no QAT possibility.

For the driver installation, that is all what need to be done. Very easy in fact.

Configuration of the SQL Server instance

In order to configure the SQL Server instance I did the following steps:

• Enable hardware offloading, setting the server configuration option

execute sp_configure 'show advanced options', 1
RECONFIGURE
execute sp_configure 'hardware offload enabled', 1
RECONFIGURE
GO

• Restart the SQL Server service (in case of AlwaysOn setup, if your server is primary, failover the availability group to the other replica){}

• Enable accelerator hardware offloading

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

• Restart the SQL Server service

• Check if you configuration is set  correctly

Run first the following query

select *
from sys.configurations c
where c.name like 'backup compression%'
or c.name like '%offload%'
order by c.name

Check that the values of the SQL Server instance configuration parameter
hardware offload enabled = 1

Check if the IntelQAT driver is detected

select * from sys.dm_server_accelerator_status

Check if you have the QAT accelarator [mode_reason_desc] has the value ‘SOFTWARE_MODE_ACCELERATOR_HARDWARE_NOT_FOUND’
Check also the [accelerator_library_version] column to check the version of the driver detected. 

Test the backup

With normal compression algorithm = MS_XPRESS 

BACKUP DATABASE [TestDB] TO DISK = N'\\BackupShareDrive\TestDB_FULLbackup_CopyOnly_20231102_MS_XPRESS.bak' 
WITH CHECKSUM, COMPRESSION, COPY_ONLY

Processed 15956856 pages for database 'TestDB', file 'TestDB' on file 1.
Processed 5 pages for database 'TestDB', file 'TestDB_log' on file 1.
BACKUP DATABASE successfully processed 15956861 pages in 499.640 seconds (249.505 MB/sec).

Completion time: 2023-11-02T15:33:54.1892323+01:00

With IntelQAT compression algorithm = QAT_DEFLATE

BACKUP DATABASE [TestDB] TO DISK = N'\\BackupShareDrive\TestDB_FULLbackup_CopyOnly_20231102_QAT_DEFLATE.bak' 
WITH CHECKSUM, COMPRESSION (ALGORITHM = QAT_DEFLATE), COPY_ONLY,  STATS = 10 


Processed 15956704 pages for database 'TestDB', file 'TestDB' on file 1.
Processed 1 pages for database 'TestDB', file 'TestDB_log' on file 1.
BACKUP DATABASE successfully processed 15956705 pages in 238.594 seconds (522.484 MB/sec).

Completion time: 2023-11-02T15:40:47.8642423+01:00

We can notice that the backup time was reduce by 2

Check the backup information

select
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
1.0 * datediff(second,bs.backup_start_date,bs.backup_finish_date)/60 as BackupDuration_Min,
bs.backup_finish_date,
bs.backup_size,
bs.compressed_backup_size,
bs.compression_algorithm

from msdb.dbo.backupset bs
where bs.database_name = 'TestDB'

Configure the backup to have the IntelQAT software compression with algorithm = QAT_DEFLATE by default.

execute sp_configure 'backup compression algorithm', 2
RECONFIGURE

If you start now a database backup the QAT_DEFLATE compression algorithm will be apply by default. You do not have to specify it specifically on your backup command.

Conclusion

The feature is quite easy to configure. It can speed up your big database backups with very few configuration effort.

It is a mean to save time and disk space too, although the compression ratio is not really higher than the standard compression mode.

It is just annoying that the Intel QAT driver documentation specify that only processors with the QAT feature and Hyper-V virtualization is supported by this driver…but it seems to work well using the software only option.

But before deciding to go for it, of course, I do not have to tell you, please test the restore process…