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:

/*
.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
Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in T-SQL Script and tagged , . Bookmark the permalink.

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

  1. Pingback: SPLIT TEMPDB FILES BASED ON SERVER CORES | pradydba

  2. Claus says:

    Hi Nick,
    Thanks for a script like that.
    I think you can improve the script by adding a part to set ALL datafiles of the tempdb to the same filesize.
    Kind regards
    Claus

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s