T-SQL Script to Split Tempdb Data Files with Alignment of the Number of CPU Cores

“One tempdb date file per core” is proved to be a myth by Paul Randal and quoted from his blog it is a conlusion that :

“If you have > 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time.”

Therefore the following script will never set up more than 8 cores on a box:

[sourcecode language=”sql”]
/*
.SYNOPSIS
This script slipts tempdb into several data files in alignment with number of CPU cores.

.NOTE: please execute the output T-SQL from the below script and then restart sql server service to put this change into effect.

Applied Version: 2005+
Last Modified by: Nick Xu on 2014-03-17
*/

DECLARE @FileLocation varchar(250), @AddFileCMD varchar(1000)
DECLARE @FileCount int, @CurrentCount int, @CurrentTempFiles int

SELECT @FileLocation = SUBSTRING(physical_name, 1, CHARINDEX(‘tempdb.mdf’, physical_name) -1)
FROM sys.master_files
WHERE database_id = db_id(‘tempdb’)
AND file_id = 1

SELECT @CurrentTempFiles = COUNT(1)
FROM sys.master_files WHERE database_id = db_id(‘tempdb’)
AND type = 0

SELECT @FileCount =
CASE
WHEN cpu_count >= 32 THEN 8 – @CurrentTempFiles –(cpu_count/4 ) – @CurrentTempFiles
WHEN cpu_count >= 16 THEN (cpu_count/3 ) – @CurrentTempFiles
WHEN cpu_count >= 8 THEN (cpu_count/2 ) – @CurrentTempFiles
ELSE (cpu_count) – @CurrentTempFiles
END
FROM sys.dm_os_sys_info

SET @CurrentCount = 1
PRINT ‘———- ADDITIONAL TEMPFILES NEEDED : ‘ + Convert(varchar(10), @FileCount) + ‘ ———-‘
WHILE @CurrentCount <= @FileCount
BEGIN
SET @AddFileCMD = ‘GO’ +
+CHAR(10) + ‘ALTER DATABASE [tempdb] ADD FILE ( NAME = N”tempdev_’ + convert(varchar(3), @CurrentCount) + ”’, FILENAME = N”’
+ @FileLocation + ‘tempdev_’+ convert(varchar(3), @CurrentCount) + ‘.ndf” , SIZE = 524288KB , FILEGROWTH = 524288KB )’
PRINT @AddFileCMD
SET @CurrentCount = @CurrentCount + 1
END
[/sourcecode]