SQL Server中的SELECT INTO和使用临时表阻塞问题

Question

最近，一位数据库管理员告诉我们不能使用以下语法：

SELECT X, Y, Z
INTO #MyTable
FROM YourTable

在我们的环境中创建临时表时，因为这种语法会导致存储过程在执行期间锁定TempDB。现在，我找到了许多关于临时表的工作方式、执行范围、清理等方面的信息。但是对于我来说，我没有看到任何关于由于使用它们而导致阻塞的信息。

我们正在努力寻找证据，证明我们不必为所有临时表执行CREATE TABLE #MyTable...，但双方都找不到证据。我正在寻找SO社区用户能提供的任何见解。

附加信息

目前使用的是SQL Server 2005，并即将升级到SQL Server 2008（企业版）。

- Mitchel Sellers

Answer 1

这个建议已经很久以前就流传开来了：

SQL Server 6.5的瓶颈

许多人使用SELECT...INTO查询来创建一个临时表，像这样：

SELECT * INTO #TempTable FROM SourceTable

虽然这有效，但它会在整个SELECT语句的持续时间内对tempdb数据库进行锁定（如果您正在浏览源表中的大量数据，则需要相当长的时间，如果SELECT...INTO位于较长运行的显式事务的开头，则需要更长时间）。 当锁定发生时，没有其他用户可以创建临时表。 瓶颈实际上是对tempdb系统表的锁定。 在SQL Server的后续版本中，锁定模型已更改，因此避免了此问题。

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

- BradC

---

请问您能否更新您的答案，加入SQL的新功能呢？因为您的答案在谷歌搜索中排名第一，所以它应该适用于最新版本。谢谢！:) - Raj Shah

@RajShah 什么已经过时了？除非后来的版本再次出现问题，否则我无法想象“它在7.0及更高版本中已经修复。”这句话会过时。（而“对于6.5来说是个问题”肯定不会）。 - OJFord

Answer 2

这个错误观念会长期存在，滋养着各种“顾问”的口袋。就像所有神话一样，它有一点真实性和很多胡说八道。

事实上，SQL 2000及之前的版本在分配tempdb中的extents方面存在已知的争用问题，虽然所有数据库都存在争用问题，但由于tempdb的大量使用，该问题在tempdb中更加明显。据KB328551所述:

当tempdb数据库被大量使用时，SQL Server在尝试分配页面时可能会出现竞争。

从sysprocesses系统表输出中，waitresource可能显示为"2:1:1"(PFS Page)或"2:1:3"(SGAM Page)。根据竞争的程度，这也可能导致SQL Server在短时间内看起来无响应。

以下操作大量使用tempdb: 重复创建和删除临时表(本地或全局)。 使用tempdb存储目的的表变量。 与CURSOR相关联的工作表。 与ORDER BY子句相关联的工作表。 与GROUP BY子句相关联的工作表。 与HASH PLANS相关联的工作文件。

这些活动的重度和显着使用可能导致争用问题。

在SQL Server 2000 SP3中添加了一个跟踪标志-T1118，它强制SQL使用循环分配混合页面的算法。当将tempdb部署在一组大小相等的文件(每个CPU一个)之上时，该新算法可以缓解争用。虽然该跟踪标志仍存在于SQL 2005/2008中，但需要使用的情况较少。

关于这个错误观念的所有其他内容都是胡说八道。

#temp表的使用会导致阻塞吗？不会。最多只会在SQL 2000及早期版本中增加负载下的争用，但这远非表示它会阻塞任何内容。您需要先进行测量并确认情况，如果确实存在，则要使用纠正措施（为每个CPU分配一个tempdb文件，使它们大小相等，打开-T1118）。

select ... into #temp会在select过程中阻塞某些东西吗？实际上不会。

select ... into #temp会在包含该select语句的存储过程的持续时间内阻塞某些东西吗？当然不会。仅是读取该声明，我就忍不住笑了。

更多详情，请参见此文章：TF1118周围的误解。

- Remus Rusanu

---

如果[select ... into #temp]在事务中，那么在事务提交之前，在任何其他会话中执行[select * from sysobjects]都将被阻塞。 - Roy Liu

Answer 3

为什么不这样做？

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

这个语句可以立即执行 - 创建您的临时表并避免任何可能的锁定。 然后，您可以像通常一样插入数据：

INSERT #MyTable
SELECT X, Y, Z
FROM YourTable

- Eric Pelot

Answer 4

如果在事务内部创建#temp表，可能会导致阻塞。虽然通常不建议这样做，但我经常看到这样的情况。

然而，这种阻塞是在tempdb中的一些系统表上引起的，不会影响其他连接创建临时表（除了SQL 2000之前的版本？）。这意味着运行sp_spacesused命令时将会阻塞，除非您将事务隔离级别设置为读取未提交的数据。此外，从SSMS查看tempdb属性将失败并超时，无疑是因为它使用了读取提交的事务隔离级别。

- IainS

Answer 5

虽然SELECT INTO已经被修复，不会阻塞tempdb，但在编写此类代码时仍需谨慎，因为在测试过程中某些系统表可能会被阻塞。

Reference: http://www.sqlservercentral.com/Forums/Topic1642797-2799-1.aspx

- SeeCoolGuy

Answer 6

如果这是真的，那么mssql就会有问题，因为任何大型查询都可以利用tempdb来保存行的副本。这通常可以在查询计划中看到，例如表卷或者HASH JOIN操作符如果其桶用完了内存。

您可以考虑使用表变量，它们将尝试在内存中存储，并在它们增长到大时移动到tempdb。

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

当然，你应该先评估是否需要临时表和复制。但是，如果查询足够复杂，使用临时表会更易读，并且可能值得使用临时表。

- Chris Chilvers

Answer 7

SELECT INTO #temp_table 在执行期间会在 tempdb 中持有模式锁，因为它所完成的工作之一是创建表。这与首先使用 CREATE TABLE #.... 创建表，然后运行基于集合的 INSERT 是根本不同的。相对于 INSERT，SELECT INTO 确实具有优势，特别是如果数据库的恢复模型是简单或批量日志记录，则操作将被最小化记录。

- Michael Lutz

Answer 8

我认为缺乏锁定证明意味着没有锁定，这就是你的证明。为什么创建临时表的方法（CREATE还是SELECT ... INTO）会对锁定TempDB产生影响呢？

- KM.

---

这是我们的想法和论点......但是他们坚持认为使用SELECT INTO会在存储过程的持续时间内导致阻塞。 - Mitchel Sellers

我想知道为什么临时表被锁定也是一个问题。如果它是全局临时表，那就不同了。 - OMG Ponies

@rexem：OP 的 DBA 没有说临时表被锁定，而是 TempDB，所有临时表和其他临时存储的位置都被锁定。我不知道这位 DBA 是否正确，我将听取更明智的人的意见。 - Shannon Severance