SQL Server – Tempdb Add Files Require Restart

performanceperformance-tuningsql serversql-server-2012tempdb

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/

Best Answer

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.

Related Solutions

SQL Server – Changing Tempdb Settings and Recommendations

As stated in Paul's recommendations and in KB 2154845 use 8 files, and adjust as needed.

As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

Now also make sure that the tempdb files are created such that you don't need auto growth and that they are all the same size with the same growth parameters not in percentiles.

Your tempdb is now ~140 GB so that is about the size you need to provision for. Create 8x15-20 GB datafiles and a single log file (as transaction logs are used sequentially) set all the files with exactly the same growth parameters, something that makes sense on your storage 512mb is fine if you have instant file allocation. As your transaction log is just about 5GB create a single 6-8GB file for the transaction log and set autogrowth to be in MB. Log growth is not affected by instant file allocations so make that parameter smaller than the one on the database files.

If you still see contention then go for 16x 8-10 GB files and dont change the transaction log parameters

Sql-server – Moved ‘tempdb’, now file ‘tempdev2’ does not exist

You are not providing a unique physical filename for each file. You should alter tempdb like this:

use master
go
alter database tempdb
modify file (name = tempdev, filename = 'S:\tempdev.mdf')
go
alter database tempdb
modify file (name = templog, filename = 'S:\templog.ldf')
go
alter database tempdb
modify file (name = tempdev2, filename = 'S:\tempdev2.mdf')
go

Microsoft Docs for ALTER DATABASE file and filegroup options state the following:

To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:
MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )  

Best Answer

Related Solutions

SQL Server – Changing Tempdb Settings and Recommendations

Sql-server – Moved ‘tempdb’, now file ‘tempdev2’ does not exist

Related Question