Improving Performance of T-SQL Statements with Encrypted Columns

I’ve always seen database encryption as a bit of a double-edged sword. It’s great for security, but it can absolutely kill performance if you’re not careful. We were working on a project where we had to encrypt sensitive columns like Social Security Numbers (SSNs), and it didn’t take long for us to realize that the database was struggling just to do simple lookups.

The problem is that once a column is encrypted, the database can't use standard indexes to find a specific row. It has to decrypt every single value in that column just to see if it matches your search criteria. It's a massive CPU drain and makes simple JOINs or WHERE clauses incredibly slow.

We encountered a major performance bottleneck in a previous project involving Social Security Numbers (SSNs). Database SELECT operations filtered by SSN were extremely slow because the engine was performing full-column decryptions for every comparison. Looking at the execution metrics below, the initial query required over 4,400 logical reads and took 302 milliseconds to complete.

The Strategy: Partial Clear-Text Indexing

To resolve this, we implemented a hybrid approach: we stored the last four digits of the SSN in a clear-text column and created a non-clustered index on it. By first filtering the dataset using the indexed last four digits and then applying the full SSN decryption only to the resulting subset, we achieved a massive performance gain.

Current Problem

begin
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

declare @ssn varchar(max) = '123123123'
with PersonalIdentifierInformationCTE as
(
select CONVERT (CHAR(32), DECRYPTBYKEYAUTOCERT(CERT_ID('CertificateName'), NULL, Identifier)) as Identifier
from PersonalIdentifierInformation
)
select *
from PersonalIdentifierInformationCTE where Identifier is not null
and Identifier=@ssn
end

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(8 row(s) affected)
Table 'PersonalIdentifierInformation'. Scan count 1, logical reads 4413, 
physical reads 3, read-ahead reads 4406, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 302 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Improved Solution

begin
declare @ssn varchar(max) = '123123123'

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

with PersonalIdentifierInformationCTE as 
(
select CONVERT (CHAR(32), DECRYPTBYKEYAUTOCERT(CERT_ID('CertificateName'), NULL, Identifier)) as Identifier, SSNLast4
from PersonalIdentifierInformation
)
select *
from PersonalIdentifierInformationCTE 
where SSNLast4<> 0 and SSNLast4 = substring(@ssn, LEN(@ssn)-4+1, LEN(@ssn)) 
and Identifier is not null
and  Identifier=@ssn
end

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 13 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 16 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
    (8 row(s) affected)
Table 'PersonalIdentifierInformation'. Scan count 1, logical reads 27, 
physical reads 10, read-ahead reads 8, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
    (1 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 71 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Summary

By implementing this strategy, logical reads were reduced from 4,413 down to just 27, and elapsed time dropped from 302ms to 71ms. This demonstrates that with clever indexing and partial data exposure, we can maintain strong encryption standards without sacrificing system performance.