If you're suffering from tempDB contention then it may be time to add files to tempDB. The rule of thumb is to have one file per logical processor on servers with <= 8 logic procs. For severs with more than eight, use eight and continue to add at least two files until the contention issue is resolved. It’s possible to add too many files, so add incrementally and test performance at each stage. Another avenue to explore is tuning the workload. Explore queries that make heavy use of temporary objects. Make sure temp table usage provides a clear benefit to the query performance.

Finally check to ensure trace flag 1118 is enabled. TF 1118 changes tempdb allocations and may be the silver bullet to the contention. Additionally, I’m not aware of any adverse effects of turning on this flag. If you’re running SQL 2016 then this TF has no effect because it is the default behavior.

TF1118

Here’s a script I use to get a feel for TempDB\CPU contention. It will check if the core count is greater than the tempdb file count. Then it dynamically builds the SQL statements needed to align the figures. If you’re actively experiencing TempDB contention then I recommend manual intervention instead of running the output from this script.


DECLARE @sql VARCHAR(MAX)
       ,@physical_name VARCHAR(200)
	   ,@name VARCHAR(75)
	   ,@sizeKB INT
	   ,@growthKB INT
	   ,@x TINYINT = 0
DECLARE @cpuCNT TINYINT = 
(
	SELECT count(cpu_id) as cpu_count
    FROM sys.dm_os_schedulers 
    WHERE status = 'VISIBLE ONLINE'
)

DECLARE @cpuTempfiles TINYINT = 
(
	SELECT COUNT(Name) as tempdb_files
	FROM sys.master_files 
	WHERE database_id = 2
	AND type_desc <> 'LOG'
)

;WITH datafiles_CTE AS (
SELECT ROW_NUMBER() OVER(ORDER BY LEN(name)) AS rn, 8 * size AS sizeKB,name, physical_name, 8 * growth AS growthKB
FROM tempdb.sys.database_files 
WHERE type_desc = 'ROWS'
) 

SELECT @name = name, @physical_name = LEFT(physical_name,(LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name)))), @sizeKB = sizeKB, @growthKB = growthKB
FROM datafiles_CTE
WHERE rn = 1



WHILE @cpuTempfiles < @cpuCNT


BEGIN
SET @x = @x+1
SET @sql =  'ALTER DATABASE [tempdb] ADD FILE  ( NAME = N'''+@name+'_'+CAST(@x AS VARCHAR(5))+''', FILENAME = N'''+@physical_name+'\'+@name+'_'+CAST(@x AS VARCHAR(5))+'.ndf'' , SIZE = '+CAST(@sizeKB AS VARCHAR(20))+'KB , FILEGROWTH = '+CAST(@growthKB AS VARCHAR(20))+'KB )'

SET @cpuCNT = @cpuCNT - 1

PRINT(@sql)
--EXEC (@sql)

END