主页 / dba / 问题 / 334977
Accepted
Marcello Miorelli
Asked: 2024-01-18 18:24:45 +0800 CST

为什么sql server max内存和实际利用率有差异？

  • 772

我刚刚要求基础设施向 sql server 盒子添加内存，现在总内存为 16Gb

我已将sql server中的最大内存设置为12GB。

当我运行下面的内存查询时：

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;

我明白了：

![在此输入图像描述

问题：

为什么sql server内存不是12GB？

sql-server

1 个回答

  1. Best Answer

    您修改了一个参数，允许 sql 达到该内存量。但是，直到需要时它才会使用它。经过一段时间的工作活动后，你会发现分配给buffer cache的内存会有所增加。

    但如果没有工作活动或数据要缓存，sql server将不会向操作系统请求更多内存。

    相反，如果您将 sql sever 的最小内存更改为例如 5 GB，您会发现 sql server 将为自己保留该数量。然而，如果没有任何活动并且没有足够的数据用于缓存，那么该内存量仍然会被浪费。

    • 0

相关问题