tempdb 容量规划

项目
12/15/2008

更新日期： 2006 年 4 月 14 日

本主题说明了在 SQL Server 2005 中已对 tempdb 系统数据库所做的更改，并提供了用来确定 tempdb 所需的适当磁盘空间量的指南。本主题还包含有关如何配置 tempdb 以在生产环境中获得最佳性能的建议，以及有关如何监视 tempdb 空间使用量的信息。

在 SQL Server 2005 中，tempdb 需要的磁盘空间多于在早期版本 SQL Server 中需要的磁盘空间。这是由于下列更改所致：

SQL Server 2005 中的某些新增功能使用 tempdb。
在 SQL Server 2005 中，某些在早期版本 SQL Server 中使用 tempdb 的功能可能会需要更多的 tempdb 磁盘空间。
在 SQL Server 2005 中，某些在早期版本 SQL Server 中不使用 tempdb 的功能将使用 tempdb。

由于发生上述更改，因此当您升级到 SQL Server 2005 时，提供的 tempdb 磁盘空间必须足以容纳当前生产工作负荷并满足使用 tempdb 的 SQL Server 功能的额外空间要求。

如何使用 tempdb

tempdb 系统数据库是可供连接到 SQL Server 实例的所有用户使用的全局资源。 tempdb 数据库用于存储下列对象：用户对象、内部对象和版本存储区。

用户对象

用户对象由用户显式创建。这些对象可以位于用户会话的作用域中，也可位于创建对象所用例程的作用域中。例程可以是存储过程、触发器或用户定义函数。用户对象可以是下列项之一：

用户定义的表和索引
系统表和索引
全局临时表和索引
局部临时表和索引
table 变量
表值函数中返回的表

内部对象

内部对象是根据需要由 SQL Server 数据库引擎创建的，用于处理 SQL Server 语句。内部对象可以在语句的作用域中创建和删除。内部对象可以是下列项之一：

用于游标或假脱机操作以及临时大型对象 (LOB) 存储的工作表。
用于哈希联接或哈希聚合操作的工作文件。
用于创建或重新生成索引等操作（如果指定了 SORT_IN_TEMPDB）的中间排序结果，或者某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果。

每个内部对象至少使用九页；一个 IAM 页，一个八页的区。有关页和区的详细信息，请参阅页和区。

版本存储区

版本存储区是数据页的集合，它包含支持使用行版本控制的功能所需的数据行。在 SQL Server 2005 中，有两个版本存储区：公用版本存储区和联机索引生成版本存储区。版本存储区包含下列项：

由使用快照隔离级别或已提交读隔离级别（基于行版本控制）的数据库中的数据修改事务生成的行版本。
由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。

下表列出了 SQL Server 中创建 tempdb 的用户对象、内部对象或行版本的功能。只要有可能，便会提供估计磁盘空间使用量的方法。

功能	tempdb 使用	其他信息
启用了触发器的大容量加载操作	在 SQL Server 2005 中，启用触发器后便可使用大容量导入优化。SQL Server 2005 将针对更新或删除事务的触发器使用行版本控制。每个删除或更新的行的副本都会添加到版本存储区中。请参阅此表后面部分中的“触发器”。	优化大容量导入性能
公用表表达式查询	公用表表达式可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。当公用表表达式查询的查询计划使用 Spool 运算符保存中间查询结果时，数据库引擎会在 tempdb 中创建工作表以支持此操作。	使用公用表表达式 WITH common_table_expression (Transact-SQL)
游标	键集驱动游标和静态游标使用在 tempdb 中生成的工作表。键集驱动游标使用工作表来存储标识游标中各行的键集。静态游标使用工作表来存储游标的完整结果集。如果选择的查询计划不同，则游标的磁盘空间使用量也可能会不同。如果查询计划与早期版本 SQL Server 中的查询计划相同，则磁盘空间使用量也大致相同。	关于选择游标类型
数据库邮件	请参阅此表后面部分中的“Service Broker”。	数据库邮件
DBCC CHECKDB	DBCC CHECKDB 使用 tempdb 工作表来保存中间结果或进行排序操作。如果 DBCC CHECK 操作中发生下列更改，则 DBCC CHECKDB 的空间使用量会增加：引入的逻辑检查更全面。要检查的某些基础结构更为复杂。在 SQL Server 2005 中引入了许多新的检查，以包含新增功能。若要确定操作的 tempdb 磁盘空间要求，请运行 DBCC CHECKDB WITH ESTIMATE_ONLY。	DBCC CHECKDB (Transact-SQL) 优化 DBCC CHECKDB 性能
事件通知	请参阅此表后面部分中的“Service Broker”。	了解事件通知
索引	当创建或重新生成索引（脱机或联机）并将 SORT_IN_TEMPDB 选项设置为 ON 时，可以引导数据库引擎使用 tempdb 来存储用于生成索引的中间排序结果。如果指定了 SORT_IN_TEMPDB 并需要进行排序，则 tempdb 中具有的磁盘空间必须足以包含最大的索引以及大小为 index create memory 选项值的磁盘空间。有关详细信息，请参阅索引磁盘空间示例。不使用 SQL Server 2005 功能的脱机索引操作的磁盘空间使用量与早期版本的 SQL Server 中的使用量相同。 SQL Server 2005 中 tempdb 使用量的更改在 SQL Server 2005 中，表和索引可以进行分区。对于已分区索引，如果指定了 SORT_IN_TEMPDB 索引选项并且索引与基表对齐，则 tempdb 中具有的空间必须足以容纳最大分区的中间排序段。如果索引未对齐，则 tempdb 中具有的空间必须足以容纳所有分区的中间排序段。有关详细信息，请参阅已分区索引的特殊指导原则。联机索引操作使用行版本控制来使索引操作不受其他事务所做的修改的影响。行版本控制不需要对已读取的行请求共享锁。在联机索引操作期间，并发的用户更新和删除操作需要一定的空间以用于 tempdb 中的版本记录。如果联机索引操作使用 SORT_IN_TEMPDB 并需要进行排序时，则 tempdb 除了具有用于先前所述的中间排序结果的磁盘空间之外，还必须具有额外的磁盘空间。创建、删除或重新生成聚集索引的联机索引操作也需要额外的磁盘空间来生成并维护临时映射索引。有关详细信息，请参阅索引 DDL 操作的磁盘空间要求。	tempdb 和索引创建已分区索引的特殊指导原则索引 DDL 操作的磁盘空间要求索引磁盘空间示例联机索引操作的工作方式
大型对象 (LOB) 数据类型变量和参数	大型对象数据类型包括 varchar(max)、nvarchar(max)、varbinary(max)、text、ntext、image 和 xml。这些类型的最大大小可达 2 GB，并可在存储过程、用户定义函数、批处理或查询中用作变量或参数。定义为 LOB 数据类型的参数和变量在值很小的情况下将使用主内存作为存储区。但是，较大值将存储在 tempdb 中。当 LOB 变量和参数存储在 tempdb 中时，可以将它们视为内部对象。您可以查询 sys.dm_db_session_space_usage 动态管理视图，以报告针对给定会话分配给内部对象的页。有些内部字符串函数（例如，SUBSTRING 或 REPLICATE）在针对 LOB 值执行操作时，可能需要在 tempdb 中进行中间临时存储。同样，如果对数据库启用了基于行版本控制的事务隔离级别并且对大型对象进行了修改，则 LOB 的已更改片段将被复制到 tempdb 的版本存储区中。	使用大值数据类型
多个活动的结果集 (MARS)	在 SQL Server 2005 中，可以在单一连接下出现多个活动的结果集，这称为 MARS。如果 MARS 会话在存在活动结果集的情况下发出数据修改语句（例如 INSERT、UPDATE 或 DELETE），则将受到修改语句影响的行存储在 tempdb 的版本存储区中。请参阅此表后面部分中的“行版本控制”。	Using Multiple Active Result Sets (MARS)
查询通知	请参阅此表后面部分中的“Service Broker”。	使用查询通知
查询	包含 SELECT、INSERT、UPDATE 和 DELETE 语句的查询可以使用内部对象来存储哈希联接、哈希聚合或排序的中间结果。在 SQL Server 2005 中，缓存查询执行计划时，也会缓存该计划所需的工作表。缓存工作表时，该表会截断，并且缓存中保留九页以便重用。这样可提高下次执行查询时的性能。如果系统内存不足，则数据库引擎可以删除执行计划以及关联的工作表。	执行计划的缓存和重新使用
行版本控制	行版本控制是 SQL Server 2005 中用于支持下列功能的常规框架：触发器多个活动的结果集 (MARS) 指定 ONLINE 选项的索引操作基于行版本控制的事务隔离级别：已提交读隔离级别的新实现方式，它使用行版本控制来提供语句级读取一致性。用来提供事务级读取一致性的快照隔离级别。行版本在活动事务必须对其进行访问时一直保存在 tempdb 版本存储区中。当前版本存储区的内容在 sys.dm_tran_version_store 中返回。由于版本存储区页是全局资源，所以在文件级别对其进行跟踪。您可以使用 sys.dm_db_file_space_usage 中的 version_store_reserved_page_count 列来查看版本存储区的当前大小。版本存储区清除必须考虑需要访问特定版本的运行时间最长的事务。可通过查看 sys.dm_tran_active_snapshot_database_transactions 中的 elapsed_time_seconds 列来发现与版本存储区清除相关的运行时间最长的事务。可以使用 Transactions 对象中的 Free Space in Tempdb (KB) 和 Version Store Size (KB) 计数器来监视 tempdb 中行版本存储区的大小和增长速率。有关详细信息，请参阅 SQL Server Transactions 对象。若要估计 tempdb 中行版本控制所需的空间量，请务必先考虑活动事务必须将其所有更改保留在版本存储区中。这意味着稍后启动的快照事务可以访问旧版本。此外，如果存在活动的快照事务，则由快照启动时处于活动状态的事务生成的所有版本存储区数据也必须保留。下面是基本公式： [版本存储区大小] = 2 * [每分钟生成的版本存储区数据] * [事务的最长运行时间（分钟）]	了解基于行版本控制的隔离级别行版本控制资源的使用情况
Service Broker	Service Broker 可帮助开发人员生成异步的松散耦合应用程序，可以在这些应用程序中组合使用独立组件来完成任务。这些应用程序组件将交换包含完成任务所需的信息的消息。Service Broker 显式使用 tempdb 来保留内存中无法保留的现有对话框上下文。每个对话框的大小大约为 1 KB。此外，Service Broker 将隐式使用 tempdb 在查询执行上下文中缓存对象（例如，用于计时器事件和后台传递会话的工作表）。数据库邮件、事件通知和查询通知将隐式使用 Service Broker。	Service Broker 简介
存储过程	存储过程可以创建用户对象，例如全局临时表或局部临时表及其索引、变量或参数。在 SQL Server 2005 中，可以缓存存储过程中的临时对象以优化删除和创建这些对象的操作。这一行为会增加 tempdb 磁盘空间要求。每个临时对象可最多存储九页以便重用。请参阅此表后面部分中的“临时表和 table 变量”。	创建存储过程（数据库引擎）
临时表和 table 变量用户定义的表和索引系统表和索引全局临时表和索引局部临时表和索引 table 变量表值函数中返回的表	临时表和 table 变量存储在 tempdb 中。临时表对象的磁盘空间要求与早期版本的 SQL Server 中的要求相同。估计临时表大小的方法与估计标准表大小的方法相同。有关详细信息，请参阅估计表大小。 table 变量的行为类似于局部变量。 table 变量的类型为 table，主要用于临时存储作为表值函数结果集返回的行集。保存 table 变量所需的磁盘空间取决于声明变量以及变量中存储的值的大小。在 SQL Server 2005 中，当满足下列条件时会缓存本地临时表和变量：没有创建命名约束。临时表创建之后，影响表的数据定义语言 (DDL) 语句（例如，CREATE INDEX 或 CREATE STATISTICS）语句没有运行。没有使用动态 SQL（例如 `sp_executesql N'create table #t(a int)'`）创建临时表。在其他对象（例如，存储过程、触发器、用户定义函数）内部创建临时对象；或者临时对象是用户定义表值函数的返回表。缓存临时表或 table 变量时，在临时对象完成使命时没有将其删除。相反，临时对象将截断。临时对象可最多存储九页，以便在下次执行调用时重用。通过缓存，删除和创建对象的操作可以非常快速地执行，并可减少页分配争用。为了获得最佳性能，应该使用以下公式计算 tempdb 中的缓存本地临时表或 table 变量所需的磁盘空间：每个临时表的页 9 * 每个过程的平均临时表数 * 过程的最大同时执行数	CREATE TABLE (Transact-SQL) 使用变量和参数（数据库引擎） DECLARE @local_variable (Transact-SQL)
触发器	在早期版本的 SQL Server 中，触发器逻辑基于日志记录，并且不使用 tempdb。在 SQL Server 2005 中，AFTER 触发器中使用的 inserted 和 deleted 表在 tempdb 中创建。即，由触发器更新或删除的行应进行版本控制。这包括由触发触发器的语句修改的所有行。由触发器插入的行不进行版本控制。 INSTEAD OF 触发器使用 tempdb 的方式类似于查询所使用的方式。 INSTEAD OF 触发器的磁盘空间使用量与早期版本的 SQL Server 中的使用量相同。请参阅此表前面部分中的“查询”。当您在启用触发器的情况下大容量加载数据时，将向版本存储区添加每一删除行或更新行的一份副本。	CREATE TRIGGER (Transact-SQL) 优化大容量导入性能行版本控制资源的使用情况
用户定义函数	用户定义函数可以创建临时用户对象，例如全局临时表或局部临时表及其索引、变量或参数。例如，表值函数的返回表存储在 tempdb 中。在 SQL Server 2005 中，允许用于标量函数和表值函数的参数和返回值的数据类型包括大多数 LOB 数据类型。例如，返回值的类型可以为 xml 或 varchar(max)。请参阅此表前面部分中的“大型对象 (LOB) 数据类型变量和参数”。在 SQL Server 2005 中，可以缓存用户定义表值函数中的临时对象以优化删除和创建这些对象的操作。请参阅此表前面部分中的“临时表和 table 变量”。	CREATE FUNCTION (Transact-SQL)
XML	类型为 xml 的变量和参数最大大小可达 2 GB。只要值比较小，它们便会使用主内存作为存储区。但是，较大值将存储在 tempdb 中。请参阅此表前面部分中的“大型对象 (LOB) 数据类型变量和参数”。 sp_xml_preparedocument 系统存储过程会在 tempdb 中创建一个工作表。 MSXML 分析器使用该工作表来存储已分析的 XML 文档。执行存储过程时，tempdb 的磁盘空间要求几乎与指定的 XML 文档的大小成正比。	xml 数据类型 sp_xml_preparedocument (Transact-SQL) 使用 OPENXML 查询 XML

SQL Server 2005 的升级容量规划

确定 tempdb 在生产环境中的适当大小取决于多种因素。如本主题中前面所述，这些因素包括现有工作负荷以及使用的 SQL Server 功能。建议您通过在 SQL Server 2005 测试环境中执行下列任务来分析现有的工作负荷：

设置 tempdb 的自动增长。
执行单独的查询或工作负荷跟踪文件，监视 tempdb 空间使用。
执行索引维护操作（例如，重新生成索引），监视 tempdb 空间。
使用前面步骤中的空间使用值来预测总的工作负荷使用情况；针对计划的并发活动调整此值，然后相应地设置 tempdb 的大小。

有关监视 tempdb 空间的详细信息，请参阅解决 tempdb 中磁盘空间不足的问题。有关估计索引操作过程中 tempdb 使用量的详细信息，请参阅索引磁盘空间示例。

针对生产环境对 tempdb 进行配置

若要获得最佳的 tempdb 性能，请遵守优化 tempdb 性能中提供的指导原则和建议。

如何监视 tempdb 的使用

如果 tempdb 中磁盘空间不足，则可能会严重破坏 SQL Server 生产环境，并会使正在运行的应用程序无法完成操作。在 SQL Server 2005 中，可以使用 sys.dm_db_file_space_usage 动态管理视图来监视 tempdb 文件中这些功能使用的磁盘空间。此外，若要在会话级或任务级监视 tempdb 中的页分配或页释放活动，可以使用动态管理视图 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage。这些视图可用于标识使用 tempdb 中大量磁盘空间的大型查询、临时表或表变量。还可以使用若干个计数器来监视 tempdb 中的可用空间以及使用 tempdb 的资源。有关详细信息，请参阅解决 tempdb 中磁盘空间不足的问题。

请参阅

更改历史记录

发布日期	历史记录
2006 年 4 月 14 日	更新的内容：阐明了公用表表达式查询何时使用 tempdb。