写点什么

TempDB 的使用和性能问题

发布于: 2021 年 06 月 13 日

大家好,我是悟空。


本篇给大家介绍 TempDB 的使用和性能问题总结。

一、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 行)则可以使用表变量。


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 磁盘问题


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%,不限制增长。


这个配置可以修改,要视生产环境的情况而修改。



建议如下配置


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).aspxhttps://msdn.microsoft.com/zh-cn/library/ms345368(v=sql.105).aspxhttps://msdn.microsoft.com/zh-cn/library/ms190768(v=sql.105).aspx


《SQL Server 性能优化与管理的艺术》


欢迎关注我的公众号:「悟空聊架构


作者简介:8 年互联网职场老兵|全栈工程师|90 后超级奶爸|开源践行者|公众号万粉原创号主。蓝桥签约作者,著有《JVM 性能调优实战》专栏,手写了一套 7 万字 SpringCloud 实战总结和 3 万字分布式算法总结。欢迎关注我的公众号「悟空聊架构」,免费获取资料学习。


我是悟空,努力变强,变身超级赛亚人!

发布于: 2021 年 06 月 13 日阅读数: 20
用户头像

用故事、大白话讲解Java、分布式、架构设计 2018.05.06 加入

公众号:「悟空聊架构」

评论

发布
暂无评论
TempDB 的使用和性能问题