This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The Database Administrator must know something about the best configuration of TempDB.
A general advice is, try to avoid the use of TempDB by creating temp tables.
But TempDB also stores other information related to query execution, internal sort operation, overflow memory data.
You should keep your eye open for TempDB otherwise it degrades the overall performance.
Sharing few tips to improve the TempDB performance:
Try to keep TempDB in local drive:
You should store TempDB in your local SSD drive instead of the shared network drive. This introduced by SQL Server 2012.
If you store other databases in shared network drive and TempDB in local drive, It creates a separate path for IO which divides the traffic and improves the performance.
Should configure multiple DATA Files:
You should create more data files equal to 1/2 or 1/4 the physical CPUs.
Once you have attached different data files to it, it stripped the files and performed the parallel operations.
Now It has its own PFS, GAM, and SGAM pages, so it spreads the writing process which improves the writing performance.
Few other tips:
- Set the recovery model of tempdb to SIMPLE.
- Allow for tempdb files to automatically grow as required.
- Set preallocate space for all tempdb files and it prevents tempdb from expanding too frequently.
- Make each data file the same size.
