Troubleshooting SQL Server Memory

Article
06/15/2012

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.

MemToLeave errors:

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.

 --Bpool stats

 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

 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

--

 -- 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,

 system_memory_state_desc

 from sys.dm_os_sys_memory

go

 -- Get me memory information about SQLSERVR.EXE process

 -- GetMemoryProcessInfo() API used for this

 -- physical_memory_in_use_kb

 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

 select * from sys.dm_os_ring_buffers

 where ring_buffer_type like 'RING_BUFFER_RESOURCE%'

go

 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

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

 -- 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)

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

 -- Get me the total amount of memory consumed by multi_page consumers

--

 select SUM(multi_pages_kb)/1024 as total_multi_pages_mb

 from sys.dm_os_memory_clerks

go

 -- What about multi_page 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

 -- Is anyone using AWE allocator?

--

 select SUM(awe_allocated_kb)/1024 as total_awe_allocated_mb

 from sys.dm_os_memory_clerks

go

 -- Who is the AWE user?

--

 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

 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

go

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

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/

 Thanks

 Karthick

Troubleshooting SQL Server Memory

Additional resources