How to optimize tempdb to have better performance

How to optimize tempdb to have better performance

Following are some of the best practices I have tested and experienced with regarding the optimization of tempdb. I have observed following these guidelines do improve the performance to better extent.

1. We should have additional tempdb data files based on number of CPUs. This helps in maximizing the disk bandwidth. Actually if you have multiple files for tempdb, it will help in reducing storage contention. Best practice is to create one data file for each CPU on the server.
2. We should set the tempdb file growth increment to a good fixed size to avoid the files from growing by too small of a value. If the file growth value is too small, compared to the amount of data that is being written to tempdb, database may have constantly expand. This is not good for better performance.
3. We should have all the tempdb files configured with the same size.
4. We should have tempdb database on fast I/O system.
5. We should have separate drive in OS especially for tempdb. Intention should be to keep tempdb database on a separate disk and not with the disk used by other user and system databases.
6. In my case initial size of tempdb is around 5 GB, and I have file growth configured to value of 15%. I have observed better performance. Generally we should have file growth configured as MB only when initial size between 100 MB – 500 MB.

These are the best practices we should follow for tempdb database in our SQL system as tempdb is very critical part for any SQL server.