SQL Server SELECT INTO 和临时表阻塞 [英] SQL Server SELECT INTO and Blocking With Temp Tables

问题描述

所以，最近一个 DBA 试图告诉我们，我们不能使用

So, recently a DBA is trying to tell us that we cannot use the syntax of

SELECT X, Y, Z
INTO #MyTable
FROM YourTable

在我们的环境中创建临时表，因为该语法会导致在存储过程执行期间锁定 TempDB.现在，我发现了许多详细说明临时表如何工作、执行范围、清理等的内容.但在我的一生中，我看不到因为它们的使用而阻塞的任何事情.

To create temporary tables in our environment, because that syntax causes a lock on TempDB for the duration of the stored procedure executing. Now, I've found a number of things that detail how temporary tables work, scope of execution, cleanup and the like. But for the life of me, I don't see anything about blocking because of their use.

我们正在努力寻找证据，证明我们不应该对所有临时表执行 CREATE TABLE #MyTable...，但双方都找不到证据.我正在寻找人们拥有的任何见解.

We are trying to find proof that we shouldn't have to go through and do CREATE TABLE #MyTable... for all of our temporary tables, but neither side can find proof. I'm looking for any insight SO people have.

其他信息

目前使用 SQL Server 2005，不久将使用 SQL Server 2008(企业版)

Currently working with SQL Server 2005, and soon to be SQL Server 2008 (Enterprise editions)

推荐答案

这个建议一直在流传 很长一段时间:

That advice has been floating around for a long time:

SQL Server 6.5 中的瓶颈

Bottlenecks in SQL Server 6.5

许多人使用 SELECT...INTO 查询创建一个临时表，一些东西像这样:

Many people use a SELECT...INTO query to create a temporary table, something like this:

SELECT * INTO #TempTable FROM源表

SELECT * INTO #TempTable FROM SourceTable

虽然这有效，但它会创建锁针对 tempdb 数据库SELECT 语句的持续时间(如果你在拖网，很长一段时间通过源中的大量数据表，如果SELECT...INTO 位于 a 的开头运行时间更长的显式事务)当锁就位时，没有其他用户可以创建临时表.这瓶颈的实际位置是锁定 tempdb 系统表.在以后SQL Server 的版本，锁定模型已经改变，问题是避免.

While this works, it creates locks against the tempdb database for the duration of the SELECT statement (quite a while if you are trawling through a lot of data in the source table, and longer still if the SELECT...INTO is at the start of a longer-running explicit transaction) While the lock is in place, no other user can create temporary tables. The actual location of the bottleneck is a lock on tempdb system tables. In later versions of SQL Server, the locking model has changed and the problem is avoided.

幸运的是，这只是 SQL 6.5 的问题.它已在 7.0 及更高版本中修复.

Fortunately, it was only a problem for SQL 6.5. It was fixed in 7.0 and later.

这篇关于SQL Server SELECT INTO 和临时表阻塞的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！