-- Memory pressure detection and investigation
-- ensure you have enabled advanced options
-- sp_configure
sp_configure 'show advanced options'
-- sp_configure 'show advanced options', 1
-- reconfigure

-- check memory related configuration options
sp_configure 'awe_enabled'
sp_configure 'min server memory'
sp_configure 'max server memory'
sp_configure 'min memory per query'
sp_configure 'query wait'

-- CPU, scheduler memory and buffer pool information
-- sys.dm_os_sys_info
select 
	cpu_count,
	hyperthread_ratio,
	scheduler_count,
	physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
	virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
	bpool_committed * 8 / 1024 as bpool_committed_mb,
	bpool_commit_target * 8 / 1024 as bpool_target_mb,
	bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info

-- AWE allocated memory in SQL Server
-- currently only Buffer Pool utilizes AWE mechanism and only when AWE is enabled
-- sys.dm_os_memory_clerks
select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] 
from sys.dm_os_memory_clerks

-- Compare BPool target and commit
-- use query with sys.dm_os_sys_info or DBCC MEMORYSTATUS (look at Buffer Distribution and Buffer Counts)
DBCC MEMORYSTATUS

-- BPool memory consumers, look for large memory consumption, trying to understand why
-- Total memory stolen (cached + stolen pages) from buffer pool
-- sys.dm_os_memory_clerks
select sum(single_pages_kb) as [Mem stolen from BPool, Kb]
from sys.dm_os_memory_clerks

-- you can get idea of how much is hashed + free by subtracting the above sum from committed memory by the buffer pool

-- find top 10 consumers of memory from the buffer pool
-- sys.dm_os_memory_clerks
-- you can use name column to get a better undertanding of what it is used for
select top 10 type, sum(single_pages_kb) as [Mem stolen from BPool, Kb]
from sys.dm_os_memory_clerks
group by type --with rollup 
order by 2 desc

-- drill down for more information, when memory objects use the memory clerk, but this is not exhaustive information
-- sys.dm_os_memory_objects
select mo.type, sum(pages_allocated_count * page_size_in_bytes) / 1024 as [Pages allocated, Kb] from sys.dm_os_memory_clerks as mc join sys.dm_os_memory_objects as mo on mc.page_allocator_address = mo.page_allocator_address 
where mc.type = 'MEMORYCLERK_SQLQUERYEXEC'
group by mo.type 
order by 2 desc

-- look for overall memory consumption, 
-- find consumers with that account for more than 5% of the memory consumption thourgh SPA or MPA
-- sys.dm_os_memory_clerks
declare @total_alloc bigint 
declare @tab table (
	type nvarchar(128) collate database_default 
	,allocated bigint
	,vertual_res bigint
	,virtual_com bigint
	,awe bigint
	,shared_res bigint
	,shared_com bigint
	,topFive nvarchar(128)
	,grand_total bigint
);

select	@total_alloc = sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)
from sys.dm_os_memory_clerks 

--print 'Total allocated, except BPool: ' + CAST(@total_alloc as varchar(10)) + ' Kb'

insert into @tab
select	type
,	sum(single_pages_kb + multi_pages_kb) as allocated
,	sum(virtual_memory_reserved_kb) as vertual_res
,	sum(virtual_memory_committed_kb) as virtual_com
,	sum(awe_allocated_kb) as awe
,	sum(shared_memory_reserved_kb) as shared_res 
,	sum(shared_memory_committed_kb) as shared_com
,	case  when  ((sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05 
		  then type 
		  else 'Other' 
	end as topFive
,	(sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)) as grand_total 
from sys.dm_os_memory_clerks 
group by type
order by (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)) desc

select  * from @tab

sp_configure 'max server memory', 2048
reconfigure

-- caches and memory pressure, look for caches that spin too fast
-- sys.dm_os_memory_cache_clock_hands
select *
from sys.dm_os_memory_cache_clock_hands
where 
	rounds_count > 0
	and removed_all_rounds_count > 0


select 
	distinct cc.cache_address, 
	cc.name, 
	cc.type,
	cc.single_pages_kb + cc.multi_pages_kb as total_kb, 
	cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb as total_in_use_kb, 
	cc.entries_count, 
	cc.entries_in_use_count,
	ch.removed_all_rounds_count, 
	ch.removed_last_round_count
from 
	sys.dm_os_memory_cache_counters cc 
	join sys.dm_os_memory_cache_clock_hands ch on (cc.cache_address = ch.cache_address)
/*
--uncomment this block to have the information only for moving hands caches
where 
	ch.rounds_count > 0
	and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

-- virtual memory consumption
-- sys.dm_os_virtual_address_dump
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size 

if object_id('dbo.VASummary','V') is not null
	drop view dbo.VASummary

CREATE VIEW VASummary 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)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
	--- combine all allocation according with allocation base, don't take into
	--- account allocations with zero allocation_base
	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  
	   --- we shouldn't be grouping allocations with zero allocation base
	   --- just get them as is
	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 

-- The following queries can be used to assess VAS state
-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB] FROM VASummary WHERE Free <> 0

-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB] FROM VASummary WHERE Free <> 0
