Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How much overhead does encryption add to a SQL Server query


By:   |   Read Comments (4)   |   Related Tips: More > Encryption

Problem

From visual inspection alone, one can deduce that decrypting an encrypted column using the DecryptByKey function in a SELECT statement will take longer than selecting the column stored in plain text. If you have been in the information technology industry long enough, you have probably heard the expression "this adds overhead" when discussing any extra processing added on to what is considered normal processing. I have heard this expression also, which leads me to ask the question "How can we quantify this additional overhead?"

Solution

In this tip we will explore how much additional processing time is added when decrypting an encrypted column in a T-SQL query. To run this experiment, I created a table using the command below. One column will hold a fictitious 16 digit credit card number while the other column will hold the encrypted version of the credit card number.

CREATE TABLE dbo.tblCustomerData
(
   CustomerID  int identity(1,1) NOT NULL,
   CreditCardNumberPlainText varchar(16) NOT NULL,
   CreditCardNumberEncrypted varbinary(MAX) NULL
)

For the next few steps, I used Nitansh Agarwal's tip on SQL Server Column Level Encryption Using Symmetric Keys as a guide.

I inserted 10,000,000 records into the table by running the following T-SQL code.

declare @i int;
set @i=0;
begin transaction 
   while @i<10000000
   begin
      INSERT INTO dbo.tblCustomerData with (tablock) 
      VALUES (cast((1000000000000000+@i) as varchar(16)),NULL)
      set @i=@i+1
   end
commit
GO

Next, I created the master encryption key. Of course, my password should be more complicated than the one shown below.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcdefghijklmnopqrstuvwxyz0123456789';
GO

Then, I created a certificate named Certificate1.

CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Encrypt Credit Card Number';
GO

Next, I created a symmetric key named SymmetricKey1 using the AES_128 algorithm and the certificate created in the previous step.

USE Tips;
GO
CREATE SYMMETRIC KEY SymmetricKey1 
WITH ALGORITHM = AES_128 
ENCRYPTION BY CERTIFICATE Certificate1;
GO

After creating the symmetric key, I updated the CreditCardNumberEncrypted column with the encrypted credit card number using the EncryptByKey function as shown in the follow T-SQL query.

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
UPDATE tblCustomerData
SET CreditCardNumberEncrypted=
    EncryptByKey(Key_GUID('SymmetricKey1'),CreditCardNumberPlainText)
FROM dbo.tblCustomerData;
GO
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Now, with our table loaded with plain text and encrypted credit card numbers, I created two SSIS packages to read from the table and write to a file. The only difference between the two packages is that one package selects the plain text column while the other package decrypts the encrypted column using the DecryptByKey function and then converts the result to a varchar(16). This results in the same amount of data to be written to the disk. For both packages, a variable will be used to pass in the number of records to select from the table.

The image below shows the query in the OLE DB Source Editor from the package that selects the plain text column. Notice how the query is stored as a string variable within the package.

The OLE DB Source Editor

The next image shows the query in the OLE DB Source Editor from the package that decrypts the encrypted column. Notice how the symmetric key must be opened before the SELECT statement and the use of the DecryptByKey function.

Notice how the symmetric key must be opened before the SELECT statement and the use of the DecryptByKey function.

A SQL Server Agent Job was created to run the packages with each step alternating between the package that had to perform the decryption of the credit card number and the package that selected the plain text credit card number. Both packages were run three times each with a specified number of records beginning with 1 million and incrementing in steps of 1 million until the packages were run with 10 million records. The image below shows the first 10 steps of the job.

A job was created to run the packages with each step alternating between the package that had to perform the decryption of the credit card number and the package that selected the plain text credit card

Results

The average processing times for each package for each different number of selected records were calculated by querying the dbo.sysjobhistory table in the msdb database. The results are shown in the table below.

Number of Records in Millions

Average Processing Times in Seconds for Selecting Plain Text

Average Processing Times in Seconds Using the DecryptByKey Function

1

1.167

4.000

2

2.500

6.500

3

3.667

10.668

4

4.875

13.375

5

7.333

16.500

6

8.667

19.832

7

11.833

23.163

8

12.167

28.082

9

14.083

30.335

10

15.250

33.498

The chart below visualizes the processing times. As shown in the chart, the processing times when calling the DecryptByKey function were more than doubled when averaged across all volumes of data.

The processing times when calling the DecryptByKey function were more than doubled when averaged across all volumes of data.
Summary

As we have seen in this tip, the additional processing time when using the DecryptByKey function becomes quite noticeable when working with large volumes of data, such as performing an extract of all records from a table containing over 1 million records. However, the additional processing time encountered when using the DecryptByKey function on one column for a single record will be virtually unnoticeable.

Next Steps
Please look at these other tips on MSSQLTips.com to learn more about column level encryption and querying job history data.

Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, October 24, 2014 - 11:01:09 AM - Bernie Black Back To Top

Good article.  Column level encryption is in my near future I fear.....the way of the world.  I'm reminded of a TV interview where a security expert said: "There are 2 types of companies: Ones that know they've been hacked by the Chinese and ones that DONT know they've been hacked by the Chinese".


Friday, April 11, 2014 - 10:53:42 AM - Kulwant Back To Top

It would also be useful to know what version of SQL Server this example was run on and more importantly which CPU?

Modern CPU's include specific instructions for decryption tasks, whereas older CPUs have to carry out the same work 'long-hand'.


Wednesday, April 09, 2014 - 10:36:57 AM - CBEAMON Back To Top

Excellent  article.   I did not realize how easy it was to incorporate encrypted data in a database.  With the small volume of data that I work with, performance degredation with encryption enabled would go unnoticed.   I plan to give it a shot soon. 


Wednesday, April 02, 2014 - 8:47:46 AM - Jason Carter Back To Top

Looking at your numbers, is it safe to assume that 1 million rows, the cost is proportionally higher that at 10 million rows.

1 mill = 3.42%

10 mil = 2.19%

 

 


Learn more about SQL Server tools