SQL Server 缓存清除与内存释放

Lokie.Wang
2019-11-14 11:10:34
数据库
MSSQL

近期遇到了关于SQL Server服务器内存暴涨的问题，做为一个常常工作再Linux和Mysql的用户，如今对SQL Server的确不是最熟悉，因此专门记录下相关问题。
首先需要明确这些暴涨的内存是什么，来自哪里？

内存一般都是SqlServer运行时候用作缓存的，例如你运行一个select语句，执行个存储过程，调用函数；

数据缓存：执行个查询语句，SqlServer会将相关的数据页（SqlServer操作的数据都是以页为单位的）加载到内存中来，下一次如果再次请求此页的数据的时候，就无需读取磁盘了，大大提高了速度。
执行命令缓存：在执行存储过程，自定函数时，SqlServer需要先二进制编译再运行，编译后的结果也会缓存起来，再次调用时就无需再次编译。

清除缓存存在几种方法：

方法一：
重启Sql Server服务。并不推荐这种方式，影响线上业务的正常运行，需要在非繁忙时间做，通常是三更半夜，也存在一定的风险

方法二：
可以分别清除不同的缓存。

1.清除存储过程缓存

DBCC FREEPROCCACHE

2.清除会话缓存

DBCC FREESESSIONCACHE

3.清除系统缓存

DBCC FREESYSTEMCACHE('All')

4.清除所有缓存

DBCC DROPCLEANBUFFERS

虽然我们已经清除了缓存，但是sql并未释放相应占用的内存。它只是腾出新的空间为之后所执行脚本所用。SqlServer 并没有提供任何命令允许我们释放不用到的内存。因此我们只能通过动态调整Sql Server可用的物理内存设置来强迫它释放内存

操作原理是调整内存配置大小。手动操作方法:
1.打开SqlServer Management(企业管理器);
2.打开SqlServer实例的属性面板;
3.找到内存设置，改变其中的最大服务器内存使用即可。
注意启用AWE内存管理，对于AWE下面会说道。

这里设置最大内存建议70%左右的服务器内存，SQL Server在实际生产环境中应该使用独立服务器。

脚本：

--强制释放内存 

CREATE procedure[dbo].ClearMemory 

as  

begin 

--清除所有缓存 

DBCC DROPCLEANBUFFERS 

--打开高级配置 

exec sp_configure'show advanced options', 1 

--设置最大内存值，清除现有缓存空间 

exec sp_configure'max server memory', 256 

EXEC ('RECONFIGURE') 

--设置等待时间 

WAITFOR DELAY '00:00:01' 

--重新设置最大内存值 

EXEC  sp_configure'max server memory',4096 

EXEC ('RECONFIGURE') 

--关闭高级配置 

exec sp_configure'show advanced options',0 

GO

下面提供内存查看功能的一些脚本语句：

1.内存使用情况

SELECT *FROM sys.dm_os_performance_counters
WHERE counter_nameIN('Target Server Memory (KB)','Total Server Memory (KB)')

2.内存状态

DBCC MemoryStatus

3.查看最小最大内存

SELECT configuration_idasid,nameas 名称,minimumas配置最小值,maximumas 最大值,

is_dynamic as 是否动态值, is_advanced as 是否优先,value_in_use AS 运行值
description as 描述 FROMsys.configurations

简单介绍SQL Server内存分配机制
https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver15
根据微软官方文档，SQL Server在没有干涉的情况下总是动态的尽可能大的使用内存，因为在内存会比磁盘读写快很多，事实上Mysql、Orcale的理念也是如此。
SQL Server可以通过AWE和Locked Page策略更好的管理内存
AWE是Address Windowing Extensions的缩写，Windows 2000开始支持64GB的内存，但是如果安装的32位的SQL Server，必须开启这个特性才能支持大内存，也就是64位的的SQL Server是没有开启的必要的
关于 Lockedpage,参见：
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver15
Locked Page的主要优势是锁定了页的交换，操作系统默认会将内存中的页放入虚拟内存，这种方式不是最优的。

本文为Lokie.Wang原创文章,转载无需和我联系,但请注明来自lokie博客http://lokie.wang