What’s the issue?
By default, a SQL Server installation creates only one tempdb data file. Because tempdb is shared by every database and every session on the instance, allocation operations on internal system pages (PFS, GAM, and SGAM) can become a bottleneck when multiple sessions try to allocate space at the same time. The classic symptom is PAGELATCH waits on tempdb pages.Microsoft’s current guidance is to configure the number of tempdb data files as follows: if the server has eight or fewer logical processors, create one data file per logical processor; if the server has more than eight logical processors, start with eight data files and add four more at a time only if allocation contention persists, never exceeding the number of logical processors.
This finding flags instances where the tempdb file count does not align with this guidance, either too few (typically just one file or far fewer than eight on a multi core server) or too many (a one to one match to a high core count without evidence of contention).
Why is this a problem?
With too few tempdb data files on a busy multi core server, sessions compete for the same allocation pages, producing PAGELATCH_UP and PAGELATCH_EX waits that slow down every workload using tempdb. This includes sorts, hash joins, temporary tables, table variables, and version store activity for snapshot isolation, so the impact is broad and often shows up as general slowness rather than a specific failure.With too many tempdb data files (for example, 32 files on a 32 core server when the workload does not require it), SQL Server incurs unnecessary management overhead. Proportional fill, autogrowth coordination, and file handle management all become more complex with no offsetting benefit, and contention may shift to other waits without actually improving overall throughput.
The guidance has evolved over the years, and many environments still reflect older advice (one file per core unconditionally) or simply the default single file installation. Either extreme can produce measurable performance issues that are easy to fix once identified.
What should you do about this?
Determine the current file count by querying sys.master_files where database_id = 2 and type_desc = ‘ROWS’, and compare against the logical processor count returned by SELECT cpu_count FROM sys.dm_os_sys_info;. Apply Microsoft’s guidance: match files to cores up to eight, then start at eight and add four at a time only if contention is observed.
To add files, use ALTER DATABASE tempdb ADD FILE (NAME = N’tempdev2′, FILENAME = N’
Ensure all tempdb data files are equally sized with identical autogrowth settings, since SQL Server uses proportional fill and unequal files cause uneven usage that defeats the purpose of having multiple files. Pre size the files to fill most of the dedicated tempdb drive so autogrowth is rare. Place tempdb on fast, dedicated storage (SSD or NVMe where possible).
Read more…
tempdb Database – SQL Server | Microsoft Learn