The tempdb configuration is critical for sql server performance.
Below are the recommendations when setting a tempdb:
1. Allocate a separate disk partition. Ensuring that it is larger than most frequently used/largest table, most likely this will be Message/attachment tables. Confirm by right click on table Properties/Storage.
2. The tempdb partition is recommended to be located in a fast local ssd, or a fast IOPS drive.
3. The amount of tempdb mdb files is calculated against number of CPU cores +1 log file.
i.e Server has 8 cores of CPU then tempdb mdb files = 8 + 1 log file
4. Initial size per tempdb mdb file is 15GB, Set default auto grow setting.
Ensuring large initial size is necessary as frequent auto growth can result in physical file fragmentation.
5. The settings for all tempdb mdb files should be identical
6. Upon completion of initial set-up the sql server service will require a restart to bring new tempdb files online.
