SQL Server SELECT INTO и блокировка с помощью таблиц Temp

Ответ 1

Этот совет плавал вокруг в течение длительного времени:

Узкие места в SQL Server 6.5

Многие люди используют запрос SELECT... INTO создать временную таблицу, что-то например:

SELECT * INTO #TempTable FROM SourceTable

Пока это работает, оно создает блокировки против базы данных tempdb для продолжительность оператора SELECT (довольно долго, если вы тратите через множество данных в источнике таблицы и дольше, если SELECT... INTO находится в начале более длительная явная транзакция) Пока замок на месте, никаких других пользователь может создавать временные таблицы. фактическое местоположение узкого места является блокировка системных таблиц tempdb. В дальнейшем версии SQL Server, блокировка модель изменилась, и проблема избегать.

К счастью, это была только проблема для SQL 6.5. Это было исправлено в 7.0 и более поздних версиях.

Ответ 2

Это, вероятно, будет длиться долгое время, подавая карманы различных "консультантов". Как и все мифы, у него есть ядро ​​истины и много BS.

Правда: SQL 2000 и предыдущие версии имели известные проблемы, связанные с распределением экстентов в tempdb. Утверждение действительно было во всех базах данных, но более заметным в tempdb из-за сильного использования tempdb. Он документирован в KB328551:

Когда база данных tempdb сильно SQL Server может возникнуть когда он пытается выделить страницы.

Из системной таблицы sysprocesses вывода, может появиться waitresource как "2: 1:1" (страница PFS) или "2: 1: 3" (SGAM Page). В зависимости от степени Конфликт, это также может привести к SQL Сервер не реагирует на короткие периоды.

Эти операции сильно используют tempdb:
Повторное создание и падение временных таблицы (локальные или глобальные).
Переменные таблицы, которые используют tempdb для хранения целей.
Рабочие таблицы, связанные с Курсоров.
Рабочие таблицы, связанные с предложение ORDER BY.
Рабочие таблицы, связанные с предложением GROUP BY.
Рабочие файлы, связанные с HASH PLANS.

Тяжелое и значительное использование этих действия могут привести к проблемы.

В SQL Server 2000 с пакетом обновления 3 (SP3) был добавлен флаг трассировки -T1118, который заставлял SQL использовать циклический алгоритм для распределения смешанных страниц. Этот новый алгоритм, когда он коррелирует с практикой развертывания tempdb поверх набора файлов одинакового размера, по одному для каждого процессора, будет способствовать разрешению конфликта. Флаг трассировки по-прежнему присутствует в SQL 2005/2008, хотя его гораздо менее вероятно потребуется.

Все остальное в этом мифе - это в значительной степени BS.

• Использует ли таблицы #temp блокировку? Нет. В худшем случае это увеличивает конкуренцию под нагрузкой в ​​SQL 2000 и более ранних версиях, но это далеко не означает, что он блокирует что-либо. Сначала вам нужно будет измерить и увидеть, что это так, и если это так развернуть меры по исправлению (выделить один файл tempdb на один процессор, сделать их равными по размеру, включить -T1118).
• Выбирает... в #temp блокирует что-то на время выбора? Не совсем.
• Выбирает ли... в #temp что-то блокирует в течение хранимой процедуры, содержащей выбор? Конечно нет. Просто прочитав это утверждение, и я рассмеялся.

Подробнее см. в этой статье: Заблуждения вокруг TF1118.

Ответ 3

Почему бы не сделать следующее?

SELECT X, Y, Z
INTO #MyTable
FROM YourTable
WHERE 1 = 2

Операция запускается мгновенно - создание вашей временной таблицы и исключение возможной блокировки. Затем вы можете вставить в него как обычно:

INSERT #MyTable
SELECT X, Y, Z
FROM YourTable

Ответ 4

Вы можете заблокировать, если вы создаете таблицы #temp внутри транзакции. Хотя это, как правило, не рекомендуется, я видел это много.

Однако блокировка этой причины связана с некоторыми системными таблицами в tempdb, которые не влияют на другие соединения из создания временных таблиц (за исключением, возможно, для SQL-версий до 2000?). Это означает, что запуск sp_spacesused на tempdb будет заблокирован, если вы не установите уровень изоляции транзакции для чтения без права доступа. Также просмотр свойств на tempdb из SSMS завершится с ошибкой и без ожидания, потому что он использует уровень изоляции совершенной транзакции чтения.

Ответ 5

Я бы сказал, что отсутствие фиксации означает отсутствие блокировки, что является вашим доказательством. Почему метод, созданный в таблице temp (CREATE или SELECT... INTO), влияет на блокировку TempDB?

Ответ 6

Ну, если это правда, то mssql будет иметь проблемы, так как любой большой запрос может использовать tempdb для хранения копии строк. Это часто можно увидеть в планах запросов в виде столбчатой ​​катушки или может использоваться оператором HASH JOIN, если у него не хватает памяти для своих ковшей.

Вы можете посмотреть на использование переменных таблицы, которые mssql попытается сохранить в памяти и переместиться в tempdb, если они вырастут до больших.

DECLARE @foo TABLE (x int, y int, z int)
INSERT INTO @foo(x, y, z) SELECT x, y, z FROM YourTable

Конечно, вы должны оценить, требуется ли сначала временная таблица и копия. Хотя, если запрос достаточно сложный, что использование таблицы temp является более читаемым, оно также может быть достаточно сложным, чтобы таблица temp была полезной.

Ответ 7

SELECT INTO #temp_table хранит блокировку shema в tempdb на протяжении действия инструкции, потому что часть выполняемой работы создает таблицу. Это принципиально отличается от первого создания таблицы с помощью CREATE TABLE #.... и последующего запуска набора INSERT. SELECT INTO имеет преимущества по сравнению с INSERT, в частности операция минимально регистрируется, если модель восстановления базы данных проста или заполнена журналом.

Ответ 8

В то время как SELECT INTO был исправлен из блокировки tempdb, я буду соблюдать осторожность при написании такого кода, как при тестировании, некоторые системные таблицы блокируются.

Ссылка: http://www.sqlservercentral.com/Forums/Topic1642797-2799-1.aspx