深入理解和使用Tempdb-CSDN博客

本文链接：https://blog.csdn.net/weixin_43901350/article/details/134049096

本文详细阐述了SQLServerTempdb的作用，包括存储引擎内部操作的临时空间、乐观锁版本记录、Spool操作、表变量和临时表特性。重点讲解了Tempdb在排序、存储大数据类型、版本存储和索引管理等方面的应用与注意事项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Tempdb

Tempdb是SQL Server实例的系统数据库，同时也是实例中的一个临时共享资源。当服务器重启，Tempdb将被重建，所以Tempdb是没有办法像其他数据库一样永久保存数据的。也就是说，Tempdb是一个临时的数据库，是为实例中的各种请求处理中间数据的。

1. Tempdb的工作

Tempdb主要用于存储实例请求中的一些中间数据，其存储类别大致可以分为存储引擎内部操作的中间临时存储空间、乐观锁模式下的数据版本记录存储空间，以及用户自定义的临时存储空间等。

1.1内部存储

以下几种情况会触发SQL Server数据库引擎在Tempdb中开辟一部分空间来存储执行过程中产生的临时数据。

1.1.1 排序、汇总等较大查询语句中使用

在前面的章节中提到过，当查询的哈希计算或排序操作没有足够的内存空间时，将在Tempdb中保留中间结果，以完成相应的操作。除了哈希计算和排序操作外，还包括聚合计算以及Spool操作（Spool，用于将前一步操作的数据集存储起来，以供后续的操作使用，数据集会保留在Tempdb中）。

1.1.2 大数据类型的参数，如VARCHAR(MAX)类型的存储过程参数等。

1.1.3 XML或大数据类型的变量，包括TEXT、IMAGE、VARCHAR（MAX）等。

1.1.4 由执行计划分析出的，需要进行SPOOL的操作集。

在 SQL Server 中，Spool 是一个临时对象，用于存储查询结果集的中间结果。Spool 可以在执行计划中的多个位置出现，通常是在执行查询的 Sort 或者 Hash 操作之前。
Spool 对于 SQL Server 来说是非常重要的，因为它可以帮助减少查询的成本。当查询需要多次访问同一张表时，使用 Spool 可以将结果保存到临时表中，然后再从临时表中读取结果，而不需要再次访问原始表。
Spool 的缺点之一是它会占用额外的存储空间，因此在处理大量数据时要注意。此外，Spool 还可能导致查询性能下降，特别是当查询需要排序或者哈希操作的时候。因此，在设计查询计划时需要谨慎使用 Spool。

1.1.5 静态游标、键值游标。

1.1.6 INSTEAD OF触发器的内部中间数据。

1.2 版本存储

版本存储定义为在事务过程中产生的快照数据存储。在以下几种情况中，会在Tempdb中产生版本存储。

开启online选项的索引重建时。
当SQL Server开启READ_COMMITTEN_SNAPSHOT或ALLOW_SNAPSHOT_ISLATION选项时，将开启基本版本的隔离级别，也就会使用Tempdb存储不同版本的数据。

1.3 用户存储

用户存储定义为用户自己定义的临时对象的存储空间。用户自定义存储包括以下两种：

指定了SORT_IN_TEMOPDB的索引重建。
临时表、表变量。

2. 表变量、临时表

临时表分为局部临时表和全局临时表，分别以#和##作为表名前缀。局部临时表在会话间不能共享，会话结束后就会被删除；全局临时表在会话间是可共享的，当创建的绘画关闭时，全局临时表也会被删除。
针对表变量产生过许多误解，比如，以为表变量是内存表，是存储在内存中的，其实表变量与临时表一样，都是存储在Tempdb中的。表变量是在SQL Server 2000版本中加入的，当初是为了解决临时表造成重编译的问题。

2.1 统计信息

表变量与临时表最大的差异，同时也是需要极为注意的是，表变量没有办法创建统计信息。所以，查询优化器在分析执行计划时，以“猜测”的方式对表变量进行分析。这其中的好处就是没有统计信息的更新，就不会造成执行计划的重编译。

使用下面的语句（直接点击XML格式的数据行，可以打开执行计划的可视化界面），新建临时表，并插入200条数据，执行查询：

CREATE TABLE #tempTable1
(
    C1 CHAR(200)
);
DECLARE @CNT INT = 0;
WHILE (@CNT < 200)
    BEGIN
        INSERT INTO #tempTable1 (c1) values ('a');
        set @CNT+=1;
    end;
set statistics xml on

select *
from #tempTable1 as ts
set statistics XML OFF

2-1
查看其执行计划关于表操作的详细信息，如上图2-1所示，可以看到，临时表的预估数据是200条，它的统计信息是准确的。
下面的语句，可以查看表变量的详细情况：

DECLARE @tempTable1 TABLE
                    (
                        C1 CHAR(200)
                    );
DECLARE @CNT INT = 0;
WHILE (@CNT < 200)
    BEGIN
        INSERT INTO @tempTable1 (c1) values ('a');
        set @CNT+=1;
    end;
set statistics xml on
select *
from @tempTable1 as ts;
set statistics XML OFF

2-2
执行结果如图2-2所示，预估行的数量只有1。对于SQL Server来说，表变量是没有统计信息的，所以SQL Server只能用“猜测”的方式来预估表变量的数据行，SQL Server会认为表变量都是小表，执行计划通常也只会用NESTED LOOP来处理表变量的连接查询。因此，建议只将表变量应用在小数据量的查询中。