笔记65-徐 TempDB上的PAGELATCH
1 --TempDB上的PAGELATCH
2
3
4 --分清楚数据库里面的数据页面和系统页面
5
6 --需要解释的是，SQL不仅在数据页修改的时候加latch，在数据文件的系统页面上，例如
7 --SGAM、PFS、GAM页面发生修改的时候，也会加latch。这些latch在某些情况下也有可能
8 --成为系统瓶颈
9
10 --例如：当数据库创建一张新表的时候，SQL要为这张表分配存储页面，同时SQL也要
11 --修改SGAM、PFS、GAM页面，把已经分配出去的页面标志成已使用。所以每创建一张
12 --新表，SGAM、PFS、GAM页面都会有修改动作
13
14 --这种行为对一般用户数据库不会有问题，因为正常的应用不会折腾着不停地建表，删表
15 --但是tempdb就不同了。如果一个存储过程使用了临时表，而这个存储过程被并发用户
16 --广泛使用，那很自然地就会有很多并发用户在tempdb里同时创建表，做完了以后又
17 --删除表。这时候在一个时间点，会有很多任务要修改SGAM、PFS、GAM页面。他们
18 --上面的latch就会成为系统瓶颈。所以这类问题，通常会发生在tempdb上
19
20
21 --数据页面上的“HOT” 可以通过调整表格设计来缓解，系统页面的瓶颈怎麽解决呢？
22 --其实解决方法很简单。让我们想想，在任何一个时间点，最多能有多少个任务在
23 --并发处理tempdb？假设服务器的所有CPU都在跑这些任务，最大任务数也不会超过
24 --服务器逻辑CPU的数目。DBA可以为tempdb建立和CPU数目一样多的数据文件，他们的
25 --大小又都一样。这样，SQL的任务就会平均地分不到这些数据文件上。在每个时间点
26 --只有一个任务在修改同一个数据文件上的系统分配页面，PAGELATCH瓶颈就不会出现
27
28
29 -------------------------再小结一下 解决方法要点--------------------------------------------
30 --（1）SQL使用几颗CPU在运行，就为TEMPDB创建几个数据文件
31 --（2）这些文件的大小必须一样大
32 --（3）要严格防止tempdb数据空间用尽，引发数据文件自动增长。因为自动增长只会增长
33 --其中一个文件，造成只有一个文件有空闲空间，所有的任务就会集中在他的身上，他
34 --就又变成瓶颈了
35
36
37
38
39 --现在来看一个真实案例,看看tempdb的瓶颈是如何产生的：
40
41 --一个用户反映，他的SQL会在某一段时间里，突然变得非常慢。最后他不得不重启SQL服务。
42 --重启以后，问题消失了。客户在出现问题的那段时间里，收集了主要的系统DVM，以及
43 --性能监视器里和SQL有关的那些计数器。顺便说一句，这台服务器有16颗逻辑CPU
44
45 SELECT * FROM sys.[dm_exec_requests]
46 --从上面的结果来看，问题比较明显，有很多任务在争抢页面2:18:331608上的PAGELATCH_X资源
47 --tempdb上的瓶颈是当时最大的问题
48
49 --但是2:18:331608这个值令人有点疑惑。第一，文件ID18意味着这个tempdb上至少有18个文件。
50 --除去一个日志文件，这个tempdb至少有17个数据文件。而这台服务器只有16颗CPU，为什麽
51 --大家别的数据文件都不用，非要枪这个第18号文件呢？这是很奇怪的地方。第二，SGAM、PFS、GAM
52 --页面都在数据文件的开头。只有当数据文件变得比较大以后，文件头的那几个页面已经不够用了
53 --，SQL才会在后面再分配新的系统页面。所以331608意味着这个18号文件当时已经比较大了
54
55 --带着这些疑惑，笔者又让客户收集了一个tempdb上的sp_helpfile结果（但这时问题已经消失）
56 --这个结果回答了疑惑
57
58 --像前面猜测那样，这个tempdb上果然有17个数据文件。但是这些文件的配置是不一样的。前16个
59 --文件的初始大小是256MB，最大大小是512MB。而最后一个数据文件，也就是出问题的18号，初始大小
60 --是2GB，没有上限。用户这样设置，显示是为了防止tempdb在P盘上使用太多的空间
61
62 --如果tempdb能够同时使用这17个数据文件，tempdb数据文件应该是多大呢？假使前16个小的
63 --数据文件都自动增长到了最大值，而且最后一个数据文件没有增长，大小应该是
64 --（0.5*16+2）=10GB。那出问题的时候，tempdb到底用了多大的数据文件呢？
65
66 --这个信息用户没有收集，不过在性能监视器的计数器里，有一个计数器
67 --SQLSERVER:DataBase\DataFile(s) Size(KB)这时候能够派上用场。打开一看，
68 --发现当时的值在21GB。也就是说，前面的16个小的数据文件已经用完。SQL只好使用
69 --第18号数据文件，因为他没有上限，就让他不断自动增长。所有压力都集中在了
70 --一个数据文件上，难怪这个数据文件成为了瓶颈
71
72 --防止问题再次出现的建议：就是增大前16个数据文件的初始大小。既然这次SQL会用到
73 --21GB，不如每个数据文件的初始大小都设置为2GB。这样就不容易用完了。如果P盘
74 --没有那么多空间，可以把这些数据文件都移到G盘上。当然，监视tempdb的使用情况
75 --搞清楚是谁在tempdb里占用这么多空间也是很重要的。监视方法和脚本第一章里有
76 --介绍
