Create tempdb files based on number of logical processors

Create tempdb files.sql

/*
 +-+-+-+-+-+-+        +-+ +-+-+-+ +-+-+-+-+                                                                                                                                                                                    
 |A|u|t|h|o|r|        |:| |K|i|n| |S|h|a|h|                                                                                                                                                                                    
 +-+-+-+-+-+-+-+      +-+ +-+-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+-+-+-+                                                                                                                                            
 |P|u|r|p|o|s|e|      |:| |R|e|c|o|m|m|e|n|d| |p|r|o|p|e|r| |t|e|m|p|d|b| |s|i|z|e|                                                                                                                                            
 +-+-+-+-+-+-+-+-+-+  +-+ +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ +-+-+ +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+-+ +-+-+-+ +-+-+-+-+ +-+-+-+ +-+-+-+-+-+-+-+-+-+-+                      
 |R|e|f|e|r|e|n|c|e|  |:| |-|-|-|-| |t|e|m|p|d|b| |d|a|t|a|f|i|l|e| |b|e|s|t| |p|r|a|c|t|i|c|e| |.|.| |M|u|l|t|i|p|l|e| |t|e|m|p|d|b| |d|a|t|a|f|i|l|e|s| |a|r|e| |g|o|o|d| |f|o|r| |p|e|r|f|o|r|m|a|c|e|                      
 +-+-+-+-+-+-+-+-+-+  +-+ +-+-+-+-+ +-+-+-+-+-+-+ +-+-++-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ 
                          |-|-|-|-| |S|o|u|r|c|e| |:|  |h|t|t|p|:|/|/|w|w|w|.|p|y|t|h|i|a|n|.|c|o|m|/|n|e|w|s|/|3|1|2|4|3|/|t|-|s|q|l|g|e|n|e|r|a|t|e|-|s|t|a|t|e|m|e|n|t|s|-|t|o|-|a|d|d|-|t|e|m|p|d|b|-|d|a|t|a|f|i|l|e|s|/| 
 +-+-+-+-+ +-+-+ +-+-+-+-+++-+-+++-+-+++-+-+-+++-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ 
 |T|h|i|s| |i|s| |p|a|r|t| |o|f| |m|y| |S|Q|L| |S|e|r|v|e|r| |H|e|a|l|t|h| |C|h|e|c|k|e|r| |S|c|r|i|p|t| |t|h|a|t| |d|o|e|s| |r|e|c|o|m|m|e|n|d|a|t|i|o|n| |w|i|t|h| |g|r|e|a|t| |d|e|t|a|i|l| |!|                             
 +-+-+-+-+ +-+-+ +-+-+-+-+ +-+-+ +-+-+ +-+-+-+ +-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+-+-+-+ +-+-+-+-+ +-+-+-+-+-+-+-+-+-+-+-+-+-+-+ +-+-+-+-+ +-+-+-+-+-+ +-+-+-+-+-+-+ +-+    
*/
-- declare variables
DECLARE @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
	,@SQLtempdbExec 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 @new_tempdbdev_size_MB = 10240 -- 10Gbytes , 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.

-- Get total number of Cores detected by the Operating system
SELECT @NUMPROCS = count(1)
FROM sys.dm_os_schedulers
WHERE STATUS IN (
		'VISIBLE OFFLINE'
		,'VISIBLE ONLINE'
		,'HOT_ADDED'
		)

SET @NUMPROCS = 0

SELECT @NUMPROCS = count(1)
FROM sys.dm_os_schedulers
WHERE STATUS = 'VISIBLE ONLINE'

IF @NUMPROCS > 8
	AND @NUMPROCS <= 32
	SELECT @NUMPROCS = @NUMPROCS / 2

IF @NUMPROCS > 32
	SELECT @NUMPROCS = @NUMPROCS / 4

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'

-- Determine if we already have enough datafiles
IF @tempdb_files_count >= @NUMPROCS
BEGIN
		SELECT 3 AS [PRIORITY]
		,'MEDIUM' AS SEVERITY
		,'SQL Server Configuration' AS GROUP_TYPE
		,'  TOTAL numbers of CPU cores on server :' + (
			SELECT cast(count(1) AS VARCHAR(3))
			FROM sys.dm_os_schedulers
			WHERE STATUS IN (
					'VISIBLE OFFLINE'
					,'VISIBLE ONLINE'
					,'HOT_ADDED'
					)
			) + CHAR(10) + 'Number of CPU cores Configured for usage by instance :' + (
			SELECT cast(count(1) AS VARCHAR(3))
			FROM sys.dm_os_schedulers
			WHERE STATUS = 'VISIBLE ONLINE'
			) + CHAR(10) + 'Current Number of Tempdb datafiles :' + (
			SELECT cast(COUNT(1) AS VARCHAR(5))
			FROM tempdb.sys.database_files
			WHERE type_desc = 'Rows'
				AND state_desc = 'Online'
			) + CHAR(10) + ' ' AS COMMENTS
		,' --****Number of Recommedned datafiles is already there****. Ref: http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx. http://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/' + ' ' AS RECOMMENDATIONS

	
END

SET @new_files_Location = Isnull(@new_files_Location, @tempdbdev_location)

DECLARE @FreeSpace TABLE (
	Drive CHAR(1)
	,MB_Free BIGINT
	)

INSERT INTO @FreeSpace
EXEC master..xp_fixeddrives

IF OBJECT_ID('tempdb..#tempdb_recommendation') IS NOT NULL
	DROP TABLE #tempdb_recommendation;

CREATE TABLE #tempdb_recommendation (
	id INT identity(1, 1)
	,recommendation VARCHAR(max)
	)

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
		)
	INSERT INTO #tempdb_recommendation
	SELECT '
WARNING: Some Existing datafile(s) do NOT have the same size as new ones.
It''s recommended that ALL datafiles have same size for optimal proportional-fill performance.Use ALTER DATABASE and DBCC SHRINKFILE to resize files
Ref: Optimizing tempdb Performance : http://msdn.microsoft.com/en-us/library/ms175527.aspx
' + '****Proposed New Tempdb Datafiles, PLEASE REVIEW CODE BEFORE RUNNIG  *****/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'

WHILE @tempdb_files_count < @NUMPROCS
BEGIN
	SELECT @SQLtempdbExec = '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 )'

	INSERT INTO #tempdb_recommendation
	SELECT @SQLtempdbExec

	SET @tempdb_files_count = @tempdb_files_count + 1
END

SELECT PRIORITY
	,SEVERITY
	,GROUP_TYPE
	,replace(COMMENTS, '&#x0D', '') AS COMMENTS
	,replace(RECOMMENDATIONS, '&#x0D', '') AS RECOMMENDATIONS
FROM (
	SELECT 3 AS [PRIORITY]
		,'MEDIUM' AS SEVERITY
		,'SQL Server Configuration' AS GROUP_TYPE
		,'  TOTAL numbers of CPU cores on server :' + (
			SELECT cast(count(1) AS VARCHAR(3))
			FROM sys.dm_os_schedulers
			WHERE STATUS IN (
					'VISIBLE OFFLINE'
					,'VISIBLE ONLINE'
					,'HOT_ADDED'
					)
			) + CHAR(10) + 'Number of CPU cores Configured for usage by instance :' + (
			SELECT cast(count(1) AS VARCHAR(3))
			FROM sys.dm_os_schedulers
			WHERE STATUS = 'VISIBLE ONLINE'
			) + CHAR(10) + 'Current Number of Tempdb datafiles :' + (
			SELECT cast(COUNT(1) AS VARCHAR(3))
			FROM tempdb.sys.database_files
			WHERE type_desc = 'Rows'
				AND state_desc = 'Online'
			) + CHAR(10) + CASE 
			WHEN (
					SELECT MB_Free
					FROM @FreeSpace
					WHERE Drive = LEFT(@new_files_Location, 1)
					) < @NUMPROCS * @new_tempdbdev_size_MB
				THEN '-- 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!'
			ELSE 'The server has enough space to add new tempdb datafiles  !! Refer to Recommnendations for the command to ADD tempdb datafiles.'
			END + ' ' AS COMMENTS
		,' Below are the recommendations for Proper Sizing of tempdb :' + CHAR(10) + (
			SELECT stuff((
						SELECT ',' + recommendation + CHAR(10)
						FROM #tempdb_recommendation
						FOR XML path('')
						), 1, 1, ' ')
			) + CHAR(10) + 'Ref: http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx. http://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/' + ' ' AS RECOMMENDATIONS
	) K2