TDE and DB performance

Question

TDE and DB performance

Pankaj Shere

SSC Eights!

Points: 907
More actions
March 14, 2012 at 8:39 am

#391796

Hi
We have a requirement from one of our clients to implement this. We have a high OLTP db. I was wondering if anyone has implemented this and there are any performance issues that we need to worry about. I know there is a background thread which runs constantly once TDE is enabled. As per this msdn article, http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx performance impact is estimated at 3 - 5%.
The average cpu on our db server is at 15 - 20 % with about 800 batches/sec.
Any feedback is appreciated.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

Pankaj Shere (3/14/2012)
I know there is a background thread which runs constantly once TDE is enabled.

There are a lot of background threads that run when they are needed and sleep the rest of the time. No new ones due to TDE though.

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

Pankaj Shere SSC Eights! Points: 907 More actions · Answer 2

Thanks for the correction. The background thread is spawned when TDE is enabled on the db. It runs during the initial encryption. I am quoting the article,

"When TDE is enabled (or disabled), the database is marked as encrypted in the sys.databases catalog view and the DEK state is set to Encryption In Progress. The server starts a background thread (called the encryption scan or scan) that scans all database files and encrypts them (or decrypts them if you are disabling TDE)."

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

Yeah, that's just the initial encryption of the DB, that thread runs in the background encrypting the database file, once it's complete the thread goes away.

One thing to note with TDE is that most forms of backup compression are useless (data compression, row and page are still useful). So if you're currently compressing your backups you'll need extra space for them and probably want to not compress at all.

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

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

The background thread only runs to do the encryption. Once that is finished, it is not spawned again. All encryption/decryption is then part of the normal I/O read/write processes.