合 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 |
