why there are differences between sql server max memory and the actual utilization?

Question

I have just asked infrastructure to add memory to a sql server box, the total memory now is 16Gb

I have set the maximum memory in sql server to 12GB.

when I run the memory query below:

SELECT [ServerName]=@@servername,
       [Setting Name]=name, 
       value, 
       value_in_use, 
       [Value (GB)]=CONVERT(decimal(12,2),CONVERT(decimal(12,2), value_in_use)/1024.00),
       [description] 
FROM master.sys.configurations with(nolock)
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);
--Msg 260, Level 16, State 3, Line 12
--Disallowed implicit conversion from data type sql_variant to data type numeric, 
--table 'master.sys.configurations', column 'value_in_use'. 
--Use the CONVERT function to run this query.
SELECT 
    CONVERT(decimal(12,2),physical_memory_in_use_kb/1024.00/1024.00 )                     AS sql_physical_memory_in_use_GB, 
    CONVERT(decimal(12,2),large_page_allocations_kb/1024.00/1024.00)                      AS sql_large_page_allocations_GB, 
    CONVERT(decimal(12,2),locked_page_allocations_kb/1024.00/1024.00)                     AS sql_locked_page_allocations_GB,
    CONVERT(decimal(12,2),virtual_address_space_reserved_kb/1024.00/1024.00)              AS sql_VAS_reserved_GB, 
    CONVERT(decimal(12,2),virtual_address_space_committed_kb/1024.00/1024.00)             AS sql_VAS_committed_GB, 
    CONVERT(decimal(12,2),virtual_address_space_available_kb/1024.00/1024.00)             AS sql_VAS_available_GB,
    REPLACE(CONVERT(VARCHAR(50),CAST(page_fault_count  AS MONEY),1), '.00','')            AS page_fault_count,
    memory_utilization_percentage                                                         AS sql_memory_utilization_percentage, 
    CASE WHEN process_physical_memory_low = 1 THEN 'Yes' ELSE 'No' END                    AS sql_process_physical_memory_low,
    CASE WHEN process_virtual_memory_low = 1  THEN 'Yes' ELSE 'No' END                    AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;
SELECT CONVERT(decimal(12,2),committed_kb/1024.00/1024.00 )        AS [SQL Server Committed Memory in GB],
       CONVERT(decimal(12,2),committed_target_kb/1024.00/1024.00 ) AS [SQL Server Target Committed Memory in GB],
       CONVERT(decimal(12,2),physical_memory_kb/1024.00/1024.00 ) AS [Server Physical Memory in GB],
       sql_memory_model_desc
FROM sys.dm_os_sys_info;

I get this:

Question:

Why sql server memory is not 12GB?

score 0 · Accepted Answer · answered Jan 18 '24 at 12:33

You modified a parameter that allows sql to reach that amount of ram. However, it will not use it until it needs it. After a period of work activity, you will find that the memory allocated to the buffer cache will have increased.

But if there is no work activity or data to cache, sql server will not ask the operating system for more memory.

Instead, if you change the minimum memory for sql sever, for example to 5 gb, you will find that sql server will reserve that amount for itself. However, that amount of memory will still be a waste with no activity and not enough data for caching.

why there are differences between sql server max memory and the actual utilization?

1 Answers1