Performance Implications of encrypted data in a Clustered Columnstore Index

Question

I am using SQL Server 2016 and expecting some details on below points regarding performance point of view:

1. Is there any performance gain or additional benefit on creating a primary key - unique constraint on same table on which a clustered columnstore index is already created?

2. If my table contains encrypted columns (out of 10 columns, 9 columns are encrypted), is there any benefit in case of creating clustered columnstore index? I am using SYMMETRIC KEY for encryption.

Answer 1

It's best to avoid asking multiple, unrelated questions in a single question when possible. Your first question is very broad. In general, creating an index is always a trade-off and some queries will benefit from that index. The same is true for NCIs on columnstore tables. I'll focus on the second question instead.

As far as I can tell, encryption by SYMMETRIC KEY can be a very poor match for columnstore. Inserting encrypted data into a CCI means that you're inserting long, relatively unique strings into varbinary columns. CCIs don't do well with those data types and data distributions. You're likely to hit dictionary pressure which will limit the size of your rowgroups and you'll miss out on most of the features of columnstore, except for column elimination.

I found the code at How to use SQL Server Encryption with Symmetric Keys helpful in developing a simple example. I don't know what your data looks like, so I'll just insert unique integers into a table and insert the same unique integers with encryption into a different table. Definition of the key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'you will never guess this';

CREATE CERTIFICATE MyFirstCertificate
WITH SUBJECT = 'no u';

CREATE SYMMETRIC KEY key_to_answering_217242 WITH
IDENTITY_VALUE = 'yeah nah',
ALGORITHM = AES_256,
KEY_SOURCE = 'closet full of jandals'
ENCRYPTION BY CERTIFICATE MyFirstCertificate;

T-SQL to create two tables and insert data into both of them:

DROP TABLE IF EXISTS dbo.CCI_NOT_ENCRYPTED;

CREATE TABLE dbo.CCI_NOT_ENCRYPTED (
ID BIGINT,
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_NOT_ENCRYPTED WITH (TABLOCK)
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

GO

USE master;
GO

OPEN SYMMETRIC KEY key_to_answering_217242
DECRYPTION BY CERTIFICATE MyFirstCertificate


DROP TABLE IF EXISTS [TEST].dbo.CCI_ENCRYPTED;

CREATE TABLE [TEST].dbo.CCI_ENCRYPTED (
ID VARBINARY(256),
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO [TEST].dbo.CCI_ENCRYPTED WITH (TABLOCK)
SELECT EncryptByKey(Key_GUID('key_to_answering_217242'), CAST(RN AS VARCHAR(7)))
FROM (
    SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

CLOSE SYMMETRIC KEY key_to_answering_217242;

GO

There's a clear difference in total table size. The table with encrypted data is over 20X bigger:

Looking at sys.dm_db_column_store_row_group_physical_stats, we can see that the table with encrypted data had its rowgroup size limited by dictionary pressure:

Query performance will of course be greatly impacted as well. The following query takes approximately 0 ms of CPU time:

SELECT COUNT(*)
FROM dbo.CCI_NOT_ENCRYPTED
WHERE ID = 1;

If I run that same query on the encrypted table:

USE master;
GO

OPEN SYMMETRIC KEY key_to_answering_217242
DECRYPTION BY CERTIFICATE MyFirstCertificate

SELECT COUNT(*)
FROM [TEST].dbo.CCI_ENCRYPTED
WHERE CAST(CAST(DecryptByKey(ID) AS VARCHAR(7)) AS BIGINT) = 1;

CLOSE SYMMETRIC KEY key_to_answering_217242;

GO

It takes over 600 ms of CPU time. With all of that said, there appears to be a large overhead in general in working with encrypted data. I cannot say if a columnstore will offer any benefit for your exact data and scenario, only that you definitely won't see the usual benefits of columnstore.

Answer 2

That sounds like a strange case for a columnstore index. Anyhow:

1: For a columnstore index you get the compression of data, as well as posslibly segment elimination. But there are no such thing as a seek in a columnstore index. So, if you have queries with high selectivity searching on those columns and preformance is important, then yes - having row indexes (that comes with PK and UQ) can be beneficial.

2: I have a feeling that you mean "encrypted" and not "decprypted"? You don't say which technology you are using, but since the data isn't in clear-text, then there is little SQL Server can do to help you with your queries, so I doubt that columnstore will be beneficial here. But we have very little information to go on...