TDE and Tempdb

Question

TDE and Tempdb

Jayakumar Krishnan

SSCarpal Tunnel

Points: 4710
More actions
June 28, 2010 at 10:05 pm

#387236

Comments posted to this topic are about the item TDE and Tempdb
Thanks
Jay
http://www.sqldbops.com

Viewing 15 posts - 1 through 15 (of 22 total)

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

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 1

[p]Good question.. Also the tempdb system database is a global resource that is available to all users connected to the instance of SQL Server[/p]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

tommyh SSCertifiable Points: 6302 More actions · Answer 2

The link leads to a page that says "This might have a performance effect for unencrypted databases on the same instance of SQL Server". No "great" word in that sentance. And i cant really find anything there that might indicate that encryption can be good for performance ("great" in my world is good). So anyone might wanna clearify this a bit? (since i dont have a clue)

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

This could have a great performace effect for the other unencrypted databases

I agree with tommyh, the word 'great' is misleading in this context.

Not being a native English speaker, I deciphered that sentence as 'Performance will greatly decrease if tempdb is encrypted'. After that I googled 'transparent data encryption performance' and found this article: http://www.databasejournal.com/features/mssql/article.php/3815501/Performance-Testing-SQL-2008s-Transparent-Data-Encryption.htm

Rob Garrison, the author of the article, emulated an OLTP environment and found that the average TDE overhead was 6.36%. Should this number be considered as 'great'? I don't think so 🙂

The correct answer should be 'none of the above', because:

– 'the Tempdb also automatically gets encrypted on that instance' – true;

– 'this could have a great performace effect for the other unencrypted databases' – false.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

I, too, chose the "none of the above" answer.

In addition to the links already posted by other contributors, here is a third one:

"Microsoft has estimated the entire impact of TDE on a SQL Server instance to be 3-5% depending on the server environment and data volume." (reference: http://www.simple-talk.com/sql/database-administration/transparent-data-encryption/[/url])

In my opinion, 3-5% is far from "a great impact". The statement is partially true, partially false, and since there was no "both" or "it depends" option, I chose "none of the above" as the most correct choice.

A good but somewhat flawed question. If it can be changed to read "This could have some performance effect for other unencrypted databases", it would be a great question.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

DugyC Hall of Fame Points: 3804 More actions · Answer 5

I also was caught out by the use of the word "great" which I interpreted as a "positive" performance impact.

Therefore considered the statement false as the impact is negative performance by whatever amount either significant or minimal if managed correctly.

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

chris.reynolds SSC Enthusiast Points: 186 More actions · Answer 6

DougieCow (6/29/2010)
I also was caught out by the use of the word "great" which I interpreted as a "positive" performance impact.

Me too. Great is, perhaps, too subjective a word.

mzettler SSC Enthusiast Points: 190 More actions · Answer 7

I'm a bit confused with the answer to be honest. I saw the reference article and followed the link concerning the tempdb (http://msdn.microsoft.com/en-us/library/ms190768.aspx) where it explicitly said the encryption option was set to NO by default and could not be modified. Am I missing something here?

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 8

I agree with everyone else.

I went to the link on MSDN to read about this, and it says basically that it "might" have an impact.

I've read the article as well saying 3-5% based on system.

I've got about 20 systems using TDE, if there are some metrics or data saying this does effect databases and here are some examples I'd like to see it.

Depending on load, depending on server resources, depending on instance build, depending on TempDB being on a dedicated disk with multiple datafiles, there is a lot that needs to be quantified before this question can be made black and white.

Not to mention that TDE uses the Windows API to encrypt the files on disk. Pages in memory are NOT encrypted, the TempDB files in memory would feel no ill effect from this if they have enough memory to opperate in. this would effect a server during the checkpoint where all pages are flushed to the disk. Which once again depending on how the server is sized, the load in place, and the volume of users may make a little, a lot, or no difference whatsoever.

the wording is also confusing, but I dislike the question because it makes a statement and then gives you litteraly no info to back it up.

the TDE page on MSDN links to the Understanding the TempDB page on MSDN which has absoluetly nothing on it regarding TDE.

I get it that Microsoft put it on MSDN, but come on flush out a question before you make a statement.

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

mzettler (6/29/2010)
I'm a bit confused with the answer to be honest. I saw the reference article and followed the link concerning the tempdb (http://msdn.microsoft.com/en-us/library/ms190768.aspx) where it explicitly said the encryption option was set to NO by default and could not be modified. Am I missing something here?

You can not explicitly switch TDE on in the temdb database.

But you can switch TDE on in a user database, and that will result in SQL Server automatically starting to use TDE in the tempdb as well. This is required, because encrypted data is decrypted when the data is read from disk, so it resides in unencrpted form in system memory. If data from system memory spills to tempdb (eg because of explicitly created temp tables, version store, sort or hash operations on large amounts of data, etc) writing out the unencrypted data would form a security risk. In order to make TDE 100% reliable, tempdb has te be encrypted as soon as one or more user database is encrypted.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Bradley Deem SSCrazy Points: 2565 More actions · Answer 10

Thanks for the question, I needed to brush up on my TDE knowledge. Admittedly, the "great" caused me to pause, but I still got it right. As Hugo said yesterday, the QOTD is for learning not for points and for that I thank you.

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 11

I have removed "great" from the question as it does depend. There is some performance impact, but how much is debatable.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 12

Good question. I am glad the great was removed by the time I got to it.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Thanks for the question.

As per the use of the term "Great" - it was gone by the time I answered but here is my 2 bits. The use of the word "great" most likely meant "significant." Granted that definition is also debatable since 3-5% may or may not be a significant amount depending on your environment.

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

UMG Developer SSChampion Points: 13482 More actions · Answer 14

Nice question, thanks!

I think little bits like this will help people learn and understand new features like TDE so they are better prepared when they actually need to use it.