- 21 Nis 2020
- 1,208
- 686
Welcome Turk Hack Team Members,
Problem
Every new release of SQL Server gives us new features that will hopefully improve our database system in some way. Data security has been a hot topic over the last few years and one of the new features introduced in SQL Server 2008, Transparent Data Encryption (TDE), addresses this topic. This feature gives us a way to secure our data on the backend without any front end code changes. The only concern I had with this new feature was exactly how much overhead would it produce on my server. This tip will take a look at the performance impact of implementing this feature using a few basic database operations.
Solution
For those that aren't yet familiar with TDE, you can read the following links which give an overview of TDE and also some information on how to get started implementing it on one of your own databases. In this tip will we will simply focus on the performance impact of having a TDE enabled database versus a database without TDE enabled and will not get into any details on the configuration of this feature.
Understanding Transparent Data Encryption
Database Encryption in SQL Server 2008 Enterprise Edition
SQL Server 2008: Transparent data encryption feature - a quick overview
SQL Server 2008 Transparent Data Encryption getting started
Implementing Transparent Data Encryption in SQL Server 2008
One thing to note is all the tests below were run on a server running SQL Server 2008 with a 3GB database. Prior to executing each statement I also ran DBCC DROPCLEANBUFFERS to empty the buffer cache. This ensured that for every operation data would have to be decrypted if required and not just read directly from the buffer pool as data in the buffer pool is not encrypted.
Backup Performance
As it's an operation that we perform daily, let's first take a look at how this feature will affect our backup performance. For this test I ran scripts that performed both compressed and uncompressed full backups with TDE enabled and disabled. Each backup script was run 3 times and the averages are displayed below for each type of script execution.
We see from the results above that having TDE enabled results in about a 16% increase in the cpu used during the backup operation. As well, we see the total duration of the backup took anywhere from 10-20% longer with TDE enabled (depending on whether or not you were using backup compression). All in all this is a fairly small price to pay to have your database encrypted. An important thing to note with having TDE enabled on your database is that you get the extra benefit of having your backups encrypted as well thus providing extra security for all of your offline data.
SELECT, INSERT, UPDATE and DELETE Performance
Now that we know the impact TDE is going to have on our backups let's take a look at what if any impact we can anticipate for the applications that access our database. For this test I created a script with 10 SELECT, 5 INSERT, 8 UPDATE and 5 DELETE statements. Each script was run 3 times and the averages are displayed below broken up by statement type.
Looking at these results we can see that we get approximately a 10% increase in the duration of our SQL statements with TDE enabled which isn't too bad. The real impact of using TDE is evident here with the performance hit we take in CPU. With TDE enabled we use approximately 60% more CPU for the same workload. Keep in mind these results are measuring the performance of the entire workload. Looking at each statement type individually we can see that we take an even bigger performance hit in CPU when doing inserts, updates and deletes as compared to select statements. Depending on the behaviour of your application this may be an important thing to consider when deciding on whether or not you want to use TDE.
Index Rebuild Performance
Another operation that we as DBAs perform routinely, whether it be weekly, monthly or some other schedule, is index maintenance. For this test I created a script that rebuilt every index in my database. The script was run 3 times and the averages are displayed below.
The results here are pretty consistent with what we had seen above with our other DML statements. For an index rebuild we see approximate a 50% increase in CPU usage and a 20% increase in duration with TDE enabled as compared to having it not enabled.
Summary
While this does not represent every type of operation executed on your database it gives a good indication of what you could expect the impact to be if you were to implement TDE on your database. One thing to keep in mind is that these numbers are a worst case scenario. In a real world application a lot of the data would already be in the buffer pool when a statement is run so it would not have to be decrypted and therefore the performance impact would be much less. I've read from many difference sources that in real world applications you can see anywhere from a 3-10% degradation in performance with TDE enabled. As always you should test in your own environment as your results may vary based on the nature of the data in your database and the applications accessing it.
Next Steps
//Quoted. Thanks for reading.
Problem
Every new release of SQL Server gives us new features that will hopefully improve our database system in some way. Data security has been a hot topic over the last few years and one of the new features introduced in SQL Server 2008, Transparent Data Encryption (TDE), addresses this topic. This feature gives us a way to secure our data on the backend without any front end code changes. The only concern I had with this new feature was exactly how much overhead would it produce on my server. This tip will take a look at the performance impact of implementing this feature using a few basic database operations.
Solution
For those that aren't yet familiar with TDE, you can read the following links which give an overview of TDE and also some information on how to get started implementing it on one of your own databases. In this tip will we will simply focus on the performance impact of having a TDE enabled database versus a database without TDE enabled and will not get into any details on the configuration of this feature.
Understanding Transparent Data Encryption
Database Encryption in SQL Server 2008 Enterprise Edition
SQL Server 2008: Transparent data encryption feature - a quick overview
SQL Server 2008 Transparent Data Encryption getting started
Implementing Transparent Data Encryption in SQL Server 2008
One thing to note is all the tests below were run on a server running SQL Server 2008 with a 3GB database. Prior to executing each statement I also ran DBCC DROPCLEANBUFFERS to empty the buffer cache. This ensured that for every operation data would have to be decrypted if required and not just read directly from the buffer pool as data in the buffer pool is not encrypted.
Backup Performance
As it's an operation that we perform daily, let's first take a look at how this feature will affect our backup performance. For this test I ran scripts that performed both compressed and uncompressed full backups with TDE enabled and disabled. Each backup script was run 3 times and the averages are displayed below for each type of script execution.
TDE Enabled - TDE not Enabled
CPU (ms) READS WRITES DURATION (ms) CPU (ms) READS WRITES DURATION (ms)
With compression 234 319 14 57264 195 298 13 51632
No compression 203 319 14 70384 171 300 13 55714
CPU (ms) READS WRITES DURATION (ms) CPU (ms) READS WRITES DURATION (ms)
With compression 234 319 14 57264 195 298 13 51632
No compression 203 319 14 70384 171 300 13 55714
We see from the results above that having TDE enabled results in about a 16% increase in the cpu used during the backup operation. As well, we see the total duration of the backup took anywhere from 10-20% longer with TDE enabled (depending on whether or not you were using backup compression). All in all this is a fairly small price to pay to have your database encrypted. An important thing to note with having TDE enabled on your database is that you get the extra benefit of having your backups encrypted as well thus providing extra security for all of your offline data.
SELECT, INSERT, UPDATE and DELETE Performance
Now that we know the impact TDE is going to have on our backups let's take a look at what if any impact we can anticipate for the applications that access our database. For this test I created a script with 10 SELECT, 5 INSERT, 8 UPDATE and 5 DELETE statements. Each script was run 3 times and the averages are displayed below broken up by statement type.
TDE Enabled TDE not Enabled
CPU (ms) READS WRITES DURATION (ms) CPU (ms) READS WRITES DURATION (ms)
SELECT 7390 121576 18 26037103 3062 121397 1 23409175
INSERT 125 784 16 1509177 32 798 16 1254881
UPDATE 500 2181 31 1376947 108 2215 24 1080074
DELETE 502 2380 30 1271479 63 2417 29 708979
CPU (ms) READS WRITES DURATION (ms) CPU (ms) READS WRITES DURATION (ms)
SELECT 7390 121576 18 26037103 3062 121397 1 23409175
INSERT 125 784 16 1509177 32 798 16 1254881
UPDATE 500 2181 31 1376947 108 2215 24 1080074
DELETE 502 2380 30 1271479 63 2417 29 708979
Looking at these results we can see that we get approximately a 10% increase in the duration of our SQL statements with TDE enabled which isn't too bad. The real impact of using TDE is evident here with the performance hit we take in CPU. With TDE enabled we use approximately 60% more CPU for the same workload. Keep in mind these results are measuring the performance of the entire workload. Looking at each statement type individually we can see that we take an even bigger performance hit in CPU when doing inserts, updates and deletes as compared to select statements. Depending on the behaviour of your application this may be an important thing to consider when deciding on whether or not you want to use TDE.
Index Rebuild Performance
Another operation that we as DBAs perform routinely, whether it be weekly, monthly or some other schedule, is index maintenance. For this test I created a script that rebuilt every index in my database. The script was run 3 times and the averages are displayed below.
CPU (ms) READS WRITES DURATION (ms)
TDE Enabled 14936 347345 46028 21898
TDE not Enabled 7420 347116 46190 17133
TDE Enabled 14936 347345 46028 21898
TDE not Enabled 7420 347116 46190 17133
The results here are pretty consistent with what we had seen above with our other DML statements. For an index rebuild we see approximate a 50% increase in CPU usage and a 20% increase in duration with TDE enabled as compared to having it not enabled.
Summary
While this does not represent every type of operation executed on your database it gives a good indication of what you could expect the impact to be if you were to implement TDE on your database. One thing to keep in mind is that these numbers are a worst case scenario. In a real world application a lot of the data would already be in the buffer pool when a statement is run so it would not have to be decrypted and therefore the performance impact would be much less. I've read from many difference sources that in real world applications you can see anywhere from a 3-10% degradation in performance with TDE enabled. As always you should test in your own environment as your results may vary based on the nature of the data in your database and the applications accessing it.
Next Steps
- Test performance of other forms of SQL Server encryption:
- SQL Server Column Encryption
- SQL Server Column Level Encryption Example using Symmetric Keys
- SQL Server Encryption Symmetric vs. Asymmetric Keys
- Test performance using third party tools to encrypt your database
- Key management:
- SQL Server Encryption Key Management between Dev, Test and Prod
- Managing SQL Server 2005 Master Keys for Encryption
//Quoted. Thanks for reading.
