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.
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
Comments
Anonymous
March 02, 2014
I liked the article KarthickAnonymous
April 02, 2015
Excellent Article and very useful.