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.
]]>