SQL Server选择进入并用临时表阻塞

那条建议一直在流传 for a long time :

SQL Server 6.5中的瓶颈

许多人使用select…into查询 创建临时表 这样地:

选择*到诱人的 源表

当它工作时,它会创建锁 针对tempdb数据库 select语句的持续时间 (如果你在拖网的话很长一段时间 通过源中的大量数据 桌子,如果 select…into位于 长时间运行的显式事务) 当锁到位时,没有其他的 用户可以创建临时表。这个 瓶颈的实际位置是 锁定tempdb系统表。在以后 SQL Server的版本,锁定 模型已更改,问题是 避免。

幸运的是,这只是SQL 6.5的一个问题。它在7.0和之后被修复。

这可能会漂浮很长一段时间,为各种“顾问”提供资金。像所有的神话一样,它有一个核心的真理和很多的基础知识。

事实:SQL2000和以前的版本都知道tempdb中数据块分配的争用问题。事实上,在所有数据库中争用都是正确的,但由于tempdb的使用量很大,所以在tempdb中更容易看到争用。记录在 KB328551 :

当tempdb数据库很重时 使用时,SQL Server可能会遇到 试图分配时的争用 页。

从sysprocesses系统表 输出,等待资源可能出现 如“2:1:1”(PFS页面)或“2:1:3”(SGAM 页)。取决于 争用,这也可能导致SQL 服务器似乎没有响应 短时间。

这些操作大量使用tempdb:
重复创建和删除临时 表(本地或全局)。
使用tempdb进行存储的表变量 目的。
与关联的工作表 游标。
与关联的工作表 ORDER BY子句。
与group by子句关联的工作表。
与哈希计划关联的工作文件。

大量和大量使用这些 活动可能导致争用 问题。

跟踪标志 -T1118 是在SQL Server 2000 SP3中添加的,它强制SQL对混合页分配使用循环算法。当这种新算法与在一组大小相等的文件(每个CPU一个)上部署tempdb的实践相关联时,将减轻争用。跟踪标志仍然存在于SQL 2005/2008中,尽管它的需求可能性要小得多。

关于这个神话的所有其他东西几乎都是B。

• 使用临时表是否会导致阻塞?不,在最坏的情况下 增加负载下的争用 在SQL 2000和更早的版本中,这与说它阻塞了任何东西相差甚远。您必须首先进行测量,并看到情况确实如此,如果是这样,那么部署补救措施(为每个CPU分配一个tempdb文件,使其大小相等,打开-t118)。
• 是否选择…进入温度块在选择期间的某个东西?不是真的。
• 是否选择…进入temp块,在包含select的存储过程期间有什么东西?该死的,不。只要读了那篇文章,我就大笑起来。

有关详细信息,请参阅本文: Misconceptions around TF1118 .

为什么不执行以下操作?

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

该语句将立即运行-创建临时表并避免任何可能的锁定。然后您可以像往常一样插入它:

INSERT #MyTable
SELECT X, Y, Z
FROM YourTable

如果在事务内部创建临时表,则可能会被阻塞。虽然这通常是不推荐的,但我已经看到这做了很多。

但是,阻塞这一原因发生在tempdb中的某些系统表上,这些表不会影响其他连接创建临时表(除了2000之前的SQL版本?)。这确实意味着,除非将事务隔离级别设置为“未提交读取”,否则在tempdb上运行sp_spacesused将被阻止。此外,从SSMS查看tempdb的属性将失败并超时,这是毫无疑问的,因为它使用的是读提交事务隔离级别。

我想说,没有锁的证明就意味着没有锁,这就是你的证明。为什么要在其中创建临时表的方法(创建或选择…into)在锁定tempdb方面有所不同?

如果这是真的,那么MSSQL将有问题,因为任何大型查询都可以使用tempdb来保存行的副本。在查询计划中,这通常被视为一个表假脱机,或者如果散列联接运算符的存储桶内存不足,则可以使用它。

您可以查看使用表变量,如果表变量变大,MSSQL将尝试将其存储在内存中并移动到tempdb。

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

当然,您应该首先评估是否需要临时表和副本。不过,如果查询足够复杂,使用临时表的可读性要高得多,那么它也可能足够复杂,使临时表值得使用。

SELECT INTO #temp_table 在语句期间在tempdb中持有shema锁,因为它完成的工作的一部分是创建表。这与第一次使用 CREATE TABLE #.... 然后运行基于集合的插入。 SELECT INTO 确实比 INSERT 特别是,如果数据库的恢复模型是简单的或大容量的日志,则该操作的日志记录最少。

虽然select into已从阻塞tempdb中修复,但在编写诸如“测试中”之类的代码时,我会小心一些,因为某些系统表确实会被阻塞。

参考文献: http://www.sqlservercentral.com/Forums/Topic1642797-2799-1.aspx