Good management of tempdb is critical to the overall performance of Adaptive Server. tempdb cannot be overlooked or left in a default state. It is the most dynamic database on many servers and should receive special attention.
If planned for in advance, most problems related to tempdb can be avoided. These are the kinds of things that can go wrong if tempdb is not sized or placed properly:
tempdb fills up frequently, generating error messages to users, who must then resubmit their queries when space becomes available.
Sorting is slow, and users do not understand why their queries have such uneven performance.
User queries are temporarily locked from creating temporary tables because of locks on system tables.
Heavy use of tempdb objects flushes other pages out of the data cache.
