笔记65-徐 TempDB上的PAGELATCH

笔记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 --介绍

 

posted @ 2013-07-29 09:07 桦仔 阅读(...) 评论(...) 编辑 收藏