T-SQL:Generate statements to add TEMPDB datafiles

TEMPDB, is one of SQL server hot-debated topics; there’s always something to say about its sizing, file placement or datafiles count in Multi-core instances.

WHY TEMPDB is controversial ?

This is simply because TEMPDB is a global resource that’s heavily used and very critical asset in a busy instance; this makes any advice against TEMPDB fall in the “It depends” category.

Any generalizations ?

As a starting point, some advice is usually thrown around for tempdb:

– Place Tempdb on a fast array , preferably On RAID10.

– Better if Tempdb is on an array not shared by other files if possible.

– Create more than one datafile if instance uses more than one CPU/Core.

– In case of multiple datafiles, better if all files have equal size.

More info can be found under Optimizing tempdb Performance

Not all of the above can be achieved especially when it comes to Storage requirements. However, at least it’s almost a “de facto” practice for instances using more than one CPU core, virtual or physical, to create more than one datafiles to reduce Tempdb contention when multiple CPUs(cores) try to allocate pages; you can read more info about this issue and TEMPDB improvements after SQL server 2000 under “Working with tempdb in SQL Server 2005” Whitepaper.

Situation

This blog isn’t about TEMPDB internals nor optimizations (please look at the above whitepaper for that); it’s just about this part of creating more than one datafile in case of multi-core instances.

In many cases, I’ve to create many datafiles on many instances manually, taking in consideration some parameters: Location , CPU counts, Size, File growth…etc.

This can be a bit tedious and I ran into some errors like creating files in wrong places like OS drive, creating more than enough files or using larger than the initial size should be.

For the above reasons, I created a query that will generate CREATE FILE statements to add new Datafiles to TEMPDB based on supplied parameters.

Approach

I wanted the script to give me the statements based on the following parameters:

– Number of cores used by the instance : this is stored internally; I used parts of code from “Who was that Affinity Masked Man?” to return the number of cores used by an instance.

FYI, When SQL server instance addresses more than 32 cores, information is stored in “affinity64 mask” Option.

– Location: either specify the location of new files or the location of Tempdb primary file will be used.

The script will throw a warning if the proposed location does NOT exist. It will also throw a warning if the SUM of proposed files’ size is more than the available free drive space.

– File size: It’s often advised to give the files the same size for optimal proportional-fill performance. If one file is bigger than the others, it will be chosen more often for activity, not the best situation.

The script will throw a warning if ANY of the existing files size is different than the new files’ proposed size. If you want to align the files size then you’ll have to change the proposed size or change the existing file(s) size.

– Growth: If the files are allowed to grow then best if they grow with same pace to keep thier sizes aligned; This can be easily changed after that.

I usually prefer not to grow the files to control their space and make sure the files were remain same size but I “TRY” to ensure the database has enough free space.

– Files count per core: this is always the most debatable option and advices differed after SQL server 2000 one-file-per-core especially since SQL server can now support 256 Logical cores.

MVP & ex-MS Paul Randal summarized this in following blog with the conclusion that you may be ok with the number of tempdb data files equal to 1/4 to 1/2 the number of processor cores..

I used the following formula to determine the number of files that should exist ; FEEL free to change that

IF Cores <= 8 then one file per core
IF between 8 & 32 cores inclusive then files = 1/2 # cores
IF more than 32 cores then files = 1/4 # cores

The script

The script will take all the above considerations and formulate an ALTER DATABASE ADD FILE statement; the number of statements are the number of suggested files – number of existing files.

The script in no way will make ANY changes but will raise some warnings related to path, space and existing files size.

Now the code; Please TEST, TEST, TEST WELL; the code is JUST a way to automate the creation of new files

/*————————————————————————–
Written by Mohammed Mawla, The Pythian Group
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND; USE IT AT YOUR OWN RESPONSIBILITY
  PLEASE TEST THE CODE THROUGHLY BEFORE APPLYING IT TO ANY PRODUCTION
—————————————————————————*/
USE Master
GO
SET NOCOUNT ON
GO
PRINT ‘– Instance name: ‘+ @@servername + ‘ ;
/* Version: ‘ + @@version + ‘ */’
— Variables
DECLARE @BITS Bigint                      — Affinty Mask
,@NUMPROCS Smallint                       — Number of cores addressed by instance
,@tempdb_files_count Int                  — Number of exisiting datafiles
,@tempdbdev_location Nvarchar(4000)       — Location of TEMPDB primary datafile
,@X Int									  — Counter
,@SQL Nvarchar(max)
,@new_tempdbdev_size_MB Int               — Size of the new files,in Megabytes
,@new_tempdbdev_Growth_MB Int             — New files growth rate,in Megabytes
,@new_files_Location Nvarchar(4000)       — New files path
— Initialize variables
Select  @X = 1, @BITS = 1
SELECT
@new_tempdbdev_size_MB = 4096              — Four Gbytes , it’s easy to increase that after file creation but harder to shrink.
,@new_tempdbdev_Growth_MB = 512            — 512 Mbytes  , can be easily shrunk
,@new_files_Location = NULL                — NULL means create in same location as primary file.
IF OBJECT_ID(‘tempdb..#SVer’) IS NOT NULL
BEGIN
DROP TABLE #SVer
END
CREATE TABLE #SVer(ID INT,  Name  sysname, Internal_Value INT, Value NVARCHAR(512))
INSERT #SVer EXEC master.dbo.xp_msver processorCount
— Get total number of Cores detected by the Operating system
SELECT @NUMPROCS=  Internal_Value FROM #SVer
Print ‘– TOTAL numbers of CPU cores on server :’ + cast(@NUMPROCS as varchar(5))
SET @NUMPROCS  = 0
— Get number of Cores addressed by instance.
WHILE @X <= (SELECT Internal_Value FROM #SVer ) AND @x <=32
BEGIN
    SELECT @NUMPROCS =
    CASE WHEN  CAST (VALUE AS INT) & @BITS > 0 THEN @NUMPROCS + 1 ELSE @NUMPROCS END
    FROM sys.configurations
    WHERE NAME = ‘AFFINITY MASK’
    SET  @BITS = (@BITS * 2)
    SET @X = @X + 1
END
IF (SELECT Internal_Value FROM #SVer) > 32
	Begin
	WHILE @X <= (SELECT Internal_Value FROM #SVer )
	BEGIN
		SELECT @NUMPROCS =
		CASE WHEN  CAST (VALUE AS INT) & @BITS > 0 THEN @NUMPROCS + 1 ELSE @NUMPROCS END
		FROM sys.configurations
		WHERE NAME = ‘AFFINITY64 MASK’
		SET  @BITS = (@BITS * 2)
		SET @X = @X + 1
	END
END
If @NUMPROCS = 0 SELECT @NUMPROCS=  Internal_Value FROM #SVer
Print ‘– Number of CPU cores Configured for usage by instance :’ + cast(@NUMPROCS as varchar(5))
————————————————————————————-
— Here you define how many files should exist per core ; Feel free to change
————————————————————————————-
— IF cores < 8 then no change , if between 8 & 32 inclusive then 1/2 of cores number
IF @NUMPROCS >8 and @NUMPROCS <=32
SELECT @NUMPROCS = @NUMPROCS /2
— IF cores > 32 then files should be 1/4 of cores number
If @NUMPROCS >32
SELECT @NUMPROCS = @NUMPROCS /4
— Get number of exisiting TEMPDB datafiles and the location of the primary datafile.
SELECT @tempdb_files_count=COUNT(*) ,@tempdbdev_location=(SELECT REVERSE(SUBSTRING(REVERSE(physical_name), CHARINDEX(‘\’,REVERSE(physical_name)) , LEN(physical_name) )) FROM tempdb.sys.database_files  WHERE name = ‘tempdev’)
FROM tempdb.sys.database_files
WHERE type_desc= ‘Rows’ AND state_desc= ‘Online’
Print ‘– Current Number of Tempdb datafiles :’ + cast(@tempdb_files_count as varchar(5))
— Determine if we already have enough datafiles
If @tempdb_files_count >= @NUMPROCS
Begin
Print ‘–****Number of Recommedned datafiles is already there****’
Return
End
Set @new_files_Location= Isnull(@new_files_Location,@tempdbdev_location)
— Determine if the new location exists or not
Declare @file_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)
insert into @file_results(file_exists, file_is_a_directory, parent_directory_exists)
exec master.dbo.xp_fileexist @new_files_Location
if (select file_is_a_directory from @file_results ) = 0
Begin
print ‘– New files Directory Does NOT exist , please specify a correct folder!’
Return
end
— Determine if we have enough free space on the destination drive
Declare @FreeSpace Table (Drive char(1),MB_Free Bigint)
insert into @FreeSpace exec master..xp_fixeddrives
if (select MB_Free from @FreeSpace where drive = LEFT(@new_files_Location,1) ) < @NUMPROCS * @new_tempdbdev_size_MB
Begin
print ‘– WARNING: Not enough free space on ‘ + Upper(LEFT(@new_files_Location,1)) + ‘:\ to accomodate the new files. Around ‘+ cast(@NUMPROCS * @new_tempdbdev_size_MB as varchar(10))+ ‘ Mbytes are needed; Please add more space or choose a new location!’
end
— Determine if any of the exisiting datafiles have different size than proposed ones.
If exists
(
	SELECT (CONVERT (bigint, size) * 8)/1024 FROM tempdb.sys.database_files
	WHERE type_desc= ‘Rows’
	and  (CONVERT (bigint, size) * 8)/1024  <> @new_tempdbdev_size_MB
)
PRINT
‘
/*
WARNING: Some Existing datafile(s) do NOT have the same size as new ones.
It”s recommended if ALL datafiles have same size for optimal proportional-fill performance.Use ALTER DATABASE and DBCC SHRINKFILE to resize files
Optimizing tempdb Performance : https://msdn.microsoft.com/en-us/library/ms175527.aspx
‘
Print ‘****Proposed New Tempdb Datafiles, PLEASE REVIEW CODE BEFORE RUNNIG  *****/
——————————————————————————————————————————————————————————
‘
— Generate the statements
WHILE @tempdb_files_count < @NUMPROCS
BEGIN
	SELECT @SQL = ‘ALTER DATABASE [tempdb] ADD FILE (NAME = N”tempdev_new_0’+CAST (@tempdb_files_count +1 AS VARCHAR (5))+”’,FILENAME = N”’+ @new_files_Location + ‘tempdev_new_0’+CAST (@tempdb_files_count +1 AS VARCHAR(5)) +’.ndf”,SIZE = ‘+CAST(@new_tempdbdev_size_MB AS VARCHAR(15)) +’MB,FILEGROWTH = ‘+CAST(@new_tempdbdev_Growth_MB AS VARCHAR(15)) +’MB )
GO’
	PRINT @SQL
	SET @tempdb_files_count = @tempdb_files_count + 1
END

Please feel free to post your suggestions for any modifications or fixes.