Check the Available Memory of Default Pool

  • Article

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.

[sql]
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]

[/sql]

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