First step to troubleshoot SQL Server memory is to identify whether the whether the low memory condition appears to be in MemToLeave, or in the BPool.
Note: If you do not know what is BPOOL or MemToLeave. Please read SQL Server Memory architecture before troubleshooting SQL Server memory.
Newer version of this post is available in This link.
SQL Server 2000 WARNING: Failed to reserve contiguous memory of Size= 65536. WARNING: Clearing procedure cache to free contiguous memory. Error: 17802 "Could not create server event thread." SQL Server could not spawn process_loginread thread.
SQL Server 2005/2008 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880
Buffer Pool errors: BPool::Map: no remappable address found.
BufferPool out of memory condition
Either BPool (or) MemToLeaveerrors: Error: 17803 “Insufficient memory available..” Buffer Distribution: Stolen=7901 Free=0 Procedures=1 Inram=201842 Dirty=0 Kept=572…
Error: 701, Severity: 17, State: 123. There is insufficient system memory to run this query.
Working set trim and page out
A significant part of SQL Server process memory has been paged out. This may result in performance degradation.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 2007640, committed (KB): 4594040, memory utilization: 43%.
Jump toA significant part of SQL Server process memory has been paged out
Section 1 (MTL error):
If the Problem is with MTL we have to determine whether it is SQL Server or some non-SQL component that is using the most MemToLeave memory (Remember what is in MTL? section in SQL Server Memory architecture ) .
SQL Server 2000: OS Reserved and OS Committed counters in the DBCC memory status output will tell us how many pages SQL Server itself is using in MTL.
Note: Each page is 8192 bytes so Multiply OS Committed * 8192 bytes /1024 to get value in MB.
SQLServer2005/2008: Capture sum of MultiPage Allocator for all nodes (Memory node Id = 0,1..n)from DBCC memorystatus output printed immediately after OOM errors in SQL Server errorlog.
This will tell us how many KB SQL Server itself is using in MTL.
You can also take the sum of multi_pages_kb from sys.dm_os_memory_clerks
select sum(multi_pages_kb) from sys.dm_os_memory_clerks
If SQL Server itself is using majority of the memory in MemToLeave look at MultiPage Allocator values in DBCC MEMORYSTATUS output to determine which memory clerk is consuming the majority of the memory.
sys.dm_os_memory_clerks output will also indicate which memory clerk is consuming majority of memory in MTL. Use the below query. You can further break down using sys.dm_os_memory_objects
select * from sys.dm_os_memory_clerks order by multi_pages_kb desc
select b.type,a.type,* from sys.dm_os_memory_objects a,sys.dm_os_memory_clerks b where a.page_allocator_address=b.page_allocator_address order by b.multi_pages_kb,a.max_pages_allocated_count
If SQL Server Owned memory is very less ,than determine if there are COM objects, SQL Mail, or 3rd party xprocs being used, and move them out of process if possible.
COM Objects: COM objects can be moved out of process by taking advantage of the optional third parameter ([context]) at each sp_OACreate call. If the int value 4 is passed as the third parameter to sp_OACreate, SQL will attempt to instantiate that object out of process in its own dllhost.exe process. More information on the [context] parameter can be found in the “sp_OACreate” topic in SQL Books Online. Warning: most COM objects will work fine when run out of process, but some will fail. We should run a few functional tests with context=4 to make sure that their objects can be successfully run out of process.
Linked Server OLEDB Providers: Linked server OLEDB providers can be moved out of process by setting the “AllowInProcess” OLEDB provider option for that provider to 0. Provider options are stored in the registry for each SQL instance at the location below:
Default Instance: HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers Named Instance: HKLM \SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\Providers
If the AllowInProcess reg value for the relevant 3rd party provider doesn’t exist, create it as a REG_DWORD value and set it to 0. Some OLEDB providers cannot be successfully run out of process, but most can.
Extended Stored Procedures: Extended stored procedures always run in-process; there is no direct way to execute them out of process. However, in some cases it is possible to host the xp’s in a separate instance of SQL and execute them in the remote instance using server-to-server RPCs. This technique is detailed in KB 243428.
sql server -g switch should be used as last option to resolve MTL errors.
Section 2 (BPOOL error):
If the Problem is with BPOOL
Capture sum of singlePageAllocator for all nodes (Memory node Id = 0,1..n)from DBCC memorystatus output printed immediately after OOM errors in SQL Server errorlog.
This will tell us how many KB each memory clerk is using in MTL.
sys.dm_os_memory_clerks output will also indicate which memory clerk is consuming majority of memory in BPOOL (single_pages_kb). Use the below query. You can further break down using sys.dm_os_memory_objects
select * from sys.dm_os_memory_clerks order by Single_pages_kb desc
select b.type,a.type,* from sys.dm_os_memory_objects a,sys.dm_os_memory_clerks b where a.page_allocator_address=b.page_allocator_address order by b.single_pages_kb
sys.dm_os_memory_clerks can provide a complete picture of SQL Server memory status and can be drilled down using sys.dm_os_memory_objects
Note: single_pages_kb is Bpool and multi_pages_kb is MTL
Other views which can help to troubleshoot SQL Server memory issues are
select * from sys.dm_os_memory_objects select * from sys.dm_os_memory_pools select * from sys.dm_os_memory_nodes select * from sys.dm_os_memory_cache_entries select * from sys.dm_os_memory_cache_hash_tables
Few queries which we use to troubleshoot SQL Server memory issues.
select (bpool_committed * 8192)/ (1024*1024) as bpool_committed_mb
, (cast(bpool_commit_target as bigint) * 8192) / (1024*1024) as bpool_target_mb,
(bpool_visible * 8192) / (1024*1024) as bpool_visible_mb
-- Get me physical RAM installed
-- and size of user VAS
select physical_memory_in_bytes/(1024*1024) as phys_mem_mb,
virtual_memory_in_bytes/(1024*1024) as user_virtual_address_space_size
-- Get me other information about system memory
select total_physical_memory_kb/(1024) as phys_mem_mb,
available_physical_memory_kb/(1024) as avail_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) as available_virtual_memory_mb,
-- Get me memory information about SQLSERVR.EXE process
-- GetMemoryProcessInfo() API used for this
select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb,
locked_page_allocations_kb/(1024) as awe_memory_mb,
total_virtual_address_space_kb/(1024) as max_vas_mb,
virtual_address_space_committed_kb/(1024) as sql_committed_mb,
memory_utilization_percentage as working_set_percentage,
virtual_address_space_available_kb/(1024) as vas_available_mb,
process_physical_memory_low as is_there_external_pressure,
process_virtual_memory_low as is_there_vas_pressure
select * from sys.dm_os_ring_buffers
where ring_buffer_type like 'RING_BUFFER_RESOURCE%'
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) as locked_pages_mb,
single_pages_kb/(1024) as single_pages_mb,
multi_pages_kb/(1024) as multi_pages_mb,
shared_memory_committed_kb/(1024) as shared_memory_mb
where memory_node_id != 64
with vasummary(Size,reserved,free) 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) ^ 0x0) when 0 then 1 else 0 end)
(select CONVERT(varbinary, sum(region_size_in_bytes)) as size,
region_allocation_base_address as base
where region_allocation_base_address <> 0x0
group by region_allocation_base_address
select CONVERT(varbinary, region_size_in_bytes),
where region_allocation_base_address = 0x0)
group by size)
select * from vasummary
-- Get me all clerks that take some memory
select * from sys.dm_os_memory_clerks
where (single_pages_kb > 0) or (multi_pages_kb > 0)
or (virtual_memory_committed_kb > 0)
-- Get me stolen pages
select (SUM(single_pages_kb)*1024)/8192 as total_stolen_pages
-- Breakdown clerks with stolen pages
select type, name, sum((single_pages_kb*1024)/8192) as stolen_pages
where single_pages_kb > 0
group by type, name
order by stolen_pages desc
-- Get me the total amount of memory consumed by multi_page consumers
select SUM(multi_pages_kb)/1024 as total_multi_pages_mb
-- What about multi_page consumers
select type, name, sum(multi_pages_kb)/1024 as multi_pages_mb
where multi_pages_kb > 0
order by multi_pages_mb desc
-- Let's now get the total consumption of virtual allocator
select SUM(virtual_memory_committed_kb)/1024 as total_virtual_mem_mb
-- Breakdown the clerks who use virtual allocator
select type, name, sum(virtual_memory_committed_kb)/1024 as virtual_mem_mb
where virtual_memory_committed_kb > 0
order by virtual_mem_mb desc
-- Is anyone using AWE allocator?
select SUM(awe_allocated_kb)/1024 as total_awe_allocated_mb
-- Who is the AWE user?
select type, name, sum(awe_allocated_kb)/1024 as awe_allocated_mb
where awe_allocated_kb > 0
order by awe_allocated_mb desc
-- What is the total memory used by the clerks?
-- Does this sync up with what the node thinks?
select SUM(virtual_address_space_committed_kb)/1024 as total_node_virtual_memory_mb,
SUM(locked_page_allocations_kb)/1024 as total_awe_memory_mb,
SUM(single_pages_kb)/1024 as total_single_pages_mb,
SUM(multi_pages_kb)/1024 as total_multi_pages_mb
-- Total memory used by SQL Server through SQLOS memory nodes
-- including DAC node
-- What takes up the rest of the space?
SUM(multi_pages_kb))/1024 as total_sql_memusage_mb
-- Who are the biggest cache stores?
select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024
where type like 'CACHESTORE%'
group by name, type
order by cache_size_mb desc
-- Who are the biggest user stores?
where type like 'USERSTORE%'
-- Who are the biggest object stores?
where type like 'OBJECTSTORE%'
select mc.type, mo.type from sys.dm_os_memory_clerks mc
join sys.dm_os_memory_objects mo
on mc.page_allocator_address = mo.page_allocator_address
group by mc.type, mo.type
order by mc.type, mo.type
Other performance blogs:
I liked the article Karthick
Excellent Article and very useful.