TEMPDB

In SQL Server, tempdb is used for transient operations. Tempdb configuration should be very good for maximum performance, especially on servers with very intensive transient operations.

When setting tempdb, the following items should be considered;

1. Tempdb data files must be more than one in order for the workload to be shared and transactions to continue in parallel. In the event that 4 are created at the beginning and tempdb contention occurs; the number of files can be increased to 8.16.

2. File sizes must be equal for parallel use of all data files during operations.

3. -T1117 and -T1118 trace flags must be added to SQL server for all temdb data files to grow equally. Some VERSIONS of SP and CU where this feature is set do not require adding these trace flags.

4. Tempdb data and log files should be put on a different disk than other SQL files. It is also recommended that the disk be an SSD disk because there will be too many I/O operations on the disk.