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.
- 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.
- 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.
- We
should have all the tempdb files configured with the same size.
- We
should have tempdb database on fast I/O system.
- 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.
- 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.