SQL server temp DB tuning

Temp DB file contention occurs when sorting or running processes need memory. If there is not enough memory these processes will write the information into the temp DB file(s). If separate threads try accessing the same file and one accesses the file, the others are blocked and this can diminish performance.

Match the number of temp DB files to the number of logical cores in the system up to a maximum of 8. If the system has more than 8 cores you should watch for contention before adding more temp DB files. When modifying or setting up new temp DB log files, it is important to set the initial size and growth to be the same for each file.

SQL temp files

SQL temp files and databases