April 18, 2016 at 9:21 am
Hi, I was wondering if anyone is able to offer me some direction on the below.
I'm currently running SQL Server 2012 R2 Enterprise Edition (64-bit). I have over 80+ databases on one SQL instance that range from 1GB to 70GB's in size. On 3 of these databases I've enabled TDE so TempDB is already encrypted. After enabling TDE on one database I noticed an increase in CPU by about 10-15% as expected. The business has now asked me what the impact would be if all 80+ databases were to have TDE enabled. Apart from backup sizes increasing due to compression not being as good, I was wondering what the best approach would be to understand the impact on CPU, I/O, Memory, Wait Stats etc. Obviously this can vary depending on hardware but all the research I’ve carried out only really talks about having TDE enabled on a single database I can’t seem to find a reliable source that mentions having multiple databases TDE enabled on a single SQL instance. To be honest when I enabled TDE on two additional databases I didn’t see any real impact on performance but I’m not 100% sure if this will be the case if all databases were TDE enabled.
If anyone is able to offer me any suggestions or direct me to any information I might find useful it would be much appreciated.
Thanks
April 18, 2016 at 10:13 am
I'm not sure there's much out there. Typically I've seen TDE in the 5-10% CPU range, increase, but I haven't looked to see what percentage of that is tempdb. It's very dependent on workload, so potentially that could tell you something.
Glenn Berry's DMV queries have one for cpu by db: http://www.sqlskills.com/blogs/glenn/category/dmv-queries/
This might help determine where your load is. Certainly if there is a heavy CPU usage in other databases, that's only going to increase for the encryption/decryption. The data in your db matters as well. Larger columns, with more read/writes, will cause more CPU.
What's I'd suggest is you baseline this on another machine. Restore a bunch of databases, and take a workload (trace) from the first machine and replay it on the second. Do this with and without TDE to get a better idea of the additional load on your CPU.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 19, 2016 at 2:54 am
Thanks. I have a test environment so I think I’ll just baseline this without TDE and then run the same tests with TDE enabled on multiple databases. Although the hardware configuration is different I guess this should still give me an idea on the performance impact.
April 19, 2016 at 6:29 pm
When you baseline, compare that to your current prod environment as well to see if you can extrapolate the perf levels between them.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply