In our last post we spoke a bit about encryption, specifically sys.fn_varbintohexsubstring.
As I googled to find supportive and collaborative sources, I ran into Martin Smith’s question on encryption.
As Martin Smith’s word are far better, it does make sense to try to distill it.
Here are his words…
Martin Smith – DECRYPTBYKEY slower on SQL Server 2014 than SQL Server 2012
We have been using symmetric keys for encryption/decryption on some SQL Server 2012 instances for a few years. We recently installed some new instances of SQL Server 2014 and came across some performance issues decrypting the data on SQL Server 2014 installations.
Consider a table looking like this:
CREATE TABLE [dbo].[tblCertTest] ( [article_id_enc] [varbinary](100) NOT NULL , [article_id] [int] NULL ) ON [PRIMARY]
And keys and certificates created like this:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passwrrrrd12' CREATE CERTIFICATE MyCertificateName WITH SUBJECT = 'A label for this certificate' CREATE SYMMETRIC KEY MySymmetricKeyName WITH IDENTITY_VALUE = 'a fairly secure name', ALGORITHM = AES_256, KEY_SOURCE = 'a very secure strong password or phrase' ENCRYPTION BY CERTIFICATE MyCertificateName;
Our data set has about 90000 rows, article_id is a 5 digit number. A bit simplified, article_id_enc is encrypted with the following command:
update tblCertTest set article_id_enc = ENCRYPTBYKEY( KEY_GUID('MySymmetricKeyName') ,convert(varbinary(100), article_id) )
We have applied all available hotfixes, we’ve tried with different instances of both SQL Server 2012 and SQL Server 2014 with different setups like ssd disks, ram disks etc.
We have tried the query locally on the SQL Server and remote.
Execution plans and indexes are the same on all servers.
This SELECT statement takes about 50 ms on any SQL Server 2012 server, including simple development machines. On any SQL Server 2014 server (including really powerful ones) the query takes at least 1500 ms.
OPEN SYMMETRIC KEY MySymmetricKeyName DECRYPTION BY CERTIFICATE MyCertificateName SELECT CONVERT(int, DecryptByKey(article_id_enc)) FROM dbo.tblCertTest
Any suggestions on why the query is performing so bad on SQL Server 2014? What’s changed?