适用于:是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库 tempdb 系统数据库是一个全局资源,可供连接到 SQL Server 实例或 SQL 数据库的所有用户使用。 tempdb 用于保留:
-
显式创建的临时用户对象,例如:全局或局部临时表及索引、临时存储过程、表变量、表值函数返回的表或游标。
-
由数据库引擎创建的内部对象。 其中包括:
- 用于储存假脱机、游标、排序和临时大型对象 (LOB) 存储的中间结果的工作表。
- 用于哈希联接或哈希聚合操作的工作文件。
- 用于创建或重新生成索引等操作(如果指定了 SORT_IN_TEMPDB)的中间排序结果,或者某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果。
NOTE 每个内部对象至少使用九页;一个 IAM 页,一个八页的区。 有关页和区的详细信息,请参阅页和区。
IMPORTANT Azure SQL 数据库逻辑服务器支持存储在 tempdb 中,且范围限定为数据库级别的全局临时表和全局临时存储过程。 全局临时表和全局临时存储过程供同一 Azure SQL 数据库中的所有用户会话共享。 其他 Azure SQL 数据库中的用户会话无法访问全局临时表。 有关详细信息,请参阅数据库作用域内全局临时表(Azure SQL 数据库)。 Azure SQL 数据库托管实例与 SQL Server 支持相同的临时对象。 对于 Azure SQL 数据库逻辑服务器,仅 master 数据库和 tempdb 数据库适用。 有关逻辑服务器和逻辑 master 数据库的相关概念,请参阅什么是 Azure SQL 逻辑服务器?。 有关在 Azure SQL 数据库逻辑服务器上下文中对 tempdb 的讨论,请参阅 Azure SQL 数据库逻辑服务器中的 tempdb 数据库。 对于 Azure SQL 数据库托管实例,所有系统数据库都适用。
-
版本存储区是数据页的集合,它包含支持使用行版本控制的功能所需的数据行。 共有两个版本存储区:公用版本存储区和联机索引生成版本存储区。 版本存储区包含:
- 由使用已提交读(使用行版本控制隔离或快照隔离事务)的数据库中数据修改事务生成的行版本。
- 由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。
tempdb 中的操作是最小日志记录操作,以便回滚事务。 每次启动时都会重新创建 tempdb SQL Server ,从而在系统启动时总是具有一个干净的数据库副本。 在断开联接时会自动删除临时表和存储过程,并且在系统关闭后没有活动连接。 因此 tempdb 中不会有什么内容从一个 SQL Server 会话保存到另一个会话。 不允许对 tempdb进行备份和还原操作。
SQL Server 中 tempdb 的物理属性
下表列出了 SQL Server 中 tempdb 数据和日志文件的初始配置值(基于模型数据库的默认设置)。 对于不同版本的 SQL Server,这些文件的大小可能略有不同。
| 文件 | 逻辑名称 | 物理名称 | 初始大小 | 文件增长 |
|---|---|---|---|---|
| 主数据 | tempdev | tempdb.mdf | 8 MB | 以 64 MB 的速度自动增长直到磁盘已满 |
| 次要数据文件* | temp# | tempdb_mssql_#.ndf | 8 MB | 以 64 MB 的速度自动增长直到磁盘已满 |
| 日志 | templog | templog.ldf | 8 MB | 以 64 MB 的速度自动增长直到达到上限 2 TB |
*文件数取决于计算机上的(逻辑)处理器数。 一般而言,如果逻辑处理器数目小于或等于 8,则使用的数据文件数与逻辑处理器数相同。 如果逻辑处理器数目大于 8,则使用 8 个数据文件,如果仍然存在争用,则以 4 的倍数增加数据文件的数量,直到争用减少到可接受的级别或对工作负荷/代码进行更改。
Note: 数据文件数的默认值遵循 KB 2154845中的一般准则。
在 SQL Server 中移动 tempdb 数据和日志文件
若要移动 tempdb 数据和日志文件,请参阅 移动系统数据库。
SQL Server 中 tempdb 的数据库选项
下表列出了 tempdb 数据库中每个数据库选项的默认值,以及是否可以修改该选项。 若要查看这些选项的当前设置,请使用 sys.databases 目录视图。
| 数据库选项 | 默认值 | 是否可修改 |
|---|---|---|
| ALLOW_SNAPSHOT_ISOLATION | OFF | 用户帐户控制 |
| ANSI_NULL_DEFAULT | OFF | 用户帐户控制 |
| ANSI_NULLS | OFF | 用户帐户控制 |
| ANSI_PADDING | OFF | 用户帐户控制 |
| ANSI_WARNINGS | OFF | 用户帐户控制 |
| ARITHABORT | OFF | 用户帐户控制 |
| AUTO_CLOSE | OFF | 否 |
| AUTO_CREATE_STATISTICS | ON | 用户帐户控制 |
| AUTO_SHRINK | OFF | 否 |
| AUTO_UPDATE_STATISTICS | ON | 用户帐户控制 |
| AUTO_UPDATE_STATISTICS_ASYNC | OFF | 用户帐户控制 |
| CHANGE_TRACKING | OFF | 否 |
| CONCAT_NULL_YIELDS_NULL | OFF | 用户帐户控制 |
| CURSOR_CLOSE_ON_COMMIT | OFF | 用户帐户控制 |
| CURSOR_DEFAULT | GLOBAL | 用户帐户控制 |
| 数据库可用性选项 | ONLINE MULTI_USER READ_WRITE | 否 否 否 |
| DATE_CORRELATION_OPTIMIZATION | OFF | 用户帐户控制 |
| DB_CHAINING | ON | 否 |
| ENCRYPTION | OFF | 否 |
| MIXED_PAGE_ALLOCATION | OFF | 否 |
| NUMERIC_ROUNDABORT | OFF | 用户帐户控制 |
| PAGE_VERIFY | 对于新安装的 SQL Server,为 CHECKSUM。 对于升级的 SQL Server,为 NONE。 | 用户帐户控制 |
| PARAMETERIZATION | SIMPLE | 用户帐户控制 |
| QUOTED_IDENTIFIER | OFF | 用户帐户控制 |
| READ_COMMITTED_SNAPSHOT | OFF | 否 |
| RECOVERY | SIMPLE | 否 |
| RECURSIVE_TRIGGERS | OFF | 用户帐户控制 |
| Service Broker 选项 | ENABLE_BROKER | 用户帐户控制 |
| TRUSTWORTHY | OFF | 否 |
有关这些数据库选项的说明,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。
SQL 数据库中的 Tempdb 数据库
基于 DTU 的服务层的 tempdb 大小
| SLO | 最大 tempdb 数据文件大小 (MB) | tempdb 数据文件数 | 最大 tempdb 数据大小 (MB) |
|---|---|---|---|
| “基本” | 14,225 | 1 | 14,225 |
| S0 | 14,225 | 1 | 14,225 |
| S1 | 14,225 | 1 | 14,225 |
| S2 | 14,225 | 1 | 14,225 |
| S3 | 32,768 | 1 | 32,768 |
| S4 | 32,768 | 2 | 65,536 |
| S6 | 32,768 | 3 | 98,304 |
| S7 | 32,768 | 6 | 196,608 |
| S9 | 32,768 | 12 | 393,216 |
| S12 | 32,768 | 12 | 393,216 |
| P1 | 32,768 | 12 | 393,216 |
| P2 | 32,768 | 12 | 393,216 |
| P4 | 32,768 | 12 | 393,216 |
| P6 | 32,768 | 12 | 393,216 |
| P11 | 32,768 | 12 | 393,216 |
| P15 | 32,768 | 12 | 393,216 |
| 高级弹性池(所有 DTU 配置) | 14,225 | 12 | 170,700 |
| 标准弹性池(所有 DTU 配置) | 14,225 | 12 | 170,700 |
| 基本弹性池(所有 DTU 配置) | 14,225 | 12 | 170,700 |
基于 vCore 的服务层的 tempdb 大小
限制
不能对 tempdb 数据库执行以下操作:
- 添加文件组。
- 备份或还原数据库。
- 更改排序规则。 默认排序规则为服务器排序规则。
- 更改数据库所有者。 tempdb 的所有者是 sa。
- 创建数据库快照。
- 删除数据库。
- 从数据库中删除 guest 用户。
- 启用变更数据捕获。
- 参与数据库镜像。
- 删除主文件组、主数据文件或日志文件。
- 重命名数据库或主文件组。
- 运行 DBCC CHECKALLOC。
- 运行 DBCC CHECKCATALOG。
- 将数据库设置为 OFFLINE。
- 将数据库或主文件组设置为 READ_ONLY。
Permissions
任何用户都可以在 tempdb 中创建临时对象。 用户只能访问自己的对象,除非他们获得更多的权限。 可以撤消对 tempdb 的连接权限以阻止用户使用 tempdb,但是不建议这样做,因为一些例行操作需要使用 tempdb。
在 SQL Server 中优化 tempdb 性能
tempdb 数据库的大小和物理位置可能会影响系统的性能。 例如,如果为 tempdb 定义的大小过小,则每次重新启动 SQL Server 实例时,都可能会占用部分系统处理负荷,以使 tempdb 自动增长到支持工作负荷所需的大小。
如果可以,请使用数据库即时文件初始化来提高数据文件增长操作的性能。
通过将文件大小设置为足够容纳环境中典型工作负荷的值来预分配所有 tempdb 文件的空间。 预先分配可避免 tempdb 因扩展得过于频繁而影响性能。 tempdb 数据库应设置为自动增长,但是在出现意外情况时此设置将用于增加磁盘空间。
每个文件组中的数据文件应大小一致,因为 SQL Server 使用比例填充算法,这种算法可增加可用空间,便于文件分配。 将 tempdb 分割成大小相等的多个数据文件,可以为使用 tempdb 的操作提供更高的并行效率。
将文件增量设置为合理的大小以避免 tempdb 数据库文件的增量过小。 如果文件的增量与写入 tempdb 的数据量相比过小,则 tempdb 可能需要不断扩大,并且会影响性能。
要检查当前 tempdb 大小和增长参数,请使用以下查询:
SELECT name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file grows to a maximum size of 2 TB.'
END,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed.'
WHEN growth > 0 AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
GO
将 tempdb 数据库放置在快速 I/O 子系统中。 如果有许多直接连接的磁盘,则请使用磁盘条带化。 单个或成组的 tempdb 数据文件并不一定要位于不同的磁盘或主轴上,除非存在 I/O 瓶颈。
将 tempdb 数据库放置在用户数据库使用的磁盘以外的磁盘中。
SQL Server 中 tempdb 的性能提高
从 SQL Server 2016 (13.x) 开始,已通过以下方式进一步优化 tempdb 性能:
- 已缓存的临时表和表变量。 缓存允许删除和创建临时对象的操作非常快速地执行,并减少页分配的争用问题。
- 改进了分配页闩锁协议,减少了所用 UP(更新)闩锁的数量。
- 减少了 tempdb 的日志记录开销,从而减少了 tempdb 日志文件的磁盘 I/O 带宽消耗。
- 在新的实例安装过程中,安装程序会添加多个 tempdb 数据文件。 可以使用“数据库引擎配置”部分中新增的 UI 输入控件和命令行参数 /SQLTEMPDBFILECOUNT 来完成此任务。 默认情况下,安装程序添加的 tempdb 数据文件数为逻辑处理器计数或 8,以较小者为准。
- 如果有多个 tempdb 数据文件,那么所有文件都会同时自动增长相同的量,具体取决于增长设置。 不再需要跟踪标志 1117。
- tempdb 中的所有分配使用统一盘区。 不再需要跟踪标志 1118。
- 对于主文件组,AUTOGROW_ALL_FILES 属性已启用,且不能修改此属性。
要详细了解 tempdb 中的性能改进,请参阅以下博客文章:
SQL Server 中的 tempdb 容量规划
确定 tempdb 在 SQL Server 生产环境中的适当大小取决于多种因素。 如本文中前面所述,这些因素包括现有工作负荷以及使用的 SQL Server 功能。 建议你通过在 SQL Server 测试环境中执行下列任务来分析现有的工作负荷:
- 设置 tempdb 的自动增长。
- 执行单独的查询或工作负荷跟踪文件,并监视 tempdb 空间使用。
- 执行索引维护操作(例如重新生成索引),并监视 tempdb 空间。
- 使用前面步骤中的空间使用值来预测总的工作负荷使用情况;针对计划的并发活动调整此值,然后相应地设置 tempdb 的大小。
如何监视 tempdb 的使用
如果 tempdb 中磁盘空间不足,则可能会严重破坏 SQL Server 生产环境,并会使正在运行的应用程序无法完成操作。 可以使用 sys.dm_db_file_space_usage 动态管理视图来监视 tempdb 文件中使用的磁盘空间:
-- Determining the Amount of Free Space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
-- Determining the Amount Space Used by the Version Store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;
-- Determining the Amount of Space Used by Internal Objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;
-- Determining the Amount of Space Used by User Objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;
此外,若要在会话级或任务级监视 tempdb 中的页分配或页释放活动,可以使用 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 动态管理视图。 这些视图可用于标识使用 tempdb 中大量磁盘空间的大型查询、临时表或表变量。 还可以使用若干个计数器来监视 tempdb 中的可用空间以及正在使用 tempdb 的资源。 有关详细信息,请参阅下一节。
-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;
-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
R1.internal_objects_alloc_page_count
+ SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count
+ SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count;;
相关内容
用于索引的 SORT_IN_TEMPDB 选项
系统数据库
sys.databases (Transact-SQL)
sys.master_files (Transact-SQL)
移动数据库文件
另请参阅
在 SQL Server 2005 中使用 tempdb
解决 tempdb 中磁盘空间不足的问题
历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。
来源与署名:Microsoft 和文档贡献者(原始作者元数据 stevestein)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。
