TEMP DB File Creation based on CPU

USE [master]

GO
DECLARE @cpu_count int,@file_count int,@logical_name sysname,@file_name nvarchar(520),@physical_name nvarchar(520),@size int,@max_size int,@growth int,@alter_command nvarchar(max)SELECT @physical_name = physical_name,@size = size / 128, @max_size
= max_size / 128,@growth = growth / 128FROM tempdb.sys.database_filesWHERE name = 'temp'select * from tempdb.sys.database_filesSELECT @file_count = COUNT(*)FROM tempdb.sys.database_filesWHERE type_desc = 'ROWS'SELECT @cpu_count = cpu_countFROM sys.dm_os_sys_infoWHILE @file_count < @cpu_count -- Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.BEGINSELECT @logical_name = 'tempdb' + CAST(@file_count AS nvarchar)SELECT @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf')SELECT @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' + @file_name + ''', SIZE = ' + CAST(@size AS nvarchar) + 'MB, MAXSIZE = ' + CAST(@max_size AS nvarchar) + 'MB, FILEGROWTH = ' + CAST(@growth AS nvarchar) + 'MB )'PRINT @alter_command
--EXEC sp_executesql @alter_commandSELECT @file_count = @file_count + 1
END


Exec xp_msver 'processorcount' - Number of processor to verify