【1】问题发现

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

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

 

然后查看监控面板，如下图：

　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_重启

 

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

　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_sql_02

 

 

【1.2】分析

为什么会出现这种情况？

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

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

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

 

（1）查看一下最大内存设置，并没有问题

　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_sql_03

 

（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)')

　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_sql_04

 

为什么那么多内存不用，只用了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

　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_sql_05

 

 谁装的，坑！

（4）我们看看sql server是什么时候装的

select * from sys.server_principals

2年前装的，如下图：

　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_重启_06

 

 

【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实现

右击实例=》属性，具体如下图：

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_sql_07

  

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_sql_08

 

 

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

  1. 在**“开始”菜单上，单击“运行”。 在“打开”**框中，键入 gpedit.msc。
  2. 在**“本地组策略编辑器”控制台上，展开“计算机配置”，再展开“Windows 设置”**。
  3. 展开**“安全设置”，再展开“本地策略”**。
  4. 选择**“用户权利指派”**文件夹。
    细节窗格中随即显示出策略。
  5. 在该窗格中，双击**“锁定内存中的页”**。
  6. 在**“本地安全设置 – 锁定内存中的页”对话框中，单击“添加用户或组”**。
  7. 在**“选择用户、服务帐户或组”**对话框中，添加有权运行 sqlservr.exe 的帐户。
  8. 注销后重新登录，此更改才生效。

　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_重启_09

　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_Server_10

 

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

比如我这里是 localsystem

　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_Server_11

 

 

 　　

sql server数据库占用磁盘50g,实际数据量5g sql server 占用磁盘一直在涨_sql_12

 

 

 

 

【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】中相关方法和代码，核验一下；