Add multiple tempdb files

Microsoft recommends servers with many CPUs and cores start out with 8 tempdb files to prevent contention. From there you can monitor and adjust the number of files.

There is a bug that will prevent the use of multiple tempdb files if they are not all the same size. It will only use the largest one.

I created a quick script that will create all the directories. You can copy the code below, then make any adjustments you need. I am using this on a SQL server with 32 physical processors and 128 total cores.

Create directory M:\MSSQL\Temp, then execute script below:

Use master
Go

ALTER DATABASE tempdb
MODIFY FILE (name=tempdev, filename='M:\MSSQL\Temp\tempdb.mdf', SIZE=512 , filegrowth=10%)
ALTER DATABASE tempdb
MODIFY FILE (name=templog, filename='M:\MSSQL\Temp\templog.ldf', SIZE=512 , filegrowth=10%)
ALTER DATABASE tempdb
ADD FILE (NAME=tempdev2, FILENAME='M:\MSSQL\Temp\tempdb2.mdf', SIZE=512 , filegrowth=10%);
ALTER DATABASE tempdb
ADD FILE (NAME=tempdev3, FILENAME='M:\MSSQL\Temp\tempdb3.mdf', SIZE=512 , filegrowth=10%);
ALTER DATABASE tempdb
ADD FILE (NAME=tempdev4, FILENAME='M:\MSSQL\Temp\tempdb4.mdf', SIZE=512 , filegrowth=10%);
ALTER DATABASE tempdb
ADD FILE (NAME=tempdev5, FILENAME='M:\MSSQL\Temp\tempdb5.mdf', SIZE=512 , filegrowth=10%);
ALTER DATABASE tempdb
ADD FILE (NAME=tempdev6, FILENAME='M:\MSSQL\Temp\tempdb6.mdf', SIZE=512 , filegrowth=10%);
ALTER DATABASE tempdb
ADD FILE (NAME=tempdev7, FILENAME='M:\MSSQL\Temp\tempdb7.mdf', SIZE=512 , filegrowth=10%);
ALTER DATABASE tempdb
ADD FILE (NAME=tempdev8, FILENAME='M:\MSSQL\Temp\tempdb8.mdf', SIZE=512 , filegrowth=10%);
GO

Comments are closed.