临时表与表变量及其对SQL Server性能和SQL Server 2008的影响能够重现与2005年类似的结果。
当执行只有10行的存储过程（下面的定义）时，表变量version out会执行临时表版本两次以上。
我清除了过程高速缓存并运行了10,000次这两个存储过程，然后重复该过程再运行4次。下面的结果（每批时间以毫秒为单位）
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';
查看性能计数器，可以确认在这两种情况下，临时对象都是按预期在第一次运行后缓存的，而不是每次调用都从头开始创建。
类似地在Profiler中跟踪
Auto Stats，SP:Recompile和SQL:StmtRecompile事件（下面的屏幕截图）显示，这些事件仅发生一次（在第一次调用#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
#1 楼
两者的SET STATISTICS IO ON的输出看起来都很相似。SET STATISTICS IO ON;
PRINT 'V2'
EXEC dbo.V2 10
PRINT 'T2'
EXEC dbo.T2 10
dbo.NUM上的索引查找驱动的嵌套循环计划，而#temp表版本使用残余谓词[#T].n = [dbo].[NUM].[n]进行[#T].[n]<=[@total]的索引查找，而表变量版本执行使用剩余谓词@V.n <= [@total]对@V.[n]=[dbo].[NUM].[n]进行索引查找，因此处理了更多行（这就是为什么该计划对大量行执行得如此差）的原因使用扩展事件来查看特定spid的等待类型这些结果适用于10,000次执行的
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 20
Table 'NUM'. Scan count 1, logical reads 3
T2
Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3
Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3
，这些结果适用于10,000次执行的
EXEC dbo.V2 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 |
+---------------------+------------+----------------+----------------+----------------+
显然，在
PAGELATCH_SH表的情况下，等待的#temp的数量要多得多。我不知道将等待资源添加到扩展事件跟踪中的任何方法，因此为了进一步研究此问题，我运行了+---------------------+------------+----------------+----------------+----------------+
| | | 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 |
+---------------------+------------+----------------+----------------+----------------+
/>
WHILE 1=1
EXEC dbo.T2 10
运行约15秒钟后，它收集了以下结果
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'
这两个页面被锁住的对象属于名为
sys.dm_os_waiting_tasks和tempdb.sys.sysschobjs的'nc1'基表上的（不同）非聚集索引。在运行期间查询
'nc2'表示由每个存储过程的第一次执行添加的日志记录数为多少有些变化，但对于后续执行，每次迭代所添加的数字是非常一致且可预测的。缓存了程序计划后，日志条目的数量大约是tempdb.sys.fn_dblog版本所需日志条目的一半。+-------+----------------------+
| Count | resource_description |
+-------+----------------------+
| 1098 | 2:1:150 |
| 1689 | 2:1:146 |
+-------+----------------------+
仔细查看SP的
#temp表版本的事务日志条目，每次随后对存储过程的调用都会创建三个事务，而表变量仅创建两个。+-----------------+----------------+------------+
| | Table Variable | Temp Table |
+-----------------+----------------+------------+
| First Run | 126 | 72 or 136 |
| Subsequent Runs | 17 | 32 |
+-----------------+----------------+------------+
#temp / INSERT事务名称相同。它包含插入临时表或表变量的10行中每行的日志记录，以及TVQUERY / LOP_BEGIN_XACT条目。LOP_COMMIT_XACT事务仅出现在CREATE TABLE版本中，如下所示。+---------------------------------+----+---------------------------------+----+
| #Temp Table | @Table Variable |
+---------------------------------+----+---------------------------------+----+
| CREATE TABLE | 9 | | |
| INSERT | 12 | TVQuery | 12 |
| FCheckAndCleanupCachedTempTable | 11 | FCheckAndCleanupCachedTempTable | 5 |
+---------------------------------+----+---------------------------------+----+
#Temp事务同时出现在两者中，但在FCheckAndCleanupCachedTempTable版本中有6个附加条目。这是引用#temp的6行，它们具有与上述模式完全相同的模式。+-----------------+-------------------+---------------------+
| 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 | |
+-----------------+-------------------+---------------------+
在两个事务中查看这6行，它们对应于相同的操作。第一个
sys.sysschobjs是对LOP_MODIFY_ROW, LCX_CLUSTERED中的modify_date列的更新。其余五行都与对象重命名有关。由于sys.objects是两个受影响的NCI（name和nc1）的关键列，因此对它们进行删除/插入，然后返回到聚集索引并对其进行更新。看来对于
nc2表版本，当存储过程结束由#temp事务执行的部分清理操作时，是将temp表从类似FCheckAndCleanupCachedTempTable的名称重命名为另一个内部名称（例如#T__________________________________________________________________________________________________________________00000000E316），并在输入时将#2F4A0079事务重命名它。可以通过在一个循环中执行CREATE TABLE而在另一个循环中执行dbo.T2来看到此触发器名称，示例结果
因此，Alex暗示的对观察到的性能差异的一种潜在解释是，负责在
tempdb中维护系统表的这项额外工作是负责的。在循环中运行两个过程，Visual Studio Code事件探查器将显示以下内容
+-----------------+-------------------+----------------------------------------------+
| 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 | |
+-----------------+-------------------+----------------------------------------------+
表变量版本花费大约60％的时间来执行insert语句和随后的select而临时表不到一半。这与OP中显示的时间是一致的，并且与以上结论相同，即性能差异取决于执行辅助工作所花费的时间，而不是由于查询执行本身所花费的时间。
最重要在临时表版本中占“缺失” 75％的函数是
WHILE 1=1
SELECT name, object_id, create_date, modify_date
FROM tempdb.sys.objects
WHERE name LIKE '#%'
在创建和释放函数下，函数
CMEDProxyObject::SetName都显示为包含的样本值19.6%。从中可以推断出，在临时表情况下39.2％的时间是用前面所述的重命名所占用的。 /> +-------------------------------+--------------------+-------+-----------+
| 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 |
+-------------------------------+--------------------+-------+-----------+
临时表配置文件
表变量配置文件
#2 楼
Disco Inferno由于这是一个较旧的问题，因此我决定在SQL Server的较新版本上重新讨论该问题，以查看是否仍然存在相同的性能配置文件，或者特性是否已更改。
具体来说，为SQL Server 2019添加内存系统表似乎是值得重新测试的机会。
我使用的测试工具稍有不同，因为我在处理其他问题时遇到了这个问题。
测试，测试
使用2013年版本的Stack Overflow，我有此索引以及以下两个过程：
索引：
CREATE INDEX ix_whatever
ON dbo.Posts(OwnerUserId) INCLUDE(Score);
GO
温度表：
CREATE OR ALTER PROCEDURE dbo.TempTableTest(@Id INT)
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #t(i INT NOT NULL);
DECLARE @i INT;
INSERT #t ( i )
SELECT p.Score
FROM dbo.Posts AS p
WHERE p.OwnerUserId = @Id;
SELECT @i = AVG(t.i)
FROM #t AS t;
END;
GO
表变量：
CREATE OR ALTER PROCEDURE dbo.TableVariableTest(@Id INT)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @t TABLE (i INT NOT NULL);
DECLARE @i INT;
INSERT @t ( i )
SELECT p.Score
FROM dbo.Posts AS p
WHERE p.OwnerUserId = @Id;
SELECT @i = AVG(t.i)
FROM @t AS t;
END;
GO
为了防止任何潜在的ASYNC_NETWORK_IO等待，我使用了包装程序。
CREATE PROCEDURE #TT AS
SET NOCOUNT ON;
DECLARE @i INT = 1;
DECLARE @StartDate DATETIME2(7) = SYSDATETIME();
WHILE @i <= 50000
BEGIN
EXEC dbo.TempTableTest @Id = @i;
SET @i += 1;
END;
SELECT DATEDIFF(MILLISECOND, @StartDate, SYSDATETIME()) AS [ElapsedTimeMilliseconds];
GO
CREATE PROCEDURE #TV AS
SET NOCOUNT ON;
DECLARE @i INT = 1;
DECLARE @StartDate DATETIME2(7) = SYSDATETIME();
WHILE @i <= 50000
BEGIN
EXEC dbo.TableVariableTest @Id = @i;
SET @i += 1;
END;
SELECT DATEDIFF(MILLISECOND, @StartDate, SYSDATETIME()) AS [ElapsedTimeMilliseconds];
GO
SQL Server 2017
/>
由于2014年和2016年到目前为止基本上是RELICS，所以我将从2017年开始进行测试。此外，为了简洁起见，我打算使用Perfview对代码进行概要分析。在现实生活中，我查看了等待，闩锁，自旋锁，疯狂的跟踪标志和其他内容。
对代码进行概要分析是唯一揭示感兴趣的内容的方法。
时差：
温度表：17891 ms
表变量：5891 ms
还是很明显的区别，是吗？但是，SQL Server现在正在冲击什么？
看看差异样本中前两个增加的地方，我们看到
sqlmin和sqlsqllang!TCacheStore<CacheClockAlgorithm>::GetNextUserDataInHashBucket是两个最大的违法者。 
从调用堆栈中的名称来看，清理和内部重命名临时表似乎是在临时表调用与表变量调用中花费最多的时间。 />
即使表变量在内部由临时表支持，这也不是问题。
SET STATISTICS IO ON;
DECLARE @t TABLE(id INT);
SELECT * FROM @t AS t;
表' ＃B98CE339'。扫描计数1
查看表变量test的调用堆栈根本不会显示任何主要违规者：
SQL Server 2019（Vanilla）
好吧，所以这仍然是SQL Server 2017中的问题，2019年有什么不同之处？
首先，要说明我没有袖手旁观：
SELECT c.name,
c.value_in_use,
c.description
FROM sys.configurations AS c
WHERE c.name = 'tempdb metadata memory-optimized';
时差：
温度表：15765 ms
表变量：7250 ms
两个过程都不同。临时表调用快了几秒钟，表变量调用慢了约1.5秒。表变量变慢的部分原因可能是表变量延迟编译，这是2019年的新优化器选择。
看看Perfview中的差异，它已经有所改变-sqlmin不再存在- -但是
sqllang!TCacheStore<CacheClockAlgorithm>::GetNextUserDataInHashBucket是。
SQL Server 2019（内存中的Tempdb系统表）
关于内存系统表中的新内容呢？ ？嗯？
打开它吧！
EXEC sys.sp_configure @configname = 'advanced',
@configvalue = 1
RECONFIGURE;
EXEC sys.sp_configure @configname = 'tempdb metadata memory-optimized',
@configvalue = 1
RECONFIGURE;
请注意，这需要重新启动SQL Server才能启动，请原谅我在这个可爱的星期五下午重新启动SQL。
现在情况看起来有所不同： ：
温度表：11638 ms
表变量：7403 ms
温度表的性能提高了约4秒！就是这样。
我喜欢。
这次，Perfview差异不是很有趣。并排，有趣的是注意到时间跨得很近：
diff中有趣的一点是对
hkengine!的调用，这似乎很明显由于现在已经使用了hekaton式功能。
就差异中的前两项而言，我对
ntoskrnl!?的理解不多：
或
sqltses!CSqlSortManager_80::GetSortKey，但它们在这里供Smrtr Ppl™查看：
请注意，这里没有记录在案，并且绝对不安全生产，因此请不要使用它启动跟踪标志，可以使用它在内存中功能中包含其他临时表系统对象（sysrowsets，sysallocunits和sysseobjvalues），但在这种情况下，执行时间没有明显变化。 >
Roundup
即使在较新版本的SQL Server中，高频调用表变量也比高频调用临时表快得多。
尽管很容易将责任归咎于编译，重新编译，自动统计，闩锁，自旋锁，缓存或其他问题，但问题显然仍然在于管理临时表清理。
在启用内存系统表的SQL Server 2019中，这是一个更紧密的调用，但是当调用频率很高时，表变量仍然表现更好。
当然，作为一位虔诚的圣贤沉思：“在计划选择不成问题时使用表变量”。
评论
探查器跟踪表明，统计信息仅在#temp表上创建一次，尽管已清除并随后又重新填充了9,999次。