By
Published
Mar 13 2019 10:50 AM
1,947
Views
First published on MSDN on Apr 21, 2017
Today we want to show you the speed improvements we get by supporting the Intel AES-NI instruction set for transparent data encryption ( TDE ) on Windows Azure. This instruction set reduces the CPU overhead of turning on Transparent Data Encryption for SQL Server databases.
For the testing scenario we used an Azure DS15 virtual machine with 40 CPUs and 140 GB of Memory. All 16 Disk drives were SSDs, the log drive a RAID 1 over 2 SSDs. The test were performed against SQL Server 2014 and SQL Server 2016 with a 1TB SAP Database. All 4 Encryption algorithms (AES_128, AES_192, AES_256 and TRIPLE_DES) were used, for TRIPLE_DES on SQL Server 2016 the database has to be in compatibility mode for SQL Server 2014 (120) (*) as this algorithm was deprecated in SQL Server 2016.
In this graph you see that the encryption and decryption of this 1TB database always run faster on SQL Server 2016 ((*) except for the Triple_DES algorithm that is only available in the SQL Server 2014 compatibility mode on SQL Server 2016). The decrease in runtime goes up to 67 % (AES_192 Decryption) , the average is 38% without Triple_DES (22,5 % with Triple_DES). Means SQL Server 2016 improves the encryption / decryption speed by 38% just by making use of the Intel AES-NI instruction set.
For the load test we used a DBCC and a DBCC with the physical_only option. These were the measured run times:
One can see that the run times on SQL Server 2016 (green) are much smaller due to the hardware support of the Intel AES-NI chip set and the changes for DBCC in SQL Server 2016. The execution times for the three AES algorithm do not depend very much the algorithm, the times are nearly the same encrypted or not encrypted. Even the deprecated algorithm TRIPLE_DES is on SQL Server 2016 faster than the default algorithm AES_256 on SQL Server 2014.
Building an average over all the algorithms and the 4 executions (Normal, physical_only, encrypted, encrypted and physical_only) the picture is even clearer:
The difference between the encrypted and not encrypted run (normal or physical_only) is on SQL Server 2014 much higher than on SQL Server 2016, means in our test SQL Server 2014 needed an hour more time (1:27 h to 2:22 h, 38,8 % increase) for the encrypted case whereas SQL Server 2016 only needed 10 minutes more (0:49 h to 0:59 h, 16,9 % increase). The overhead that is added through TDE (difference between blue and gray or between orange and yellow) is in SQL Server 2016 much smaller than in SQL Server 2014.
SQL Server 2016 is able to detect and to leverage the Intel AES-NI instruction set on the Azure virtual machine and to cut so the overhead of transparent data encryption in half.
Today we want to show you the speed improvements we get by supporting the Intel AES-NI instruction set for transparent data encryption ( TDE ) on Windows Azure. This instruction set reduces the CPU overhead of turning on Transparent Data Encryption for SQL Server databases.
For the testing scenario we used an Azure DS15 virtual machine with 40 CPUs and 140 GB of Memory. All 16 Disk drives were SSDs, the log drive a RAID 1 over 2 SSDs. The test were performed against SQL Server 2014 and SQL Server 2016 with a 1TB SAP Database. All 4 Encryption algorithms (AES_128, AES_192, AES_256 and TRIPLE_DES) were used, for TRIPLE_DES on SQL Server 2016 the database has to be in compatibility mode for SQL Server 2014 (120) (*) as this algorithm was deprecated in SQL Server 2016.
In this graph you see that the encryption and decryption of this 1TB database always run faster on SQL Server 2016 ((*) except for the Triple_DES algorithm that is only available in the SQL Server 2014 compatibility mode on SQL Server 2016). The decrease in runtime goes up to 67 % (AES_192 Decryption) , the average is 38% without Triple_DES (22,5 % with Triple_DES). Means SQL Server 2016 improves the encryption / decryption speed by 38% just by making use of the Intel AES-NI instruction set.
For the load test we used a DBCC and a DBCC with the physical_only option. These were the measured run times:
One can see that the run times on SQL Server 2016 (green) are much smaller due to the hardware support of the Intel AES-NI chip set and the changes for DBCC in SQL Server 2016. The execution times for the three AES algorithm do not depend very much the algorithm, the times are nearly the same encrypted or not encrypted. Even the deprecated algorithm TRIPLE_DES is on SQL Server 2016 faster than the default algorithm AES_256 on SQL Server 2014.
Building an average over all the algorithms and the 4 executions (Normal, physical_only, encrypted, encrypted and physical_only) the picture is even clearer:
The difference between the encrypted and not encrypted run (normal or physical_only) is on SQL Server 2014 much higher than on SQL Server 2016, means in our test SQL Server 2014 needed an hour more time (1:27 h to 2:22 h, 38,8 % increase) for the encrypted case whereas SQL Server 2016 only needed 10 minutes more (0:49 h to 0:59 h, 16,9 % increase). The overhead that is added through TDE (difference between blue and gray or between orange and yellow) is in SQL Server 2016 much smaller than in SQL Server 2014.
SQL Server 2016 is able to detect and to leverage the Intel AES-NI instruction set on the Azure virtual machine and to cut so the overhead of transparent data encryption in half.