Script to Create A TempDB File Per Processor

Introduction

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 Need

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 .25 TempDB files per processor or even just 1 big file on less busy servers.

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.

April 12, 2010 – Paul Randal has just blogged “A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core”. Please make sure to read that post before going down the path of 1 TempDB file per processor.

For me, on my big servers, I like to run with 1 TempDB file per core / processor. Unfortunately being big servers, these machines have a lot of cores. The last server I built had 32 cores. Creating individual files via the GUI would have taken 30 minutes to an hour assuming I did not get something wrong and have to redo it.

The Script

To satisfy my need to be lazy and protect me from myself I have written a script to add a TempDB file per processor. The script is easy enough to use. Step 1 is to figure out how much space you have in total on your TempDB data drive. I specifically say data because logs should be on another drive. Step 2 is to divide the size of your drive by the number of cores / processors in your server and make that the size of your current single TempDB file. To give an example: Your new server has 32 cores and a 66 GB TempDB drive. 32 neatly goes into 64 2 times so you would set the size of your TempDB file to 2 GB, with maybe a slightly higher maximum size if you prefer to leave autogrow on. Next you would simply double check your math and settings then run the script and 31 exact copies of that file would be created. The whole thing should take less than 5 minutes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
USE [master]
GO
DECLARE @cpu_count      int,
        @file_count     int,
        @logical_name   sysname,
        @file_name      nvarchar(520),
        @physical_name  nvarchar(520),
        @size           int,
        @max_size       int,
        @growth         int,
        @alter_command  nvarchar(max)

SELECT  @physical_name = physical_name,
        @size = size / 128,
        @max_size = max_size / 128,
        @growth = growth / 128
FROM    tempdb.sys.database_files
WHERE   name = 'tempdev'

SELECT  @file_count = COUNT(*)
FROM    tempdb.sys.database_files
WHERE   type_desc = 'ROWS'

SELECT  @cpu_count = cpu_count
FROM    sys.dm_os_sys_info

WHILE @file_count < @cpu_count -- Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.
 BEGIN
    SELECT  @logical_name = 'tempdev' + CAST(@file_count AS nvarchar)
    SELECT  @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf')
    SELECT  @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' +  @file_name + ''', SIZE = ' + CAST(@size AS nvarchar) + 'MB, MAXSIZE = ' + CAST(@max_size AS nvarchar) + 'MB, FILEGROWTH = ' + CAST(@growth AS nvarchar) + 'MB )'
    PRINT   @alter_command
    EXEC    sp_executesql @alter_command
    SELECT  @file_count = @file_count + 1
 END

Conclusion

I hope you find this script helpful. Please let me know if you run into any issues with it or if it makes your life easier. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

This entry was posted in File Management, Utilities and tagged . Bookmark the permalink.

6 Responses to Script to Create A TempDB File Per Processor

  1. Pingback: How many temp database files ? | nguyennp's blog

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.