笔记30-徐 SQLSERVER内存管理模式与接口

笔记30-徐 SQLSERVER内存管理模式与接口

  1 --SQLSERVER内存管理模式与接口
  2 
  3 
  4 --SQLSERVER是个很喜欢内存资源的程序,他的理想状态就是把所有可能会用到的数据和结构
  5 --都缓存在物理内存里,以达到最优的性能。
  6 
  7 --SQLSERVER会约束自己的内存使用量,但是最好作出合理的分配
  8 
  9 --SQL会缓存大量的数据页面,他还会缓存很多其他信息,包括存储过程的执行计划
 10 --特定用户的安全上下文等
 11 --如果这些信息没有在数据库中缓存,SQL都要重新计算一遍,花额外的时间
 12 
 13 --SQLSERVER开发出来的内存调节接口却很少,只有下面几个:
 14 
 15 --1、min server memory (MB)(sp_configure接口)
 16 --定义SQL的最小内存值 需要澄清亮点
 17 --(1)min server memory 是一个逻辑概念,控制SQLSERVER Total Server Memory的大小
 18 --至于数据是放在物理内存还是缓存文件里,由Windows决定。所以这个值不能保证SQL使用
 19 --的最小物理内存数
 20 
 21 --(2)min server memory 的意思是,在SQL的地址空间增长到这个值以后,就不会再小于这个
 22 --值。当SQL刚启动的时候,仅仅申请启动所需要的内存。所以这个时候内存使用量小于min server memory
 23 --也是正常现象
 24 
 25 
 26 
 27 --2、max server memory (MB)(sp_configure接口)
 28 --定义SQL的最大内存值 需要澄清亮点
 29 --(1)max server memory 也是一个逻辑概念,控制SQLSERVER Total Server Memory的大小
 30 --至于数据是放在物理内存还是缓存文件里,由Windows决定
 31 
 32 --(2)max server memory 只能控制SQL的一部分内存使用量,SQL的virtual bytes或working set大于
 33 --你设置的max server memory 也是一个正常现象。并不是SQL有内存泄漏问题
 34 
 35 
 36 --3、Set Working Set Size(sp_configure接口)
 37 --在SQL的执行代码中调用一个Windows参数,试图将SQL在物理内存中使用的内存数固定下来。
 38 --但是现在的Windows版本已经不再尊重这个请求。所以设置这个参数已经没有任何意义
 39 --有时候还会带来副作用,请不要使用
 40 
 41 --4、AWE Enabled(sp_configure接口)
 42 --让SQL开启用AWE方法申请的内存,以突破32位Windows2GB用户寻址空间。所以这个
 43 --设置对32位SQL意义重大。但是不是所有的SQL内存都会用AWE。AWE扩展出去的的地址
 44 --空间不是什么数据都能存放。这个也是为什麽32位AWE效果没有64位好的一个重要
 45 --原因。
 46 
 47 
 48 
 49 --5、lock pages in memory(企业版会自动开启)
 50 --这个开关是能在一定程度上确保SQL的物理内存数,但是不十分可靠。
 51 
 52 
 53 --调节SQL内存的使用参数比较有限。SQL有一套比较完善的内存自我管理调度机制,这是其他应用
 54 --软件难以企及的。绝大多数情况下,SQL能够很好做自我调节,满足用户需求,维护SQL性能优化
 55 
 56 
 57 
 58 
 59 ------------------内存使用分类--------------------------------------------------------
 60 --按用途分:
 61 
 62 --(1)database cache
 63 --1存放8KB数据页面的缓存区,8KB是数据访问的最小单元
 64 
 65 --2用户写数据到数据页面,等到checkpoint或lazy write才把数据页面写回硬盘
 66 
 67 --3用户使用完页面之后,SQL如果没有内存压力,不会马上删除页面,等以后用户
 68 --再访问这个页面,不用到磁盘读取。所有数据页面都缓存在内存里,等到checkpoint
 69 --的时候把修改过的页面写入硬盘
 70 
 71 
 72 
 73 --(2)SQL的很多功能组件都需要申请内存来完成他们的任务,功能组件简称:consumer
 74 
 75 --connection(连接池):SQL为每一个连接分配一个数据结构,存储关于这个连接的信息。另外还会分配一个
 76 --输入缓冲池,缓冲客户端发来的指令;一个输出缓冲池,存放SQL返回的结果集,等待客户端取走
 77 
 78 --general:一组大杂烩,包括:语句的编译、范式化、每个锁数据结构、事务上下文、表格、索引的元数据
 79 
 80 --query plan:语句和存储过程的执行计划。和database cache类似,如果SQL没有内存压力,他会保留每一个
 81 --生成的执行计划,供以后用户使用,减少编译消耗。所以执行计划也是一块比较大的内存使用区域
 82 
 83 --optimizer:SQL在生成执行计划的过程中需要消耗的内存
 84 
 85 --utilities:像BCP、log manager、paraller queries并行查询、backup等比较特殊操作需要的内存
 86 
 87 
 88 --(3)线程内存
 89 --SQL会为进程内的每个线程分配0.5MB的内存,以存放线程的数据结构和相关信息
 90 
 91 
 92 --(4)第三方代码申请的内存(COM,XP...)
 93 --在SQL进程里,会运行一些非SQL自身的代码。例如:用户定义CLR或扩展存储过程EXEC sys.xp_cmdshell
 94 --代码,链接服务器linked server需要加载的数据连接驱动,调用SQL Mail功能需要加载的MAPI动态库等。
 95 --这些代码运行也会申请内存,会算在SQL进程的虚拟地址空间里。但是由于他们不是SQL自身的代码,
 96 --所以他们申请了多少内存SQL自己都不知道(但是作为一个进程,Windows能够知道SQL申请的所有内存)
 97 --所以也能计算这一部分的内存使用
 98 
 99 
100 
101 
102 --按申请方式分类
103 --第一种:SQL内存申请预先reserve一块大内存,然后在使用时一小块一小块地commit
104 --第二种:内存申请直接从地址空间里commit ,这种称为stolen
105 
106 --database cache:预先reserve一块大内存,然后在使用时一小块一小块地commit
107 --其他:内存申请直接从地址空间里commit ,这种称为stolen,不是内存泄漏,也是正常使用
108 
109 --AWE扩展出去的内存只能用来存放database cache,其他内存还是要在2GB内存里想办法
110 
111 
112 --按申请大小分类
113 --SQL有两种内存申请单位
114 --1所有可以分成小于或等于8KB一个单位的内存申请,SQL会分配一个页面8KB。所有这些页面都集中管理,这块
115 --内存被称为BUFFER POOL,一次一个页面的分配称为single page allocation
116 
117 --2大于8KB单位的内存申请,SQL把他们集中在另外一个区域,成为multi-page/memtoleave,
118 --称为multi page allocation
119 
120 --对于SQL,大部分内存需求都可以以8KB为单位,放在buffer pool里
121 
122 --按用途分类:
123 --database cache:由于都是数据页面,所以都是以8KB为单位,存储在buffer pool里面
124 
125 --其他consumer:
126 --connection(连接池):和net work package size(客户端和SQL通信的每个数据包大小)有关系
127 --如果是默认4KB设置,输入输出缓存会放在buffer pool里,如果改成8KB或更大,输入输出缓存
128 --会放在 multi-page里
129 --net work package size的大小可以使用sp_configure来更改,但是任何一个客户端都可以指定
130 --他建立的连接自己的net work package size
131 
132 
133 --general:绝大部分内存,例如每个锁数据结构、事务上下文、表格、索引元数据等,都会以
134 --8KB为单位申请。但是如果偶尔一个语句特别长,他的编译和范式化需要使用大于8KB为单位
135 --的内存,会使用multi-page里的
136 
137 
138 --query plan、optimizer、utilities:和general类似,绝大部分内存使用会在buffer pool,
139 --一些特别长语句,会使用一部分的multi-page
140 
141 --第三方的代码:由于这些代码申请的内存大小SQL自己不知道,所以SQL把他们的内存申请放在
142 --multi-page
143 
144 --线程内存;每个以512KB为单位,自然放在multi-page里
145 
146 
147 
148 -- 各种内存分类方法之间的关系
149 --                              database cache      consumer           3rd party code         threads     CLR code
150 --reserved/commit                 是                一般不是             一般不是              不是         是
151 --stolen                          不是              是                     是                    是         不是
152 --buffer pool(single page)        所有               绝大部分             没有                   没有       没有
153 --memtoleave(multi page)          没有               一小部分             所有                  所有        所有
154 
155 
156 --各部分内存的分布和大小限制
157 
158 --在32位SQL里面,虚拟地址空间为2GB(除掉核心态的2GB)。这个2GB被SQL分成两部分:
159 
160 --8KB为单位的buffer pool也称BPool
161 
162 --不以8KB为单位分配的部分,memtoleave multi-page(之所以叫memtoleave,是因为SQL启动的时候会把这部分虚拟
163 --地址预留出来,地址空间是有上限的,SQL2005以上,memtoleave的默认大小是256MB加上sp_configure配置的最大
164 --线程数(服务器属性-》处理器-》最大工作线程数)乘以512KB,就是256MB+256(个threads)*0.5MB=384MB),
165 --剩下就是2GB-384MB=1664MB,就是buffer pool的最大值
166 
167 --线程内存:SQL进程为每个线程分配0.5MB的内存,以存放线程的数据结构和相关信息
168 
169 --SQL启动时buffer pool很少,随着缓存数据越来越多,SQL会不断commit内存给buffer pool,buffer pool
170 --大小达到max server memory或1664MB,SQL无法申请内存了
171 
172 --memtoleave也一样,但是他的上限是SQL启动时算出来的,跟max server memory无关
173 
174 
175 
176 --X64位系统 32位SQL:因为X64支持WOW,由于64位核心态已经不占用32位应用程序的进程的虚拟地址空间,所以
177 --WOW的32位应用程序进程用户态可以到4GB。memtoleave-MULTI-PAGE算法不变,还是默认的384MB。
178 --buffer pool可以达到3.6GB。如果开启了AWE,这3.6GB就可以基本都给buffer pool里的stolen memory使用了
179 
180 
181 --如果用户频繁使用linked server或扩展存储过程EXEC sys.xp_cmdshell等会使用multipage的功能
182 --multi-page会变得非常繁忙,SQL会先使用默认的那一块256MB。如果这里使用完了,SQL会看看预留给
183 --线程那一块内存是不是用完。一般SQL不会把256个线程都创建出来,所以这里还是有地方
184 --SQL会在这里存放multi-page的申请,直到把这一块内存用完
185 
186 --SQL会记录这些缓存被引用的次数,每个缓存页面和执行计划都有一个权值。每隔一段时间,权值减一
187 --当系统内存有压力时,SQL根据权值是0的那个缓存(即在一段时间没有被引用过)从内存里清理出去
188 --这个清理出去的动作由lazy writer完成,如果lazy writer经常被触发表明系统有内存压力
189 
190 --memtoleave multi-page里的执行计划也归这个机制管理,当buffer pool有压力时,memtoleave里的
191 --一部分缓存也会被清理出去
192 
193 
194 
195 --64位操作系统,SQL虚拟地址空间扩展了很多,不会再为multi-page预留一块地址空间。就没有了
196 --memtoleave这个概念。不管single page 还是multi page,都可以无约束地申请虚拟地址空间里的
197 --地址资源。所以在64位操作系统上会遇到一些32位不会遇到的问题
198 
199 
200 
201 
202 
203 -----------------------database cache的上限一般由max server memory来控制-------------------------------------------
204 
205 
206 --64位SQL,stolen内存没有限制,只要物理内存足够,buffer pool还没有达到max server memory的限制
207 --就可以不断地缓存执行计划,尤其是小型数据库内存量比较大的服务器,stolen内存数量一般会超过
208 --database cache的数量。这种变化DBA要注意

 

posted @ 2013-07-27 15:56 桦仔 阅读(...) 评论(...)  编辑 收藏