Manvendra Singh
sysprocesses details for BACKUP DATABASE statement after encryption

Performance impact analysis of enabling Transparent Data Encryption (TDE) on SQL Server

October 14, 2021 by

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.

  1. Analyze Performance before enabling Transparent Data Encryption
    • INSERT operations
    • UPDATE statements
    • SELECT Statements
    • Database Backups
  2. Enable Transparent Data Encryption
  3. 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.

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.

Here is the output of the above statement.

Performance stats of INSERT statement before data encryption

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.

Above statements have returned below output:

Check sysprocesses for INSERT statement

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.

Here are the performance statistics of the above execution.

Performance stats for UPDATE statement before encryption

I also checked the sysprocesses table for the above session-id 57 to see its other performance stats.

sysprocesses for UPDATE statement

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.

Above execution has returned with the below statistics.

Performance stats for SELECT statement before encryption

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.

sysprocesses for SELECT statement

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.

Have a look at this output:

BACKUP DATABASE performance stats before enabling Transparent Data Encryption

System object sysprocesses has below details captured for this session id 70.

sysprocesses for backup database

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.

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.

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.

Check Transparent Data Encryption

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.

INSERT statement performance stats after TDE

I also fetched details from the sysprocesses system object for this session id 55. I got the below result from the sysprocesses system view.

sysprocesses for INSERT statement post encryption

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.

Performance stats for UPDATE statement post encryption

I again checked details of session id 65 from the sysprocesses system object. Please see its output.

sysprocesses details for UPDATE statement after encryption

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.

SELECT statement performance stats after encryption

Other performance stats from the sysprocesses system table for session id 52 under which the above transaction was executed is showing below.

sysprocesses details for SELECT statement after encryption

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.

performance stats of BACKUP DATABASE statement after enabling Transparent Data Encryption

System object sysprocesses has below details for above session id 72.

sysprocesses details for BACKUP DATABASE statement after encryption

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

Manvendra Singh
168 Views