导航：首页 > 数据库 >

SQL Server内存故障排除

发表于：2024-11-23 作者：千家信息网编辑

千家信息网最后更新 2024年11月23日，SQLServer内存故障排除翻译自：https://blogs.msdn.microsoft.com/karthick_pk/2012/06/15/troubleshooting-sql-serve

千家信息网最后更新 2024年11月23日SQL Server内存故障排除

SQLServer内存故障排除

翻译自：https://blogs.msdn.microsoft.com/karthick_pk/2012/06/15/troubleshooting-sql-server-memory/

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

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

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

MemToLeave errors:

SQL Server 2000WARNING: 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 bwherea.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\ProvidersNamed Instance: HKLM\SOFTWARE\Microsoft\Microsoft SQLServer\\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 bwherea.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_objectsselect * from sys.dm_os_memory_poolsselect * from sys.dm_os_memory_nodesselect * fromsys.dm_os_memory_cache_entriesselect * 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_mbfrom sys.dm_os_sys_infogo -- Get me physical RAM installed and sizeof user VASselect physical_memory_in_bytes/(1024*1024)as phys_mem_mb,virtual_memory_in_bytes/(1024*1024) asuser_virtual_address_space_sizefrom sys.dm_os_sys_infogo --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_descfrom sys.dm_os_sys_memorygo -- Memory utilized by SQLSERVR processGetMemoryProcessInfo() API used for thisselect 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_pressurefrom sys.dm_os_process_memorygo --Reosurce monitor ringbufferselect * from sys.dm_os_ring_bufferswhere 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_mbfrom sys.dm_os_memory_nodeswhere memory_node_id != 64go --Vas summarywith 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 basefrom sys.dm_os_virtual_address_dumpwhere region_allocation_base_address<> 0x0group by region_allocation_base_addressUNION(select CONVERT(varbinary,region_size_in_bytes),region_allocation_base_addressfrom sys.dm_os_virtual_address_dumpwhere region_allocation_base_address = 0x0))as vadumpgroup by size)select * from vasummarygo -- Clerks that are consuming memoryselect * from sys.dm_os_memory_clerkswhere (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_pagesfrom sys.dm_os_memory_clerksgo -- Breakdown clerks with stolen pagesselect type, name,sum((single_pages_kb*1024)/8192) as stolen_pagesfrom sys.dm_os_memory_clerkswhere single_pages_kb > 0group by type, nameorder by stolen_pages descgo -- Non-Bpool allocation from SQL Serverclerks select SUM(multi_pages_kb)/1024 astotal_multi_pages_mbfrom sys.dm_os_memory_clerksgo-- Who are Non-Bpool consumers--select type, name, sum(multi_pages_kb)/1024as multi_pages_mbfrom sys.dm_os_memory_clerkswhere multi_pages_kb > 0group by type, nameorder by multi_pages_mb descgo -- Let's now get the total consumption ofvirtual allocator--selectSUM(virtual_memory_committed_kb)/1024 as total_virtual_mem_mbfrom sys.dm_os_memory_clerksgo -- Breakdown the clerks who use virtualallocatorselect type, name,sum(virtual_memory_committed_kb)/1024 as virtual_mem_mbfrom sys.dm_os_memory_clerkswhere virtual_memory_committed_kb > 0group by type, nameorder by virtual_mem_mb descgo -- memory allocated by AWE allocator API'Sselect SUM(awe_allocated_kb)/1024 astotal_awe_allocated_mbfrom sys.dm_os_memory_clerksgo -- Who clerks consumes memory using AWE select type, name,sum(awe_allocated_kb)/1024 as awe_allocated_mbfrom sys.dm_os_memory_clerkswhere awe_allocated_kb > 0group by type, nameorder by awe_allocated_mb descgo -- What is the total memory used by theclerks?select (sum(multi_pages_kb)+SUM(virtual_memory_committed_kb)+SUM(awe_allocated_kb))/1024from sys.dm_os_memory_clerksgo---- 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_mbfrom sys.dm_os_memory_nodeswhere memory_node_id != 64go---- 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_mbfrom sys.dm_os_memory_nodesgo---- Who are the biggest cache stores?select name, type,(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024as cache_size_mbfrom sys.dm_os_memory_cache_counterswhere type like 'CACHESTORE%'group by name, typeorder by cache_size_mb descgo---- Who are the biggest user stores?select name, type,(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024as cache_size_mbfrom sys.dm_os_memory_cache_counterswhere type like 'USERSTORE%'group by name, typeorder by cache_size_mb descgo---- Who are the biggest object stores?select name, type,(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024as cache_size_mbfrom sys.dm_os_memory_clerkswhere type like 'OBJECTSTORE%'group by name, typeorder by cache_size_mb descgo --Which object is really consuming fromclerkselect * from sys.dm_os_memory_clerks a,sys.dm_os_memory_objects bwhere a.page_allocator_address =b.page_allocator_address--group by a.type, b.typeorder by a.type, b.typego --To get the list of 3rd party DLL loadedinside SQL server memoryselect * from sys.dm_os_loaded_moduleswhere company <> 'Microsoft Corporation'go --Which database page is in my memoryselect db_name(database_id),(cast(count(*)as bigint)*8192)/1024/1024 as "size in mb" fromsys.dm_os_buffer_descriptorsgroup by db_name(database_id)

其他SQL Server内存博文：

https://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/

https://mssqlwiki.com/2012/05/18/sql-server-performance-degraded-in-32-bit-sql-server-after-i-adding-additional-ram/

https://mssqlwiki.com/2012/06/27/a-significant-part-of-sql-server-process-memory-has-been-paged-out/

其他性能博文：

https://mssqlwiki.com/sqlwiki/sql-performance/io-bottlenecks/

https://mssqlwiki.com/sqlwiki/sql-server-agent/sql-agent-maxworkerthreads-and-agent-subsystem/

https://mssqlwiki.com/sqlwiki/sql-performance/async_network_io-or-network_io/

很赞哦！