sql server不吃内存,sql server不占用内存导致磁盘压力暴增

时间:2024-02-20 11:37:58

【1】问题发现

【1.1】sql server服务器磁盘队列暴增报警

告警项: 磁盘队列阻塞 
项目组:XXX
实例名:XXX_WIN_9182
详情: D: 盘队列3m中内持续等待队列大于10 VALUE = 86 
级别: warning 
开始时间: 2021-02-19 08:23:50
++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++

 

然后查看监控面板,如下图:

  

 

发现,磁盘IO,磁盘读写 太大,且总内存16G,只用了 4.8G 左右

  

 

 

【1.2】分析

为什么会出现这种情况?

  磁盘IO,磁盘读写 太大,且总内存16G,只用了 4.8G 左右

sql server改本性了?开始不狂吃内存,反而害羞的像个小姑娘,不怎么占用内存了?

这就和恋爱中的小姑娘一样,既害怕对方乱来,更害怕对方不来

 

(1)查看一下最大内存设置,并没有问题

  

 

(2)查看当前sql server 内存占用使用,发现只申请了3G,且最优只能申请3G

select *,cntr_value/1.0/1024/1024 as Mem_GB from sys.dm_os_performance_counters where counter_name like \'%memory%\'
and rtrim(ltrim(counter_name)) in (\'Target Server Memory (KB)\',\'Total Server Memory (KB)\')

  

 

为什么那么多内存不用,只用了3G多呢?

(3)最终发现,是因为sql server 是32位的

《1》select @@version

=》

  Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 6.1 (Build 7601: Service Pack 1) 

《2》exec xp_msver

  

 

 谁装的,坑!

(4)我们看看sql server是什么时候装的

select * from sys.server_principals

2年前装的,如下图:

  

 

 

【2】解决问题

【2.1】开启 AWE,配置最大内存

(1)t-sql实现

sp_configure \'show advanced options\', 1  --开启高级选项
RECONFIGURE
GO

sp_configure \'awe enabled\', 1  --开启 awe 功能
RECONFIGURE
GO

sp_configure \'min server memory\', 2000--设置MSSQL服务占用最小内存为2GB
RECONFIGURE
GO

sp_configure \'max server memory\', 11000 --设置MSSQL服务占用最大内存为11GB
RECONFIGURE
GO

(2)ssms实现

右击实例=》属性,具体如下图:

  

 

 

【2.2】给MSSQL 引擎服务器账户添加=》锁定内存页

参考官网:https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms190730(v=sql.110)

  1. 在**“开始”菜单上,单击“运行”。 在“打开”**框中,键入 gpedit.msc。

  2. 在**“本地组策略编辑器”控制台上,展开“计算机配置”,再展开“Windows 设置”**。

  3. 展开**“安全设置”,再展开“本地策略”**。

  4. 选择**“用户权利指派”**文件夹。

    细节窗格中随即显示出策略。

  5. 在该窗格中,双击**“锁定内存中的页”**。

  6. 在**“本地安全设置 – 锁定内存中的页”对话框中,单击“添加用户或组”**。

  7. 在**“选择用户、服务帐户或组”**对话框中,添加有权运行 sqlservr.exe 的帐户。

  8. 注销后重新登录,此更改才生效。

  

  

 

 然后我们需要用SSCM,或者在服务列表中,看一下sql server 引擎和代理账户是什么,然后好吧它加入到 运行锁定内存页中去

比如我这里是 localsystem

  

 

 

   

 

 

 

 

【2.3】配置好后,sql server服务必须重启

  要是是固定账户:则注销后重新登录,此更改才生效。

  最好是重启一下机器吧;

【2.4】AWE的设置在各个版本上的情况(参考)

在坊间流传的有关AWE的设置的各种版本让人非常困惑。比如说如何设置起作用,如何设置不起作用,在32位和64位上是否需要AWE等。

  • 在64位系统(sql SERVER 2005+版本)
    • AWE是不需要的(即使是ON状态,也毫无影响)
    • 开启“锁定内存页”使得缓冲池中的内存页不会被置换到虚拟内存中(实际上所有的Single Page Allocator分配和Stolen的内存都不会被置换)
    • 当开启“锁定内存页时”,sql Server使用Windows AWE API来分配内存,这种方式略快
    • “锁定内存页”仅仅在标准版和企业版中存在(译者注:在非生产环境的开发版也是存在的)
  •     在32位系统(sql SERVER 2005+版本)
    • 为了使用大于4G的内存,必须开启AWE来使用额外的虚拟地址空间
    • 为了使用AWE,“锁定内存页”权限必须开启
    • “锁定内存页”仅仅在标准版和企业版中存在(译者注:在非生产环境的开发版也是存在的)

 

【3】核验

重启后,再使用【1】中相关方法和代码,核验一下;