合 MSSQL中tempDB的使用和性能问题
Tags： MSSQLSQL Servertempdb性能优化
- 简介
- tempDB是什么?
- tempDB用来存放什么？
- 2.1.用户临时对象
- 2.2.内部临时对象
- 2.3.版本存储
- tempDB上的存在的性能问题
- 3.1 空间使用情况
- 3.2 I/O问题
- 优化tempDB
- 1.配置文件的大小
- 2.存放文件的地方
- 3.文件的个数
- 其他
- 1.不能对TempDB执行什么操作
- 2、查看TempDB的配置项
- SQL Server 中 tempdb 的物理属性
- 在 SQL Server 中移动 tempdb 数据和日志文件
- SQL Server 中 tempdb 的数据库选项
- Azure SQL 中的 tempdb
- SQL 数据库中的 tempdb
- SQL 托管实例中的 tempdb
- 限制
- 权限
- 在 SQL Server 中优化 tempdb 性能
- SQL Server 中 tempdb 的性能提高
- 在 SQL Server 2016 (13.x) 中引入
- 在 SQL Server 2017 (14.x) 中引入
- 在 SQL Server 2019 (15.x) 中引入
- 在 SQL Server 2022 (16.x) 中引入
- 内存优化 tempdb 元数据
- 配置和使用内存优化 tempdb 元数据
- 内存优化 tempdb 限制
- SQL Server 中的 tempdb 容量计划
- 监视 tempdb 的使用
- 减少SQL Server tempdb 数据库中的分配争用的建议
- 症状
- 原因
- 解决方案
- 增加大小相等的 tempdb 数据文件数
- 增加 tempdb 数据文件的数量如何减少争用
- 实现跟踪标志 -T1118 如何减少争用
- 缺点
- 参考
- tempdb监控
- 总结
- 参考
简介
tempdb 系统数据库是包含以下内容的全局资源：
显式创建的临时用户对象。 它们包括全局或局部临时表及索引、临时存储过程、表变量、表值函数返回的表或游标。
数据库引擎创建的内部对象。 它们包括：
- 用于储存假脱机、游标、排序和临时大型对象 (LOB) 存储的中间结果的工作表。
- 用于哈希联接或哈希聚合操作的工作文件。
- 用于创建或重新生成索引等操作（如果指定了
SORT_IN_TEMPDB）的中间排序结果，或者某些GROUP BY、ORDER BY或UNION查询的中间排序结果。
每个内部对象至少使用九页：一个 IAM 页，一个八页的盘区。 有关页和盘区的详细信息，请参阅页和盘区。
版本存储区是数据页的集合，它包含支持用于行版本控制的功能的数据行。 有两种类型：公用版本存储区和联机索引生成版本存储区。 版本存储区包含：
- 由通过行版本控制隔离或快照隔离事务使用
READ COMMITTED的数据库中的数据修改事务生成的行版本。 - 由数据修改事务为实现联机索引操作、多重活动结果集 (MARS) 以及
AFTER触发器等功能而生成的行版本。
- 由通过行版本控制隔离或快照隔离事务使用
tempdb 中的操作是最小日志记录操作，以便回滚事务。 每次启动 SQL Server 时都会重新创建 tempdb，从而在系统启动时总是具有一个干净的数据库副本。 在断开联接时会自动删除临时表和存储过程，并且在系统关闭后没有活动连接。
tempdb 不会有什么内容从 SQL Server 的一个会话保存到另一个会话。 不允许对 tempdb 执行备份和还原操作。
tempDB是什么?
1.TempDB是一个系统数据库。从SQL SERVER 2000开始就一直存在。
2.只有Simple恢复模式。自动截断模式。
3.存放局部变量/全局临时表/表变量/临时用法（如hash表等）。
4.机器重启或SQL Server服务重启后，都会按照Model库的配置重新创建。
5.如果临时对象是在会话或存储过程范围内产生的，在会话结束后就会自动回收，不能再查询或使用。
6.默认情况下都具有访问权限。
tempDB用来存放什么？
2.1.用户临时对象
（1）由用户再会话中显示创建的实体表和上面的索引。重启后清空。
（2）全局临时表+索引。##开头的表。
（3）局部临时表及上面的索引。#开头的表。
（4）表变量。@开头。
注意：
（1）全局临时表对所有会话都可见。当创建临时表的会话断开数据库的联接，而且也没有活动再引用全局临时表时，SQL Server会自动删除相应的全局临时表。
（2）局部临时表只对创建它的会话再创建级和调用堆栈内部级（内部的过程、函数、触发器、以及动态批处理）是可见的。当创建例程弹出调用堆栈，SQL Server就会自动删除相应的临时表
（3）表变量在tempdb数据库中也有对应的表作为其物理表示。只对当前会话的批处理可见。对调用堆栈中当前批处理的内部批处理是不可见的，对会话中随后的批处理也是不可见的。
（4）根据国外专家的经验，对于大数据，偏向使用临时表，小数据量（一般来说小于100行）则可以使用表变量。
| 是否具有统计信息 | 是否可以创建索引 | 是否是物理存储 | |
|---|---|---|---|
| 临时表 | Y | Y | Y |
| 表变量 | N | N | N |
2.2.内部临时对象
在查询过程中存储临时数据的对象，如Sorts、假脱机、Hash关联和游标等。
可以使用下面的SQL语句进行查看：
1 | SELECT * FROM sys.dm_db_session_space_usage; |
查看internal_object_alloc_page_count列
2.3.版本存储
开启乐观并发模式后，会使用Temp DB存放修改前的版本数据。
注意：
版本存储将会造成Temp DB的非预期增长，需要对Temp DB的文件大小及使用空间进行监控。
tempDB上的存在的性能问题
3.1 空间使用情况
TempDB是系统数据库，被很多地方用到，如果配置和使用不当，空间会被迅速消耗，可能出现报错，影响服务器的正常运行。
查看TempDB的空间使用情况。
3.1.1 可以用性能监视器看下SQL server的空间使用情况。
3.1.2 用SQL语句查询空间使用情况。
（1）查看tempdb的使用情况
1 | Exec sp_spaceused |
（2）查看tempdb.mdf文件的大小
1 | SELECT * FROM dbo.sysfiles |
（3）查看tempdb的使用空间
1 | SELECT * FROM sys.dm_db_file_space_usage |
（4）查看会话的空间分配情况，不包含当前活动的任务。
1 | SELECT * FROM sys.dm_db_session_space_usage WHERE session_id > 50 |
（5）查看TempDB中当前运行任务的信息。
1 | SELECT * FROM sys.dm_db_task_space_usage WHERE session_id > 50 |
3.1.3 诊断TempDB磁盘问题
| 错误 | 引发错误的情况 |
|---|---|
| 1101 或 1105 | 任何会话都必须分配 tempdb 中的空间。 |
| 3959 | 版本存储区已满。此错误在日志中通常出现在错误 1105 或 1101 之后。 |
| 3967 | 由于 tempdb 已满，版本存储区被强制收缩。 |
| 3958 或 3966 | 事务在 tempdb 中找不到所需的版本记录。 |
3.2 I/O问题
（1）用函数sys.dm_io_virtual_file_stats查看当前实例上的TempDB上的磁盘读写情况。
1 2 3 4 5 6 7 8 | SELECT DB_NAME(database_id) AS 'Database Name' , file_id , io_stall_read_ms / num_of_reads AS 'AVG Read Transfer/ms' , io_stall_write_ms / num_of_writes AS 'AVG Write Transfer/ms' , * FROM sys.dm_io_virtual_file_stats(-1, -1) WHERE num_of_reads > 0 AND num_of_writes > 0 |
参考时间：10~20ms 可接受的范围。
（2）大量、频繁地创建和删除临时表及表变量
优化tempDB
1.配置文件的大小
默认配置：
初始大小8M
自动增长10%，不限制增长。
这个配置可以修改，要视生产环境的情况而修改。
建议如下配置
| tempdb 文件大小 | FILEGROWTH 增量 |
|---|---|
| 0 至 100 MB | 10 MB |
| 100 至 200 MB | 20 MB |
| 200 MB 或更多 | 10%* |
2.存放文件的地方
一般要将TempDB的文件单独放到一个磁盘中。如果追求性能，考虑放到RAID0，但是不具有容灾性。
RAID:磁盘阵列
RAID 0 无奇偶校验的条带磁盘。数据横跨所有的物理磁盘，无任何容灾特性。
RAID 1 磁盘镜像。最少需要两个物理磁盘。可同时从两个磁盘读取数据，写数据需要备份到另外一个盘。具有容灾特性。浪费50%的磁盘空间。
RAID 5 具有奇偶校验的条带磁盘。最少需要3个物理磁盘，一个用来存放奇偶校验信息，另外两个用来存放数据，。具有容灾特性。浪费50%的磁盘空间。
RAID 10 或RIAD 0+1 组合。读写性能最好且具有容灾性。
3.文件的个数
TempDB只有一个primary文件组，所有的数据文件都会存放到这个文件组中。常规建议是4个书文件开始，并且需要进行监控，如果发现不够，可以再增加4个。依次类推。建议将文件个数控制再两位数以内。
其他
1.不能对TempDB执行什么操作
- 添加文件组。
- 备份或还原数据库。
- 更改排序规则。默认排序规则为服务器排序规则。
- 更改数据库所有者。tempdb 的所有者是 dbo。
- 创建数据库快照。
- 删除数据库。
- 从数据库中删除 guest 用户。
- 启用变更数据捕获。
- 参与数据库镜像。
- 删除主文件组、主数据文件或日志文件。
- 重命名数据库或主文件组。
- 运行 DBCC CHECKALLOC。
- 运行 DBCC CHECKCATALOG。
- 将数据库设置为 OFFLINE。
- 将数据库或主文件组设置为 READ_ONLY。
2、查看TempDB的配置项
1 | SELECT * FROM sys.databases WHERE name = 'tempdb' |
SQL Server 中 tempdb 的物理属性
下表列出了 SQL Server 中 tempdb 数据和日志文件的初始配置值。 这些值基于 model 数据库的默认值。 对于不同版本的 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 的倍数增加数据文件的数量，直到争用减少到可接受的级别或对工作负荷/代码进行更改。
数据文件数的默认值遵循 KB 2154845中的一般准则。
要检查 tempdb 的当前大小和增长参数，请查询视图 tempdb.sys.database_files。
在 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 | 否 |
| 加密 | 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)。
Azure SQL 中的 tempdb
Azure SQL 数据库中 tempdb 的行为不同于 Azure VM 中的 SQL Server、Azure SQL 托管实例和 SQL Server 的行为。
SQL 数据库中的 tempdb
Azure SQL 数据库中的单一数据库和共用数据库支持存储在 tempdb 中并且范围为数据库级别的全局临时表和全局临时存储过程。 全局临时表和全局临时存储过程供同一个数据库中的所有用户会话共享。 其他数据库中的用户会话无法访问全局临时表。 有关详细信息，请参阅数据库作用域内全局临时表（Azure SQL 数据库）。
对于 Azure SQL 数据库中的单个数据库和共用数据库，在所有系统数据库中，仅可访问 master 数据库和 tempdb 数据库。 有关详细信息，请参阅 Azure 中的逻辑服务器是什么？
若要详细了解 Azure SQL 数据库中的 tempdb 大小，请查看：
SQL 托管实例中的 tempdb
Azure SQL 托管实例以与 SQL Server 相同的方式支持临时对象，其中所有全局临时表和全局临时存储过程都可由同一托管实例中的所有用户会话访问。 同样，所有系统数据库均可访问。
可以配置 tempdb 文件数、其增长增量及其最大大小。 有关在 Azure SQL 托管实例中配置 tempdb 设置的详细信息，请参阅为 Azure SQL 托管实例配置 tempdb 设置。
若要详细了解 Azure SQL 托管实例中的 tempdb 大小，请查看资源限制。
限制
不能在 tempdb 数据库中执行下列操作：
- 添加文件组。
- 备份或还原数据库。
- 更改排序规则。 默认排序规则为服务器排序规则。
- 更改数据库所有者。
tempdb的所有者是 sa。 - 创建数据库快照。
- 删除数据库。
- 从数据库中删除 guest 用户。
- 启用变更数据捕获。
- 参与数据库镜像。
- 删除主文件组、主数据文件或日志文件。
- 重命名数据库或主文件组。
- 正在运行
DBCC CHECKALLOC。 - 正在运行
DBCC CHECKCATALOG。 - 将数据库设置为
OFFLINE。 - 将数据库或主文件组设置为
READ_ONLY。
权限
任何用户都可以在 tempdb 中创建临时对象。 用户只能访问自己的对象，除非他们获得更多的权限。 可以撤销对 tempdb 的连接权限以阻止用户使用 tempdb。 我们不建议这样做，因为一些例程操作需要使用 tempdb。
在 SQL Server 中优化 tempdb 性能
tempdb 数据库的大小和物理位置可能会影响系统的性能。 例如，如果为 tempdb 定义的大小过小，则每次重启 SQL Server 实例时，都可能会占用部分系统处理负荷，以使 tempdb 自动增长到支持工作负荷所需的大小。
如果可以，请使用即时文件初始化来提高数据文件增长操作的性能。
通过将文件大小设置为足够容纳环境中典型工作负载的值来预分配所有 tempdb 文件的空间。 预先分配可避免 tempdb 因扩展得过于频繁而影响性能。 tempdb 数据库应设置为自动增长，以便在出现意外情况时增加磁盘空间。
每个文件组中的数据文件应大小一致，因为 SQL Server 使用比例填充算法，这种算法可增加可用空间，便于文件分配。 将 tempdb 分割成大小相等的多个数据文件，可以为使用 tempdb 的操作提供更高的并行效率。
将文件增量设置为合理的大小并确保所有数据文件增量设置相同，以免 tempdb 数据库文件的增量过小。 如果文件的增量与写入 tempdb 的数据量相比过小，则 tempdb 可能需要通过自动增长事件频繁扩大。 自动增长事件对性能产生负面影响。
对tempdb依赖较高的实例，可以为tempdb单独规划一个快速的物理磁盘，例如SSD，让tempdb独享I/O资源，不和用户数据库文件争抢，从而加快tempdb的响应速度。如果服务器有基于磁盘镜像的容灾软件，为tempdb规划单独的磁盘的另一个好处是不用把temdp文件同步到备机。
要检查 tempdb 的当前大小和增长参数，请使用以下查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT FileName = df.name, current_file_size_MB = df.size*1.0/128, max_size = CASE df.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_value = CASE WHEN df.growth = 0 THEN df.growth WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0 WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth END, growth_increment_unit = CASE WHEN df.growth = 0 THEN 'Size is fixed.' WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN 'Growth value is MB.' WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN 'Growth value is a percentage.' END FROM tempdb.sys.database_files AS df; GO |
将 tempdb 数据库放置在快速 I/O 子系统中。 如果有许多直接连接的磁盘，则请使用磁盘条带化。 单个或成组的 tempdb 数据文件并不一定要位于不同的磁盘或主轴上，除非存在 I/O 瓶颈。
将 tempdb 数据库放置在用户数据库使用的磁盘以外的磁盘中。
备注
尽管数据库选项 DELAYED_DURABILITY 设置 tempdb 为 DISABLED，但 SQL Server 使用延迟提交刷新对磁盘的 tempdb 日志更改，因为 tempdb 是在启动时创建的，并且不需要运行恢复过程。
SQL Server 中 tempdb 的性能提高
在 SQL Server 2016 (13.x) 中引入
- 已缓存的临时表和表变量。 缓存允许删除和创建临时对象的操作非常快速地运行。 缓存还可以减少页分配和元数据争用问题。
- 改进了分配页闩锁协议，减少了所用
UP（更新）闩锁的数量。 - 减少了
tempdb的日志记录开销，从而减少了tempdb日志文件的磁盘 I/O 带宽消耗。 - 在新的实例安装过程中，安装程序会添加多个
tempdb数据文件。 可以使用“数据库引擎配置”部分中新增的 UI 输入控件和命令行参数/SQLTEMPDBFILECOUNT来完成此任务。 默认情况下，安装程序添加的tempdb数据文件数为逻辑处理器计数或 8，以较小者为准。 - 如果有多个
tempdb数据文件，那么所有文件都会同时自动增长相同的量，具体取决于增长设置。 不再需要跟踪标志 1117。 有关详细信息，请阅读 TEMPDB 和用户数据库的 -T1117 和 -T1118 更改。 tempdb中的所有分配使用统一盘区。 不再需要跟踪标志 1118。 有关tempdb中性能改进的详细信息，请参阅博客文章 TEMPDB - Files and Trace Flags and Updates, Oh My!（TEMPDB - 文件和跟踪标志以及更新，天哪！）。- 对于主文件组，
AUTOGROW_ALL_FILES属性已启用，且不能修改此属性。
在 SQL Server 2017 (14.x) 中引入
- SQL 安装体验改进了初始
tempdb文件分配指南。 如果初始文件大小设置为大于 1 GB 的值且未启用即时文件初始化，则 SQL 安装程序会警告客户，防止实例启动延迟。 - SQL Server 2017 引入了新的 DMV sys.dm_tran_version_store_space_usage，用于跟踪每个数据库的版本存储使用情况。 此新的 DMV 将可用于为 DBA 的版本存储使用情况监视
tempdb，这些 DBA 可以根据每个数据库的版本存储使用情况要求主动规划tempdb大小调整。 - 新的智能查询处理功能（如自适应联接和内存授予反馈）可减少连续执行查询的内存溢出，从而减少不必要的
tempdb使用。
在 SQL Server 2019 (15.x) 中引入
- 从 SQL Server 2019（15.x）开始，SQL Server 在打开
tempdb的文件以获取最大磁盘吞吐量时不使用FILE_FLAG_WRITE_THROUGH选项。 由于tempdb在 SQL Server 启动时重新创建，因此不需要这些选项，因为它们适用于其他系统数据库和用户数据库，以便实现数据一致性。 有关FILE_FLAG_WRITE_THROUGH的详细信息，请参阅在 SQL Server 中扩展数据可靠性的日志记录和数据存储算法。 - 内存优化
tempdb元数据消除了tempdb中 PAGELATCH 等待的瓶颈，达到了全新的可伸缩性水平。 有关详细信息，请观看本关于方式（以及时机）的视频演示：内存优化 TempDB 元数据。 有关详细信息，请阅读监视和排查内存优化 tempdb 元数据的问题。 - 并发页可用空间 (PFS) 页更新可减少所有数据库中的补丁闩锁争用，这是
tempdb中最常见的问题。 此改进改变了使用 PFS 更新来管理并发的方式，这样就能在共享闩锁（而不是排他闩锁）下更新它们。 自 SQL Server 2019 (15.x) 起，此行为在所有数据库（包括 TempDB）中默认处于启用状态。 有关 PFS 页面的详细信息，请阅读表象之下：GAM、SGAM 和 PFS 页面。 - 默认情况下，Linux 上的 SQL Server 新安装会根据逻辑内核数创建多个
tempdb数据文件（最多八个数据文件）。 这不适用于就地次要版本或主版本升级。 每个tempdb文件的大小为 8MB，且自动增长大小为 64MB。 此行为类似于 Windows 上的默认 SQL Server 安装。
在 SQL Server 2022 (16.x) 中引入
- SQL Server 2022 (16.x) 引入了通过系统页闩锁并发增强功能改进的可伸缩性。 对全局分配映射 (GAM) 页和共享全局分配映射 (SGAM) 页的并发更新可减少分配/解除分配数据页和区时的页闩锁争用。 这些增强功能适用于所有用户数据库，尤其有益于
tempdb密集型工作负载。 有关 GAM 和 SGAM 页面的详细信息，请阅读表象之下：GAM、SGAM 和 PFS 页面。 有关详细信息，请观看系统页闩锁并发增强功能 (Ep. 6) |公开的数据。
内存优化 tempdb 元数据
对于 SQL Server 上运行的许多工作负载，tempdb 中的元数据争用历来是可伸缩性的瓶颈。 SQL Server 2019 (15.x) 引入了一项新功能，它属于内存数据库功能系列：内存优化 tempdb 元数据。
此功能有效地消除了这种瓶颈，并为 tempdb 繁重的工作负荷提供了新级别的可伸缩性。 在 SQL Server 2019 (15.x) 中，管理临时表元数据时所涉及的系统表可以移动到无闩锁的非持久内存优化表中。
备注
目前，内存优化 tempdb 元数据功能在 Azure SQL 数据库或 Azure SQL 托管实例中不可用。
本视频时长 7 分钟，请观看它来大致了解如何及何时使用经过内存优化的 tempdb 元数据：
配置和使用内存优化 tempdb 元数据
要选择加入此新功能，请使用以下脚本：
1 | ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON; |
此配置更改需要重新启动服务才能生效。
可使用以下 T-SQL 命令验证 tempdb 是否经过内存优化：
1 | SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized'); |
