Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TDE (SQL2008R2) in a production environment? Expand / Collapse
Are you using TDE (SQL2008R2) in a production environment?
Poll ResultsVotes
Yes - 10 or more databases
 
22.22%
2
Yes - 5-10 databases
0%
0
Yes - 1-4 databases
 
11.11%
1
No - but planning
 
22.22%
2
No - used it before
0%
0
No - but use it with SQL2012
0%
0
No - but use it with SQL2014
0%
0
No - I do not need encryption
 
33.33%
3
 
11.11%
1
Member Votes: 9, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Thursday, August 7, 2014 8:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:16 AM
Points: 256, Visits: 450
Hello,

I'm wondering if TDE is used in an productive environment.

Thanks for your poll answer.

Cheers,
Akki
Post #1600651
Posted Thursday, August 7, 2014 8:28 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 17,825, Visits: 15,758
I have a client that has TDE enabled for all databases on specific production servers. There is a fair amount of overhead with that.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1600664
Posted Thursday, August 7, 2014 8:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:00 PM
Points: 31,181, Visits: 15,627
I have a few friends with TDE enabled on large production databases (> 100GB) and fairly low overhead. Around 4-5% for their workload.

Overhead is workload dependent and can impact performance if the encryption load is heavy for your db or tempdb.

I give an encryption talk and in the 20 or so times I've delivered it, I have found about 20-30 people (out of the 250-300 people that have seen the talk) are using TDE. For most the overhead is low and it's a non issue. However, the majority of them have also not experienced a DR event, so they don't know about impacts for restoration under pressure and if their certificate backups are easily obtainable and good.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1600668
Posted Thursday, August 7, 2014 8:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 17,825, Visits: 15,758
To be fair, this client is running about 1.5TB worth of databases in TDE. The impact is felt in about a 10% rise in cpu, and a 20x increase in number of locks and lock wait time. A lot of that is to do with the SQL workflow for their applications. And another big part of that is the demands it (encryption) places on tempdb.

As the indexes fragment throughout the day, the impact becomes more obvious. We will see continual slowdowns throughout the workday as the indexes get closer to 30% fragmentation.

Defrag the indexes and we are good for a little while. Disable tde and we are good for a long while.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1600678
Posted Thursday, August 7, 2014 12:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:00 PM
Points: 31,181, Visits: 15,627
Not to hijack too much, but is the data highly distributed towards numbers or characters? Large fields of text?

Is the impact more in tempdb or the user db?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1600805
Posted Thursday, August 7, 2014 12:21 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 17,825, Visits: 15,758
Steve Jones - SSC Editor (8/7/2014)
Not to hijack too much, but is the data highly distributed towards numbers or characters? Large fields of text?

Is the impact more in tempdb or the user db?


yes ;)

they have large text fields. They have guids as the clustering key - big impact on it.

Tempdb is less of an issue than the fragmentation of the indexes.

I think the bigger issue is really that the fragmentation becomes a lot more noticeable because of the little bit of a hit that is caused by TDE.

Nothing a lot of code tweaking and perf tuning can't really handle.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1600813
Posted Thursday, August 7, 2014 12:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:00 PM
Points: 31,181, Visits: 15,627
That makes sense. Text encryption/decryption is expensive. Working with numbers is much easier and gives much, much less of a hit.

Akki, does this help you?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1600823
Posted Thursday, August 7, 2014 12:54 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 40,210, Visits: 36,619
SQLRNNR (8/7/2014)
I think the bigger issue is really that the fragmentation becomes a lot more noticeable because of the little bit of a hit that is caused by TDE.


And fragmentation means memory's used less efficiently (because of half-full pages), which means more pages read in from disk, which means more decryption overhead.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1600829
Posted Friday, August 8, 2014 5:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:16 AM
Points: 256, Visits: 450
Hi,

the posts help a lot.
I did some testing in our environment and I get about 10% slower performance with TDE enabled. Which is quite okay.

Thanks for the info about the index fragmentation, I will keep an eye on it, usually we perform an index defragmentation each night, so it should keep the indexes in a good shape.
Our databases are around 600 GB-800 GB each with a high OLTP load on it.

Thanks.

Best Regards,
Akki
Post #1601129
Posted Friday, August 8, 2014 7:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 17,825, Visits: 15,758
MrAkki (8/8/2014)
Hi,


I did some testing in our environment and I get about 10% slower performance with TDE enabled. Which is quite okay.

Thanks.

Best Regards,
Akki


That is sooo good to hear.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1601181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse