tempdb大量闩锁等待问题分析
背景
客户业务系统升级后,高峰期运行缓慢,在SQL专家云上看到数据库出现严重等待,需要分析原因并紧急处理。现象
登录到SQL专家云中,进入实时可视化页面,在活动会话里面看到有大量资源等待的会话。
![]()
点击一个时间点,进入到该时间点的活动会话原始数据。看到大量会话的等待类型为PAGELATCH_UP,等待资源为“2:1:xxxxxxx” ,SQL语句都和临时表有关。
![]()
![]()
分析
会话等待的资源“2:1:xxxxxxx” 代表ID为 2 的数据库(tempdb)的1号文件(tempdev)的xxxxxxx页。SQL语句创建一个临时表时,相当于在tempdb中创建一张表,SQL Server要为这张表分配存储页面,需要修改SGAM、PFS、GAM系统数据页,为了其他表不会分配到同一个数据页,在修改时使用闩锁,修改完成后释放闩锁。
这种机制对一般的用户数据库不会有问题,因为正常的应用不会折腾着不停地建表、删表。但是tempdb就不同了,经常会有高并发的SQL语句使用临时表。因此在同一个时间点会有很多线程要修改系统页,就会产生大量的PAGELATCH_UP闩锁等待。
解决
最直接的解决办法是增加tempdb数据文件的个数,这样多个线程修改系统页时就被分配到不同的数据文件上,从而降低了闩锁争用。在增加数据文件时有2点重要的注意事项:
文件个数一定要和CPU核数对齐,也就是能被CPU核数整除。例如是40个核,则可以创建4个、8个、10个、20个等。建议最少4个,最多可以和CPU的核数一样多,但一般的服务器CPU核数都在几十个,没有必要创建这么多
- 。每个数据文件的初始大小和增长大小的配置一定要一致,如果不一致,SQL Server将不会平均地使用每个数据文件,而是先使用容量最大的数据文件。
![]()
tempdb的重要性
临时表、表变量、查询语句(ORDER BY、GROUP BY、UNION等)、触发器、索引维护、快照事务隔离级别等很多功能都会用到tempdb,详细参考微软官方文档:https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15。
![]()
最佳实践
对tempdb依赖较高的实例,可以为tempdb单独规划一个快速的物理磁盘,例如SSD,让tempdb独享I/O资源,不和用户数据库文件争抢,从而加快tempdb的响应速度。如果服务器有基于磁盘镜像的容灾软件,为tempdb规划单独的磁盘的另一个好处是不用把temdp文件同步到备机。
相关文章
- 直接在代码里面对list集合进行分页
- .NET Framework 4.5新特性详解
- 大数据的简要介绍
- 大数据的由来
- 高斯混合模型的自然梯度变量推理
- timing-wheel 仿Kafka实现的时间轮算法
- 使用Navicat软件连接自建数据库(Linux系统)
- 那一天,我被Redis主从架构支配的恐惧
- Redis 深入了解键的过期时间
- C#使用委托调用实现用户端等待闪屏
- 基于流计算 Oceanus 和 Elasticsearch Service 构建百亿级实时监控系统
- GRAND | 转录调控网络预测数据库
- JFreeChart API中文文档
- 临床相关突变查询数据库
- TIGER | 人类胰岛基因变化查询数据库
- 视频边缘计算网关EasyNVR在视频整体监控解决方案中的应用分析
- Apache Arrow - 大数据在数据湖后的下一个风向标
- 常见的电商数据指标体系
- AKShare-艺人数据-艺人流量价值
- MySQL中多表联合查询与子查询的这些区别,你可能不知道!