SQL Server 环形缓冲区(Ring Buffer) -- RING_BUFFER_RESOURCE_MONITOR 诊断SQL Server内存压力

  1. 云栖社区>
  2. 博客>
  3. 正文

SQL Server 环形缓冲区(Ring Buffer) -- RING_BUFFER_RESOURCE_MONITOR 诊断SQL Server内存压力

技术小甜 2017-11-08 13:27:00 浏览983
展开阅读全文

由于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)是简单的系统告警记录用户内部诊断。从这个动态管理视图可以看到大量不同的告警。在系统中运行如下查询:

 

1
SELECT FROM sys.dm_os_ring_buffers AS dorb;

 

结果如下:

clip_image001

 

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

 

如果我们看一个类型为RING_BUFFER_RESOURCE_MONITOR消息的XML列示例,如下:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<Record id = "1" type ="RING_BUFFER_RESOURCE_MONITOR" time ="129031647">
<ResourceMonitor>
<Notification>RESOURCE_MEMPHYSICAL_LOW</Notification>
<IndicatorsProcess>2</IndicatorsProcess>
<IndicatorsSystem>0</IndicatorsSystem>
<NodeId>0</NodeId>
<Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>
<Effect type="APPLY_HIGHPM" state="EFFECT_IGNORE" reversed="0">128991020</Effect>
<Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>
</ResourceMonitor>
<MemoryNode id="0">
<TargetMemory>153576</TargetMemory>
<ReservedMemory>9022400</ReservedMemory>
<CommittedMemory>201728</CommittedMemory>
<SharedMemory>0</SharedMemory>
<AWEMemory>0</AWEMemory>
<PagesMemory>152320</PagesMemory>
</MemoryNode>
<MemoryRecord>
<MemoryUtilization>100</MemoryUtilization>
<TotalPhysicalMemory>4193848</TotalPhysicalMemory>
<AvailablePhysicalMemory>2175836</AvailablePhysicalMemory>
<TotalPageFile>8385844</TotalPageFile>
<AvailablePageFile>6064244</AvailablePageFile>
<TotalVirtualAddressSpace>8589934464</TotalVirtualAddressSpace>
<AvailableVirtualAddressSpace>8580567180</AvailableVirtualAddressSpace>
<AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace>
</MemoryRecord>
</Record>

 

在MemoryNode和MemoryRecord节点的信息对于查明问题非常有用,但是关键数据是在上面的ResourceMonitor节点的Notification、IndicatorsProcess和IndicatorSystem元素的值。首先,这个一个从环形缓冲区捕获到的RESOURCE_MEMPHYSICAL_LOW的消息。意味着物理内存很少。接着的两个值说明了什么少。如果IndicatorsProcess返回0,且IndicatorsSystem返回一个值,那么这个问题是系统级的。但是,在我们的示例中,IndicatorsProcess有值而IndicatorsSystem返回0。意味着某个单一进程正在运行在低内存下,而不是整个系统。相应的值表示的含义如下:

 


取值

含义

1

高物理内存

2

低物理内存

4

低虚拟内存


 

当你看到这些,你可以知道在系统中内存压力的程度。判断是否是进程级别还是系统级别的问题是有帮助的。即使是进程级别的,你仍然看到在系统中低内存被进程占用。那可能意味着内存太小,也可能意味着进程需要重写。

 

注意:可以得到进程有内存压力的线索,但是并不知道是哪个进程。可以用扩展事件(Extended Events)捕获该事件,然后通过查询获取信息,但不能直接从这些信息得到特定的系统进程。

 

如何查询?

 

从环形缓冲捕获的告警分析内存压力是非常好的,但是你想通过脚本捕获这些信息。你不想去从大量的数据中查找特定的事件。我们可以用Xquery从该视图获取信息:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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可以获得实际日期和时间。如果你只是对内存压力感兴趣,先停下来。如果你对上面问题的诊断有兴趣,你将获取剩下的内存信息,像这样:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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


以下为常用脚本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
--常用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 <90... MEANS WEre starting to page out
select from #tbl_ringBuffer where MemUtil < 90
go
drop table #tbl_ringBuffer
























本文转自UltraSQL51CTO博客,原文链接:http://blog.51cto.com/ultrasql/1583934 ,如需转载请自行联系原作者


网友评论

登录后评论
0/500
评论
技术小甜
+ 关注