Check the Available Memory of Default Pool


One capacity planning best practice of using SQL In-Memory OLTP engine is to bind the database with a resource governance pool. The calculation of memory resource of pools is from here, and I translate it into below TSQL code  to check both configured value and available value in run time.

declare @MinMemoryPercent  decimal(4,3)
declare @MaxAvailableMemoryKB bigint
declare @InusedMemoryKB bigint
declare @ResPoolMinMemoryCapKB bigint
declare @AvailableSharedMemoryKB bigint

select @MinMemoryPercent  = sum(min_memory_percent)/100, @InusedMemoryKB = sum(used_memory_kb)  from sys.dm_resource_governor_resource_pools

select @MaxAvailableMemoryKB = available_physical_memory_kb  from sys.dm_os_sys_memory 

set @ResPoolMinMemoryCapKB = @MinMemoryPercent * @MaxAvailableMemoryKB


if(@InusedMemoryKB>@ResPoolMinMemoryCapKB) set @AvailableSharedMemoryKB = @MaxAvailableMemoryKB - @InusedMemoryKB
else set @AvailableSharedMemoryKB = @MaxAvailableMemoryKB - @ResPoolMinMemoryCapKB

select 
@MaxAvailableMemoryKB/1024.0 as [Max_Available_Memory_MB]
,@InusedMemoryKB/1024.0 as [Inused_Memory_MB]
,@ResPoolMinMemoryCapKB/1024.0 as [Resource_Pool_Min_Memory_Cap_MB]
,@AvailableSharedMemoryKB /1024.0 as [Available_Shared_Memory_MB]

-------------------------------------------
Posted by Shiyang Qiu, 2017 Mar 30
Thanks to Frankie Lai for peer review.


Comments (0)

Skip to main content