Coaching & Solutions - TempDB

Tempdb is part of the system database that is included by default in SQL Server instance upon installation. It is a global resource to all users connected to SQL database and holds temporary user objects (table, index, SP, variables &functions) and internal objects (spools, cursors, hash & sorts operations) that are created in database engine.

Improper configuration (storage, sizing, #counts) can result to slowdown or downtime because of excessive I/O or page latch contention. We can avoid this if we align properly our setup based on the specification of server that you have. To improve Tempdb concurrency, below are my suggestions:

1. Dedicated lun, drive, volume, array for TempDB

Put the data files and log files on the same drive for TempDB.
If you can use SSDs for TempDB much better - Preference to eliminate SAN path traffic for data & log file reads.
It will not just help user transaction performance but also for logical management reasons.
SQL Server boot-up will be much faster.

2. Add/create additional TempDB Data files.

If the logical processors is less than equal (>=) eight (8) - use the same number of data files as the logical processor
If the number of logical processors is greater than eight (8) - use eight (8), if contention continues, increase the number of data files by multiples of four (4) until it reach the logical processor count. Or, calculate and use the power of two so the round-robin and proportional fill algorithms are not unbalanced.

3. Sized data files equally.

Allocate 8 GB for each data file - I normally follow the 8 KB per page.
Auto-grow it by 1 GB (1024 MB) - We want to prevent this to trigger so better consider to fill-up the drive, if the drive is dedicated to TempDb, better consume the allocated space so we will not worry about the uneven data file growth size.

4. Single log file.

Log file can be a quarter (1/4) of the total data files size.
Leave the log file count as is.

Sources and good reads:

Report abuse