По материалам статьи Neil Boyle на SWYNK.COM «Avoiding bottlenecks with temporary tables»

Нейл пишет, что использование временных таблиц может стать причиной появления узких мест на вашем сервере баз данных. Проблемы блокировок tempdb могут наблюдаться в 6.5 и 7 версиях SQL сервера. В версии 6.5, часто используют select...into, для создания временной таблицы, например:

select *
   into #tempTable
   from source

В течение исполнения этого запроса, временная база данных будет заблокирована, пока не закончится вся выборка. Это может занимать много времени, если исходная таблица содержит большое количество данных. Во время блокировки, другие пользователи не могут создавать временные таблицы. Фактическое, узкое место в том, что блокируются системные таблицы tempdb (sysobjects). В более поздних версиях SQL сервера модель блокировок изменилась, что позволяет избегать этой проблемы.
Для того, что бы максимально смягчить последствия блокировок в tempdb необходимо добиться, что бы операции "create temporary table" исполнялись настолько быстро насколько это возможно. Например, можно использовать следующую конструкцию: 

create table #temp(
    ........
    )
    insert #temp
    select *
    from sourceTable

В этом примере вначале создаётся временная таблица, которая сразу же освобождается в sysobjects. Блокировка снимается настолько быстро насколько это возможно.
Если Вам необходимо избегать инструкций insert...into, или если Вы пишете только отдельный модуль кода и не знаете точное определение таблицы до момента выполнения, Вы можете воспользоваться следующей уловкой:

select *
    into #temp
    from sourceTable
    where 1 = 0

    insert #temp
    select *
    from sourceTable

Очевидно, что условие where 1 = 0 никогда не будет истиной. Независимо от того, сколько данных sourceTable находятся в оптимизаторе, SQL сервер достаточно умён, чтобы понять, что не стоит тратить время на просмотр таблицы. (Нейл всегда проверял план исполнения такого запроса, что бы в этом удостоверится, но никогда оптимизатор не выполнял просмотр таблицы). Не смотря на то, что SQL сервер не будет просматривать исходную таблицу, таблица *temp  будет определена и основана на формате данных из инструкции отбора, но не будет содержать ни одной строки. После этого Вы можете выполнять insert...select, не опасаясь, что будут блокированы другие процессы доступа к tempdb.

Описанная выше проблема не относится к MS SQL Server версии 7, но есть - один случай, когда Вы можете неумышленно создать узкое место под любой версией.

Проблема возникает, когда Вы используете INSERT...EXEC, чтобы загрузить временную таблицу, и саму хранимую процедуру создающую временную таблицу. Вы столкнётесь с блокировкой tempdb подобной описанной выше. Представленные выше уловки для 6.5 вряд ли Вам здесь помогут, а сама конструкция "INSERT #temp EXEC server.database.owner.proc" не идеальна во всех возможных применениях. Для получения более детального описания проблем блокировок временных таблиц и путей их разрешения, прочтите следующие статьи:
http://support.microsoft.com/support/kb/articles/Q153/4/41.asp
http://support.microsoft.com/support/kb/articles/Q162/7/53.asp