SQL Server Tempdb Considerations.

SQL Server tempdb is a database in SQL Server which is used to store temporary user objects and internal objects. Because the tempdb is used a lot by SQL Server it is important for the performance to configure it correctly. Tempdb is only using one data file by default. Multiple data files can improve the I/O performance. It is recommended to use between 1/4 and 1/2 * number of processor cores. So for example if you have an 8 core server use 2 to 4 files for the tempdb. The tempdb data files and its log file should be placed on a fast disk. You should create big enough data files with the same original size and only use auto growth on tempdb to prevent errors.

To add data files to your tempdb:

USE master

GO

ALTER DATABASE tempdb ADD FILE ( NAME = tempdbDatafile2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdbDatafile2.mdf',

SIZE = 100MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10%),

( NAME = tempdbDatafile3,

FILENAME = 'C:\Program Files\Microsoft SQL Server\

MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdbDatafile3.mdf',

SIZE = 100MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10%)

GO

To move the tempdb files to a new location:

USE master;

 GO

 ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Temp\tempdb.mdf');

 GO

 ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\Temp\templog.ldf');

 go

 ALTER DATABASE tempdb  MODIFY FILE (NAME = tempdbDatafile2,  FILENAME = 'D:\Temp\tempdbDatafile2.mdf');

 go

 ALTER DATABASE tempdb MODIFY FILE (NAME = tempdbDatafile3,  FILENAME ='D:\Temp\tempdbDatafile3.mdf');

 GO

Now restart the instance and check is the files are in their new location. Then delete the old files.