sql-server - 为什么启动服务后第一次此查询的速度变慢？

Question

好。这是我尝试运行的内容：

USE tempdb;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;

这是“使我成为数字表”查询之一。

这是问题所在。如果我在（重新）启动SQL Server服务后立即运行此命令，它将花费很长时间。不会像十秒一样长久，我想要更快。永远如此，我让它意外地经过了两个多小时，但仍然不得不将其杀死。我在想它永远不会回来。通常，我的机器需要不到两秒钟的时间来运行它。

但是，如果我改为这样做：

USE tempdb;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3;

DROP TABLE Numbers;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;

然后，它可以按您期望的那样工作-第一个 SELECT在不到两秒钟的时间内运行，第二秒钟也是如此。为什么我不只使用三表版本？因为 sys.objects中没有足够的条目来表示该数字等于100万个结果行。但这不再是重点。

无论如何，从现在开始，我可以根据需要重复第二个 DROP / SELECT…INTO，没问题。不知何故，第一个三张表的版本永远没问题。至少直到下一次重新启动服务和/或重新启动计算机为止。此时，再次运行最后一个 SELECT永远不会回来。再次。

这就是开始变得更奇怪的地方。如果我将第一个 SELECT切回两张表的版本：

USE tempdb;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2;

DROP TABLE Numbers;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;

这也使第二个 SELECT永远运行。一表版本也是如此。不知何故，那个三张桌子的版本是神奇的！

这里发生了什么？为什么这么慢？

（在有人指出我正在 tempdb中创建永久表之前，是的，我知道。更改为实际的临时表没有任何区别。）



添加的信息：


这是SQL Server 2012 Developer Edition
EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC'的输出（脚本为XML，因此可以在此处读取）为：


<？xml version =“ 1.0”吗？>
<结果1>
<记录>

00 00：10：45.066
52
<？query-
SELECT TOP 1000000 IDENTITY（INT，1，1）数字
INTO号码
从sys.objects s1
交叉联接sys.objects s2
交叉联接sys.objects s3
CROSS JOIN sys.objects s4;

-？>
我自己的登录名已删除
（99ms）LCK_M_X
9,750
713
702
NULL
0
583,273
537
50
3
<状态>已暂停
2
NULL
我自己的计算机名称已删除
tempdb
Microsoft SQL Server Management Studio-查询
2013-11-23 23：48：19.473
2013-11-23 23：47：47.060
0
2013-11-23 23：59：04.560






更多附加信息：

我将其放在tempdb中的原因是，它是旨在在原始安装上运行的脚本的一部分，并且tempdb被保证在那里。如我所说，更改为全局临时表没有什么不同。

Answer 1

我也可以在机器上100％地重现此时间。 （请参阅末尾的注释）

问题的要点是您要对S中的系统表行取消tempdb锁，这些锁可能与内部tempdb清理事务所需的锁冲突。

当此清理工作分配给拥有S锁的同一会话时，可能会发生不确定的挂起。

为确保避免此问题，您需要停止引用system中的tempdb对象。

可以完全不引用任何外部表的情况下创建数字表。以下内容不需要读取任何基表行，因此也不需要任何锁。

WITH Ten(N) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)   
SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO   Numbers
FROM   Ten T10,
       Ten T100,
       Ten T1000,
       Ten T10000,
       Ten T100000,
       Ten T1000000 

重现步骤

首先创建一个程序

CREATE PROC P
AS
    SET NOCOUNT ON;

    DECLARE @T TABLE (X INT)
GO

然后重新启动SQL Service并在一个连接中执行

WHILE NOT EXISTS(SELECT *
                 FROM   sys.dm_os_waiting_tasks
                 WHERE  session_id = blocking_session_id)
  BEGIN

      /*This will cause the problematic droptemp transactions*/
      EXEC sp_recompile 'P'

      EXEC P
  END;

SELECT *
FROM   sys.dm_os_waiting_tasks
WHERE  session_id = blocking_session_id 

然后在另一个连接中运行

USE tempdb;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO #T
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;

DROP TABLE #T

填充Numbers表的查询似乎设法通过清理临时对象（例如表变量）的内部系统事务来解决活动锁定问题。

我设法以这种方式阻止了会话ID 53。它被无限期地阻止。 sp_WhoIsActive的输出显示此spid几乎花费了所有暂停时间。在连续运行中， reads列中的数字增加，但其他列中的值基本保持不变。

等待时间并未显示增加的模式，尽管它表明必须定期取消阻止它才能再次被阻止。

SELECT *
FROM   sys.dm_os_waiting_tasks
WHERE  session_id = blocking_session_id

退货

+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+
| waiting_task_address | session_id | exec_context_id | wait_duration_ms | wait_type |  resource_address  | blocking_task_address | blocking_session_id | blocking_exec_context_id |                                       resource_description                                       |
+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+
| 0x00000002F2C170C8   |         53 |               0 |               86 | LCK_M_X   | 0x00000002F9B13040 | 0x00000002F2C170C8    |                  53 | NULL                     | keylock hobtid=281474978938880 dbid=2 id=lock2f9ac8880 mode=U associatedObjectId=281474978938880 |
+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+

在资源说明中使用ID

SELECT o.name
FROM   sys.allocation_units au WITH (NOLOCK)
       INNER JOIN sys.partitions p WITH (NOLOCK)
         ON au.container_id = p.partition_id
       INNER JOIN sys.all_objects o WITH (NOLOCK)
         ON o.object_id = p.object_id
WHERE  allocation_unit_id = 281474978938880 

退货

+------------+
|    name    |
+------------+
| sysschobjs |
+------------+

跑步

SELECT resource_description,request_status
FROM   sys.dm_tran_locks 
WHERE request_session_id = 53 AND request_status <> 'GRANT'

退货

+----------------------+----------------+
| resource_description | request_status |
+----------------------+----------------+
| (246708db8c1f)       | CONVERT        |
+----------------------+----------------+

通过DAC连接并运行

SELECT id,name
FROM   tempdb.sys.sysschobjs WITH (NOLOCK)
WHERE %%LOCKRES%% = '(246708db8c1f)' 

退货

+-------------+-----------+
|     id      |   name    |
+-------------+-----------+
| -1578606288 | #A1E86130 |
+-------------+-----------+

好奇那是什么

SELECT name,user_type_id
FROM tempdb.sys.columns
WHERE object_id = -1578606288 

退货

+------+--------------+
| name | user_type_id |
+------+--------------+
| X    |           56 |
+------+--------------+

这是存储的proc使用的表变量中的列名。

跑步

SELECT request_mode,
       request_status,
       request_session_id,
       request_owner_id,
       lock_owner_address,
       t.transaction_id,
       t.name,
       t.transaction_begin_time
FROM   sys.dm_tran_locks l
       JOIN sys.dm_tran_active_transactions t
         ON l.request_owner_id = t.transaction_id
WHERE  resource_description = '(246708db8c1f)' 

退货

+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+
| request_mode | request_status | request_session_id | request_owner_id | lock_owner_address | transaction_id |    name     | transaction_begin_time  |
+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+
| U            | GRANT          |                 53 |           227647 | 0x00000002F1EF6800 |         227647 | droptemp    | 2013-11-24 18:36:28.267 |
| S            | GRANT          |                 53 |           191790 | 0x00000002F9B16380 |         191790 | SELECT INTO | 2013-11-24 18:21:30.083 |
| X            | CONVERT        |                 53 |           227647 | 0x00000002F9B12FC0 |         227647 | droptemp    | 2013-11-24 18:36:28.267 |
+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+

因此， SELECT INTO事务在 S中与表变量 tempdb.sys.sysschobjs有关的行上持有 #A1E86130锁。由于冲突的 droptemp， X事务无法在此行上获得 S锁。

重复运行此查询将显示 transaction_id事务的 droptemp反复更改。

我推测SQL Server必须在用户spid上分配这些内部事务，并在进行用户工作之前确定它们的优先级。因此，会话ID 53停留在一个恒定的周期中，在该周期中它启动 droptemp事务，但该进程被在同一spid上运行的用户事务阻止。回滚内部事务，然后无限期地重复该过程。

通过在spid挂起之后跟踪SQL Server Profiler中的各种锁定和事务事件，可以证明这一点。



在此之前，我还跟踪了锁定事件。

锁定事件阻止



SELECT INTO事务对 sysschobjs中的键进行的大多数共享键锁定都将立即释放。唯一的例外是 (246708db8c1f)上的第一个锁。

由于该计划显示了 [sys].[sysschobjs].[clst] [o]的嵌套循环扫描，因此这是有道理的，并且由于临时对象获得了负的对象ID，因此它们将是扫描顺序中遇到的第一行。

我还遇到了OP中描述的情况，首先运行三向交叉联接似乎可以使四向交叉成功。

SELECT INTO事务的跟踪中的前几个事件存在完全不同的模式。



这是在服务重新启动之后，因此text数据列中的锁定资源值不能直接比较。

与其保留第一个键的锁定，然后是获取和释放后续键的模式，不如先获取更多的锁，而不必先释放它们。

我认为执行策略中必须有一些避免该问题的方法。



更新资料

The Connect Item I raised about this尚未被标记为固定，但我现在使用的是SQL Server 2012 SP2，现在只能重现临时的自我阻止，而不是永久的。我仍然得到自我阻止，但是在成功执行 droptemp事务的尝试失败了一些之后，它似乎又回到了处理用户事务的过程。之后，提交系统事务，然后成功执行。仍在同一个spid上。 （在一个示例中运行了8次尝试。我不确定是否会一再重复此操作）