Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance和sql Server 2008能够再现与2005年那里显示的结果类似的结果.
当执行只有10行的存储过程(下面的定义)时,表变量版本out执行临时表版本的次数超过两次.
我清除了程序缓存并运行了两次存储过程,然后重复该过程再运行4次.以下结果(每批ms的时间)
T2_Time V2_Time ----------- ----------- 8578 2718 6641 2781 6469 2813 6766 2797 6156 2719
我的问题是：表变量版本性能更好的原因是什么？
我做了一些调查.例如用性能计数器看
SELECT cntr_value from sys.dm_os_performance_counters where counter_name = 'Temp Tables Creation Rate';
确认在两种情况下,临时对象在第一次运行as expected之后被缓存,而不是在每次调用时从头开始再次创建.
类似地跟踪Profiler中的Auto Stats,SP：Recompile,sql：StmtRecompileevents(下面的屏幕截图)显示这些事件只发生一次(在第一次调用#temp表存储过程时),而其他9,999次执行不会引发任何这些事件事件. (表变量版本不会获得任何这些事件)
第一次运行存储过程的稍微大一些的开销绝不能解释大的整体差异,但是因为它仍然只需要几毫秒来清除过程高速缓存并运行两个过程所以我不相信统计数据或重新编译可能是原因.
创建所需数据库对象
CREATE DATABASE TESTDB_18Feb2012;
GO
USE TESTDB_18Feb2012;
CREATE TABLE NUM
(
n INT PRIMARY KEY,s VARCHAR(128)
);
WITH NUMS(N)
AS (SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY $/0)
FROM master..spt_values v1,master..spt_values v2)
INSERT INTO NUM
SELECT N,'Value: ' + CONVERT(VARCHAR,N)
FROM NUMS
GO
CREATE PROCEDURE [dbo].[T2] @total INT
AS
CREATE TABLE #T
(
n INT PRIMARY KEY,s VARCHAR(128)
)
INSERT INTO #T
SELECT n,s
FROM NUM
WHERE n%100 > 0
AND n <= @total
DECLARE @res VARCHAR(128)
SELECT @res = MAX(s)
FROM NUM
WHERE n <= @total
AND NOT EXISTS(SELECT *
FROM #T
WHERE #T.n = NUM.n)
GO
CREATE PROCEDURE [dbo].[V2] @total INT
AS
DECLARE @V TABLE (
n INT PRIMARY KEY,s VARCHAR(128))
INSERT INTO @V
SELECT n,s
FROM NUM
WHERE n%100 > 0
AND n <= @total
DECLARE @res VARCHAR(128)
SELECT @res = MAX(s)
FROM NUM
WHERE n <= @total
AND NOT EXISTS(SELECT *
FROM @V V
WHERE V.n = NUM.n)
GO
测试脚本
SET NOCOUNT ON; DECLARE @T1 DATETIME2,@T2 DATETIME2,@T3 DATETIME2,@Counter INT = 0 SET @T1 = SYSDATETIME() WHILE ( @Counter < 10000) BEGIN EXEC dbo.T2 10 SET @Counter += 1 END SET @T2 = SYSDATETIME() SET @Counter = 0 WHILE ( @Counter < 10000) BEGIN EXEC dbo.V2 10 SET @Counter += 1 END SET @T3 = SYSDATETIME() SELECT DATEDIFF(MILLISECOND,@T1,@T2) AS T2_Time,DATEDIFF(MILLISECOND,@T2,@T3) AS V2_Time
解决方法
SET STATISTICS IO ON; PRINT 'V2' EXEC dbo.V2 10 PRINT 'T2' EXEC dbo.T2 10
给
V2 Table '#58B62A60'. Scan count 0,logical reads 20 Table 'NUM'. Scan count 1,logical reads 3 Table '#58B62A60'. Scan count 10,logical reads 3 T2 Table '#T__ ... __00000000E2FE'. Scan count 0,logical reads 3 Table '#T__ ... __00000000E2FE'. Scan count 0,logical reads 3
正如Aaron在评论中指出的那样,表变量版本的计划实际上效率较低,因为虽然两者都有一个由dbo.NUM上的索引搜索驱动的嵌套循环计划,但#temp表版本执行了对[#的索引的搜索] T] .n = [dbo].[NUM].[n]具有残差谓词[#T].[n]< = [@ total]而表变量版本对@Vn执行索引搜索< = [ @total]与剩余谓词@V.[n] = [dbo].[NUM].[n]然后处理更多行(这就是为什么这个计划对大量行表现不佳的原因) 使用Extended Events查看特定spid的等待类型,可以为10,000次EXEC执行结果dbo.T2 10
+---------------------+------------+----------------+----------------+----------------+ | | | Total | Total Resource | Total Signal | | Wait Type | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) | +---------------------+------------+----------------+----------------+----------------+ | SOS_SCHEDULER_YIELD | 16 | 19 | 19 | 0 | | PAGELATCH_SH | 39998 | 14 | 0 | 14 | | PAGELATCH_EX | 1 | 0 | 0 | 0 | +---------------------+------------+----------------+----------------+----------------+
这些结果是10,000次执行EXEC dbo.V2 10
+---------------------+------------+----------------+----------------+----------------+ | | | Total | Total Resource | Total Signal | | Wait Type | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) | +---------------------+------------+----------------+----------------+----------------+ | PAGELATCH_EX | 2 | 0 | 0 | 0 | | PAGELATCH_SH | 1 | 0 | 0 | 0 | | SOS_SCHEDULER_YIELD | 676 | 0 | 0 | 0 | +---------------------+------------+----------------+----------------+----------------+
很明显,在#temp表格案例中,PAGELATCH_SH等待的数量要高得多.我不知道有任何方法可以将等待资源添加到扩展事件跟踪中,以便进一步调查我运行
WHILE 1=1 EXEC dbo.T2 10
在另一个连接中轮询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)的一个关键列,所以它作为删除/插入执行,然后它返回到聚簇索引并更新它.
似乎对于#temp表版本,当存储过程结束由FCheckAndCleanupCachedTempTable事务执行的清理的一部分时,将临时表从#T __________________________________________________________________________________________________________________ 00000000E316重命名为不同的内部名称,例如#2F4A0079,并在输入时CREATE TABLE事务将其重命名.这个翻转名称可以通过一个连接在循环中执行dbo.T2而在另一个连接中看到
WHILE 1=1 SELECT name,object_id,create_date,modify_date FROM tempdb.sys.objects WHERE name LIKE '#%'
示例结果
因此,Alex所提到的观察到的性能差异的一个可能的解释是,这是维护tempdb中负责的系统表的额外工作.
在循环中运行这两个过程,Visual Studio代码分析器显示以下内容
+-------------------------------+--------------------+-------+-----------+ | Function | Explanation | Temp | Table Var | +-------------------------------+--------------------+-------+-----------+ | CXStmtDML::XretExecute | Insert ... Select | 16.93 | 37.31 | | CXStmtQuery::ErsqExecuteQuery | Select Max | 8.77 | 23.19 | +-------------------------------+--------------------+-------+-----------+ | Total | | 25.7 | 60.5 | +-------------------------------+--------------------+-------+-----------+
表变量版本花费大约60％的时间执行insert语句和后续select,而临时表不到一半.这与OP中显示的时序一致,并且上面的结论表明,性能差异是执行辅助工作所花费的时间,而不是由于在查询执行本身花费的时间.
在临时表版本中导致“缺失”75％的最重要功能是
+------------------------------------+-------------------+ | Function | Inclusive Samples | +------------------------------------+-------------------+ | CXStmtCreateTableDDL::XretExecute | 26.26% | | CXStmtDDL::FinishNormalImp | 4.17% | | TmpObject::Release | 27.77% | +------------------------------------+-------------------+ | Total | 58.20% | +------------------------------------+-------------------+
在create和release函数下,函数CMEDProxyObject :: SetName的包含样本值为19.6％.从中可以推断,临时表案例中有39.2％的时间用于前面描述的重命名.
表变量版本中最大的一个对其他40％有贡献
+-----------------------------------+-------------------+ | Function | Inclusive Samples | +-----------------------------------+-------------------+ | CTableCreate::LCreate | 7.41% | | TmpObject::Release | 12.87% | +-----------------------------------+-------------------+ | Total | 20.28% | +-----------------------------------+-------------------+
临时表格简介
表变量配置文件
