tempdb大量闩锁等待问题分析

合集 - SQL Server数据库运维(59)

1.SQL SERVER日常运维巡检系列之-日志2024-12-30 2.关于SQL Server镜像的一个小误区2016-07-02 3.一切从“简”，解放IT运维人员2016-12-16 4.CPU问题定位与解决2017-02-07 5.数据库优化案例——————某市中心医院HIS系统2017-01-18 6.系统隐形杀手——阻塞与等待2017-01-11 7.SQL Server常见问题介绍及快速解决建议2016-12-20 8.tempdb日志文件暴增分析2022-11-09 9.数据库服务器CPU不能全部利用原因分析2022-11-07 10.如何快速定位TempDB产生问题2017-02-28 11.索引的重要性2017-02-16 12.SQL语句的优化建议2017-02-14 13.通过分析等待类型解决系统产生问题2017-02-13 14.磁盘问题定位与解决2017-02-09 15.内存问题定位与解决2017-02-08 16.写给数据库运维的兄弟2023-02-27 17.为什么带NOLOCK的查询语句还会造成阻塞2023-02-23 18.链接服务器查询导致的阻塞2023-02-21 19.CXPACKET等待类型分析2023-02-20 20.为什么sleeping的会话会造成阻塞(2)2023-02-17 21.为什么sleeping的会话会造成阻塞2023-02-14 22.为SQL Server配置连接加密2023-02-14 23.SQL SERVER——高可用技术概述2023-02-04 24.数据库服务器CPU不能全部利用原因分析2023-01-15 25.管理工具造成的阻塞2023-01-09 26.自动增长配置不合理导致的性能抖动2023-01-05 27.Profiler导致的严重性能问题2023-01-02 28.CPU持续100%分析并解决分析2022-12-26

29.tempdb大量闩锁等待问题分析2022-11-29

30.探究SQL SERVER 更改跟踪2022-11-15 31.tempdb数据文件暴增分析2022-11-10 32.SQL SERVER 2016 AlwaysOn 无域集群+负载均衡搭建与简测2024-12-16 33.Moebius兼容AlwaysOn2024-12-16 34.Moebius for SQL Server2024-12-16 35.SQL SERVER日常运维巡检系列——数据库备份2024-12-09 36.SQL SERVER日常运维巡检系列——结构设计2024-11-26 37.Moebius for SQL Server2024-11-20 38.SQL SERVER日常运维巡检系列之-数据库2024-10-09 39.【能力提升】SQL Server常见问题介绍及快速解决建议2024-06-19 40.CDC作业历史记录无法删除问题2024-03-04 41.为Oracle链接服务器使用分布式事务2023-11-21 42.大事务导致数据库恢复时间长2023-10-25 43.SQL Server关于AlwaysOn的理解-读写分离的误区(一)2023-09-14 44.链接服务器导致SQL Server停止响应2023-09-05 45.简单配置Sql专家云2023-07-31 46.SQL专家云回溯某时间段内的阻塞2023-07-04 47.SQL专家云快速解决阻塞2023-06-19 48.SQL专家云汇总低效的SQL语句2023-06-05 49.数据库优化案例—某市中心医院HIS系统2023-05-19 50.细说数据库协作运维2023-05-08 51.医院核心数据库一体化建设实践2023-04-24 52.【能力提升】SQL Server常见问题介绍及快速解决建议2023-04-17 53.Moebius数据库多活集群2023-04-10 54.30分钟带你熟练性能优化的那点儿事儿（案例说明）2023-04-03 55.SQL Server底层架构技术对比2023-03-28 56.Moebius兼容AlwaysOn2023-03-23 57.透过等待看数据库2023-03-21 58.数据库自动收缩造成的阻塞2023-03-09 59.CPU持续100%分析并解决2023-03-02

背景

客户业务系统升级后，高峰期运行缓慢，在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文件同步到备机。