【T-SQL性能优化】01.TempDB的使用和性能问题
【T-SQL性能优化】01.TempDB的使用和性能问题
以前总是追求新东西，发现基础才是最重要的，今年主要的目标是精通SQL查询和SQL性能优化。
本系列【T-SQL基础】主要是针对T-SQL基础的总结。
一、TempDB是什么?
1.TempDB是一个系统数据库。从SQL Server2000开始就一直存在。
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语句进行查看：
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的使用情况
Exec sp_spaceused
（2）查看tempdb.mdf文件的大小
SELECT * FROM dbo.sysfiles
（3）查看tempdb的使用空间
SELECT * FROM sys.dm_db_file_space_usage
（4）查看会话的空间分配情况，不包含当前活动的任务。
SELECT * FROM sys.dm_db_session_space_usage WHERE session_id > 50
（5）查看TempDB中当前运行任务的信息。
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上的磁盘读写情况。
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的配置项
SELECT * FROM sys.databases WHERE name = 'tempdb'
参考资料：
https://msdn.microsoft.com/zh-cn/library/ms176029(v=sql.105).aspx
https://msdn.microsoft.com/zh-cn/library/ms345368(v=sql.105).aspx
https://msdn.microsoft.com/zh-cn/library/ms190768(v=sql.105).aspx
《SQL Server 性能优化与管理的艺术》
作 者： Jackson0714 出 处：http://www.cnblogs.com/jackson0714/ 关于作者：专注于微软平台的项目开发。如有问题或建议，请多多赐教！ 版权声明：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接。 特此声明：所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误，共同进步。或者直接私信我 声援博主：您的鼓励是作者坚持原创和持续写作的最大动力！