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 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内存博文：

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/