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:
Script results detailed description:
Column name | Data type | Description |
---|---|---|
DatabaseName | sysname | Name of current database (tempdb). Just for screens. |
LogicalFileName | sysname | Logical name of the file in the database. |
CPUs | int | Specifies the number of logical CPUs on the system. Not nullable. |
FileId | int | ID of the file within database. The primary file_id is always 1. |
FileSizeMB | decimal | Current file size in MB |
IsPercentGrowth | varchar | Flag is current auto-growth is set by percent increments. |
AutoGrowthIncrement | varchar | Increment definition. Can be specified in MB or %. |
NextAutoGrowthSizeMB | Next increment size in MB. This column useful when increments specified by percents. | |
PhysicalFileName | nvarchar(260) | Operating-system file name. |