tempdb needs to be big enough to handle the following processes for every concurrent Adaptive Server user:
Worktables for merge joins
Worktables that are created for distinct, group by, and order by, for reformatting, and for the OR strategy, and for materializing some views and subqueries
Temporary tables (those created with “#” as the first character of their names)
Indexes on temporary tables
Regular user tables in tempdb
Procedures built by dynamic SQL
Some applications may perform better if you use temporary tables to split up multitable joins. This strategy is often used for:
Cases where the optimizer does not choose a good query plan for a query that joins more than four tables
Queries that join a very large number of tables
Very complex queries
Applications that need to filter data as an intermediate step
You might also use tempdb to:
Denormalize several tables into a few temporary tables
Normalize a denormalized table to do aggregate processing
For most applications, make tempdb 20 to 25% of the size of your user databases to provide enough space for these uses.
