SQL Server - TempDB configuration check

TempDB is heavily used database in every instance, time to check it. As every DBA know TempDB database is shared for all sessions within DB engine. This makes its performance pretty crucial for whole SQL Server overall performance. One of well known best practices is to set number of TempDB logical files equally to number of logical processors, but maximally 8. Keep auto-growth of every file within same filegroup equal, keep all files the same size, etc.

There are configuration issues like TempDB allocation contention (and more). Microsoft suggest to have even more than 8 files to reduce contention. I think this is subject for testing. It definitely depends on your workload. I think going with maximally 8 logical files is good starting point.

This script just quickly checking current configuration of your TempDB database.

TIP: Get familiar with TempDB changes in SQL Server 2016 in this article.

SELECT 	
	DB_NAME(mf.database_id) AS DatabaseName,
	mf.name AS LogicalFileName,
	(SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info) AS CPUs,
	mf.file_id AS FileId,
	CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, size) / 128)) AS FileSizeMB,
	CASE mf.is_percent_growth
		WHEN 1 THEN 'Yes'
		ELSE 'No'
	END AS IsPercentGrowth,
	CASE mf.is_percent_growth
		WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
		WHEN 0 THEN CONVERT(VARCHAR, mf.growth / 128) + ' MB'
	END AS AutoGrowthIncrement,
	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 NextAutoGrowthSizeMB
	,physical_name AS PhysicalFileName
FROM sys.master_files mf
WHERE database_id = 2
	AND type_desc = 'rows'

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
DatabaseNamesysnameName of current database (tempdb). Just for screens.
LogicalFileNamesysnameLogical name of the file in the database.
CPUsintSpecifies the number of logical CPUs on the system. Not nullable.
FileIdintID of the file within database. The primary file_id is always 1.
FileSizeMBdecimalCurrent file size in MB
IsPercentGrowthvarcharFlag is current auto-growth is set by percent increments.
AutoGrowthIncrementvarcharIncrement definition. Can be specified in MB or %.
NextAutoGrowthSizeMBNext increment size in MB. This column useful when increments specified by percents.
PhysicalFileNamenvarchar(260)Operating-system file name.

Leave a Reply