SQL Server 2005性能排错（2）

2024-08-31 00:50:08

字体：大中小

来源：转载

供稿：网友

　　内存压力

　　内存压力表示当可用内存数量受到限制。识别sql server何时运行在内存压力下将帮助你排除内存相关的问题。sql server依赖于不同类型的内存压力特征也不一样。下表汇总了内存压力类型，和他们潜在的原因。在所有的情况下，你可以更多的会见到超时或显示的内存不足错误消息。

　　表 2

　　压力

　　外部

　　内部

　　物理

　　物理内存(ram)运行值低。这导致系统整理当前运行的工具集，导致整体性能下降。

　　sql server监测到这种条件，依赖于配置，可以减少缓存池的目的提交并开始清理内部缓存。

　　sql server检测内部较高的内存消耗，导致在不同内部组件间的内存重新分配。

　　内部内存压力可以导致：

　　·　　　　导致外部内存压力（sql server设置地的内存使用能力）。

　　·　　　　改变内存设置（例如‘max server memory’）。

　　·　　　　改变内部组件的内存分布（导致预留的高百分比并从缓存池中获取页）。

　　虚拟的

　　在系统页面文件运行在较低值。这样可以导致系统分配内存失败。不能扩展当前的内存分配。这可以导致着整个系统响应很慢或者可能导致系统关机。

　　在vas运行值低，导致分页（很多vas可用，但是被分为小块）与/或消耗（直接分配，dll加载到sql vas，大量的线程）。

　　sql server检测到这种条件并可以释放vas中保留的区域，减少缓存池提交的目标并开始收缩缓存。

　　windows有通知的机制　如果物理内存运行在过高或过低的情况下。sql server在他的内存管理决策中使用这种机制。

　　一般排错的步骤显示在表3中。

　　表 3

　　压力

　　内部

　　外部

　　物理

　　·　　　　找到主要的系统内存消耗组件。

　　·　　　　尝试消除消耗(如果可能)。

　　·　　　　检查适当的系统ram和考虑添加额外ram（通常需要更仔细研究）

　　·　　　　识别sql server内主要的内存消耗

　　·　　　　确认系统配置。

　　·　　　　进一步操作依赖于研究；检查负载；可能出现的设计问题；其他的资源瓶颈。

　　虚拟

　　·　　　　增加交换文件大小。

　　·　　　　检查主要物理内存的使用和外部物理内存压力调用步骤。

　　·　　　　外部物理内存压力调用步骤。

工具

　　下列工具和资源可以用于排错。

　　◆内存相关的dmvs

　　◆dbcc memorystatus 命令

　　◆性能计数器: 性能监视器或sql server指定对象的dmv

　　◆任务管理器

　　◆事件查看器: 应用程序日志和系统日志

　　检测内存压力

　　内存压力自身不会预示问题。内存压力是需要的，但时不是为服务器以后遇到内存错误的充分条件。在内存压力下工作将被任务是服务器的正常操作。然而内存压力的征兆可以于是服务器运行已经接近设计容量并且潜在存在内存不足的错误。在正常运行情况下，这些信息将作为基线决定以后内存不足的原因。

　　外部物理内存压力

　　打开人物管理器的性能视图，检查physical memory节的available项的值。如果可用内存总数很低，这表现了有外部内存压力。这个准确值依赖于很多因素，然而你可以在当这个值降低到50-100mb开始查找问题。当这个总数小于10mb时，外部内存压力将表现得很明显。

　　相同信息也可以使用在系统监视器中的memory: available bytes计数器获取。

　　如果存在外部内存压力并且你看到了内存相关的错误，你需要确认在系统中主要的内存消耗者。为了这个，考虑process: working set性能计数器或在任务管理器中process栏中的mem usage列，找到最大的内存消耗者。

　　系统中所有使用的物理内存可以通过汇总下列计数器获取。

　　◆process 对象，每个进程的 working set计数器

　　◆memory 对象

　　◆系统的cache bytes计数器

　　◆未分页池的pool nonpaged bytes 计数器

　　◆available bytes (等于任务管理其中的available 值)

　　如果没有外部压力，process: private bytes计数器或在任务管理器中虚拟内存将接近工作集的大小（process: working set或任务管理器中的mem usage），意味着我们没有内存用于分页了。

　　注意任务管理器中的mem usage列和相应的性能计数器不能计算通过awe分配的内存。这样如果使用awe，信息将是不完整的。这种情况下，你需要考虑在sql server内分配的内存来获取完整的信息。

　　你可以使用sys.dm_os_memory_clerks dmv找到sql server通过awe机制分配了多少内存，如下所示。

select
sum(awe_allocated_kb) / 1024 as [awe allocated, mb]
from
sys.dm_os_memory_clerks

　　注意在sql server中，只有当前缓存池（’memoryclerk_sqlbufferpool’类型）使用这种机制并且只能是在使用awe功能时。通过识别和除去主要物理内存使用者（如果有可能）和/或通过添加更多的内存的方法解除外部内存压力一般可以解决与此相关的内存问题。

　　外部虚拟内存压力

　　你需要确定是否页面文件为当前内存的分配能提供足够的空间。为了检查，可以打开任务管理器中的性能视图，并检查commit charge节。如果total接近于limit则说明可以被提交的最大数量内存没有扩展页面的空间。注意任务管理器中的commit charge total指出潜在使用的页面文件，而不是实际使用值。实际使用的页面文件将增加物理内存压力。

　　同样可以通过下列技术起获取相关信息：memory: commit limit, paging file: %usage, paging file: %usage peak。

　　你可以通过每个进程的process: working set减去process private bytes计数器来评估内存总数。

　　如果paging file: %usage peak（或peak commit charge）很高，检查系统日志中是否有指出页面文件增长或通知“running low on virtual memory”的信息。你可能需要增加你的页面文件大小。high paging file: %usage指出物理内存超过要提交的值并也要考虑外部物理内存压力（大量的内存需求，没有足够的ram）。

　　内部物理内存压力

　　内部内存压力来自于sql server自身，应首先通过检查在缓存分布中的异常来考虑在sql server内存分布。通常在sql server中缓存会占用最多提交的内存。为了确定在缓存池中的内存总数，我们可以使用dbcc memroystatus命令。在buffer counts节，可以找到target值。下列输出显示了在服务器达到正常负载时dbcc memorystatus的结果。

buffer counts　　　　　　　　　buffers
------------------------------ --------------------
committed　　　　　　　　　　　201120
target　　　　　　　　　　　　 201120
hashed　　　　　　　　　　　　 166517
reserved potential　　　　　　 143388
stolen potential　　　　　　　 173556
external reservation　　　　　 0
min free　　　　　　　　　　　 256
visible　　　　　　　　　　　　201120
available paging file　　　　　460640

　　target是sql server计算出它在不导致分页时可以提交的8-kb每页的页数。

　　target是被定期的重新计算的来反映内存的低或高。在常规服务负载下target页面过低可能预示出现了外部内存压力。

　　如果sql server占用了大量的内存(通过process: private bytes或任务管理器中mem usage 列显示)，请查看是否target的数值。注意，如果启用awe，你还要从sys.dm_of_memory_clerks或dbcc memorystatus输出计算awe分配的总量。

　　考虑上面的示例（没有启用awe），target*8kb=1.53gb，而服务器的process: private bytes大约是1.62gb或缓存池用sql server占用了94%的内存。注意，如果服务器没有过载，target是应该超过process: private bytes性能计数器报告的数量。

　　如果target过低，但是服务器的process: private bytes或任务管理器中mem usage 值很高，我们也许要面对从缓存池外使用内存的组件带来的内部内存压力。被加载到sql server进程中的组件，例如com对象，连接服务器，扩展存储过程，sqlclr或其他会从缓存池外占用内存。如果不使用sql server内存接口，将没有方法跟踪组件在缓存池外占用的内存。

　　适用于sql server内存管理机制的组件使用在缓存池中分配很少的内存。如果分配的大于8kb，这些组将将通过多页分配器借口使用缓存池外的内存。

　　下列方法可以快速检查通过多业分配器接口占用的内存数量。

-- amount of mem allocated though multipage allocator interface
select sum(multi_pages_kb) from sys.dm_os_memory_clerks

　　你可以这样获得通过多页分配器分发内存的详细信息：

select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type
type　　　　　　　　　　　　　　　　　　　
------------------------------------------ ---------
memoryclerk_sqlstoreng　　　　　　　　　　 56
memoryclerk_sqloptimizer　　　　　　　　　 48
memoryclerk_sqlgeneral　　　　　　　　　　 2176
memoryclerk_sqlbufferpool　　　　　　　　　536
memoryclerk_sosnode　　　　　　　　　　　　16288
cachestore_stackframes　　　　　　　　　　 16
memoryclerk_sqlservicebroker　　　　　　　 192
memoryclerk_sni　　　　　　　　　　　　　　32

　　如果通过多页分配器分发了过大的内存( 100-200mb或更多)，应该做进一步的研究。

　　如果你看到了通过多页分配器分发的大量内存，检查服务器的配置并尝试使用之前或后续的查询确定哪个组件占用的最多的内存。

　　如果target值低，但是在百分比上它占用了最多的内存，请在前面部分中查找描述外部内存压力的部分(external physical memory pressure)，或查看服务器内存配置参数。

　　如果你设置了max server memory 和/或min server memory，你应该用这些值和target值进行比较。max server memory选项限制了在缓存池中占用内存的最大值，而服务器还可以占用其他的部分。min server memory选项告诉服务器当小于该值时不能释放缓存池的内存。如果target小于min server memory设置并且服务器没有过载，这可能预示服务器遇到了外部内存压力并且不能获得这个设置大小的内存。它也可能预示着从内部组件的内存压力，就像上面描述的那样。target 数值不能超过max server memory选项的设置。

　　首先，检查从dbcc memorystatus输出中stolen页面数量

buffer distribution　　　　　　buffers
------------------------------ -----------
stolen　　　　　　　　　　　　 32871
free　　　　　　　　　　　　　 17845
cached　　　　　　　　　　　　 1513
database (clean)　　　　　　　 148864
database (dirty)　　　　　　　 259
i/o　　　　　　　　　　　　　　0
latched　　　　　　　　　　　　0

　　相对于stolen和target页面的高百分比(>75-80%)预示着内部内存压力。

　　更多关于服务器组件内存分配的信息可以使用sys.dm_of_memory_clerks dmv获取。

-- amount of memory consumed by components outside the buffer pool
-- note that we exclude single_pages_kb as they come from bpool
-- bpool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as [overall used w/o bpool, kb]
from
sys.dm_os_memory_clerks
where
type <> 'memoryclerk_sqlbufferpool'
-- amount of memory consumed by bpool
-- note that currenlty only bpool uses awe
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [used by bpool with awe, kb]
from
sys.dm_os_memory_clerks
where
type = 'memoryclerk_sqlbufferpool'

　　每个组件的详细信息可以通过下列语句获取（这包括从缓存池内和缓存池外分配的内存）。

declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topfive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as
it represents the largest consumer which is normal
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (case when type <> 'memoryclerk_sqlbufferpool'
then virtual_memory_committed_kb
else 0 end)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'total allocated (including from buffer pool): '
+ cast(@total_alloc as varchar(10)) + ' kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case　when　(
(sum(single_pages_kb
+ multi_pages_kb
+ (case when type <> 'memoryclerk_sqlbufferpool'
then virtual_memory_committed_kb
else 0 end)
+ shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05
then type
else 'other'
end as topfive
,(sum(single_pages_kb
+ multi_pages_kb
+ (case when type <> 'memoryclerk_sqlbufferpool'
then virtual_memory_committed_kb
else 0 end)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb + (case when type <>
'memoryclerk_sqlbufferpool' then virtual_memory_committed_kb else 0 end)
+ shared_memory_committed_kb)) desc
select　* from @tab

　　注意之前的查询将buffer pool与通过单页分配器提供给组件的内存视为不同的部分。通过下列查询可以确定在缓存池中耗费内存最多的10个组件（通过单页分配器）。

-- top 10 consumers of memory from bpool
select
top 10 type,
sum(single_pages_kb) as [spa mem, kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc

　　你通常不会控制内部组件对内存的占用。然而，确定哪个组件占用最多的内存可以帮助对问题的后续研究。

　　系统监视器(perfmon)

　　你也可以通过检查下列计数器来确定内存压力（sql server联机丛书中有详细地描述）：

　　sql server: buffer manager 对象

　　◆low buffer cache hit ratio

　　◆low page life expectancy

　　◆high number of checkpoint pages/sec

　　◆high number lazy writes/sec

　　不足的内存和i/o开销经常会导致瓶颈。请查看本文的i/o 瓶颈部分。

　　高速缓存和内存压力

　　查看外部和内部内存压力的方法是查看内存中高速缓存的行为。

　　sql server 2005与sql server 2000的高速缓存设计上有一些细微的不同，其中之一就是统一了高速缓存的框架。为了从高速缓存中删除最近很少使用的项，该框架实现了一套时钟算法。现在它使用2支不同的时钟指针，一个是内部时钟指针，一个是外部时钟指针。

　　内部时钟指针控制与其他高速缓存相关的缓存大小。当框架预测到高速缓存要使用到尽头是它开始移动。

　　当sql server总体上陷入内存压力时，外部时钟指针开始移动。外部时钟指针的移动可以导致外部和内部的内存压力。在内部和外部内存压力时不会混乱的移动外部时钟和内部时钟。

　　关于时钟移动的信息可以通过sys.dm_os_memory_cache_clock_hands dmv显示，如下代码所示。每个高速缓存项在内部和外部时钟指针都有不同的行。如果你看到rounds count和removed all rounds count增加，说明服务器遇到内部/外部内存压力。

select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0

　　通过如下所示，通过通过sys.dm_os_cache_counters dmv你可以获得更多关于缓存的信息。

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on (cc.cache_address = ch.cache_address)
/*
--uncomment this block to have the information only for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

　　注意userstore项，正在使用的页面数量将不会被报告，因为结果将是null。

　　ring buffers

　　更多有意义的内存调试信息可以通过sys.dm_os_ring_buffers的ring buffers dmv获取。每个ring buffer保留了之前几次某种类型的通知。指定ring buffer的详细信息将在下面描述。

　　ring_buffer_resource_monitor

　　你可以使用从资源监视器的通知识别内存改变的状态。在内部，sql server有一个监视不同内存压力的架构。当内存状态改变，资源监视器任务生成一个通知。这个通知用于内部组件根据内存状态调整它们内存使用并通过sys.dm_os_ring_buffers dmv来暴露，如下列代码所示。

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'ring_buffer_resource_monitor'

　　结果类似于：

<record id="1701" type="ring_buffer_resource_monitor" time="149740267">
<resourcemonitor>
<notification>resource_memphysical_low</notification>
<indicators>2</indicators>
<nodeid>0</nodeid>
</resourcemonitor>
<memorynode id="0">
<reservedmemory>1646380</reservedmemory>
<committedmemory>432388</committedmemory>
<sharedmemory>0</sharedmemory>
<awememory>0</awememory>
<singlepagesmemory>26592</singlepagesmemory>
<multiplepagesmemory>17128</multiplepagesmemory>
<cachedmemory>17624</cachedmemory>
</memorynode>
<memoryrecord>
<memoryutilization>50</memoryutilization>
<totalphysicalmemory>3833132</totalphysicalmemory>
<availablephysicalmemory>3240228</availablephysicalmemory>
<totalpagefile>5732340</totalpagefile>
<availablepagefile>5057100</availablepagefile>
<totalvirtualaddressspace>2097024</totalvirtualaddressspace>
<availablevirtualaddressspace>336760</availablevirtualaddressspace>
<availableextendedvirtualaddressspace>0</availableextendedvirtualaddressspace>
</memoryrecord>
</record>

　　从这些记录来看，你可以减少服务器收到的低物理内存的通知。你也可以查看内存总量（kb为单位）。你可以通过使用sql server的xml能力来查询这些信息，例如下列代码。

select
x.value('(//notification)[1]', 'varchar(max)') as [type],
x.value('(//record/@time)[1]', 'bigint') as [time stamp],
x.value('(//availablephysicalmemory)[1]', 'int') as [avail phys mem, kb],
x.value('(//availablevirtualaddressspace)[1]', 'int') as [avail vas, kb]
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
[time stamp] desc

　　上面收到了低内存的通知，缓存池重新计算target。注意target数量保持在指定的min server memory 和max server memory选项限制中。如果缓存池中心的提交比当前的提交缓存还小，缓存池将开始收缩直到外部内存压力被移除。注意，当运行在启用awe时sql server 2000不是这样来缓解物理内存压力的。

　　ring_buffer_oom

　　下列代码示例，展示了ring buffer中包含预示服务器内存不足的记录。

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'ring_buffer_oom'

　　结果类似于：

<record id="7301" type="ring_buffer_oom" time="345640123">
<oom>
<action>fail_virtual_commit</action>
<resources>4096</resources>
</oom>

　　这条记录告诉我们那个操作失败了（提交，保留或页面分配）并表明了请求的内存数量。

　　ring_buffer_memory_broker and internal memory pressure

　　当监测到内部内存压力时，为组件在缓存池分配内存的低内存通知将被打开。打开低内存通知允许从使用缓存池的高速缓存和其他组件中回收页面。

　　内部内存压力可以通过调整max server memory选项或当stolen页面与缓存池的比例超过80%时触发。

　　内部内存压力通知(‘shrink’)能通过使用下列代码查询ring buffer的调用来发现。

select
x.value('(//record/@time)[1]', 'bigint') as [time stamp],
x.value('(//notification)[1]', 'varchar(100)') as [last notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'ring_buffer_memory_broker') as r(x)
order by
[time stamp] desc

　　ring_buffer_buffer_pool

　　ring buffer将包含预示严重的缓存池失败的记录，包括缓存池溢出的条件。

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'ring_buffer_buffer_pool'

　　结果类似于：

<record id="1234" type="ring_buffer_buffer_pool" time="345640123">
< bufferpoolfailure id="fail_oom">
<committedcount>84344 </committedcount>
<committedtarget>84350 </committedtarget >
<freecount>20</freecount>
<hashedcount>20345</hashedcount>
<stolencount>64001 </stolencount>
<reservedcount>64001 </reservedcount>
</ bufferpoolfailure >

　　这条记录告诉我们出现了什么样的失败 (fail_oom, fail_map,fail_reserve_adjust,fail_lazywriter_no_buffers) 基当时的缓存池状态。

　　内部虚拟内存压力

　　vas的占用可以使用sys.dm_os_virtual_address_dump dmv来跟踪。vas汇总可以使用下列视图来查询。

-- virtual address space summary view
-- generates a list of sql server regions
-- showing number of reserved and free regions of a given size
create view vasummary as
select
size = vadump.size,
reserved =　sum(case(convert(int, vadump.base)^0) when 0 then 0 else 1 end),
free = sum(case(convert(int, vadump.base)^0) when 0 then 1 else 0 end)
from
(
--- combine all allocation according with allocation base, don't take into
--- account allocations with zero allocation_base
select
convert(varbinary, sum(region_size_in_bytes)) as size,
region_allocation_base_address as base
from sys.dm_os_virtual_address_dump
where region_allocation_base_address <> 0x0
group by region_allocation_base_address
union　
--- we shouldn't be grouping allocations with zero allocation base
--- just get them as is
select convert(varbinary, region_size_in_bytes), region_allocation_base_address
from sys.dm_os_virtual_address_dump
where region_allocation_base_address　= 0x0
)
as vadump
group by size

　　下列查询可以用于访问vas状态：

-- available memory in all free regions
select sum(size*free)/1024 as [total avail mem, kb]
from vasummary
where free <> 0
-- get size of largest availble region
select cast(max(size) as int)/1024 as [max free size, kb]
from vasummary
where free <> 0

　　如果最大可用区域小于4mb，我们可能遇到了vas压力。sql server 2005监视和响应vas压力。sql server 2000不会监视从vas带来的压力，但是当出现虚拟内存不足错误是，它会清理高速缓存。

　　一般的内存错误排错步骤

　　下列是一些常规步骤，有助于你排除内存错误。

　　1.验证是否服务器运行在外部内存压力。如果出现外部内存压力，尝试先解决它，然后再看是否依然存在这个问题或错误。

　　2.开始收集性能计数器：sql server: buffer manager, sql server: memory manager

　　3.确认内存配置参数(sp_configure)， min memory per query，min/max server memory，awe enabled和　lock pages in memory 权利。观察不正常的值。纠正配置。提供为sql server 2005增加内存的理由。

　　4.检查所有可能影响服务器的非默认sp_configure参数。

　　5.检查内部内存压力。

　　6.当你见到内存错误消息时，观察dbcc memorystatus输出和改变的方法。

　　7.检查负载（并发会话数量，并发执行查询数量）。

　　内存错误

　　701 - there is insufficient system memory to run this query.