SQL Server数据库内存故障排除
小标 2018-07-18 来源 : 阅读 1158 评论 0

摘要:本文主要向大家介绍了SQL Server数据库内存故障排除,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。

本文主要向大家介绍了SQL Server数据库内存故障排除,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。

SQL Server内存故障排除的第一步,是识别是否低内存条件出现在了MemToLeave或BPool或者因为外部内存压力。

 

注意:如果你不知道什么是BPOOL或者MemToLeave。在排除SQLServer内存故障之前,请阅读SQLServer内存架构基础。

 

如果你通过这篇博文不能找到原因,或者如果你想说明在SQL Server错误日志里dbcc memorystatus打印输出的错误,在这篇博文的评论部分或者在facebook用户组贴出来。我们将尝试协助你。

 

MemToLeave errors:

SQL Server 2000

WARNING: Failed to reserve contiguousmemory of Size= 65536.

WARNING: Clearing procedure cache to freecontiguous memory.

Error: 17802 “Could not create server eventthread.”

SQL Server could not spawn process_loginreadthread.


SQL Server 2005/2008

                Failed Virtual Allocate Bytes:FAIL_VIRTUAL_RESERVE 122880

  

                Failed to initialize the CommonLanguage Runtime (CLR) v2.0.50727 due to memory pressure. This is probably dueto memory pressure in the MemToLeave region of memory

   

 

Buffer Pool errors:

                BPool::Map: no remappableaddress found.

  

                BufferPool out of memorycondition

  

                LazyWriter: warning, no freebuffers found.

   

 

Either BPool (or) MemToLeave errors:

         Error: 17803 “Insufficient memory available..”

Buffer Distribution:  Stolen=7901 Free=0 Procedures=1 Inram=201842Dirty=0 Kept=572…

  

                Error: 701, Severity: 17,State: 123.

There is insufficient system memory to runthis query.

There is insufficient system memory inresource pool ‘default’ to run this query

   

 

Working set trim and page out errors(external memory pressure)  

A significant part of SQL Server processmemory has been paged out. This may result in performance degradation.

  

A significant part of sql server processmemory has been paged out. This may result in a performance degradation.Duration: 0 seconds. Working set (KB): 2007640, committed (KB): 4594040, memoryutilization: 43%.

   

 

如果你看到以上错误,请跳转到SQLServer进程内存的重要部分被页交换出。

 

 

第1部分(MTL错误):

 

如果问题是使用MTL,我们需要要确定SQL Server或者一些Non-SQL组件使用了大多数MemToLeave内存(记得MTL是什么吗?请参阅SQLServer内存架构)。

 

SQL Server 2000:在DBCCmemorystatus输出中,OS Reserved和OS Committed计数器将会告诉你SQL Server自己在MTL中使用了多少页。

注意:每页是8192字节,因此,MultipyOS Committed * 8192字节/1024 得到MB。

 

SQL Server 2005/2008:在SQL Server错误日志中OOM错误之后,立即打印的DBCCmemorystatus输出中,捕获所有节点(内存节点Id = 0,1..n)的MultiPage Allocator总和。这将告诉你SQL Server自己在MTL使用了多少KB。

 

你也可以从sys.dm_os_memory_clerks求multi_pages_kb的总和:

select sum(multi_pages_kb)  fromsys.dm_os_memory_clerks

   

 

如果SQL Server自身使用了MemToLeave内存的大部分,查看DBCC MEMORYSTATUS输出的MultiPage Allocator值,确定哪个memory clerk消耗内存的大多数。

 

Sys.dm_os_memory_clerks输出也将表明哪个memoryclerk正消耗MTL内存的大多数。使用以下查询。使用sys.dm_os_memory_objects将会深入了解。

select *  from sys.dm_os_memory_clerksorder by  multi_pages_kb  desc

  

select b.type,a.type,* fromsys.dm_os_memory_objects a,sys.dm_os_memory_clerks b

wherea.page_allocator_address=b.page_allocator_address order by  b.multi_pages_kb desc

,a.max_pages_allocated_count desc

   

 

如果SQL Server拥有的内存非常少,确定是否有COM对象、SQL Mail或第三方扩展存储过程正被使用,如果可能将它们从进程中移出。

 

COM对象:

COM对象可以在每个sp_OACreate调用中利用可选的第三个参数([context])从进程中移出。如果传递给sp_OACreate的第三个参数的int值为4,SQL将尝试在它自己的dllhost.exe进程的外部实例化该对象。更多关于[context]参数可以在联机帮助“sp_OACreate”专题里找到。

警告:大多数COM对象在进程外运行良好,而某些会失败。我们应该使用context=4运行一些功能性测试,确保它们的对象可以成功在进程外运行。

 

链接服务器OLEDB提供者:

链接服务器OLEDB提供者可以通过设置“AllowInProcess”OLEDB提供者选项将该提供者设置为0来移出进程。对于每个SQL实例提供者选项存储在注册表的以下位置:

Default Instance:HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers

Named Instance: HKLM\SOFTWARE\Microsoft\Microsoft SQL

Server\<instance>\Providers

   

如果对于相关第三方提供者的AllowInProcess注册表值不存在,以REG_DWORD值创建它并赋值为0.有些OLEDB提供者不能被成功移出进程,但是大多数可以。

 

扩展存储过程:

扩展存储过程总是在进程内运行;没有直接的方法移出进程。然而,在某些情况下可以将扩展存储过程放在一个独立的SQL实例,并使用服务器对服务器的RPC调用来在远程实例执行它们。该技术在KB 243428中有详细描述。

 

第2部分(BPOOL错误):

 

如果问题是使用BPOOL,在SQL Server错误日志的OOM错误后立即打印的DBCC memorystatus输出,捕获所有节点(内存节点Id=0,1..n)的singlePageAllocator总和。这将会告诉你在MTL中每个memory clerk使用了多少KB。

 

Sys.dm_os_memory_clerks输出也表明哪个memoryclerk正消耗大多数BPOOL(single_pages_kb)内存。使用以下查询,你可以通过sys.dm_os_memory_objects深入了解:

select *  from sys.dm_os_memory_clerksorder by  Single_pages_kb  desc

  

select b.type,a.type,* fromsys.dm_os_memory_objects a,sys.dm_os_memory_clerks b

wherea.page_allocator_address=b.page_allocator_address order by  b.single_pages_kb desc

   

 

sys.dm_os_memory_clerks可以提供SQLServer内存状态的完整图片,并可以sys.dm_os_memory_objects细化。

注意:single_pages_kb是Bpool,multi_pages_kb是MTL

 

可以帮助SQL Server内存故障排除的其他视图:

select * from sys.dm_os_memory_objects

select * from sys.dm_os_memory_pools

select * from sys.dm_os_memory_nodes

select * fromsys.dm_os_memory_cache_entries

select * fromsys.dm_os_memory_cache_hash_tables

   

 

一些用于SQL Server内存故障排除的查询:

--Bpool statistics

  

select

(cast(bpool_committed as bigint) * 8192)/(1024*1024)  as bpool_committed_mb,

(cast(bpool_commit_target as bigint) *8192) / (1024*1024) as bpool_target_mb,

(cast(bpool_visible as bigint)* 8192) /(1024*1024) as bpool_visible_mb

from sys.dm_os_sys_info

go

  

-- Get me physical RAM installed and sizeof user VAS

select physical_memory_in_bytes/(1024*1024)as phys_mem_mb,

virtual_memory_in_bytes/(1024*1024) asuser_virtual_address_space_size

from sys.dm_os_sys_info

go

  

--System memory information

  

select total_physical_memory_kb/(1024) asphys_mem_mb,

available_physical_memory_kb/(1024) asavail_phys_mem_mb,

system_cache_kb/(1024) as sys_cache_mb,

(kernel_paged_pool_kb+kernel_nonpaged_pool_kb)/(1024)as kernel_pool_mb,

total_page_file_kb/(1024) as total_virtual_memory_mb,

available_page_file_kb/(1024) asavailable_virtual_memory_mb,

system_memory_state_desc

from sys.dm_os_sys_memory

go

  

-- Memory utilized by SQLSERVR processGetMemoryProcessInfo() API used for this

select physical_memory_in_use_kb/(1024) assql_physmem_inuse_mb,

locked_page_allocations_kb/(1024) asawe_memory_mb,

total_virtual_address_space_kb/(1024) asmax_vas_mb,

virtual_address_space_committed_kb/(1024)as sql_committed_mb,

memory_utilization_percentage asworking_set_percentage,

virtual_address_space_available_kb/(1024)as vas_available_mb,

process_physical_memory_low asis_there_external_pressure,

process_virtual_memory_low asis_there_vas_pressure

from sys.dm_os_process_memory

go

  

--Reosurce monitor ringbuffer

select * from sys.dm_os_ring_buffers

where ring_buffer_type like'RING_BUFFER_RESOURCE%'

go

  

--Memory in each node

  

select memory_node_id as node,virtual_address_space_reserved_kb/(1024) as VAS_reserved_mb,

virtual_address_space_committed_kb/(1024)as virtual_committed_mb,

locked_page_allocations_kb/(1024) aslocked_pages_mb,

single_pages_kb/(1024) as single_pages_mb,

multi_pages_kb/(1024) as multi_pages_mb,

shared_memory_committed_kb/(1024) asshared_memory_mb

from sys.dm_os_memory_nodes

where memory_node_id != 64

go

  

--Vas summary

with vasummary(Size,reserved,free) as (select size = vadump.size,

reserved = SUM(case(convert(int,vadump.base) ^ 0)  when 0 then 0 else 1end),

free = SUM(case(convert(int, vadump.base) ^0x0) when 0 then 1 else 0 end)

from

(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(

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)

select * from vasummary

go

  

-- Clerks that are consuming memory

select * from sys.dm_os_memory_clerks

where (single_pages_kb > 0) or(multi_pages_kb > 0)

or (virtual_memory_committed_kb > 0)

go

  

-- Get me stolen pages

--

select (SUM(single_pages_kb)*1024)/8192 astotal_stolen_pages

from sys.dm_os_memory_clerks

go

  

-- Breakdown clerks with stolen pages

select type, name,sum((single_pages_kb*1024)/8192) as stolen_pages

from sys.dm_os_memory_clerks

where single_pages_kb > 0

group by type, name

order by stolen_pages desc

go

  

-- Non-Bpool allocation from SQL Serverclerks

  

select SUM(multi_pages_kb)/1024 astotal_multi_pages_mb

from sys.dm_os_memory_clerks

go

-- Who are Non-Bpool consumers

--

select type, name, sum(multi_pages_kb)/1024as multi_pages_mb

from sys.dm_os_memory_clerks

where multi_pages_kb > 0

group by type, name

order by multi_pages_mb desc

go

  

-- Let's now get the total consumption ofvirtual allocator

--

selectSUM(virtual_memory_committed_kb)/1024 as total_virtual_mem_mb

from sys.dm_os_memory_clerks

go

  

-- Breakdown the clerks who use virtualallocator

select type, name,sum(virtual_memory_committed_kb)/1024 as virtual_mem_mb

from sys.dm_os_memory_clerks

where virtual_memory_committed_kb > 0

group by type, name

order by virtual_mem_mb desc

go

  

-- memory allocated by AWE allocator API'S

select SUM(awe_allocated_kb)/1024 astotal_awe_allocated_mb

from sys.dm_os_memory_clerks

go

  

-- Who clerks consumes memory using AWE

  

select type, name,sum(awe_allocated_kb)/1024 as awe_allocated_mb

from sys.dm_os_memory_clerks

where awe_allocated_kb > 0

group by type, name

order by awe_allocated_mb desc

go

  

-- What is the total memory used by theclerks?

select (sum(multi_pages_kb)+

SUM(virtual_memory_committed_kb)+

SUM(awe_allocated_kb))/1024

from sys.dm_os_memory_clerks

go

--

-- Does this sync up with what the nodethinks?

--

selectSUM(virtual_address_space_committed_kb)/1024 as total_node_virtual_memory_mb,

SUM(locked_page_allocations_kb)/1024 astotal_awe_memory_mb,

SUM(single_pages_kb)/1024 astotal_single_pages_mb,

SUM(multi_pages_kb)/1024 astotal_multi_pages_mb

from sys.dm_os_memory_nodes

where memory_node_id != 64

go

--

-- Total memory used by SQL Server throughSQLOS memory nodes

-- including DAC node

-- What takes up the rest of the space?

select(SUM(virtual_address_space_committed_kb)+

SUM(locked_page_allocations_kb)+

SUM(multi_pages_kb))/1024 astotal_sql_memusage_mb

from sys.dm_os_memory_nodes

go

--

-- Who are the biggest cache stores?

select name, type,(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024

as cache_size_mb

from sys.dm_os_memory_cache_counters

where type like 'CACHESTORE%'

group by name, type

order by cache_size_mb desc

go

--

-- Who are the biggest user stores?

select name, type,(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024

as cache_size_mb

from sys.dm_os_memory_cache_counters

where type like 'USERSTORE%'

group by name, type

order by cache_size_mb desc

go

--

-- Who are the biggest object stores?

select name, type,(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024

as cache_size_mb

from sys.dm_os_memory_clerks

where type like 'OBJECTSTORE%'

group by name, type

order by cache_size_mb desc

go

  

--Which object is really consuming fromclerk

select * from sys.dm_os_memory_clerks a

,sys.dm_os_memory_objects b

where a.page_allocator_address =b.page_allocator_address

--group by a.type, b.type

order by a.type, b.type

go

  

--To get the list of 3rd party DLL loadedinside SQL server memory

select * from sys.dm_os_loaded_moduleswhere company <> 'Microsoft Corporation'

go

  

--Which database page is in my memory

select db_name(database_id),(cast(count(*)as bigint)*8192)/1024/1024 as "size in mb" fromsys.dm_os_buffer_descriptors

group by db_name(database_id)

本文由职坐标整理并发布,希望对同学们学习SQL Server有所帮助,更多内容请关注职坐标数据库SQL Server数据库频道!


本文由 @小标 发布于职坐标。未经许可,禁止转载。
喜欢 | 0 不喜欢 | 0
看完这篇文章有何感觉?已经有0人表态,0%的人喜欢 快给朋友分享吧~
评论(0)
后参与评论

您输入的评论内容中包含违禁敏感词

我知道了

助您圆梦职场 匹配合适岗位
验证码手机号,获得海同独家IT培训资料
选择就业方向:
人工智能物联网
大数据开发/分析
人工智能Python
Java全栈开发
WEB前端+H5

请输入正确的手机号码

请输入正确的验证码

获取验证码

您今天的短信下发次数太多了,明天再试试吧!

提交

我们会在第一时间安排职业规划师联系您!

您也可以联系我们的职业规划师咨询:

小职老师的微信号:z_zhizuobiao
小职老师的微信号:z_zhizuobiao

版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
 沪公网安备 31011502005948号    

©2015 www.zhizuobiao.com All Rights Reserved

208小时内训课程