sql-server – 在这种特定情况下,为什么使用表变量的速度是#temp

在另一个连接中轮询sys.dm_os_waiting_tasks

CREATE TABLE #T(resource_description NVARCHAR(2048))

WHILE 1=1
INSERT INTO #T
SELECT resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id=<spid_of_other_session> and wait_type='PAGELATCH_SH'

离开运行约15秒后,它收集了以下结果

+-------+----------------------+
| Count | resource_description |
+-------+----------------------+
|  1098 | 2:1:150              |
|  1689 | 2:1:146              |
+-------+----------------------+

这两个被锁存的页面都属于名为’nc1’和’nc2’的tempdb.sys.sysschobjs基表上的(不同的)非聚簇索引.

在运行期间查询tempdb.sys.fn_dblog表示由第一次执行每个存储过程添加的日志记录的数量有些变化,但对于后续执行,每次迭代添加的数量非常一致且可预测.缓存过程计划后,日志条目的数量大约是#temp版本所需的一半.

+-----------------+----------------+------------+
|                 | Table Variable | Temp Table |
+-----------------+----------------+------------+
| First Run       |            126 | 72 or 136  |
| Subsequent Runs |             17 | 32         |
+-----------------+----------------+------------+

查看SP的#temp表版本的事务日志条目,每次后续调用存储过程都会创建三个事务,而表变量只有两个.

+---------------------------------+----+---------------------------------+----+
|           #Temp Table                |         @Table Variable              |
+---------------------------------+----+---------------------------------+----+
| CREATE TABLE                    |  9 |                                 |    |
| INSERT                          | 12 | TVQuery                         | 12 |
| FCheckAndCleanupCachedTempTable | 11 | FCheckAndCleanupCachedTempTable |  5 |
+---------------------------------+----+---------------------------------+----+

除名称外,INSERT / TVQUERY事务是相同的.它包含插入临时表或表变量的10行中的每一行的日志记录以及LOP_BEGIN_XACT / LOP_COMMIT_XACT条目.

CREATE TABLE事务仅出现在#Temp版本中,如下所示.

+-----------------+-------------------+---------------------+
|    Operation    |      Context      |    AllocUnitName    |
+-----------------+-------------------+---------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                     |
| LOP_SHRINK_NOOP | LCX_NULL          |                     |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1  |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2  |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_COMMIT_XACT | LCX_NULL          |                     |
+-----------------+-------------------+---------------------+

FCheckAndCleanupCachedTempTable事务同时出现在#temp版本中,但有6个附加条目.这些是指向sys.sysschobjs的6行,它们具有与上面完全相同的模式.

+-----------------+-------------------+----------------------------------------------+
|    Operation    |      Context      |                AllocUnitName                 |
+-----------------+-------------------+----------------------------------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                                              |
| LOP_DELETE_ROWS | LCX_NONSYS_SPLIT  | dbo.#7240F239.PK__#T________3BD0199374293AAB |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1                           |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2                           |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_COMMIT_XACT | LCX_NULL          |                                              |
+-----------------+-------------------+----------------------------------------------+

在两个事务中查看这6行,它们对应于相同的操作.第一个LOP_MODIFY_ROW,LCX_CLUSTERED是sys.objects中modify_date列的更新.剩下的五行都与对象重命名有关.因为name是受影响的NCI(nc1和nc2)的一个关键列,所以它作为删除/插入执行,然后它返回到聚簇索引并更新它.

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!