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. Ken says:

    Very nice looking script David. I will definitely be giving this bad boy a try. I’ve been searching all over the place for guidance on the number of files when we have more than 8 cores. My servers have 16 and am wondering whether I really want 16 or not. Some places I’ve seen say start with 10 and measure the performance impact. My thought process says if the recommendation is 1 per CPU why would you want to start with 10 when you have 16 cores. That just doesn’t seem to make sense to me. You’ve created with 32 though so I’m curious if you have been able to notice any difference or are you just following the recommendations provided by Microsoft. As much as everyone bashes Microsoft I do tend to implement their recommendations because they obviously know the workings of their product better than me.

    Thx,
    Ken

    • David Levy says:

      In the example here I am just following the best practice. You really have to be careful though because if your IO is not up to the challenge you could have trouble.

      This post inspired some feedback from Paul Randal (Blog|Twitter) about whether multiple files was even a good idea at all. We may see a blog post from him in his “Myth a Day” series in response to this practice.

  2. jinsoo lee says:

    thx so much ~~

    but there is some error , so i fix it..

    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,
    @is_percent_growth int,
    @growth int,
    @alter_command nvarchar(max)

    SELECT @physical_name = physical_name,
    @size = size / 128,
    @max_size = max_size / 128,
    @is_percent_growth = is_percent_growth,
    @growth = growth
    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 0
    SELECT @alter_command = @alter_command + ‘, MAXSIZE = ‘ + CAST(@max_size AS nvarchar) + ‘MB ‘

    if @is_percent_growth > 0
    SELECT @alter_command = @alter_command + ‘, FILEGROWTH = ‘ + CAST(@growth AS nvarchar) + ‘% ‘
    else
    SELECT @alter_command = @alter_command + ‘, FILEGROWTH = ‘ + CAST(@growth /128 AS nvarchar) + ‘MB ‘

    SELECT @alter_command = @alter_command + ‘)’
    — 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

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

  4. 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,
    @is_percent_growth int,
    @growth int,
    @alter_command nvarchar(max)
    select @alter_command=”
    SELECT @physical_name = physical_name,
    @size = size / 128,
    @max_size = max_size / 128,
    @is_percent_growth = is_percent_growth,
    @growth = growth
    FROM tempdb.sys.database_files
    WHERE name = ‘tempdev’
    SELECT @logical_name = ‘tempdev1’
    SELECT @file_name = REPLACE(@physical_name, ‘tempdb.mdf’, @logical_name + ‘.ndf’)
    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= ‘0’
    SELECT @alter_command = @alter_command + ‘, MAXSIZE = ‘ + CAST(@max_size AS nvarchar) + ‘MB ‘
    print @is_percent_growth
    if @is_percent_growth > 0
    Begin
    SELECT @alter_command = @alter_command + ‘, FILEGROWTH = ‘ + CAST(@growth AS nvarchar) + ‘% ‘
    End
    else
    Begin
    SELECT @alter_command = @alter_command + ‘, FILEGROWTH = ‘ + CAST((@growth /128) AS nvarchar) + ‘ MB ‘
    SELECT @alter_command = @alter_command + ‘)’

    select @growth=@growth /128
    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

    i have done some correction

Leave a Reply

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