笔记31-徐 一些SQL内存使用的错误理解以及内存使用状况分析

笔记31-徐 一些SQL内存使用的错误理解以及内存使用状况分析

  1 --一些SQL内存使用的错误理解以及内存使用状况分析
  2 
  3 --1、Windows上还有很多物理内存没有被使用,就意味着SQL不缺内存
  4 --SQL很可能设置了max server memory,约束了SQL继续申请内存的能力
  5 --32位机器上,由于虚拟地址空间的限制,SQL可能已经无法继续申请内存了
  6 
  7 --例如,一台4GB的32位服务器上,SQL最多只能使用2GB物理内存。一般Windows
  8 --会使用500MB左右,这台机器可能就有1GB多的空闲物理内存。这部分是SQL不开AWE
  9 --就用不到的
 10 
 11 --2、SQL进程的private bytesH或working set在不停地向上涨,说明SQL有内存泄漏的问题
 12 --对于申请内存,SQL有严格限制,在32位服务器上,不管是buffer pool还是memtoleave,都有一个
 13 --使用最大上限。当Windows感觉到有内存压力的时候,SQL会自动地释放内存,所以作为一个进程
 14 --SQL发生内存泄漏的机会是非常小的
 15 --因为刚开始SQL只会commit他启动所需要的那部分内存,当使用越多数据缓存,会不断申请内存,
 16 --Windows感到有内存压力。如果想限制SQL内存申请,设置max server memory就可以了
 17 
 18 --3、max server memory的值,就是SQL内存使用量的最大值,超过这个值就不正常
 19 --max server memory只是buffer pool的上限,不是SQL所有内存的上限。由于memtoleave一般
 20 --只有384MB,远远小于buffer pool,所以控制好buffer pool就基本控制住了SQL的整体内存使用量
 21 
 22 --4、SQL内存使用总量就是性能监视器里的SQLSERVER:memory manager-total server memory的值
 23 --性能监视器里收集的数据都是SQL自己收集的。从SQL2005以后,SQL整合了所有的内存申请,让他们
 24 --使用同一接口。所以SQL对自己申请的内存数量是了如指掌的。但是问题是SQL进程里运行的代码不都是
 25 --SQL自己的代码。对第三方代码,SQL是不知道他们申请了多少内存的
 26 --如果SQL开启了AWE,Windows就没有办法正确判断出一个使用了AWE内存的进程究竟用了多少内存
 27 
 28 
 29 
 30 
 31 --5、当系统有内存压力的时候,SQL总是会自动释放内存
 32 --如果使用了lock page in memory ,就不会释放内存,这时候最好设置一下max server memory
 33 
 34 
 35 --6、SQL有办法将自己的内存绑定在物理内存里
 36 --企业版的SQL能通过lock page in memory锁定内存,但是用户态应用程序受限于核心态,如果核心态发出
 37 --内存请求,比如Windows和一些驱动程序,SQL就会被逼把内存释放出来
 38 
 39 
 40 
 41 --7、增加memtoleave的大小可以提高SQL的性能
 42 --在32位SQLSERVER上,memtoleave的大小是384MB,如果memtoleave使用完了,那么一些重要功能不能使用
 43 --甚至连接都建立不起来。例如linked server分布式查询,CLR 扩展存储过程,要增加memtoleave使用
 44 ---g 启动参数,如果想把memtoleave设成512MB,可以这样
 45 
 46 --   /g 512
 47 
 48 
 49 --  -g  memory_to_reserve
 50 --指定内存的兆字节整数,该内存被保留下来用于SQLServer2000内部(进程内)运行的其它应用程序。
 51 
 52 
 53 --但是要注意,SQL只有2GB虚拟地址空间,memtoleave多,那么buffer pool就会变少
 54 
 55 
 56 --8、增加物理内存一定会提高SQL的性能
 57 --(1)检查内存瓶颈:database cache  \stolen  \buffer pool    \memtoleave 
 58 --(2)SQL是否用得上,最好升级到64位操作系统
 59 --(3)如果database cache没有压力,SQL会缓存太多的TSQL执行计划,反而会增加SQL的维护成本
 60 --只有在database cache缺内存的时候,增加内存对性能才有帮助。
 61 --如果是小型数据库,常用数据页面已经缓存在内存里,增加内存对性能不会有太大帮助
 62 
 63 
 64 
 65 
 66 
 67 -----------------------SQL内存使用状况分析------------------------------------
 68 --DBA可以从三个方面分析SQL内存:
 69 --(1)性能监视器
 70 --(2)SQL内存相关计数器
 71 --(3)sys.dm_os_memory_clerks动态管理视图  clerk:店员
 72 --在SQL2005以后,SQL内存管理作出了调整,使用memory clerk的方式统一管理SQL内存的分配和回收
 73 
 74 --所有SQL代码要申请或释放内存都需要通过他们的clerk。SQL通过这些clerk之间的协调,满足不同
 75 --形式的需求。某一些clerk使用得多,SQL会通知其他clerk,请他们释放一部分内存,让出空间来
 76 --给新内存的clerk。通过这种机制,SQL可以知道每个clerk使用了多少内存,从而能够知道自己
 77 --总共用了多少内存。这些信息保存在动态管理视图里。
 78 
 79 --有些信息是以前SQL版本无法知道的,所以在SQL2005以后,定期查询动态管理视图是一种比较好
 80 --的监视手段
 81 
 82 --动态管理视图为监视SQL的运行打开了一个新的大门
 83 
 84 
 85 --sys.dm_os_memory_clerks:他返回实例中当前处于活动状态的全部内存clerk的集合
 86 --从这个视图可以看到内存是怎麽被SQL使用掉的
 87 --但是SQL进程里的第三方代码所申请的内存是不能被这个视图跟踪的。也就是说,从这个视图
 88 --可以看到所有buffer pool(或者叫single page)的使用,以及memtoleave(或者叫multi page)
 89 --里被SQL代码使用掉的内存。memtoleave里的另一部分内存(第三方代码)将不会被包含
 90 
 91 
 92 SELECT  type ,
 93         SUM(virtual_memory_reserved_kb) AS [vm reserved] ,
 94         SUM(virtual_memory_committed_kb) AS [vm commited] ,
 95         SUM(awe_allocated_kb) AS [awe allocated] ,
 96         SUM(shared_memory_reserved_kb) AS [sm reserved] ,
 97         SUM(shared_memory_committed_kb) AS [sm committed] ,
 98         SUM(single_pages_kb) AS [singlepage allocator],
 99         SUM(multi_pages_kb) AS [multi page allocated]
100 FROM    sys.dm_os_memory_clerks
101 GROUP BY type
102 ORDER BY type
103 
104 --字段含义如下:
105 --type:memory clerk的名称,可以根据clerk的名字大致知道内存的用途
106 
107 -- SUM(virtual_memory_reserved_kb):内存clerk reserve的虚拟内存量。这是使用此clerk
108 --的组件直接保留的内存量。在多数情况下,只有使用buffer pool的memory clerk才会有这种机制
109 
110 --  SUM(virtual_memory_committed_kb) :相对于reserve内存,commit的虚拟内存量。
111 --这是clerk提交的内存量。提交的内存量应始终小于保留的内存量。这部分内存主要存储数据页
112 
113 -- SUM(awe_allocated_kb):内存clerk使用地址窗口扩展插件AWE分配的内存量。在SQL中,只有缓冲池
114 --clerk memoryclerk_sqlbufferpool使用此机制,而且仅在启用AWE后使用
115 
116 -- SUM(shared_memory_reserved_kb):内存clerk保留的共享内存量。保留以供共享内存和文件映射
117 --使用的内存量
118 
119 -- SUM(shared_memory_committed_kb) :内存clerk提交的内存量。这两个字段可以跟踪shared memory
120 --的大小。一般shared memory的值都很小
121 
122 --SUM(multi_pages_kb):分配的多页内存量(KB),此内存在缓冲池外面分配,也就是我们传统说的
123 --SQL自己的代码使用的memtoleave的大小
124 
125 
126 --SUM(single_pages_kb):通过stolen分配的单页内存量(KB)。也就是说,是buffer pool里的stolen memory的大小
127 
128 
129 --reserved/Commit:SUM(virtual_memory_reserved_kb)+SUM(virtual_memory_committed_kb)
130 
131 --stolen:SUM(single_pages_kb)+SUM(multi_pages_kb)
132 
133 --buffer pool(single page):SUM(shared_memory_committed_kb)+SUM(single_pages_kb)
134 
135 --memtoleave(multi page):SUM(multi_pages_kb)
136 
137 --通过type字段从而知道各个memory clerk申请内存的数量从而知道什么样的行为用掉了内存
138 
139 
140 
141 --type:MEMORYCLERK_SQLBUFFERPOOL:正常来讲这个clerk会是最大的,他的值基本就是buffer pool里
142 --database page的大小。很有意思的是这个clerk的multi page常不为0,意味着他会用一些multi-page内存
143 --所以说SQL内存管理是非常复杂的,内存分析的时候要抓住最大的内存使用者,对于一些小量的内存使用
144 --可以不用花太大注意力
145 
146 --cachestore开头:对象缓存
147 
148 --type:cachestore_objcp: 触发器、存储过程、函数的执行计划缓存
149 
150 
151 --type:cachestore_sqlcp:动态TSQL语句adhoc query、预编译TSQLprepared语句的执行计划缓存
152 --sqlcp:sys.dm_exec_cached_plans cp的缩写!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
153 
154 --type:cachestore_phdr:视图缓存、用户自定义函数,帮助SQL更快生成执行计划
155 
156 --type:cachestore_xproc:扩展存储过程缓存,例如:sp_executesql,sp_cursor*,sp_trace*等等
157 
158 --type:cachestore_temptables:临时表对象,例如local temp table,global temp table,table variable等等
159 
160 --type:cachestore_clrporc:sqlcrl过程缓存
161 
162 --type:cachestore_events:存储service broker的事件和消息
163 
164 --type:cachestore_cursors:存储所有的游标,包括local tsql cursors,global tsql cursor,api cursors
165 
166 --USERSTORE开头:信息缓存 用户信息 数据库对象信息 运行上下文
167 
168 --type:USERSTORE_TOKENPERM:保存所有用户的安全上下文以及各种安全相关的令牌token,当SQL要运行一句
169 --tsql语句的时候,他需要检查当前的用户是否能够访问这句话所涉及的所有对象,表格,视图等。有了这些
170 --令牌缓存,反复执行同一句话的时候,部分检查工作就不需要反复进行
171 
172 
173 --type:USERSTORE_SXC:暂时存放正在执行中的语句的参数。例如,客户调用存储过程,或者用sp_executesql
174 --调用动态TSQL语句时,需要带入过程参数。如果参数过长,这部分内存的使用量就会比较大
175 
176 
177 --memoryclerk开头  SQLSERVER功能组件consumer
178 --memoryclerk_sqlgeneral
179 --memoryclerk_sqlstoreng
180 --memoryclerk_sqlutilities
181 --memoryclerk_sqlclr
182 --memoryclerk_sqlmgr
183 
184 --正常情况下,这些功能组件内存不会使用很多。在32位系统下,内存主要还是会用在数据页面缓存上
185 
186 --在SQL2005之前,我们很难看到SQL到底缓存了哪些对象,例如,数据页面缓存,缓存了哪个数据库,
187 --哪个表,对于执行计划,是什么语句,随着DMV的功能开放,现在可以基本看到了
188 
189 
190 --内存中的数据页面由哪些表组成,各占多少
191 --sys.dm_os_buffer_descriptors,记录了SQL缓冲池中当前所有数据页的信息。根据数据库,对象,类型
192 --来确定缓冲池里数据库页的分布
193 
194 --打印出当前内存里缓存的所有页面的统计信息
195 DECLARE @name NVARCHAR(100)
196 DECLARE @cmd  NVARCHAR(1000)
197 DECLARE dbnames CURSOR FOR SELECT name FROM  sys.databases
198 OPEN dbnames
199 FETCH NEXT FROM dbnames INTO @name
200 WHILE @@FETCH_STATUS=0
201 BEGIN
202 SET @cmd='select b.database_id,db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) from '
203 +@name+'.sys.allocation_units a,'
204 +@name+'.sys.dm_os_buffer_descriptors b,'+@name+'.sys.partitions p
205 where a.allocation_unit_id=b.allocation_unit_id
206 and a.container_id=p.hobt_id
207 and b.database_id=db_id('''+@name+''')
208 group by b.database_id,p.object_id,p.index_id
209 order by b.database_id,buffer_count desc'
210 EXEC(@cmd)
211 FETCH NEXT FROM dbnames INTO @name
212 END
213 CLOSE dbnames
214 DEALLOCATE dbnames
215 GO
216 
217 --and b.database_id=db_id('''+@name+''') 如果你写成这样db_id('+@name+')
218 --那么编译成db_id(gposdb)
219 --db_id('''+@name+''') 编译成db_id('gposdb')
220 --第一个单引号引住前面哪些字符串,第二个单引号是转义
221 
222 
223 --例子中表明 对象2077305481的堆上(index_id=0)和1774629365的聚集索引上(index_id=1)分别
224 --缓存了15个页面和11个页面
225 
226 --在sys.partition里,堆的index_id=0 聚集所以的index_id=1  非聚集索引的index_id>1
227 --hobt_id:包含此分区的行的数据堆或 B 树的 ID。
228 
229 --运行上面的sql语句就知道3个问题
230 --1、一个应用经常要访问的数据到底有哪些表,有多大
231 
232 --2、如果以间隔很短的时间运行上面的sql脚本,前后返回的结果有很大的差异,说明SQL刚刚
233 --为新的数据作了paging,SQL的缓存区有压力。而在后面那次运行出现的新数据,就是刚刚paging in
234 --进来的数据
235 
236 
237 --3、一条语句第一次执行前后各运行一次上面的脚本,就知道这句话要读入多少数据到内存里
238 
239 --例如:
240 DBCC DROPCLEANBUFFERS
241 GO
242 
243 --上面的SQL脚本
244 
245 USE AdventureWorks
246 GO
247 SELECT * FROM Person.Address
248 go
249 --上面的脚本
250 GO
251 
252 
253 
254 
255 --执行计划
256 --SQL会为下面的对象缓存执行计划
257 -- proc             存储过程
258 --prepared          预定义语句
259 --adhoc             动态查询
260 --replproc          复杂筛选过程
261 --trigger           触发器
262 --view              视图
263 --default            默认值
264 --usrtab            用户表
265 --systab             系统表
266 --check              check约束
267 --rule               规则
268 
269 
270 --查询各种对象占了多少内存
271 SELECT objtype ,SUM(size_in_bytes) AS sum_size_in_bytes,COUNT(bucketid) AS cache_counts
272 FROM sys.dm_exec_cached_plans
273 GROUP BY objtype
274 
275 
276 
277 
278 --要分析具体存储了哪些对象,用下面语句,但是要把结果输出到文件里,不要输出到SSMS里,不然会引起资源争用
279 SELECT usecounts,refcounts,size_in_bytes,cacheobjtype,objtype,text
280 from sys.dm_exec_cached_plans cp
281 CROSS APPLY sys.dm_exec_sql_text(plan_handle)
282 ORDER BY objtype DESC
283 GO
284 
285 --SQL的内存按分配大小和申请方式主要分3块:数据页面(database page) \bufferpool里的stolen部分\multi-page部分

 

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