tempdb 数据库

tempdb 系统数据库是一个全局资源,可供连接到 SQL Server 实例的所有用户使用,并可用于保存下列各项:

  • 显式创建的临时用户对象,例如全局或局部临时表、临时存储过程、表变量或游标。

  • SQL Server 数据库引擎创建的内部对象,例如,用于存储假脱机或排序的中间结果的工作表。

  • 由使用已提交读(使用行版本控制隔离或快照隔离事务)的数据库中数据修改事务生成的行版本。

  • 由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。

tempdb 中的操作是最小日志记录操作。这将使事务产生回滚。每次启动 SQL Server 时都会重新创建 tempdb,从而在系统启动时总是保持一个干净的数据库副本。在断开联接时会自动删除临时表和存储过程,并且在系统关闭后没有活动连接。因此 tempdb 中不会有什么内容从一个 SQL Server 会话保存到另一个会话。不允许对 tempdb 进行备份和还原操作。

tempdb 的物理属性

下表列出了 tempdb 数据和日志文件的初始配置值。对于不同版本的 SQL Server,这些文件的大小可能略有不同。

文件

逻辑名称

物理名称

文件增长

主数据

tempdev

tempdb.mdf

按 10% 自动增长,直到磁盘已满

日志

templog

templog.ldf

以 10% 的速度自动增长到最大 2 TB

tempdb 的大小可以影响系统性能。例如,如果 tempdb 的大小太小,则每次启动 SQL Server 时,系统处理可能忙于数据库的自动增长,而不能支持工作负荷要求。可以通过增加 tempdb 的大小来避免此开销。有关详细信息,请参阅优化 tempdb 性能tempdb 容量规划

tempdb 的性能提高

在 SQL Server 中,tempdb 性能以下列方式进行提高:

  • 可能缓存临时表和表变量。缓存允许删除和创建临时对象的操作非常快速地执行,并减少页分配的争用问题。

  • 分配页闩锁协议得到改善。从而减少使用的 UP(更新)闩锁数。

  • 减少了 tempdb 的日志开销。从而降低了 tempdb 日志文件上的磁盘 I/O 带宽占用。

  • tempdb 中分配混合页的算法得到改善。

移动 tempdb 数据和日志文件

若要移动 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

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)

限制

不能对 tempdb 数据库执行以下操作:

  • 添加文件组。

  • 备份或还原数据库。

  • 更改排序规则。默认排序规则为服务器排序规则。

  • 更改数据库所有者。tempdb 的所有者是 dbo

  • 创建数据库快照。

  • 删除数据库。

  • 从数据库中删除 guest 用户。

  • 启用变更数据捕获。

  • 参与数据库镜像。

  • 删除主文件组、主数据文件或日志文件。

  • 重命名数据库或主文件组。

  • 运行 DBCC CHECKALLOC。

  • 运行 DBCC CHECKCATALOG。

  • 将数据库设置为 OFFLINE。

  • 将数据库或主文件组设置为 READ_ONLY。