Transparent Data Encryption (TDE) encrypts database files to secure your data. It also encrypts the tempdb database to secure your data in a temporary space. The process of encryption and decryption adds additional overhead to the database system. Even non-encrypted databases hosted on the same SQL Server instance would have some performance degradation because of tempdb encryption. Today I will show you performance impact analysis using few simple T-SQL statements by comparing their stats gathered before and after enabling TDE.
I will execute T-SQL statements (INSERT, UPDATE, SELECT, BACKUP DATABASE) before and after enabling encryption (Transparent Data Encryption) and gather their performance statistics during each execution. Finally, once we will have performance stats taken before and after enabling encryption then we will compare them to understand the performance impact analysis. I have used DBCC DROPCLEANBUFFERS before executing each query to clean the buffer cache.
Performance analysis will be observed based on the below 3 parameters:
- CPU time
- Physical_io
- Elapsed time
CPU time is the total time a process has taken on CPU whereas elapsed time is the total end-to-end duration of that task. Physical_IO is the number of disks reads and writes performed by that process.
After reading this article, you will reach on a conclusion whether TDE put some additional workload on database transactions, or it is a myth. I will use very simple straightforward T-SQL statements to keep it very simple to execute on the system.
System performance depends on various factors like your system configuration, existing workload, poor application programming, and few advanced configurations, etc. I have not made any changes in the system except enabling TDE for our target database. The rest of all factors was the same before and after enabling encryption.
I have divided this analysis into 3 sections.
-
Analyze Performance before enabling Transparent Data Encryption
- INSERT operations
- UPDATE statements
- SELECT Statements
- Database Backups
- Enable Transparent Data Encryption
-
Analyze Performance after enabling Transparent Data Encryption
- INSERT operations
- UPDATE statements
- SELECT Statements
- Database Backups
I will gather performance stats of each operation mentioned above before enabling TDE in the first section then I will enable encryption on user database testdb on which I am doing this testing and finally I will again gather performance stats of each operation post enabling the encryption.
Preparation
Create a database named TESTDB for this testing followed by creating a row table testable with 3 columns code, name, and date. Run the below statements to create them during your testing.
|
1 2 3 4 5 6 7 8 9 |
--Sample database and table creation CREATE DATABASE TESTDB GO USE TESTDB GO CREATE TABLE testtable ( [code] INT IDENTITY, [date] DATETIME DEFAULT GETDATE (), [name] CHAR (25) DEFAULT 'SQLSHACK'); |
Now, I will INSERT, UPDATE and SELECT data in this table and collect their performance stats for further analysis.
Analyze Performance before enabling Transparent Data Encryption
This section will collect performance statistics of each T-SQL statement given below. I have used SET STATISTICS TIME and SET STATISTICS IO statements to gather their details. Let’s start with the INSERT statement.
Performance statistics during the INSERT operation
Run below INSERT statement to insert 10000 rows in above newly created table testtable. Use SET STATISTICS TIME and SET STATISTICS IO statements to gather their performance stats details.
|
1 2 3 4 5 6 7 8 9 |
USE Testdb GO SET STATISTICS TIME ON SET STATISTICS IO ON GO INSERT INTO testtable DEFAULT VALUES ; GO 10000 SET STATISTICS IO OFF SET STATISTICS TIME OFF |
Here is the output of the above statement.
Session id for the above execution is showing as 56 so I have retrieved performance statistics of this session from the sysprocesses system view using the below T-SQL statement.
|
1 2 3 4 |
SELECT spid, cpu, physical_io, memusage, st.text FROM sysprocesses AS sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) st WHERE spid IN (56) |
Above statements have returned below output:
Let’s run the UPDATE statement and gather similar details.
Performance statistics during the UPDATE operation
I used DBCC DROPCLEANBUFFERS before running these statements to ensure we are gathering correct details for comparison. Run below T-SQL statements to update above inserted rows.
|
1 2 3 4 5 6 7 8 9 10 |
USE Testdb GO SET STATISTICS TIME ON SET STATISTICS IO ON GO UPDATE testtable SET name='SQL Server' GO SET STATISTICS IO OFF SET STATISTICS TIME OFF |
Here are the performance statistics of the above execution.
I also checked the sysprocesses table for the above session-id 57 to see its other performance stats.
Performance statistics during the SELECT operation
I have also gathered performance stats for the SELECT statement. I ran the below SELECT statement to return all rows we have inserted and updated in the above steps.
|
1 2 3 4 5 6 7 8 9 |
USE testdb GO SET STATISTICS TIME ON SET STATISTICS IO ON GO SELECT code, name, date FROM testtable Go SET STATISTICS IO OFF SET STATISTICS TIME OFF |
Above execution has returned with the below statistics.
I am gathering details from the sysprocesses system view for each session id so here also I have done the same and here is its output.
Performance statistics during Backup operations
I have also executed a database backup and observed its performance stats. I have executed the below statements to capture these details during a database backup.
|
1 2 3 4 5 6 7 8 |
SET STATISTICS TIME ON SET STATISTICS IO ON GO BACKUP DATABASE testdb TO DISK =’C:\program files\testdb.bak Go SET STATISTICS IO OFF SET STATISTICS TIME OFF |
Have a look at this output:
System object sysprocesses has below details captured for this session id 70.
Next, I will enable transparent data encryption on this database TestDB and then I will restart SQL Server. Once the SQL Server instance will come online, I will execute the same queries in the same sequence to gather their performance statistics to see their comparison.
Enable Transparent Data Encryption
This section will help us to enable TDE on user database TESTDB on which we are studying a performance analysis before and after enabling encryption (Transparent Data Encryption).
Run below set of T-SQL statements to enable TDE on user database TESTDB.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
--Create master key USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD='$ql$h@ck@12'; GO --Create Certificate using above master key CREATE CERTIFICATE TestDB_TDECert WITH SUBJECT='TestDB_Encryption'; GO --Create a database encryption key USE TestDB GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TestDB_TDECert; GO --Enable encryption ALTER DATABASE TestDB SET ENCRYPTION ON; GO |
Once you will execute the above statements, TDE will be enabled for database TESTDB. Next, check and verify TESTDB database has been encrypted using transparent data encryption or not. I have executed the below statements to return all encrypted databases from this SQL Server instance.
|
1 2 3 4 |
--Check TDE for user database SELECT DB_Name(database_id) As [DB Name], encryption_state_desc FROM sys.dm_database_encryption_keys Go |
Here is its output which shows user database TESTDB has been ENCRYPTED now. As we know tempdb database also gets encrypted if we enable transparent data encryption for any user database that’s why tempdb is showing as ENCRYPTED in the below image.
Now, we have enabled TDE on the user database TESTDB. Next, we will repeat the same activities which we have done in the first section of this article to gather performance statistics of each execution of INSERT, UPDATE, SELECT and BACKUP DATABASE. Restart your SQL Server instance to gather performance stats from scratch. Although it’s not mandatory I did it during this analysis.
Analyze Performance after enabling Transparent Data Encryption
I will not give T-SQL statements again in this section as we are going to execute the same statements that we have executed in the first section. Use the same statements after enabling encryption which you have executed for INSERT, UPDATE, SELECT, and BACKUP DATABASE before enabling it as showing in the first section. If you are performing your analysis on some different set of statements, then also use the same statements before and after enabling TDE on the database.
Performance statistics during INSERT operations
I copied the query from the first section for INSERT operation and executed it to get the below stats.
I also fetched details from the sysprocesses system object for this session id 55. I got the below result from the sysprocesses system view.
Now you can compare its details with the one we have captured in the first section. Below are our findings in comparing a simple INSERT statement execution:
- CPU time is increased from 2078 ms to 2091 ms
- Physical_io is also increased 194 to 483
- Elapsed time has increased from 44 sec to 46 sec
The above findings clearly show an increase in its utilization and execution time post enabling TDE on the user database. Let’s go and check how the performance stats for UPDATE statement are showing in the next section.
Performance statistics during UPDATE operations
Again, use the same UPDATE T-SQL statement which you have executed to gather performance statistics before enabling transparent data encryption in the first section. I executed the same script and got the below output.
I again checked details of session id 65 from the sysprocesses system object. Please see its output.
Here are the findings for UPDATE statement execution.
- CPU time is the same as it was before enabling encryption
- Elapsed time has significantly increased from 32 ms to 259ms after enabling encryption
- Physical_IO is also increased from 146 to 169 after encryption
Performance statistics during SELECT operations
Let’s analyze the SELECT statement. Execute the same T-SQL statement which you have used in the first section to gather its performance statistics.
Other performance stats from the sysprocesses system table for session id 52 under which the above transaction was executed is showing below.
Find the observations for SELECT statement post enabling transparent data encryption on user database TESTDB.
- CPU time increased from 0 to 16ms post encryption
- Elapsed time has increased from 242 ms to 244ms after enabling encryption
- Physical_IO has surprisingly reduced from 248 to 162 after encryption. There could be multiple factors for this. It is not mandatory that every time physical_io will be reduced
Performance statistics during Backup operations
The final use case is database backup. Run a database backup using the same script which you have used before enabling TDE and see the difference in performance stats. Here is my output for this execution.
System object sysprocesses has below details for above session id 72.
Below are the findings for the BACKUP DATABASE statement post enabling transparent data encryption on user database TESTDB.
- CPU time increased from 124ms to 185ms post encryption
- Elapsed time has increased from 791ms to 848ms after enabling encryption
- Physical_IO has also increased from 2317 to 2950 after encryption
Conclusion
I did a simple comparison on a few DML statements and database backup to see TDE performance impacts whether has any effect on database performance or not. We can see Transparent Data Encryption has increased overhead on CPU and physical io on each of this execution we have tested in this analysis. I have also given a line-by-line comparison for each execution in the below table. Have a look at them and you will come to know that this encryption will put some extra burden on your database system.
|
Type of Operations |
Performance Statistics before enabling TDE |
Performance Statistics after enabling TDE |
||||
|
CPU ms |
Elapsed Time ms |
Physical read |
CPU ms |
Elapsed Time ms |
Physical read |
|
|
INSERT |
2078 |
44 sec |
194 |
2091 |
46sec |
483 |
|
UPDATE |
31 |
32 |
146 |
15 |
259 |
169 |
|
SELECT |
0 |
242 |
248 |
16 |
244 |
162 |
|
Backup |
124 |
791 |
2317 |
185 |
848 |
2950 |
He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.
You can also find him on LinkedIn
View all posts by Manvendra Singh
- Configure backup storage redundancy for Azure Cosmos DB account - May 15, 2024
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023