Script to create multiple tempdb files

The script below can be used to accomplish the task of modifying the existing TempDB data file and creating 3 additional files. One important thing to note when adding additional data files is that you generally want all TempDB data files to be configured the same way. Though these settings will very likely need to be changed to meet the needs of your database, this should give you a pretty good idea of how to script out the changes that you will need.

If using this script you will need to modify the path when creating additional data files. In many cases you probably want all TempDB data files reside in the same directory, though there are situations where you may want to split these out to different drives.

Situations like this can range from large scale SQL Server environments to scenarios with limited disk space. Each deployment is unique and you will need to consider these these options before pulling the trigger.

Trying to keep the streak alive so here is my 5th blog post in 5 days. This one is another script and by now you are probably wondering if I am either really incredibly lazy or just hate screwing up.

The answer to both is an unqualifed YES. The best practice from Microsoft is to have 1 TempDB file per processor on a dedicated disk. The files should be set large enough so that they do not need to grow. You can read more here. I should warn you that there is some debate on this recommendation and many people run. Update: There is not a lot of good information on this subject outside of the best practices recommendations and I am not trying to fill that void here.

I am not advocating a certain number of files for TempDB in this post, that would probably occupy an entire series of blog posts and require intimate knowledge of the internals of the storage engine.

This post is a good start at dispelling some of the myths around how many files you should have for TempDB. Most noticeably, it only recommends multiple files when you are seeing latch contention in TempDB. I plan to update this post directly as new information is discovered to get the best information out there so please either check back or subscribe to the feed to stay informed. If you are working with a server that has the data file on the C: drive, then you will want to be sure to move the file.

In the code example below, I am moving the tempdb data file to a folder on a drive labeled T. Next, the size needs to be increased. I prefer using the tsql code below. This script sets the size of the data file to 1GB. One thing I should mention here is that the size will also depend on how many data files you will have and how large the drive is.

Ideally, you would want to use up as much space on the drive as possible. The next step is to add more data files. With multiple data files, the workload gets spread out across all the files which will help to reduce contention.

The general consensus is that you should have one data file per logical processor up to a max of 8. The following script creates three more data files for tempdb in the same directory referenced above. All three of these data files are set to the same size as the first one. Also, take note of the file growth setting.

This is where the size discussion comes in. If a file needs to grow, that will take processing time. That is why it is best to keep all the data files on their own dedicated drive so that they can be sized to take up all the space. Sometimes you may need the ability to let the files grow.