A stored procedure that creates a temporary table runs thousands of times a minute, and the database starts showing contention and bloat unrelated to the business tables. What is happening, and how would you fix it?

## Why object creation is the expensive part A temporary table is cheap to *write into* — reduced logging, unshared buffers — but creating and dropping it is not cheap, because it is real DDL. The engine inserts rows describing the table, its columns, its indexes, and its statistics into system catalogs, allocates initial storage, and reverses all of that at drop time. That work touches structures shared by every session in the instance. At ten creations a minute nobody notices. At ten thousand, the catalog is the hottest object in the database and the profile looks bizarre: heavy contention with no business table involved. ## The three symptoms **Metadata contention.** Concurrent sessions creating temp objects collide on the same allocation and catalog pages. SQL Server's version — allocation-page (PFS/GAM/SGAM) and system-table contention in `tempdb` — was so common it drove multiple product changes: multiple tempdb data files, then memory-optimized tempdb metadata in SQL Server 2019. In PostgreSQL the same workload shows as rapid growth and bloat of `pg_class`, `pg_attribute`, and `pg_type`, with autovacuum unable to keep up and catalog lookups slowing for everyone. **Recompilation.** A cached plan referencing a temp table is tied to that session's specific object with its own statistics. Create a fresh object each call and the plan cannot be reused, so every call pays optimization cost. In a routine with several statements over the temp table, compilation can exceed execution. **Shared space exhaustion.** Temporary space is a global resource that also backs sorts, hash joins, and spills for every session. One job staging a huge intermediate can push unrelated queries into failure or force them to spill differently. Because the failure surfaces in innocent queries, diagnosis is often slow. ## A fourth, sneakier problem: pooled sessions With connection pooling, session-scoped temp tables persist across logical requests on the same physical connection. Two failure modes: stale rows read by an unrelated request, and unbounded accumulation of temp objects on long-lived connections that never disconnect, so the automatic cleanup never fires. ## The fixes, in the order to try them **1. Do you need the temp table at all?** In OLTP paths the intermediate is often small enough to be a derived table, a values list, or an array parameter. Removing the staging step removes every problem above. This is the fix that most often applies and is most often skipped. **2. Create once, reuse per call.** Keep the definition for the session and clear it per invocation — `ON COMMIT DELETE ROWS`, or an explicit truncate/delete at the start of the routine, with creation guarded so it happens only once. Catalog cost drops from per-call to per-session, and plans referencing the stable object become reusable. Oracle's global temporary tables take this further: the definition is permanent and created at deploy time, so no session ever pays DDL cost, while contents remain per-session. **3. Use engine-specific relief.** Memory-optimized tempdb metadata and multiple tempdb files in SQL Server; keeping temp objects small enough to stay in memory in MySQL; on PostgreSQL, aggressive autovacuum settings for the catalogs plus attention to the fact that a long-lived session's temp objects hold catalog entries. **4. Bound the blast radius.** Cap concurrency for the jobs that stage large intermediates, monitor temporary-space usage with alerting well below the limit, and set per-session limits where the engine supports them so one query cannot consume all of it. **5. Batch instead of repeating.** If a routine is called ten thousand times because the application loops row by row, the real fix is set-based: one call staging ten thousand rows beats ten thousand calls staging one each — which also collapses ten thousand round trips into one. ## Diagnosing it Look for wait or lock events on system catalog objects and allocation structures rather than user tables; a high rate of compilations relative to executions; growth in catalog table sizes not explained by schema changes; and temporary-space usage that tracks call rate rather than data volume. The tell that separates this from ordinary load is that the contention sits on metadata while business tables look idle. ## What a strong answer includes Name the mechanism (DDL against shared catalogs, not row writes), the three consequences (metadata contention, recompilation, shared space), and a fix ladder that starts with eliminating the staging step rather than tuning around it. Mentioning that the same procedure is harmless at low call rates shows you understand it is a rate problem, not a design taboo.