Featured Database Articles

SqlCredit Part 7: Performance Impact of EncryptByCert and DecryptByCert

This is part 7 of a series. If you have not read part 6, you
can find it here.

Lets Get This Out Right Away

The task here is to determine the performance impact of EncryptByCert
and DecryptByCert. In short, the cost is high. Microsoft acknowledges this in the
Remarks section of Books Onlines
coverage of EncryptByCert, but it is not a well-publicized limitation:

This function encrypts data with
the public key of a certificate. Such asymmetric transformations are very
costly compared to encryption and decryption using a symmetric key. Asymmetric
encryption is therefore not recommended when working with large datasets such
as user data in tables.

I searched for articles that dealt with EncryptByCert and DecryptByCert
and found a number of places where people discuss the functionality with no
mention of the large datasets limitation.

The net is that EncryptByCert and DecryptByCert work for
small bits of sensitive data but are inappropriate for use in high-volume
tables like Card.

Never Fear, We Do Have Options

These performance results are valuable, even if they make it
clear that we need another solution. Next month, I will change all the EncryptByCert
and DecryptByCert calls to EncryptByKey and DecryptByKey (symmetric encryption)
and use the tests created here to allow direct comparisons.

Performance Testing

What I did was build a test that gives an indication of the
performance hit when using EncyptByCert and DecryptByCert. All of the code is
included, so please do download the code and try it out
yourself. I do not have access to any heavy machinery that I can play with, so
if you run these tests on some high-powered system with a SAN, please share
your results in the forum.

The test results here are from two different desktop-class
systems. The first is my primary development system; the second is a sandbox I
set up to try SQL Server 2005 before it was released. (As soon as I have time,
I will be loading the SQL Server 2008 CTP on that system so I can check out the
Resource Governor).

The Tests

The tests are fairly simple. They create two tables, one
with encryption (CardEncrypt) and one without (CardNoEncrypt).

There is an index on CardNoEncrypt (CardNumber1,
SecurityCode1) but no corresponding index on CardEncrypt.

In a real application, you would not want to encrypt every
column in a table, so CardEncrypt includes three encrypted columns (a bigint, a
smallint, and a varchar(36)) while the rest are unencrypted.

Test Steps:

Insert n records into each table.

Read n random records by ID.

Each test is separated by a five-second delay to allow the system
to settle between tests.

To run the tests, run BuildSqlCredit.bat, then EncryptPerfTest_Setup.sql,
and finally EncryptPerfTest_Test.sql. The Test script has one important
parameter that maps to the n, above: @createMax. The tests were run with
it set to 10,000, but when you run it on your own system, set it low to start
with.

One Running Change

For Next Time

Change all the EncryptByCert and DecryptByCert calls to
EncryptByKey and DecryptByKey (symmetric encryption) and use the tests created
here to allow direct comparisons. Encryption and decryption using symmetric
keys is the recommended approach when dealing with large amounts of data. Here
is the quote from the Books Onlines
coverage of EncryptByKey:

Symmetric encryption and decryption
is relatively fast, and is suitable for working with large amounts of data.

One very serious limitation here is that EncryptByCert, EncryptByKey,
EncryptByAsymKey, and EncryptByPassPhrase all create different results (ciphertext)
each time they are called. We will discuss message authentication codes as a
way to allow indexed look-ups of encrypted data.

Not covered yet: Last month, I said I really dont like
passing actual numbers around for StatusCd. I will change that to use UDFs to
translate between numbers and their corresponding status strings. Well get to
that eventually, but for now, the focus is on encryption.