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?
Best Answer
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.