I spoke on columnstore indexes in SQL 2014 last weekend at SQL Saturday #262 in Boston—I had an audience question come up about using Transparent Data Encryption (TDE) in conjunction with columnstore indexes. Of the top of my head, I couldn’t think of any reason why it would be a problem—I was correct, but I also wanted to test it out and check the performance and compression levels. I used the standard BigTransactionHistory table (courtesy of Adam Machanic b|t) for all testing.
Testing
The first thing I did was create a new database with Transparent Data Encryption turned on.
USE
master;
GO
CREATE
DATABASE TDE_TEST;
GO
CREATE
MASTER
KEY
ENCRYPTION
BY
PASSWORD
=
‘Demo4Blog’;
GO
CREATE
CERTIFICATE DemoBlog WITH
SUBJECT
=
‘DemoBlogCert’
GO
USE TDE_Test
GO
CREATE
DATABASE
ENCRYPTION
KEY
WITH
ALGORITHM
=
AES_128
ENCRYPTION
BY
SERVER
CERTIFICATE DemoBlog
GO
ALTER
DATABASE TDE_Test
SET
ENCRYPTION
ON
GO
Next, I created a table and a clustered columnstore index in my new database.
select
*
into tde_test.dbo.TDE_CS_Test from bigtransaction_cs;
create
clustered
columnstore
index CCI_TDE_Test on tde_test.dbo.TDE_CS_Test;
BigTransaction_CS is my source table which lives in the unencrypted AdventureWorks2012 database—I’m just making a copy of it in my new encrypted TDE_Test database which was created above.
Size Matters
Using a query to find the sizes of objects in my databases I examined the size of each object.
TableName | indexName | RowCounts | TotalPages | TotalSpaceMB |
Columnstore | ClusteredColumnStoreIndex-20140219-151205 |
62527202 |
38570 |
301 |
Encrypted_Columnstore | CCI_TDE_Test |
62527202 |
43322 |
338 |
TDE_NoCompression | NULL |
31263601 |
143441 |
1120 |
Clustered_NoEncrypt | pk_bigTransactionHistory |
31263601 |
143693 |
1122 |
As you can see—the row counts are the same, but the page counts and space used is higher—this is one of the penalties of TDE—the data won’t compress as well, however the difference in compression is only about about 10%. Despite this the compression effects are rather dramatic, even with encryption. The below chart shows the compressed versus uncompressed sizes of the data.
Figure 1 Size of uncompressed and compressed tables with and without encryption
Performance
In my demos on this topic, I have a fairly standard query that I use. Each query was run 6 times, with the last 5 runs counting for the data.
SELECT transactiondate
,avg(quantity)
,avg(actualcost)
FROM bigtransaction_cs
WHERE TransactionDate <
‘2007-07-01’
AND Quantity > 70
AND Quantity < 92
GROUP
BY TransactionDate
ORDER
BY transactionDate;
Encryption typically does negatively impact query performance, due to the CPU overhead, and it is clearly illustrated below.
Figure 2 Query Performance with Encryption
There’s roughly a 36% performance degradation using TDE—based on the logical read counts from the queries, it is all related the overhead and not really affected by the smaller difference in compression.
Summary
Generally speaking, if you are using encryption there is some legal or regulatory reason why you are using it, and you don’t have any other options. Just be aware, that you can still get pretty good compression levels using it in conjunction with clustered columnstore indexes in SQL Server 2014, even though your performance may not be as amazing as it otherwise would be.
Did this get cleaned up when 2016 allowed for better compression with TDE?
I don’t think anything changed, but I also haven’t tested.