TDE (SQL2008R2) in a production environment?

Question

TDE (SQL2008R2) in a production environment?

MrAkki

SSCrazy

Points: 2574
More actions
August 7, 2014 at 8:12 am

#294033

Hello,
I'm wondering if TDE is used in an productive environment.
Thanks for your poll answer.
Cheers,
Akki

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 2

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 4

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?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 6

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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, MVP, M.Sc (Comp Sci)
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

MrAkki SSCrazy Points: 2574 More actions · Answer 8

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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[/url]
Learn Extended Events