dbDigger, Performance Tunning and Optimization, Temporary Tables
Temporary tables are notorious for their intensive disk I/O to tempDB. Mostly these are avoided but when used following considerations may be used for optimizing performance.
- By default these are created as heap so you must consider implementation of indexing
- Rather than using SELECT INTO for creating temporary tables, use DDL statements
- To avoid recomopiles of a stored procedure, create all temporary objects at start of procedure
- Create temporary objects, outside the transactions
- Fully try to not to create cursors on temporary tables