How to add the tempdb data files automatically depending on the number of CPU core count available

Question

If there are less number of data files in the tempdb then we would have noticed that there could be issue in performance and queries will go for pagelatch_up waitType.
And we need to manually add the data files by checking the number of cores available. Instead of adding manually I found automatic the scirpt completely will help everyone so I came with some script which might help everyone with less knowledge on SQL server database alter commands.

Answer 1

Please find the complete script which adds the data file to tempdb based on CPU core available. if we have more than 8 cores then the script limits the file addition to 8 data files.

--Script to add the temp data files to temp databases. 
USE [tempdb]
go

DECLARE @CPU tinyint, @loopCount tinyint, @fileLoc  Varchar(500), @DataFileCount tinyint, @Name Varchar(100);  
SELECT @CPU = cpu_count FROM sys.dm_os_sys_info;

SELECT @DataFileCount = COUNT(*) FROM  sys.database_files WHERE  [type] =0;
SELECT TOP 1 @Name = [name], @fileLoc = physical_name  FROM  sys.database_files WHERE  [type] =0;  


IF @DataFileCount = @CPU
BEGIN 
    PRINT 'Tempdb database is having all required datafiles...!! No Action Needed..!!'
    RETURN;
END;

/**Set the number of data file based on CPU count.
by default one data file will be available in the tempdb, so we need to add extra. 
ideal number files is 8 and it depends on the number of CPU cores. 
So if there are more than 8cores then we should have only 8 data files, but since already one data file available then we need add 7. 
if the number of cores are less then are equal to 8 then cpucore minus one number of data files we need to add. **/

IF @cpu > 8 
BEGIN 
    SET @loopCount = 8-@DataFileCount;
END
ELSE
BEGIN
    SET @loopCount = @CPU-@DataFileCount; 
END


PRINT 'Available CPU cores: '+ convert(varchar(10), @cpu); 
PRINT 'Number of Data files being added as per CPU core availabe: '+ convert(varchar(10), @loopCount);
PRINT 'Current tempdb file available in: ' +@fileLoc;
              
--get the file location
  
SET @fileLoc = REPLACE(@fileloc,'.mdf','');


declare @i int=1,@Filenumber int = @DataFileCount+1, @sql NVarchar(1000);
WHILE (@i <= @loopCount)
BEGIN 
    SET @sql = 'ALTER DATABASE [tempdb] ADD FILE(NAME = N'''+@Name+CONVERT(VARCHAR(2),@Filenumber)+''', FILENAME=N'''+@fileLoc+CONVERT(VARCHAR(2),@Filenumber)+'.ndf'' , SIZE = 1GB , FILEGROWTH = 64MB);'
    SET @i = @i+1;
    SET @Filenumber =@Filenumber+1
    --PRINT @SQL;
    PRINT 'Executing '+ @sql;
    EXEC sp_executesql @SQL;
END;