Automatic Tempdb Configuration Script

The default configuration for TempDb leaves a lot to be desired, and although good changes are on the way in 2016, it will be quite some time before any of us get to use that in production.

I manage many SQL servers, and the TempDb files have long been ignored on them, mostly due to overpowered server hardware. Recently I’ve been working to correct their file allocations and, most importantly, set up some standards for future server builds.

Microsoft best practices suggest assigning one data file per logical processor, up to eight. If contention remains, you can then assign additional data files, in groups of four, until contention lessens. I decided an automatic script could handle the initial setup.

If you hate reading you can skip to the code. I’ve commented everything you need to change and explained each step in detail. Even better, if you smash F5 and run the code, it will just show you the debug mode by default, no changes will be made.

Script Explanation

This script is meant to run against a new server that has the default settings in place for Tempdb, with a single data and log file. The script will rename those files per your input and then create additional data files up to the calculated limit based on logical CPUs or eight, whichever is lower.

The script sets the location for the data and log files, but if you are one of the lucky few who have a single drive per data file, this script cannot help you in its present state. You can modify the script to deal with multiple file locations, or manually make the changes.

One of my biggest pet peeves is percentage based autogrowth. It’s a very dangerous setting as your files grow. It isn’t even an option in this script. Generally it’s a good idea to presize your files anyhow, so the autogrowth shouldn’t matter regardless most of the time. You also may be asking why the size variables are all VARCHARs. That is only so they do not have to be converted when used later in the script.

My second favorite feature, after the number of data files calculation, is that you supply the total data file size. The script will calculate the size of each file for you. If you want an eight gig data file and have eight files, each file will be set to one gig. Simple. No math required.

The @Debug variable is supplied by default as 1. This allows you to run the script to see the actual ALTER DATABASE commands without executing them. Once you are happy with the results, you can rerun the script with @Debug=0 and the files will be modified and created immediately.


USE [master]
GO
SET NOCOUNT ON;

--- Set these values
DECLARE
 @Debug BIT = 1 --1=Information Only; 0=Execute Command
,@DataName VARCHAR(25) = 'Tempdb_Data' --Desired file name for data files
,@DataTotal VARCHAR(10) = '1024' --Total size of all Tempdb files; to be divided per file (in mb)
,@DataGrowth VARCHAR(10) = '100' --Data Autogrowth size (in mb)
,@DataLocation VARCHAR(250) = 'D:\DATA' --Data file location
,@LogName VARCHAR(25) = 'Tempdb_Log' --Desired file name for log file
,@LogSize VARCHAR(10) = '512' --Size of Log (in mb)
,@LogGrowth VARCHAR(10) = '50' --Log Autogrowth size (in mb)
,@LogLocation VARCHAR(250) = 'E:\LOG' --Log file location

--- Everything else is calculated for you!
,@Count INT = 2
,@LogicalCPU INT
,@DataSize VARCHAR(10)
,@OldData VARCHAR(25)
,@OldLog VARCHAR(25)

--- This will set # of Data files = Logical CPUs or 8, whichever is smaller
--- Afterwards, manually increase past 8 files, in groups of 4, if contention remains.
SELECT @LogicalCPU = CASE WHEN cpu_count>8 THEN 8 ELSE cpu_count END FROM sys.dm_os_sys_info
SET @DataSize = @DataTotal / @LogicalCPU
SELECT @OldData = Name FROM sys.master_files WHERE database_id = 2 AND file_id = 1
SELECT @OldLog = Name FROM sys.master_files WHERE database_id = 2 AND file_id = 2

--- Modify original single log and data files to follow desired inputs
DECLARE @SQL VARCHAR(MAX) = '
ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'''+@OldLog+''', NEWNAME= N'''+@LogName+''', FILENAME= N'''+@LogLocation+'\Tempdb_log.ldf'', SIZE= '+@LogSize+'MB, FILEGROWTH= '+@LogGrowth+'MB);

ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'''+@OldData+''', NEWNAME= N'''+@DataName+'1'', FILENAME= N'''+@DataLocation+'\Tempdb1.mdf'', SIZE= '+@DataSize+'MB, FILEGROWTH= '+@DataGrowth+'MB);
'

--- Generates additional data files up to calculated limit
WHILE(@Count<=@LogicalCPU)
BEGIN
SET @SQL+= '
ALTER DATABASE [tempdb]
ADD FILE (NAME= N'''+@DataName+''+CONVERT(VARCHAR,@Count)+''', FILENAME= N'''+@DataLocation+'\Tempdb'+CONVERT(VARCHAR,@Count)+'.ndf'', SIZE= '+@DataSize+'MB, FILEGROWTH= '+@DataGrowth+'MB);
'
SET @Count+=1
END

--- Debug=1 Prints SQL commands to execute for testing; Debug=0 will process the commands
IF(@Debug=1)
PRINT @SQL
IF(@Debug=0)
EXEC (@SQL)

Comments and suggestions are welcome. I’ve tested this on over a dozen servers so far with no issues (other than forgetting to change the drive path on a server with different drive letters).

Advertisements