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.

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

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
,a.max_pages_allocated_count 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:
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.

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:

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/

Other performance blogs:

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/

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/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

16 Responses to “Troubleshooting SQL Server Memory”

John Couch said

August 10, 2012 at 12:41 AM
I think some of your calculations are wrong. The value in the field is by pages. multiplying by 8 would give you KB and dividing by 1024 would give you MB. Or you could simply divide the value by 128 and reach the same value in MB.

select (bpool_committed/128) as bpool_committed_mb
, (bpool_commit_target/128) as bpool_target_mb,
(bpool_visible / 128) as bpool_visible_mb
from sys.dm_os_sys_info
go

Reply
- Karthick P.K said
  
  August 10, 2012 at 12:32 PM
  Look more carefully “select (bpool_committed * 8192)/ (1024*1024) as bpool_committed_mb”
  
  bpool_committed *8192 (8192 is Page size in bytes) /1024 (to get size in KB) /1024 (to get size in MB) . it is same as (bpool_committed/128)
  
  If I divide by 128 directly readers may not understand how i got 128.
  
  Thanks
  Karthick
  
  Reply
  - John Couch said
    
    August 10, 2012 at 2:41 PM
    When I execute your code I get divide by zero errors, which is what led me to changing it to the 128. When I originally looked at your code I must have been thinking it was multiplying by 8 and not 8192. You are right, but it still generates a divide by zero error on my systems. For some reason it works fine when just using /128.
    
    Reply
SQL Server fails to start with error "Failed allocate pages: FAIL_PAGE_ALLOCATION 1" During startup « MSSQLWIKI said

January 6, 2013 at 3:24 AM
[…] Troubleshooting SQL Server Memory […]

Reply
Troubleshooting SQL Server Memory - Karthick PK 's Blog - Site Home - MSDN Blogs said

January 6, 2013 at 3:40 AM
[…] version of this post is available in This […]

Reply
SQL Server -g « MSSQLWIKI said

March 5, 2013 at 7:08 AM
[…] Troubleshooting SQL Server Memory […]

Reply
SQL Server memory - Internals - Karthick PK 's Blog - Site Home - MSDN Blogs said

March 26, 2013 at 9:03 AM
[…] Troubleshooting steps for all types of SQL Server Memory errors […]

Reply
False warning “A significant part of sql server process memory has been paged out” « MSSQLWIKI said

June 13, 2013 at 6:11 AM
[…] Troubleshooting SQL Server Memory […]

Reply
SQL Server performance degraded in 32-Bit SQL Server after adding additional RAM. « MSSQLWIKI said

June 14, 2013 at 5:09 PM
[…] Troubleshooting SQL Server Memory […]

Reply
Herpes Gone said

July 25, 2013 at 1:24 PM
Yesterday, while I was at work, my sister stole my apple ipad and tested to see if it can
survive a forty foot drop, just so she can be a
youtube sensation. My apple ipad is now broken and she has 83 views.
I know this is completely off topic but I had to share it
with someone!

Reply
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said

January 11, 2014 at 12:56 PM
[…] Troubleshooting SQL Server Memory […]

Reply
kaitlyn said

June 10, 2015 at 9:27 AM
Would this work for SQL Server 2012? I know there are some changes in memory in SQL 2012 and I think some of these queries won’t work. We’ve been having alot of memory issues on our SQL Server and I’ve been trying to find the root cause but haven’t been able to pinpoint it to anything yet. I was hoping these queries would help.

Reply
Patrick said

June 17, 2015 at 11:46 PM
Is there an alternative way to find memory allocation outside the buffer pool for SQL Server 2012 now that the multi_pages_kb counter has bee removed? in regards to the below queries.

— 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

Reply
Memory troubleshooting « Simon's SQL said

December 1, 2016 at 6:50 AM
[…] Troubleshooting SQL Server Memory […]

Reply
John said

July 5, 2017 at 1:29 AM
Hi Karthik – I have memory issue and sqlservr.exe hogs the RAM and it won’t let it go. This is SQL Server 2012 Standard ED SP2 on VM. PLE is 8312 now, MAX SQL Memory is 3 GB and Physical RAM is 10 GB. I have tried to make sense of the DBCC Memorystatus but I do not exactly know what they’re saying. Any idea? Here is the result: Process/System Counts Value
—————————————- ———-
Available Physical Memory 723529728
Available Virtual Memory 8739202625536
Available Paging File 4321280
Working Set 9229266944
Percent of Committed Memory in WS 100
Page Faults 5444425
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 1
2017-06-29 21:06:49.69 spid535
Memory Manager KB
—————————————- ———-
VM Reserved 19056840
VM Committed 4281396
Locked Pages Allocated 0
Large Pages Allocated 0
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 4281400
Current Committed 4281400
Pages Allocated 2189672
Pages Reserved 0
Pages Free 1901216
Pages In Use 439112
Page Alloc Potential 3628224
NUMA Growth Phase 2
Last OOM Factor 7
Last OS Error 1455
2017-06-29 21:06:49.69 spid535
Memory node Id = 0 KB
—————————————- ———-
VM Reserved 19056776
VM Committed 4281376
Locked Pages Allocated 0
Pages Allocated 2189672
Pages Free 1901216
Target Committed 4281376
Current Committed 4281376
Foreign Committed 0
Away Committed 0
Taken Away Committed 0
2017-06-29 21:06:49.69 spid535
Memory node Id = 64 KB
—————————————- ———-
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
2017-06-29 21:06:49.69 spid535
MEMORYCLERK_SQLGENERAL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16608
2017-06-29 21:06:49.69 spid535
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
—————————————- ———-
VM Reserved 4060392
VM Committed 105732
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1943096
2017-06-29 21:06:49.69 spid535
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2017-06-29 21:06:49.69 spid535
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1376
2017-06-29 21:06:49.69 spid535
MEMORYCLERK_SQLUTILITIES (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 136
2017-06-29 21:06:49.69 spid535
MEMORYCLERK_SQLSTORENG (node 0) KB
—————————————- ———-
VM Reserved 17728
VM Committed 17728
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 22840
2017-06-29 21:06:49.69 spid535
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 10776
2017-06-29 21:06:49.69 spid535
MEMORYCLERK_SQLCLR (node 0) KB
—————————————- ———-
VM Reserved 6300672
VM Committed 7120
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 11184
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 840
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SQLHTTP (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SNI (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1712
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SNI (node 64) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SNI (Total) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1728
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_FULLTEXT (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 296
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SQLXP (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_BHF (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2032
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_XE_BUFFER (node 0) KB
—————————————- ———-
VM Reserved 8832
VM Committed 8832
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_XTP (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_HOST (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 96
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SOSNODE (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 49848
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SOSNODE (node 64) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2432
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SOSNODE (Total) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 52280
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SOSOS (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SOSMEMMANAGER (node 0) KB
—————————————- ———-
VM Reserved 33264
VM Committed 33152
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_FILETABLE (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_XE (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2720
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_SQLLOGPOOL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2136
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_LWC (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2017-06-29 21:06:49.70 spid535
MEMORYCLERK_FSCHUNKER (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48
2017-06-29 21:06:49.70 spid535
CACHESTORE_OBJCP (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4768
2017-06-29 21:06:49.70 spid535
CACHESTORE_SQLCP (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8280
2017-06-29 21:06:49.70 spid535
CACHESTORE_PHDR (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1816
2017-06-29 21:06:49.70 spid535
CACHESTORE_XPROC (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
CACHESTORE_TEMPTABLES (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2017-06-29 21:06:49.70 spid535
CACHESTORE_NOTIF (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
CACHESTORE_VIEWDEFINITIONS (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
CACHESTORE_XMLDBTYPE (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_XMLDBELEMENT (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_STACKFRAMES (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_STACKFRAMES (node 64) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_STACKFRAMES (Total) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
CACHESTORE_BROKERTBLACS (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 80
2017-06-29 21:06:49.70 spid535
CACHESTORE_BROKERKEK (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_BROKERDSH (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_BROKERRSB (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_BROKERREADONLY (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2017-06-29 21:06:49.70 spid535
CACHESTORE_BROKERTO (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
CACHESTORE_EVENTS (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
CACHESTORE_SEHOBTCOLUMNATTRIBUTE (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 968
2017-06-29 21:06:49.70 spid535
CACHESTORE_SYSTEMROWSET (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 304
2017-06-29 21:06:49.70 spid535
CACHESTORE_CONVPRI (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 80
2017-06-29 21:06:49.70 spid535
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2017-06-29 21:06:49.70 spid535
CACHESTORE_SEARCHPROPERTYLIST (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
CACHESTORE_COLUMNSTOREOBJECTPOOL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2017-06-29 21:06:49.70 spid535
CACHESTORE_XML_SELECTIVE_DG (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
USERSTORE_SCHEMAMGR (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 20552
2017-06-29 21:06:49.70 spid535
USERSTORE_DBMETADATA (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1312
2017-06-29 21:06:49.70 spid535
USERSTORE_TOKENPERM (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 43888
2017-06-29 21:06:49.70 spid535
USERSTORE_OBJPERM (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1448
2017-06-29 21:06:49.70 spid535
USERSTORE_SXC (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3992
2017-06-29 21:06:49.70 spid535
USERSTORE_SXC (node 64) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-06-29 21:06:49.70 spid535
USERSTORE_SXC (Total) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4000
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_LBSS (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 96
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_SNI_PACKET (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 12288
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_SNI_PACKET (node 64) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_SNI_PACKET (Total) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 12344
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_SERVICE_BROKER (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 496
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_LOCK_MANAGER (node 0) KB
—————————————- ———-
VM Reserved 16384
VM Committed 16384
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3536
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_LOCK_MANAGER (node 64) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_LOCK_MANAGER (Total) KB
—————————————- ———-
VM Reserved 16384
VM Committed 16384
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3560
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_XACT_CACHE (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 288
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_XACT_CACHE (node 64) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-06-29 21:06:49.70 spid535
OBJECTSTORE_XACT_CACHE (Total) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 304
2017-06-29 21:06:49.71 spid535
Buffer Pool Value
—————————————- ———-
Database 242630
Simulated 60636
Target 20480000
Dirty 16710
In IO 0
Latched 6
Page Life Expectancy 3114
2017-06-29 21:06:49.72 spid535
Procedure Cache Value
—————————————- ———-
TotalProcs 30
TotalPages 1860
InUsePages 1183
2017-06-29 21:06:49.72 spid535
Global Memory Objects Pages
—————————————- ———-
Resource 478
Locks 445
XDES 1014
DirtyPageTracking 4
SETLS 4
SubpDesc Allocators 4
SE SchemaManager 1246
SE Column Metadata Cache 1322
SE Column Metadata Cache Store 24
SQLCache 877
Replication 2
ServerGlobal 52
XP Global 2
SortTables 3
2017-06-29 21:06:49.72 spid535
Query Memory Objects (internal) Value
—————————————- ———-
Grants 0
Waiting 0
Available 387258
Current Max 387258
Future Max 387258
Physical Max 387258
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2017-06-29 21:06:49.72 spid535
Small Query Memory Objects (internal) Value
—————————————- ———-
Grants 0
Waiting 0
Available 20382
Current Max 20382
Future Max 20382
2017-06-29 21:06:49.72 spid535
Remote Query Memory Objects (internal) Value
—————————————- ———-
Grants 0
Waiting 0
Available 193629
Current Max 193629
2017-06-29 21:06:49.72 spid535
Optimization Queue (internal) Value
—————————————- ———-
Overall Memory 3562119168
Target Memory 3288080384
Last Notification 1
Timeout 6
Early Termination Factor 5
2017-06-29 21:06:49.72 spid535
Small Gateway (internal) Value
—————————————- ———-
Configured Units 16
Available Units 14
Acquires 2
Waiters 0
Threshold Factor 380000
Threshold 380000
2017-06-29 21:06:49.72 spid535
Medium Gateway (internal) Value
—————————————- ———-
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 12
Threshold 137003349
2017-06-29 21:06:49.72 spid535
Big Gateway (internal) Value
—————————————- ———-
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2017-06-29 21:06:49.72 spid535
Memory Pool Manager Pages
—————————————- ———-
Reserved Current 0
Reserved Limit 482545
2017-06-29 21:06:49.72 spid535
Memory Pool (internal) Pages
—————————————- ———-
Allocations 52630
Predicted 149444
Private Target 0
Private Limit 0
Total Target 535175
Total Limit 535175
OOM Count 0
2017-06-29 21:06:49.72 spid535
MEMORYBROKER_FOR_CACHE (internal) Pages
—————————————- ———-
Allocations 13168
Rate -745
Target Allocations 398899
Future Allocations 0
Overall 434829
Last Notification 1
2017-06-29 21:06:49.72 spid535
MEMORYBROKER_FOR_STEAL (internal) Pages
—————————————- ———-
Allocations 15646
Rate -26
Target Allocations 401377
Future Allocations 0
Overall 434829
Last Notification 1
2017-06-29 21:06:49.72 spid535
MEMORYBROKER_FOR_RESERVE (internal) Pages
—————————————- ———-
Allocations 0
Rate 0
Target Allocations 434829
Future Allocations 96814
Overall 434829
Last Notification 1
2017-06-29 21:06:49.72 spid535
MEMORYBROKER_FOR_COMMITTED (internal) Pages
—————————————- ———-
Allocations 23814
Rate 0
Target Allocations 409545
Future Allocations 0
Overall 434829
Last Notification 1
2017-06-29 21:06:49.72 spid535
MEMORYBROKER_FOR_XTP (internal) Pages
—————————————- ———-
Allocations 2
Rate 0
Target Allocations 385733
Future Allocations 0
Overall 434829
Last Notification 1
2017-06-29 21:06:49.72 spid535
Memory Broker Clerk (Buffer Pool) Pages
—————————————- ———-
Total 242630
Simulated 60636
Simulation Benefit 0
Internal Benefit 0
External Benefit 0
Value Of Memory 0
Periodic Freed 0
Internal Freed 0

Reply
Abhishek Kulkarni said

July 6, 2022 at 10:15 AM
I am getting below error while starting a Transactional Replication –

Message: 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. For more information, see the CLR integration documentation in SQL Server Books Online.
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. For more information, see the CLR integration documentation in SQL Server Books Online.
Command Text: if object_id(‘sys.sp_replcleanupccsprocs’) < 0 exec sp_replcleanupccsprocs @publication
Parameters: @publication = ziD365AX09ItemBarCodesStaging

Its on a 2008 Version ( Subscriber is 2014)
I desperately need help with this. Can you please help?

Reply

MSSQLWIKI

Karthick P.K on SQL Server

Karthick P.K

Categories

Archives

Tags

Facebook

Twitter

Do you have a question in SQL Server or stuck in SQL Server issue? Click here to join our facebook group and post your questions to SQL Server experts

Email Subscription

SQLWiki

Subscribe

feedburner email subscription

Visits

SQL Server Blogs

Author

Other SQL blogs

SQLSERVERSCRIBBLES.COM

Troubleshooting SQL Server Memory

16 Responses to “Troubleshooting SQL Server Memory”

John Couch said

Karthick P.K said

John Couch said

SQL Server fails to start with error "Failed allocate pages: FAIL_PAGE_ALLOCATION 1" During startup « MSSQLWIKI said