How to measure Transparent Data Encryption (TDE) Performance

Question: What is the performance impact of enabling Transparent Data Encryption (TDE) on SQL Server database query performance? How can I measure the performance impact ?

Answer: There is a general outline about the pros and cons of different Encryption on Encrypting data in SQL Server   . So once you\ or your organisation has made the decision to enable TDE for SQL Server databases . it's time to assess the impact : performance , volume and DBA management overhead. 

According to the Microsoft documentation "TDE has an estimated performance impact around 3-5% and can be much lower if most of the data accessed is stored in memory.The impact will mainly be on the CPU, I/O will have a smaller impact"   

A few initial notes

1) TDE encrypts data at disk. When held in memory it is not encrypted. The main performance impact will be when reading from disk 

2) The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. Some thought may need to be given on unencrypted database performance .

3) Be aware!! After implementing TDE on any user database, Tempdb is  encrypted.  Even after you remove TDE from the user database, the encryption in tempdb remain.  Highly recommended to do all testing in a non-production environment.     To fix the  permanent encryption issue on TempDB - check FIX: The tempdb system database is still encrypted after you turn off encryption on an instance of SQL Server  . Thanks to Wilfred for supplying this link

Potential tests to run 

1) Test 1 - Benchmark testing - Run a workload from a NON TDE enabled database - direct from disk 

2) Test 2 - Benchmark testing - Run a workload from a NON TDE enabled database - with data in buffer cache

3) Test 3 -  Run a workload from a TDE enabled database - direct from disk  - compare results with Test 1 

4) Test 4 -  - Run a workload from a TDE enabled database - with data in buffer cache - compare  with Test 2

5) Scale according to the size required

These tests are just a starting point and will get you to start thinking about how to approach testing. You would expect for a given workload and given infrastructure to  see some increase in IO and and increase in CPU. To what extent this has on response times will depend on the spare capacity in the existing systems. i.e has the workload been taxing the systems? 

Does your system use excessive tempdb? As mentioned earlier a TDE enabled user database encrypts the tempdb . If the tempdb tables are paged , there may be a performance impact. If large data sets are generated on tempdb tables - then some investigation may be required. 

If you are using Extended Events to monitors various TDE activity -  use this query to find TDE related events. 

select * from sys.dm_xe_objects where (name like '%tde%' or name like '%encrypt%') and object_type = 'event'

--Output 

database_tde_encryption_scan_duration | Database TDE Encryption Scan
always_encrypted_query_count  | Query using Always Encrypted
db_encryption_state_change | Fires when Hk receives database encryption notification.
db_encryption_scan_completion | Fires when an encryption scan completes.
db_reencryption_merge_completion | Fires when a merge triggered by reecnryption completes.

Read more on encryption 

A guide to setup TDE for SQL Server

Encrypting data in SQL Server - SQL Server DBA