SQL服务器内存如何分配好？如何管理？

Question

我是服务器管理员，对SQL算不上精通；由于SQL的缓存占有模式，因此给多少内存最终都能被占用满的。

有两个问题：

1，如何评估一台SQL服务器的内存给多少最合理，够用？或者本身没有合理一说，本来就是越多越好？

2，公司DBA对数据库不加限制，给多少都占用到近100%，1是监控会报警，2是导致操作系统非常缓慢，这样是否合理？ 如果不合理，现在内存是128G，该如何限制内存大小为宜？ 个人的理解不限制的话操作系统慢了，会影响到应用的速度，应该限定在110G-120G水平，留一些给操作系统，不知道是否合理。  请提供相应的官方介绍链接，便于与DBA沟通。谢谢大家。

Answer 1

越多越好，没有合理值

但应该设置上限，为服务器操作系统和服务器上的其他程序预留内存， 可以用 sp_configure 查看和配置

exec sp_configure 'show advanced options', 1; reconfigure;

exec sp_configure 'max server memory (mb)'

具体的可以查看联机帮助上的相关说明

Answer 2

For server with 128gb memory and dedicated to sql, you can set sql max memory to 120gb as start. Make adjustment if necessary. If it's not dedicated to sql, have to leave more memory for OS and other apps.

Answer 3

给你一个脚本，具体参数自己调整

--分等级设置MAX SERVER MEMORY


DECLARE @level BIGINT --等级（1，2）1代表专用数据库服务器，不需要设置最大内存，2代表不是专用数据库服务器，需要设置最大内存
DECLARE @phymemory BIGINT --服务器的内存值
DECLARE @maxmemory BIGINT --设置的内存值

SET @level = 2  --★Do
SELECT @phymemory=([physical_memory_kb]/1024)  FROM sys.[dm_os_sys_info] 

IF ( @level = 2 )
    BEGIN
        IF ( @phymemory < 9216 ) --8G内存
            BEGIN
                SET @maxmemory = 6000
            END
        ELSE
            IF ( @phymemory < 18000 AND @phymemory > 9216)  --16G内存
                BEGIN
                    SET @maxmemory = 14000
                END
            ELSE
                IF ( @phymemory < 33000  AND @phymemory > 18000)  --32G内存
                    BEGIN
                        SET @maxmemory = 28000
                    END
                ELSE
                    IF ( @phymemory < 64000 AND @phymemory > 33000)  --64G内存
                        BEGIN
                            SET @maxmemory = 58000
                        END
                    ELSE
                        IF ( @phymemory > 64000 )  --大于64G内存
                            BEGIN
                                SET @maxmemory = @phymemory * 0.8
                            END

        EXEC sys.[sp_configure] @configname = 'show advanced options', -- varchar(35)
            @configvalue = 1 -- int
        RECONFIGURE WITH OVERRIDE

        EXEC sys.[sp_configure] @configname = 'max server memory (MB)', -- varchar(35)
            @configvalue = @maxmemory -- int
        RECONFIGURE WITH OVERRIDE


    END 

---

Love SQL

Answer 4

建议先设置到75%，然后观察cache hit ratio，小于95%就加，但不要超过95%。

另外，如果不是独立SQL服务器，有其它应用，那么要减去其它应用所需空间。

如果懒得慢慢调整，可以考虑如下公式：

SQL Server MAX_SERVER_MEMORY=服务器物理内存减 最大工作线程数 乘以 线程堆栈大小(32位: 512K; X64: 2MB; IA64: 4MB)
  再减 操作系统(大概 2-4GB)和其它应用程序所需内存

---

想不想时已是想，不如不想都不想。

Answer 5

建议先设置到75%，然后观察cache hit ratio，小于95%就加，但不要超过95%。

另外，如果不是独立SQL服务器，有其它应用，那么要减去其它应用所需空间。

如果懒得慢慢调整，可以考虑如下公式：

SQL Server MAX_SERVER_MEMORY=服务器物理内存减 最大工作线程数 乘以 线程堆栈大小(32位: 512K; X64: 2MB; IA64: 4MB)
  再减 操作系统(大概 2-4GB)和其它应用程序所需内存

---

想不想时已是想，不如不想都不想。

为什么还在迷信cache hit ratio？

当你真正观察过cache hit ratio的时候，你会发现他很难低于95%，不管它的内存有多么的紧张

https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/

Answer 6

good。

cache hit ratio和页面生产周期都是监控内存是否有瓶颈的常用指标。

---

chenl

Answer 7

通常内存占95%以上时，应用程序会通常很卡

内存128G时，可能系统和应用程序预留3G以上

会用JOB定时检查内存值，有没有占用过多而需要释放

提供一种思路，计算缓冲池占用内存再决定释放

如:

USE master
GO

DECLARE @Memory DECIMAL(18,2)


/* --计算缓冲池占用内存
SELECT 	
	@Memory=COUNT(row_count)*8.0/1024.0 
FROM sys.dm_os_buffer_descriptors
*/
--也可用进程占用内存
SELECT @Memory=physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);


IF  @Memory>102400				--100G时释放内存
BEGIN 
	DBCC FREEPROCCACHE			--删除计划缓存中的所有元素
	DBCC FREESESSIONCACHE		--刷新针对 Microsoft SQL Server 实例执行的分布式查询所使用的分布式查询连接缓存
	DBCC FREESYSTEMCACHE('All')with MARK_IN_USE_FOR_REMOVAL		--从所有缓存中释放所有未使用的缓存条目
	
	CHECKPOINT				--将当前数据库的全部脏页写入磁盘
	DBCC DROPCLEANBUFFERS	--从缓冲池中删除所有清除缓冲区
	EXEC sp_configure 'show advanced options', 1;
	EXEC sys.sp_configure N'max server memory (MB)', '102400' --设置最大值为100G
	WAITFOR DELAY '00:00:05'
	EXEC sys.sp_configure N'max server memory (MB)', '2147483647'--设置为无限制
	RECONFIGURE WITH OVERRIDE
END
GO

Roy Wu(吳熹Blog)(微博)