What are impacts of TDE enabling on database performance?

Question

If I use Transparent Data Encryption on my database, is there any impact on my db performance? Is it cause any problem for database indexing?

Archle

Answer 1

Some folks have tested this (see here, here, here and here) and have given some generic advice (see here and here). Seems the common perception is that the impact is about 3-5%. But 3-5% of what? If that is CPU and your workload is already CPU-bound, is that acceptable? Could it be worse? If that is I/O and you're already having I/O issues, is it going to kill your system? Who knows?

The best way you're going to determine what impact it will have on your performance is to test it in your environment. There are so many "it depends" factors here it's not funny. You should test your workload, against your database, on your hardware, over your network, with your usage patterns and backup procedures. You may have people coming back and saying "it hurt my performance a lot" or "I didn't even notice it" but how similar do you think their scenario is to yours?

Canonize

Is there any impact on indexing? – Archle

I'm not quite sure what you're asking. Will it cost more to create an index in your environment with TDE turned on? Will queries against your indexes be slower or faster with TDE? Will your indexes be larger with TDE? I don't know - again, too many variables. My point is that you should consider your workload as a whole, and weigh the impact of TDE on your workload (with proper testing) against your need for encryption. – Canonize

Answer 2

Aron is absolutely right. What will be surely affected is size of all your backups. You have to think about backup compression as it doesn't exist when you use TDE. And it means all backups & log shipping will have much worse performance.

Kerrill