由于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;
|
结果如下：
只有四列，但最后一列是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://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
|
