Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

Check the Available Memory of Default Pool

Discussion in 'Official Microsoft News' started by Shiyang Qiu, Mar 30, 2017.

Thread Status:
Not open for further replies.
  1. Shiyang Qiu

    Shiyang Qiu Guest

    Blog Posts:
    0
    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.

    Continue reading...
     
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software