Where should I put the tempdb log file ?

Question

Post reply

Where should I put the tempdb log file ?

awu

SSCrazy

Points: 2037
More actions
May 11, 2012 at 4:40 pm

#256301

Just got a new box with three LUNs for Data , TLog and TempDB, everything else is good, just wondering where should I put log of tempdb , on Log LUN or TempDB LUN ?

ddfg

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

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

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 1

I would keep it on the tempdb LUN myself, but it would really depend on how heavily tempdb is used on your system and how the LUNs were built, the SAN and how much cache is on the SAN.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Golfer22 SSCrazy Points: 2256 More actions · Answer 2

The canonical answer would be a LUN that is using RAID1. The underlying hardware configuration of the LUN would be relevant for your determination. The tempdb may take many small writes. Some DBAs think that distributing the information over many hard disks can reduce performance. Some people find that RAID 5 traditionally increases performance as there are multiple hard drive controllers writing. But for the type of work a tempdb will be doing with the storage, RAID 1 may be the best choice.

Check this out:

http://sqlblog.com/blogs/linchi_shea/archive/2007/02/07/is-raid-5-really-that-bad.aspx

I know a tempdb is different from a TLog, this article may be useful:

http://sqlskills.com/blogs/kimberly/post/8-steps-to-better-transaction-log-throughput.aspx

awu SSCrazy Points: 2037 More actions · Answer 3

tempdb will have heavy disk IO as the databases have snapshot isolation turned on .

All three LUNs are RAID 10, both data and log luns will have snap mirror over to other data center and tempdb LUN will not , and data LUN are read ahead cache and Log LUN not turn it on.

So, it could improve diskIO if i put the log on TLog LUN instead of tempdb LUN, but it will cost more SAN snapshot space on the other hand ....., so wondering how you guys handling this on your servers ? Thanks.

ddfg

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

The way I'd handle it is to ask the san folks to reconfigure the TempDB LUN to Raid 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions