All databases on one SQL instance shares only one tempdb. System internal processing (such as sorting) also uses tempdb. Excessive usage of temporary tables incurs heavy traffic to tempdb and the underlying disc I/O.
Consider the following when using temporary tables.
- Creating temp tables and then dropping them incurs system overhead and unnecessary disc I/Os. It is much slower than queries processed in memory.
- Try to avoid using temp tables as much as possible. A simple example is like “select ..into #tmp; select … from table1 join #tmp” should be replaced by using derived query “select … from table1 join (select … from..)” or a common table expression. Don’t make this style of querying a habit: “I will put these into a temp table first then I will do something else”. When working with a high traffic database, this could negatively impact performance.
- When writing a very complex query, my experience is to first write the query to fulfill all the requests — then do the optimizations using all the tools we have. And using temp table is often a last resort.
- If you look at the performance of database servers, the worst part is often disk I/O contention (look at disk queue length, it’s one of the best indicators of disk I/O issues). This could be due to excessive usage of temp tables – especially inside loops. When you write queries or you are doing some ad-hoc research on a database, give second thought when you are about to create a (big) temp table just because the coding is easier or convenient.