There is a number of well known best practices for configuring the tempdb database on SQL Server. Specifically:
- Multiple data files are used (some sources recommend 1 data file per CPU core, while others recommend anywhere from 1/4 to 1 file per CPU core)
- All data files have equal size
- All data files have equal maximum size
- All data files have equal growth increment
- No data files have percent growth increment
Note that the multiple data file recommendation is usually presented in terms of the number of files per CPU core. So how do we determine the number of CPU cores to use in this calculation? In the general case, it would be incorrect to use the total number of cores in the machine, because not every core may be in use by SQL Server due to a non-default CPU affinity mask. Ultimately, what matters here is not so much the number of cores, but the number of scheduler threads. The reasoning behind this best practice is to provide a separate data file for each scheduler thread, so that multiple simultaneous tempdb space allocation requests can use separate data files, thus reducing space allocation contention.
Starting from SQL Server 2005, the number of schedulers for a SQL Server instance can be easily found from the scheduler_count column in sys.dm_os_sys_info DMV. This is the value used in the query below to determine if the multiple data file recommendation is followed. The specific rule I’m using here is between 1/2 and 1 data file per scheduler. If needed, it is trivial to change the query for a different definition of this best practice.
Here is a query that returns a single row result set showing if tempdb is configured according to these best practices. This can be run against multiple instances using a multi-server query in SSMS 2008, to quickly find out if tempdb is configured according to best practices across the enterprise.
WITH
TempdbDataFile AS
(
SELECT size,
max_size,
growth,
is_percent_growth,
AVG(CAST(size AS decimal(18,4))) OVER() AS AvgSize,
AVG(CAST(max_size AS decimal(18,4))) OVER() AS AvgMaxSize,
AVG(CAST(growth AS decimal(18,4))) OVER() AS AvgGrowth
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS'
AND
state_desc = 'ONLINE'
)
SELECT CASE WHEN (SELECT scheduler_count FROM sys.dm_os_sys_info)
BETWEEN COUNT(1)
AND COUNT(1) * 2
THEN 'YES'
ELSE 'NO'
END
AS MultipleDataFiles,
CASE SUM(CASE size WHEN AvgSize THEN 1 ELSE 0 END)
WHEN COUNT(1) THEN 'YES'
ELSE 'NO'
END AS EqualSize,
CASE SUM(CASE max_size WHEN AvgMaxSize THEN 1 ELSE 0 END)
WHEN COUNT(1) THEN 'YES'
ELSE 'NO'
END AS EqualMaxSize,
CASE SUM(CASE growth WHEN AvgGrowth THEN 1 ELSE 0 END)
WHEN COUNT(1) THEN 'YES'
ELSE 'NO'
END AS EqualGrowth,
CASE SUM(CAST(is_percent_growth AS smallint))
WHEN 0 THEN 'YES'
ELSE 'NO'
END AS NoFilesWithPercentGrowth
FROM TempdbDataFile;
© 2019 Microsoft. All rights reserved.