Memory Related Queries

----------------------------------------------------------------------

-- Memory Related Queries
---------------------------------------------------------------------
/*
dbcc memorystatus
Memory manager = VM Committed + AWE Allocated = Total Physical Memory for the instance
Single Page
Check CACHE_STORE_OBJCP to check how much memory SP,Funtions,Triggers,Views are consuming
Check CACHE_STORE_SQLCP to check how much memory adhoc queries are consuming
Check CACHE_STORE_XPROC to check how much memory xtended SPs are consuming
Check Buffer Pool
Committed - Total Physical Memory Committed
Target - Required
If Target > Committed Memory Pressure
If Target = Committed OK
If Target < Committed Memeory will Shrink
Free - Free Memory Available ( Not Commited /Reserved )
Stolen -- This is memory stolen by SQL Intenal components from Buffer Pool
Check Procedure Cache for
TotalProcs,TotalPages,Inuse pages
*/
dbcc memorystatus
------------------------------------------------------------------
-- Investigate Buffer pool and Procedure Cache Memory allocation
declare @sqlobject varchar(80)
exec @sqlobject = dbo.lib_fn_sqlobject
select object_name,counter_name,instance_name,cntr_value,
(cntr_value*8)/1024 as size_MB
from sys.dm_os_performance_counters a
where
(a.object_name = @sqlobject+'Buffer Manager' and counter_name = 'Database pages')
or (a.object_name = @sqlobject+'Plan Cache' and counter_name = 'cache pages')
---------------------------------------------------------------------

-- Total amount of memory consumed (including AWE) by the buffer pool:
select sum(multi_pages_kb + virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb)/1024.00/1000.00 as [used by bpool, gb]
from sys.dm_os_memory_clerks
where type = 'MEMORYCLERK_SQLBUFFERPOOL';
-----------------------------------------------------------------------------

/*
Internal memory pressure occurs because internal
components have stolen pages from buffer pool,
Identify internal components that are stealing
the most pages from buffer pool
Note: single_pages are allocated from Buffer Pool
multi_pages are allocated from outside buffer pool
*/
select type, sum(single_pages_kb)/1024.00 as stolen_mem_mb
from sys.dm_os_memory_clerks
group by type
order by stolen_mem_mb desc;
---------------------------------------------------------------------

/*
Internal components that have
allocated memory outside of buffer pool by using
multipage allocator
*/
select type, sum(multi_pages_kb)/1024.00 as multi_page_allocated_mb
from sys.dm_os_memory_clerks
where multi_pages_kb != 0
group by type
order by multi_page_allocated_mb desc;
---------------------------------------------------------------------
/*

Total : single_page_allocator + multi_page_allocator
for internal components
*/
select type, sum(single_pages_kb + multi_pages_kb)/1024.00 as total_mem_mb
from sys.dm_os_memory_clerks
group by type
order by total_mem_mb desc;

OR
--see the amount of memory allocated in the multipage units.

select name, type, (single_pages_kb/1024.00) single_mb,
(multi_pages_kb/1024.00) multi_mb,
(single_pages_in_use_kb/1024.00) single_use_mb,
(multi_pages_in_use_kb/1024.00) multi_use_mb
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP';

---------------------------------------------------------------------