SQL Server內(nèi)存故障排除

SQLServer內(nèi)存故障排除

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

SQL Server內(nèi)存故障排除的第一步，是識(shí)別是否低內(nèi)存條件出現(xiàn)在了MemToLeave或BPool或者因?yàn)橥獠績(jī)?nèi)存壓力。

注意：如果你不知道什么是BPOOL或者MemToLeave。在排除SQLServer內(nèi)存故障之前，請(qǐng)閱讀SQLServer內(nèi)存架構(gòu)基礎(chǔ)。

如果你通過這篇博文不能找到原因，或者如果你想說明在SQL Server錯(cuò)誤日志里dbcc memorystatus打印輸出的錯(cuò)誤，在這篇博文的評(píng)論部分或者在facebook用戶組貼出來(lái)。我們將嘗試協(xié)助你。

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%.

如果你看到以上錯(cuò)誤，請(qǐng)?zhí)D(zhuǎn)到SQLServer進(jìn)程內(nèi)存的重要部分被頁(yè)交換出。

第1部分（MTL錯(cuò)誤）：

如果問題是使用MTL，我們需要要確定SQL Server或者一些Non-SQL組件使用了大多數(shù)MemToLeave內(nèi)存（記得MTL是什么嗎？請(qǐng)參閱SQLServer內(nèi)存架構(gòu)）。

SQL Server 2000：在DBCCmemorystatus輸出中，OS Reserved和OS Committed計(jì)數(shù)器將會(huì)告訴你SQL Server自己在MTL中使用了多少頁(yè)。

注意：每頁(yè)是8192字節(jié)，因此，MultipyOS Committed * 8192字節(jié)/1024 得到MB。

SQL Server 2005/2008：在SQL Server錯(cuò)誤日志中OOM錯(cuò)誤之后，立即打印的DBCCmemorystatus輸出中，捕獲所有節(jié)點(diǎn)（內(nèi)存節(jié)點(diǎn)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內(nèi)存的大部分，查看DBCC MEMORYSTATUS輸出的MultiPage Allocator值，確定哪個(gè)memory clerk消耗內(nèi)存的大多數(shù)。

Sys.dm_os_memory_clerks輸出也將表明哪個(gè)memoryclerk正消耗MTL內(nèi)存的大多數(shù)。使用以下查詢。使用sys.dm_os_memory_objects將會(huì)深入了解。

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擁有的內(nèi)存非常少，確定是否有COM對(duì)象、SQL Mail或第三方擴(kuò)展存儲(chǔ)過程正被使用，如果可能將它們從進(jìn)程中移出。

COM對(duì)象：

COM對(duì)象可以在每個(gè)sp_OACreate調(diào)用中利用可選的第三個(gè)參數(shù)（[context]）從進(jìn)程中移出。如果傳遞給sp_OACreate的第三個(gè)參數(shù)的int值為4，SQL將嘗試在它自己的dllhost.exe進(jìn)程的外部實(shí)例化該對(duì)象。更多關(guān)于[context]參數(shù)可以在聯(lián)機(jī)幫助“sp_OACreate”專題里找到。

警告：大多數(shù)COM對(duì)象在進(jìn)程外運(yùn)行良好，而某些會(huì)失敗。我們應(yīng)該使用context=4運(yùn)行一些功能性測(cè)試，確保它們的對(duì)象可以成功在進(jìn)程外運(yùn)行。

鏈接服務(wù)器OLEDB提供者：

鏈接服務(wù)器OLEDB提供者可以通過設(shè)置“AllowInProcess”OLEDB提供者選項(xiàng)將該提供者設(shè)置為0來(lái)移出進(jìn)程。對(duì)于每個(gè)SQL實(shí)例提供者選項(xiàng)存儲(chǔ)在注冊(cè)表的以下位置：

Default Instance:HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers
Named Instance: HKLM\SOFTWARE\Microsoft\Microsoft SQL
Server\<instance>\Providers

如果對(duì)于相關(guān)第三方提供者的AllowInProcess注冊(cè)表值不存在，以REG_DWORD值創(chuàng)建它并賦值為0.有些OLEDB提供者不能被成功移出進(jìn)程，但是大多數(shù)可以。

擴(kuò)展存儲(chǔ)過程：

擴(kuò)展存儲(chǔ)過程總是在進(jìn)程內(nèi)運(yùn)行；沒有直接的方法移出進(jìn)程。然而，在某些情況下可以將擴(kuò)展存儲(chǔ)過程放在一個(gè)獨(dú)立的SQL實(shí)例，并使用服務(wù)器對(duì)服務(wù)器的RPC調(diào)用來(lái)在遠(yuǎn)程實(shí)例執(zhí)行它們。該技術(shù)在KB 243428中有詳細(xì)描述。

第2部分（BPOOL錯(cuò)誤）：

如果問題是使用BPOOL，在SQL Server錯(cuò)誤日志的OOM錯(cuò)誤后立即打印的DBCC memorystatus輸出，捕獲所有節(jié)點(diǎn)（內(nèi)存節(jié)點(diǎn)Id=0,1..n）的singlePageAllocator總和。這將會(huì)告訴你在MTL中每個(gè)memory clerk使用了多少KB。

Sys.dm_os_memory_clerks輸出也表明哪個(gè)memoryclerk正消耗大多數(shù)BPOOL（single_pages_kb）內(nèi)存。使用以下查詢，你可以通過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內(nèi)存狀態(tài)的完整圖片，并可以sys.dm_os_memory_objects細(xì)化。

注意：single_pages_kb是Bpool，multi_pages_kb是MTL

可以幫助SQL Server內(nèi)存故障排除的其他視圖：

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內(nèi)存故障排除的查詢：

--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內(nèi)存博文：

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/