This will probably float around for a long time, feeding the pockets of various 'consultants'. Like all myths, it has a kernel of truth and a lot of BS.
The truth: SQL 2000 and previous versions had known contention issues around the allocation of extents in tempdb. The contention was true in fact in all databases, but more visible in tempdb due to some heavy tempdb usage. It is documented in KB328551:
When the tempdb database is heavily
used, SQL Server may experience
contention when it tries to allocate
pages.
From the sysprocesses system table
output, the waitresource may show up
as "2:1:1" (PFS Page) or "2:1:3" (SGAM
Page). Depending on the degree of
contention, this may also lead to SQL
Server appearing unresponsive for
short periods.
These operations heavily use tempdb:
Repeated create and drop of temporary
tables (local or global).
Table variables that use tempdb for storage
purposes.
Work tables associated with
CURSORS.
Work tables associated with
an ORDER BY clause.
Work tables associated with an GROUP BY clause.
Work files associated with HASH PLANS.
Heavy and significant use of these
activities may lead to the contention
problems.
A trace flag -T1118
was added in SQL Server 2000 SP3 that was forcing SQL to use a round-robin algorithm for mixed pages allocations. This new algorithm, when correlated with the practice of deploying tempdb on top of a set of equal size files, one for each CPU, would alleviate the contention. The trace flag is still present in SQL 2005/2008, although its far less likely to be needed.
Everything else about this myth is pretty much BS.
- does use of #temp tables cause blocking? No. At worst it increases contention under load in SQL 2000 and earlier, but that is a far cry from saying that it blocks anything. You would have to measure first and see that this is the case, and if so deploy the remediation measures (allocate one tempdb file per CPU, make them equal size, turn on -T1118)..
- Does select ... into #temp block something for the duration of the select? Not really.
- Does select ... into #temp block something for the duration of the stored procedure containing the select? Hell no. Just reading that claim and I burst into laugh.
For more details, there is this article: Misconceptions around TF1118.