SQL Server Performance Forum – Threads Archive

reducing locks on tempdb

I read in the articles and forum topics that "SELECT INTO … #temptable" creates locks on tempdb so we should use "INSERT #temptable SELECT … Also we should create temp tables at the beginning of sp to avoid recompiles". I also read that while we use "INSERT #temptable EXECUTE spName" from within stored procedure, locks on syscolumns, sysobjects, and sysindexes on tempdb are created, and so we should avoid using "INSERT #temptable EXECUTE spName". I suppose "INSERT #temptable SELECT …" statement also causes locks to be created on those tables, too. Those of three statements use tempdb except "SELECT INTO … #temptable" because this also creates a temp table, and so this statement’s execution time is longer. Am I wrong or if I’m right, then what should we use instead of "INSERT #temptable EXECUTE spName"?

---

I know it could be an issue in SQL 7, not sure if it’s an issue anymore in SQL 2000. You could always create your table in advance before inserting.

---

]]>