This technology works by implementing real-time I/O encryption and decryption, so this implementation is transparent for your applications and users.
However, this type of implementation could lead to some performance degradation since more resources must be allocated in order to perform the encrypt/decrypt operations.
On this post we will compare how much longer take some of the most common DB operations, so in case you are planning to implement it on your database, you can have an idea on what to expect from different operations.
Setup of our example
For this example we will use the Wide World Importers sample database, and restore it 2 times, one decrypted, and the other one encrypted. Please note that the restored database is around 3.2 GB size, a relatively small one.
To encrypt one of the databases we use the following T-SQL (more info about how TDE works here):
After the above T-SQL has been executed, now we have the 2 databases ready for our example:
So it is just a matter of testing and measuring different operations and compare them (in no particular order):
We run a dbcc checkdb on each database with the following T-SQL:
we can see the results, it took around 6% more to complete:
Before we start this one, read this post about issues found on database backups with TDE enabled, so it is strongly recommended to patch your instances before using it.
Surprisingly, this one took less time with encryption enabled, around 15% less time... but wait! this is because of the number of pages processed by each backup operation, because if you check the average speed, you can see the encrypted backup operation is in fact, slower (around 33%), so in the case of this operation, it can vary for your environment:
If you plan to restore an encrypted database to a different server, remember that you must back up your master key and certificate and restore it on the new server in order to do it, otherwise you will not able to recover encrypted data.
Since now we are on the same instance, master key and certificate are already there, so we just run the simple RESTORE command:
As with the backup operation, the restore took less time for the encrypted database, but you can see that is because of the number of pages processed, because if you check the average restore speed, for the encrypted database is slower (167% slower), so also check this one for your own databases:
We execute Rebuild index statements for random tables with the following T-SQL:
You can see the relevant execution results:
The encrypted database took around 24% more time (on average for all the operations) to complete.
Now we execute update statistics with full scan on various tables with the following T-SQL:
And these are the results:
Even when some of the tables took less time, on average the encrypted database took around 15% more time to complete the statistics update. This should be other to have into account to check for your own database.
For this post we will not test any DML or SELECT statement, but you are free to do your own tests and determine if TDE implementation suits for you.
As we stated earlier, we have demonstrated that additional workload is put on TDE enabled databases in order to support the encrypt/decrypt operations. Now is is your turn to perform your own validations to see how much is impacted your own databases with this, since this can vary on different storage types (for example on fast SSD storages, it could be barely noticed).