SQLServer内存配置操作

boonya

已于 2022-06-13 16:05:53 修改

阅读量2.7k

点赞数

分类专栏： SQLServer 文章标签： sqlserver sql 数据库

于 2022-06-13 16:04:56 首次发布

本文链接：https://blog.csdn.net/boonya/article/details/125261962

版权

SQLServer 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

SqlServer的内存使用默认大小无限制，有些场景下导致服务内存被全部打满，影响服务器性能。

原文地址：Server memory configuration options - SQL Server | Microsoft Docs

核心的语句处理：



-- A 设置内存为4GB
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO


-- B 确定当前内存分配
SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage,
   process_physical_memory_low AS sql_process_physical_memory_low,
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

-- C 查询最大服务器内存设置
SELECT [value],[value_in_use]
FROM sys.configurations WHERE [name] = 'max server memory (MB)';