Creating new TempDB files

Question

I would like advice on what would be the best setup for our SQL Server 2005 TempDB.

At the moment our Temp DB consists of a single MDF file of 3 GB and a single log file of 120 MB on the D: drive of a 3 spindle RAID-5 array which is partitioned in to C: and D: drives. The D: drive also holds our application core programs and our reports database.

I would like to move the temp DB to its own drive which is a 2 spindle RAID-1 array with a single partition, drive F:.

I would like to create 4 x 1 GB temp db MDF files when I move it to this drive. I do not have another spare dedicated drive to split up the TempDB MDF and LDF files, so both would have to go on the same drive.

Should I leave the LDF file behind on the current drive or should I move all the files to the new one?

Answer 1

Also striping tempdb on the same underlying spindle is just as likely to increase latency as it is to lower it, since this may actually make tempdb access slower - a single drive still has the same moving parts, and the more files you are trying to read/write simultaneously, the worse off you'll be.

I would start with 2 files at most and see if it improves the situation or not. You may even consider leaving one file where it is and adding a second on the new drive (depending on the other activity on the original drive).

Also make sure they are sized exactly the same and they have the same autogrowth setting. As soon as one file is larger than the other, the proportional fill algorithm will only use one file. Consider using trace flag 1117 to make sure that all data files autogrow at the same time, keeping the proportional fill algorithm in check. Just note that this trace flag affects all databases - so if you have other databases with multiple data files, you'll want to make sure their autogrow settings won't cause an issue.

• kdis: Thanks for your response aaron, some great points. I should have mentioned I am not seeing PATCHLATCH waits on TEMPDB at the moment. I think I will start by creating one extra mdf on the new drive.

Answer 2

A well known SQL Server expert, Paul Randal, recommends that the number of tempdb data files should equal the number of logical cores. So if you have 4 CPU's with hyperthreading, you'd create 8 tempdb data files.

If you have more than 8 logical cores, he recommends to start with 8, and then increase in blocks of 4 if you notice tempdb contention. We've adopted that approach to our main SQL Server, and the improvement in concurrency was noticable.

Tempdb is usually stored in memory. Its transactions can complete without writing to disk. That means that the disk speed for tempdb is only important for queries with very heavy tempdb usage.

• georgeb: A bit more from Paul Randal on this topic from a couple of years ago: sqlskills.com/BLOGS/PAUL/post/…
• kdis: Thanks for your responses. I follow Paul's blog and have read this post recently. The thing is I am not seeing PATCHLATCH waits on TEMPDB at the moment and we only have one data file and 8 cores. I was looking to improve the current setup of the system and thought that starting of with 8 files might actually decrease the performance rather than improve it.

Answer 3

Both raid 1 and having the ldf on the same drive is a bit of a performance hit when writing but you may have limited resources. Leaving the ldf file behind would help if TempDB is a performance bottleneck.