设为首页 收藏本站
查看: 709|回复: 0

[经验分享] SQL Server 环形缓冲区(Ring Buffer) -- RING_BUFFER_RESOURCE_MONITOR 诊断SQL Server内存压力

[复制链接]

尚未签到

发表于 2018-10-12 11:03:31 | 显示全部楼层 |阅读模式
SQL Server 环形缓冲区(Ring Buffer) -- RING_BUFFER_RESOURCE_MONITOR 诊断SQL Server 内存压力
  由于SQL Server与Windows操作系统交互的方式,使得内存和内存管理相当复杂。如果你只是查看在标准SQL Server配置下操作系统分配的内存,你可能看到所有,或者大多数被SQL Server使用。在SQL Server中如果你查看资源计数器像总服务器内存,没有提供太多信息来帮助判断是否有内存的压力。你需要能快速和容易判断是否有内存问题。利用sys.dm_os_ring_buffers能让你快速而准确的决定是否有内存问题,这些问题是来自Windows操作系统还是SQL Server内部。
内存分配
  在我们查询sys.dm_os_ring_buffers之前,让我们准确的知道在SQL Server和Windows之间有多少内存被管理。严格的考虑SQL Server的上下文,内存和内存问题可能是内部问题,是SQL Server和它的内存管理的一部分;或者是操作系统和它的内存管理的一部分。但你反过来看,从Windows操作系统的物理内存来看,内存被Windows管理,或着说虚拟地址空间(VAS)内存被分配给SQL Server,但它可能是或者不是实际的物理内存。在VAS中,SQL Server分配需要的内存不会返还给操作系统。然而,操作系统仍然管理内存,将它移动到物理内存或者通过分页文件写入磁盘。
  当开始设置SQL Server时,你可能设置了最大内存限制。当SQL Server工作时,它将从操作系统请求内存直到最大值。将很少返还内存给操作系统,这就是为什么当SQL Server运行时,如此频繁的看到内存使用过量。看起来有问题,其实不是。然而,的确在操作系统的内存管理中增加了负载,因为越来越少的内存能被操作系统使用。尤其是当你不仅仅是在系统上运行了SQL Server而已,会更严重。
  由于内存分裂,有两个地方将会在不同进程间耗尽内存。在操作系统内你需要更多的内存用于进程,例如,你会看到物理内存或外部内存压力。在VAS中你也需要更多的内存用于SQL Server,当你看到虚拟内存或内部内存压力。
是否有内存压力?
  问题来了,你如何判断是否有内存压力,内存压力来自哪里。让我们看看sys.dm_os_ring_buffers去了解有哪些有价值的信息来回答这个问题。
  这个动态管理视图在BOL中是未公开的。它可以被微软修改无需提醒。
  环形缓冲区(Ring Buffer)是简单的系统告警记录用户内部诊断。从这个动态管理视图可以看到大量不同的告警。在系统中运行如下查询:
SELECT * FROM sys.dm_os_ring_buffers AS dorb;  结果如下:
DSC0000.jpg

  只有四列,但最后一列是XML类型,可以得到丰富的信息。虽然有大量不同的环形缓冲信息,我们只对名为RING_BUFFER_RESOURCE_MONITOR类型感兴趣。这些记录记录着内存分配的改变,这是一个看到什么时候内存耗尽的好方法,因为一条内存很少的消息相当好的说明了你看到了内存压力。
  如果我们看一个类型为RING_BUFFER_RESOURCE_MONITOR消息的XML列示例,如下:
  

  
RESOURCE_MEMPHYSICAL_LOW
  
2
  
0
  
0
  
0
  
128991020
  
0
  

  

  
153576
  
9022400
  
201728
  
0
  
0
  
152320
  

  

  
100
  
4193848
  
2175836
  
8385844
  
6064244
  
8589934464
  
8580567180
  
0
  

  

  在MemoryNode和MemoryRecord节点的信息对于查明问题非常有用,但是关键数据是在上面的ResourceMonitor节点的Notification、IndicatorsProcess和IndicatorSystem元素的值。首先,这个一个从环形缓冲区捕获到的RESOURCE_MEMPHYSICAL_LOW的消息。意味着物理内存很少。接着的两个值说明了什么少。如果IndicatorsProcess返回0,且IndicatorsSystem返回一个值,那么这个问题是系统级的。但是,在我们的示例中,IndicatorsProcess有值而IndicatorsSystem返回0。意味着某个单一进程正在运行在低内存下,而不是整个系统。相应的值表示的含义如下:
  取值
  含义
  1
  高物理内存
  2
  低物理内存
  4
  低虚拟内存
  当你看到这些,你可以知道在系统中内存压力的程度。判断是否是进程级别还是系统级别的问题是有帮助的。即使是进程级别的,你仍然看到在系统中低内存被进程占用。那可能意味着内存太小,也可能意味着进程需要重写。
  注意:可以得到进程有内存压力的线索,但是并不知道是哪个进程。可以用扩展事件(Extended Events)捕获该事件,然后通过查询获取信息,但不能直接从这些信息得到特定的系统进程。
如何查询?
  从环形缓冲捕获的告警分析内存压力是非常好的,但是你想通过脚本捕获这些信息。你不想去从大量的数据中查找特定的事件。我们可以用Xquery从该视图获取信息:
WITH RingBuffer  
AS (SELECT CAST(dorb.record AS XML) AS xRecord,
  
dorb.timestamp
  
FROM sys.dm_os_ring_buffers AS dorb
  
WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
  
)
  
SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
  
xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess,
  
xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem,
  
DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) AS RmDateTime
  
FROM RingBuffer AS rb
  
CROSS APPLY rb.xRecord.nodes('Record') record (xr)
  
CROSS JOIN sys.dm_os_sys_info AS dosi
  
ORDER BY RmDateTime DESC;
  上面的查询显示了基本的可用信息,从timestamp可以获得实际日期和时间。如果你只是对内存压力感兴趣,先停下来。如果你对上面问题的诊断有兴趣,你将获取剩下的内存信息,像这样:
WITH RingBuffer  
AS (SELECT CAST(dorb.record AS XML) AS xRecord,
  
dorb.timestamp
  
FROM sys.dm_os_ring_buffers AS dorb
  
WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
  
)
  
SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
  
xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess,
  
xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem,
  
DATEADD(ms, -1 * (dosi.ms_ticks - rb.timestamp), GETDATE()) AS RmDateTime,
  
xr.value('(MemoryNode/TargetMemory)[1]','bigint') AS TargetMemory,
  
xr.value('(MemoryNode/ReserveMemory)[1]','bigint') AS ReserveMemory,
  
xr.value('(MemoryNode/CommittedMemory)[1]','bigint') AS CommitedMemory,
  
xr.value('(MemoryNode/SharedMemory)[1]','bigint') AS SharedMemory,
  
xr.value('(MemoryNode/PagesMemory)[1]','bigint') AS PagesMemory,
  
xr.value('(MemoryRecord/MemoryUtilization)[1]','bigint') AS MemoryUtilization,
  
xr.value('(MemoryRecord/TotalPhysicalMemory)[1]','bigint') AS TotalPhysicalMemory,
  
xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]','bigint') AS AvailablePhysicalMemory,
  
xr.value('(MemoryRecord/TotalPageFile)[1]','bigint') AS TotalPageFile,
  
xr.value('(MemoryRecord/AvailablePageFile)[1]','bigint') AS AvailablePageFile,
  
xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]','bigint') AS TotalVirtualAddressSpace,
  
xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]','bigint') AS AvailableVirtualAddressSpace,
  
xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]','bigint') AS AvailableExtendedVirtualAddressSpace
  
FROM RingBuffer AS rb
  
CROSS APPLY rb.xRecord.nodes('Record') record (xr)
  
CROSS JOIN sys.dm_os_sys_info AS dosi
  
ORDER BY RmDateTime DESC;
  现在这个查询包含了所有来自于XML的可用信息。有一个例外,我没有考虑AWE内存,因为现在大多数人都运行在64位系统下,该值总为0。现在你可以看到内存压力在哪里产生的。
总结
  如本文所述,将不会告诉你内存压力来自哪里,但是用sys.dm_os_ring_buffers动态管理视图查询RING_BUFFER_RESOURCE_MONITOR事件类型,将完全让你知道你是否经历着内存压力。你可以查询该视图作为监控方案的一部分,或者用resource_monitor_ring_buffer_recorded事件来捕获该事件。无论哪种,你都要处理XML输出。幸运的是这些查询能帮助你跟踪SQL Server中的内存问题。
深入阅读
  关于SQL Server如何进行内存管理:
  http://blogs.msdn.com/b/slavao/archive/2005/02/19/376714.aspx
  关于RING_BUFFER_RESOURCE_MONITOR的更多细节:
  http://blogs.msdn.com/b/psssql/archive/2009/09/17/how-it-works-what-are-the-ring-buffer-resource-monitor-telling-me.aspx
  关于关闭环形缓冲数据收集以提高性能:
  http://support.microsoft.com/kb/920093
  以下为常用脚本:
--常用SQL语句:  
/*
  
RING BUFFER...using RESOURCE MONITOR
  
   View RESOURCE_MONITOR --itmonitors Internal/external pressure
  
     will broadcast to Clerks-- to change mem
  
    want menutilization ==100..majority of commited mem is resident..is part of the workingSet
  
      if < 70---means some pages were swappedout...so degraded performance was experienced
  

  
      do... consider LOCKING PAGES IN MEMORY
  
*/
  

  
ifobject_id('tempdb..#tbl_ringBuffer')  is not null
  
drop table #tbl_ringBuffer
  
go
  

  
select
  
    x.value('(//Notification)[1]', 'varchar(max)')                          as MsgType,
  
    x.value('(//Record/@time)[1]','bigint')                                     as TheTimeStamp,
  
    x.value('(//AvailablePhysicalMemory)[1]','bigint')             as AvailPhysMemKb,
  
    x.value('(//AvailableVirtualAddressSpace)[1]','bigint')     as AvailVAsKB,
  
    x.value('(//MemoryUtilization)[1]','bigint')                            as MemUtil,
  
   x.value('(//MemoryNode/@id)[1]','bigint')                             as NodeId,
  
    x.value('(//TotalPageFile)[1]','bigint')                                        as totalPageFile
  

  
into #tbl_ringBuffer
  
from
  
    (
  
      select cast(record as xml)
  
     from sys.dm_os_ring_buffers
  
     where ring_buffer_type ='RING_BUFFER_RESOURCE_MONITOR'
  
    )
  
      as R(x)
  
order by
  
    TheTimeStamp desc
  
----vieW LL RECORD
  
select * from #tbl_ringBuffer
  
go
  
---VIEW RECORDS WITH mEN

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-620701-1-1.html 上篇帖子: SQL Server 审计功能-记录所有的操作记录 下篇帖子: SQL Server内存架构基础
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表