Troubleshooting SQL Server Memory
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 or because of external memory pressure.
Note: If you do not know what is BPOOL or MemToLeave. Please read SQL Server Memory architecture before troubleshooting SQL Server memory.
If you can’t find the cause by following this blog (or) if you need clarification regarding your error paste the error you get along with dbcc memorystatus output printed in SQL Server errorlog in comments session of this blog (or) In This face book group. We will try to assist you.
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
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. This is probably due to memory pressure in the MemToLeave region of memory
Buffer Pool errors:
BPool::Map: no remappable address found.
BufferPool out of memory condition
LazyWriter: warning, no free buffers found.
Either BPool (or) MemToLeave errors:
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.
There is insufficient system memory in resource pool ‘default’ to run this query
Working set trim and page out errors (external memory pressure)
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%.
If you see above error jump to A 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 desc
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 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
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.
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 desc
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.
--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 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 from sys.dm_os_sys_info go --System memory information 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, system_memory_state_desc from sys.dm_os_sys_memory go -- Memory utilized by SQLSERVR 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 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) 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 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 1 end), 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 as total_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 Server clerks select SUM(multi_pages_kb)/1024 as total_multi_pages_mb from sys.dm_os_memory_clerks go -- Who are Non-Bpool consumers -- select type, name, sum(multi_pages_kb)/1024 as 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 of virtual allocator -- select SUM(virtual_memory_committed_kb)/1024 as total_virtual_mem_mb from sys.dm_os_memory_clerks go -- Breakdown the clerks who use virtual allocator 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 as total_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 the clerks? 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 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 from sys.dm_os_memory_nodes where memory_node_id != 64 go -- -- Total memory used by SQL Server through SQLOS 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 as total_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 from clerk 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 loaded inside SQL server memory select * from sys.dm_os_loaded_modules where 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" from sys.dm_os_buffer_descriptors group by db_name(database_id)
Other SQL Server memory blogs:
Other performance blogs:
If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/