MSSQLWIKI

Karthick P.K on SQL Server

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

 

15 Responses to “Troubleshooting SQL Server Memory”

  1. John Couch said

    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

    • 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

      • John Couch said

        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.

  2. […] Troubleshooting SQL Server Memory […]

  3. […] version of this post is available in This […]

  4. […] Troubleshooting SQL Server Memory […]

  5. […] Troubleshooting steps for all types of SQL Server Memory errors […]

  6. […] Troubleshooting SQL Server Memory […]

  7. […] Troubleshooting SQL Server Memory […]

  8. 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!

  9. […] Troubleshooting SQL Server Memory […]

  10. kaitlyn said

    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.

  11. Patrick said

    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

  12. […] Troubleshooting SQL Server Memory […]

  13. John said

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: