Tempdb Add files require Restart

Question

Does adding Tempdb files require a restart? We would like to Add files on the same drive, Not Delete and Not modify. We tested in Dev and QA environment, restart was not required. However for production I would like to be sure everything works properly. Some are stating conditions, not always necessary, sometimes necessary, I am reading resources below:

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 

Do You Need an Outage to Add tempdb files? Brent Ozar https://www.brentozar.com/blitz/tempdb-data-files/ "Technically, not always. But practically speaking, yes. If you have to shrink files, that’s very difficult while SQL Server is in use, and we’ve seen cases where anti-virus/file control tools didn’t act up until SQL Server was restarted after the files were added."

http://jackworthen.com/2017/08/24/adding-additional-data-files-to-the-tempdb-database-in-sql-server/ "Once the additional files have been created, it’s not always necessary to perform a service restart. However, in many production environments, simply modifying the filesize of the existing TempDB datafile (tempdev) is not possible without restarting services first. "

The following resources are saying no restart required: https://www.codykonior.com/2015/08/10/modifying-tempdb-database-files-without-a-restart/ http://jackworthen.com/2017/08/24/adding-additional-data-files-to-the-tempdb-database-in-sql-server/

Answer 1

ADD - no outage required. Although as Sean from Microsoft pointed out, SQL will prefer to use the lower filled files. If you are going from 1 data file and adding more, then SQL will use the new ones for a while, but your performance won't be worse than only having one file. However, if you have 2+ already and add one more, then it will hotspot on the new one and decrease performance.

SHRINK - may require an outage, depending on load. The tail of the file needs to be empty which may not happen in a busy server.

GROW - no outage required although you may prefer to take one to avoid the performance penalty. Instant File Initialization will mitigate this. As Aaron pointed out, it will only mitigate for data files, but since you aren't going to add more than one log file (not useful)

REMOVE - may require an outage (has to be shrunk/emptied first)

MOVE - requires an outage since you need to restart SQL to make it start using the files in the new location.

EDIT - Added details about HotSpotting.

When SQL Server has more than one TempDB data file, it looks at them and selects the one with the lowest amount of data to use (sort of load balancing). So, in the scenario where you have two or more existing data files and add a new one, SQL will observe that the one you just added is lowest filled and will utilize that one almost exclusively until it reaches the same level as the others. This will negate the advantages of having multiple tempdb files as all of the activity will concentrate on just the one. Basically, you will see tempdb contention (Pagelatch on tempdb) for the file you just added until it reaches equilibrium.

If you have 1 file and add 1 or more to it, then this will still occur (SQL will prefer to use the one that is lowest filled) but as you were going from one file with the contention, you won't see any downtick in performance, but you won't see any uptick in performance until the files all reach equilibrium.