[SQL Server 2005] TEMPDB optimization
In SQL 2005 we have done quite a bit of internal improvements in tempdb scalability. These include:
- Caching of IAM and first data page for temp table and table variables.
- Improved allocation page latching protocol so that we use UP latch less frequently.
- Reduced logging overhead for tempdb so that we consume less IO bandwidth in tempdb log file.
- More efficient allocation algorithm for mixed pages in tempdb.
There should be less need of implementing the trace flag 1118. The TF is still there in case you need it.
We recommend the following if you see latch contention on tempdb allocation or system catalog pages:
Avoid auto grow. Pre-allocate space for tempdb files.
Make sure your temp tables are cached ( SQLServer:General Statistics Temp Tables Creation Rate)
Make as many tempdb files as you have CPU's (accounting for any affinity mask settings)
File sizes of equal amounts
