SQL Server 2016 - TempDB Number of Files

One change to the installation process of SQL Server is the ability to configure TempDB at the point of install, not afterwards...which means another restart of the SQL Server.

Now the installation process gives us the ability to specify number of files, initial size and autogrowth options for our TempDB database.

Yes, I know its the C drive - this is my sandpit VM - in ALL other instances TempDB files are on a dedicated volume.

Why would you need to do this? 
Well multiple files help in some way to ease TempDB contention which roughly speaking refers to a bottleneck in accessing allocation pages...when an object like a temporary table is created in TempDB a page is allocated and a metadata page is held to essentially map pages to the table.

Multiple files assist in reducing the problem by spreading the allocation workload over our multiple files.  TempDB contention is seen on our SQL instances by the presence of PAGELATCH_UP or _EX waits if the resource description is 2:1:1 (PFS Page) or 2:1:3 (SGAM)

Here's a little code snippet to look for these wait types:

select session_id, wait_duration_ms,  resource_description
from sys.dm_os_waiting_tasks
where wait_type like 'PAGE%LATCH_%'
and resource_description like '2:%';

I've encountered SQL instances with only one file for TempDB so many times and a quick check for these waits can mean a very simple but effective fix for some resource problems. 

OK, thats fine but how many files? 
Good question, as a general rule it is widely accepted that there are no particular drawbacks for having four TempDB files as a minimum. You may read advice that you should assign one file per core but this is another one of those famous SQL Server myths. Evidence should be the driver here so add four files and check for contention. If it exists, add more files until a happy balance is found (note, too many files can increase PAGEIOLATCH waits).

Configuring TempDB in the install process is a real time saver and a welcome enhancement. It will (hopefully) also increase awareness of potential problems like contention...and that is only a good thing.

For more information, check out Paul Randals article on the subject of TempDB contention here: http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/