Create Multiple Tempdbs based on Logical CPUs

1. 1. Use below script to file current tempdb files 

select DB_NAME(mf.database_id) database_name 

, mf.name logical_name, mf.file_id 

, CONVERT (DECIMAL (20,2) 

, (CONVERT(DECIMAL, size)/128)) as [file_size_MB] 

, CASE mf.is_percent_growth 

WHEN 1 THEN 'Yes' 

ELSE 'No' 

END AS [is_percent_growth] 

, CASE mf.is_percent_growth 

WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%' 

WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB' 

END AS [growth_in_increment_of] 

, CASE mf.is_percent_growth 

WHEN 1 THEN CONVERT(DECIMAL(20,2) 

,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024) 

WHEN 0 THEN CONVERT(DECIMAL(20,2) 

, (CONVERT(DECIMAL, growth)/128)) 

END AS [next_auto_growth_size_MB] 

, physical_name from sys.master_files mf 

where database_id =2 and type_desc= 'rows' 

2. 2. Use below script to find number of logical CPUS 

SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info 

3. 3. Use below Alter DB command to add more tempdb files 

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2', 

FILENAME = N'F:\Data\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 64MB) --<<--Update the data file location/Size/AutoGrowth 

GO