Martin Smith – StackOverFlow – DECRYPTBYKEY slower on SQL Server 2014 than SQL Server 2012

Intro

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

Link

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?

Continue reading