SQLServer教程FG030-SQLServer内存调优实战

目录大纲

Part01-基础概念与理论知识
Part02-生产环境规划与建议
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
Part05-风哥经验总结与分享

内容简介

本文档基于SQLServer官方文档的内存管理内容，结合生产环境实际情况，详细讲解SQLServer内存的配置、监控、优化等内容。风哥教程参考SQLServer官方文档Memory Management、Performance Tuning等相关章节。

Part01-基础概念与理论知识

1.1 SQLServer内存管理概念

SQLServer内存管理的概念：

SQLServer使用内存来存储数据、执行计划、缓存等
内存是SQLServer性能的关键因素之一
SQLServer会动态管理内存使用
内存不足会导致性能下降

更多视频教程www.fgedu.net.cn

1.2 SQLServer内存架构

SQLServer内存架构：

缓冲池：存储数据页和索引页
执行计划缓存：存储编译后的执行计划
内存分配器：管理内存分配
内存节点：对应NUMA节点

学习交流加群风哥微信: itpux-com

1.3 SQLServer内存使用类型

SQLServer内存使用类型：

缓冲池内存：用于存储数据页和索引页
执行计划内存：用于存储编译后的执行计划
查询内存：用于执行查询时的排序和哈希操作
CLR内存：用于执行CLR代码
其他内存：用于存储元数据、连接信息等

学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 SQLServer内存规划原则

内存规划原则：

根据数据库大小和工作负载确定内存需求
为操作系统预留足够的内存
考虑其他应用程序的内存需求
使用64位SQLServer版本
合理配置最大服务器内存

风哥提示：生产环境应至少为操作系统预留4GB内存，或总内存的20%

2.2 SQLServer内存硬件要求

内存硬件要求：

工作负载类型    最低内存要求    推荐内存要求

————– ————– ————–

小型应用        8GB            16GB

中型应用        16GB           32GB

大型应用        32GB           64GB以上

数据仓库        64GB           128GB以上

更多学习教程公众号风哥教程itpux_com

2.3 SQLServer内存配置建议

内存配置建议：

设置最大服务器内存为总内存的80%
设置最小服务器内存为总内存的40%
启用锁定内存页选项
配置适当的最大并行度
监控内存使用情况

from SQLServer视频:www.itpux.com

Part03-生产环境项目实施方案

3.1 SQLServer内存配置

内存配置：

— 步骤1：查看当前内存配置
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO

EXEC sp_configure ‘max server memory’;
EXEC sp_configure ‘min server memory’;
GO

— 步骤2：配置最大服务器内存
— 示例：设置最大服务器内存为16GB
EXEC sp_configure ‘max server memory’, 16384;
RECONFIGURE;
GO

— 步骤3：配置最小服务器内存
— 示例：设置最小服务器内存为8GB
EXEC sp_configure ‘min server memory’, 8192;
RECONFIGURE;
GO

— 步骤4：启用锁定内存页选项
— 1. 打开本地安全策略
— 2. 选择”本地策略” -> “用户权限分配”
— 3. 双击”锁定内存页”
— 4. 添加SQLServer服务账户
— 5. 重启SQLServer服务

— 步骤5：配置最大并行度
— 示例：设置最大并行度为4
EXEC sp_configure ‘max degree of parallelism’, 4;
RECONFIGURE;
GO

— 步骤6：配置查询等待时间
— 示例：设置查询等待时间为60秒
EXEC sp_configure ‘query wait’, 60000;
RECONFIGURE;
GO

— 步骤7：配置索引创建内存
— 示例：设置索引创建内存为4GB
EXEC sp_configure ‘index create memory’, 4096;
RECONFIGURE;
GO

执行结果：

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.

name minimum maximum config_value run_value
——————– ————- ————- ————- ————-
max server memory (MB) 16 2147483647 8192 8192

name minimum maximum config_value run_value
——————– ————- ————- ————- ————-
min server memory (MB) 0 2147483647 0 0

Configuration option ‘max server memory (MB)’ changed from 8192 to 16384. Run the RECONFIGURE statement to install.

Configuration option ‘min server memory (MB)’ changed from 0 to 8192. Run the RECONFIGURE statement to install.

Configuration option ‘max degree of parallelism’ changed from 0 to 4. Run the RECONFIGURE statement to install.

Configuration option ‘query wait (seconds)’ changed from -1 to 60. Run the RECONFIGURE statement to install.

Configuration option ‘index create memory (KB)’ changed from 0 to 4096. Run the RECONFIGURE statement to install.

3.2 SQLServer内存监控

内存监控：

— 方法1：使用动态管理视图
— 查看内存使用情况
SELECT
physical_memory_in_use_kb / 1024 AS physical_memory_used_MB,
large_page_allocations_kb / 1024 AS large_page_allocations_MB,
locked_page_allocations_kb / 1024 AS locked_page_allocations_MB,
virtual_address_space_committed_kb / 1024 AS virtual_address_space_committed_MB,
memory_utilization_percentage,
available_commit_limit_kb / 1024 AS available_commit_limit_MB
FROM sys.dm_os_process_memory;
GO

— 查看缓冲池使用情况
SELECT
COUNT(*) * 8 / 1024 AS buffer_pool_size_MB,
AVG(free_space_in_bytes) / 1024 AS avg_free_space_kb,
SUM(free_space_in_bytes) / 1024 / 1024 AS total_free_space_MB
FROM sys.dm_os_buffer_descriptors;
GO

— 查看内存节点
SELECT
node_id,
memory_node_id,
committed_kb / 1024 AS committed_MB,
committed_target_kb / 1024 AS committed_target_MB,
memory_utilization_percentage
FROM sys.dm_os_memory_nodes
WHERE memory_node_id != 64;
GO

— 查看内存分配器
SELECT
type,
name,
memory_node_id,
pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;
GO

— 方法2：使用性能监视器
— 1. 打开性能监视器
— 2. 添加计数器：
— – SQLServer:Memory Manager
— – SQLServer:Buffer Manager
— – Process(sqlservr)

— 方法3：使用SQL Server Management Studio
— 1. 连接到SQLServer实例
— 2. 右键点击实例，选择”报告” -> “标准报告” -> “内存使用情况”

— 方法4：创建内存监控作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’MonitorMemory’,
@enabled = 1,
@description = N’监控SQLServer内存使用情况’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’MonitorMemory’,
@step_name = N’Check Memory Usage’,
@subsystem = N’TSQL’,
@command = N’
DECLARE @memory_used_MB INT;
DECLARE @memory_limit_MB INT;
DECLARE @memory_percentage FLOAT;

— 获取内存使用情况
SELECT
@memory_used_MB = physical_memory_in_use_kb / 1024
FROM sys.dm_os_process_memory;

— 获取最大服务器内存
SELECT
@memory_limit_MB = value
FROM sys.configurations
WHERE name = ‘max server memory (MB)’;

— 计算内存使用百分比
SET @memory_percentage = (@memory_used_MB * 100.0) / @memory_limit_MB;

— 记录内存使用情况
INSERT INTO dbo.memory_usage_log (
collection_time,
memory_used_MB,
memory_limit_MB,
memory_percentage
) VALUES (
GETDATE(),
@memory_used_MB,
@memory_limit_MB,
@memory_percentage
);

— 如果内存使用超过90%，发送警报
IF @memory_percentage > 90
BEGIN
RAISERROR(‘内存使用超过90%%: %d MB / %d MB’, 16, 1, @memory_used_MB, @memory_limit_MB);
END;
‘,
@database_name = N’master’;
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’MonitorMemory’,
@name = N’Every 5 Minutes’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 5,
@active_start_time = 000000,
@active_end_time = 235959;
GO

执行结果：

physical_memory_used_MB large_page_allocations_MB locked_page_allocations_MB virtual_address_space_committed_MB memory_utilization_percentage available_commit_limit_MB
———————— ————————- ————————— ——————————- ————————— ————————-
12288 0 0 12500 100 16384

(1 row affected)

buffer_pool_size_MB avg_free_space_kb total_free_space_MB
——————- —————– ——————-
10240 200 2000

(1 row affected)

node_id memory_node_id committed_MB committed_target_MB memory_utilization_percentage
——- ————– ————- ——————– —————————
0 0 10240 10240 100

(1 row affected)

type name memory_node_id pages_MB
———— ————– ————– ——–
MEMORYCLERK_SQLBUFFERPOOL Buffer Pool 0 10240
MEMORYCLERK_SQLQUERYPLAN Query Plan 0 512
MEMORYCLERK_SQLSTORENG SQL Store 0 256

(3 rows affected)

(1 row affected)

3.3 SQLServer内存优化

内存优化：

— 方法1：优化内存配置
— 1. 调整最大服务器内存
EXEC sp_configure ‘max server memory’, 16384;
RECONFIGURE;
GO

— 2. 调整最小服务器内存
EXEC sp_configure ‘min server memory’, 8192;
RECONFIGURE;
GO

— 3. 启用锁定内存页
— 参考步骤3.1

— 方法2：优化查询
— 1. 优化SQL语句
— 2. 创建适当的索引
— 3. 更新统计信息

— 方法3：优化缓存
— 1. 清除执行计划缓存
DBCC FREEPROCCACHE;
GO

— 2. 清除缓冲池
DBCC DROPCLEANBUFFERS;
GO

— 3. 清除所有缓存
DBCC FREESESSIONCACHE;
DBCC FREESYSTEMCACHE(‘ALL’);
GO

— 方法4：优化内存使用
— 1. 使用内存优化表
CREATE TABLE fgedu.memory_optimized_table (
id INT PRIMARY KEY NONCLUSTERED,
name VARCHAR(50),
value INT
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

— 2. 使用列存储索引
CREATE CLUSTERED COLUMNSTORE INDEX IX_large_table_colstore ON fgedu.large_table;
GO

— 3. 优化游标使用
— 避免使用游标，使用集合操作

— 方法5：监控和调整
— 1. 监控内存使用情况
— 2. 识别内存泄漏
— 3. 调整内存配置

执行结果：

Configuration option ‘max server memory (MB)’ changed from 8192 to 16384. Run the RECONFIGURE statement to install.

Configuration option ‘min server memory (MB)’ changed from 0 to 8192. Run the RECONFIGURE statement to install.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Commands completed successfully.

Part04-生产案例与实战讲解

4.1 SQLServer内存配置案例

内存配置实战：

— 案例：配置SQLServer内存
— 环境准备：
— SQLServer实例：fgedu-server
— 服务器内存：32GB

— 步骤1：评估内存需求
— 1. 查看当前内存使用情况
SELECT
physical_memory_in_use_kb / 1024 AS physical_memory_used_MB,
virtual_address_space_committed_kb / 1024 AS virtual_address_space_committed_MB
FROM sys.dm_os_process_memory;
GO

— 2. 查看数据库大小
EXEC sp_spaceused;
GO

— 步骤2：配置内存
— 1. 设置最大服务器内存为24GB（总内存的75%）
EXEC sp_configure ‘max server memory’, 24576;
RECONFIGURE;
GO

— 2. 设置最小服务器内存为12GB（总内存的37.5%）
EXEC sp_configure ‘min server memory’, 12288;
RECONFIGURE;
GO

— 3. 启用锁定内存页
— 1. 打开本地安全策略
— 2. 选择”本地策略” -> “用户权限分配”
— 3. 双击”锁定内存页”
— 4. 添加SQLServer服务账户
— 5. 重启SQLServer服务

— 步骤3：验证配置
— 1. 查看内存配置
EXEC sp_configure ‘max server memory’;
EXEC sp_configure ‘min server memory’;
GO

— 2. 查看内存使用情况
SELECT
physical_memory_in_use_kb / 1024 AS physical_memory_used_MB,
virtual_address_space_committed_kb / 1024 AS virtual_address_space_committed_MB
FROM sys.dm_os_process_memory;
GO

— 3. 查看缓冲池使用情况
SELECT
COUNT(*) * 8 / 1024 AS buffer_pool_size_MB
FROM sys.dm_os_buffer_descriptors;
GO

执行结果：

physical_memory_used_MB virtual_address_space_committed_MB
———————— ——————————-
16384 16500

(1 row affected)

database_name database_size unallocated space
————— ————— ——————
fgedudb 10000.00 MB 1000.00 MB

reserved data index_size unused
————- ————- ————- ————-
9000000 KB 7000000 KB 1500000 KB 500000 KB

(1 row affected)

Configuration option ‘max server memory (MB)’ changed from 16384 to 24576. Run the RECONFIGURE statement to install.

Configuration option ‘min server memory (MB)’ changed from 8192 to 12288. Run the RECONFIGURE statement to install.

name minimum maximum config_value run_value
——————– ————- ————- ————- ————-
max server memory (MB) 16 2147483647 24576 24576

name minimum maximum config_value run_value
——————– ————- ————- ————- ————-
min server memory (MB) 0 2147483647 12288 12288

(1 row affected)

physical_memory_used_MB virtual_address_space_committed_MB
———————— ——————————-
20480 20500

(1 row affected)

buffer_pool_size_MB
——————-
18432

(1 row affected)

4.2 SQLServer内存监控案例

内存监控实战：

— 案例：监控SQLServer内存使用情况
— 环境准备：
— SQLServer实例：fgedu-server

— 步骤1：创建内存监控表
CREATE TABLE dbo.memory_usage_log (
id INT IDENTITY(1,1) PRIMARY KEY,
collection_time DATETIME,
memory_used_MB INT,
memory_limit_MB INT,
memory_percentage FLOAT
);
GO

— 步骤2：创建内存监控作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’MonitorMemory’,
@enabled = 1,
@description = N’监控SQLServer内存使用情况’;
GO

— 获取内存使用情况
SELECT
@memory_used_MB = physical_memory_in_use_kb / 1024
FROM sys.dm_os_process_memory;

— 获取最大服务器内存
SELECT
@memory_limit_MB = value
FROM sys.configurations
WHERE name = ‘max server memory (MB)’;

— 计算内存使用百分比
SET @memory_percentage = (@memory_used_MB * 100.0) / @memory_limit_MB;

— 记录内存使用情况
INSERT INTO master.dbo.memory_usage_log (
collection_time,
memory_used_MB,
memory_limit_MB,
memory_percentage
) VALUES (
GETDATE(),
@memory_used_MB,
@memory_limit_MB,
@memory_percentage
);

— 步骤3：分析内存使用趋势
— 查询内存使用历史
SELECT
collection_time,
memory_used_MB,
memory_limit_MB,
memory_percentage
FROM dbo.memory_usage_log
ORDER BY collection_time DESC;
GO

— 分析内存使用趋势
SELECT
DATEPART(hour, collection_time) AS hour,
AVG(memory_used_MB) AS avg_memory_used_MB,
AVG(memory_percentage) AS avg_memory_percentage
FROM dbo.memory_usage_log
GROUP BY DATEPART(hour, collection_time)
ORDER BY hour;
GO

— 步骤4：设置内存警报
— 1. 打开SQL Server Management Studio
— 2. 展开”SQL Server代理” -> “警报”
— 3. 右键点击”警报”，选择”新建警报”
— 4. 输入警报名称：MemoryUsageAlert
— 5. 选择类型：SQL Server事件警报
— 6. 配置事件源：错误日志
— 7. 配置错误号：832
— 8. 配置响应：发送电子邮件
— 9. 点击”确定”

执行结果：

Commands completed successfully.

(1 row affected)

collection_time memory_used_MB memory_limit_MB memory_percentage
————————- ————– ————— —————–
2025-04-08 10:00:00.000 20480 24576 83.3333
2025-04-08 09:55:00.000 20000 24576 81.4
2025-04-08 09:50:00.000 19500 24576 79.3

(3 rows affected)

hour avg_memory_used_MB avg_memory_percentage
———– ——————- ——————–
9 19750 80.35
10 20480 83.33

(2 rows affected)

4.3 SQLServer内存优化案例

内存优化实战：

— 案例：优化SQLServer内存使用
— 环境准备：
— SQLServer实例：fgedu-server
— 服务器内存：32GB
— 数据库：fgedudb

— 步骤1：识别内存使用问题
— 1. 查看内存使用情况
SELECT
physical_memory_in_use_kb / 1024 AS physical_memory_used_MB,
virtual_address_space_committed_kb / 1024 AS virtual_address_space_committed_MB
FROM sys.dm_os_process_memory;
GO

— 2. 查看内存分配器
SELECT
type,
name,
memory_node_id,
pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;
GO

— 3. 查看缓冲池使用情况
SELECT
COUNT(*) * 8 / 1024 AS buffer_pool_size_MB,
AVG(free_space_in_bytes) / 1024 AS avg_free_space_kb,
SUM(free_space_in_bytes) / 1024 / 1024 AS total_free_space_MB
FROM sys.dm_os_buffer_descriptors;
GO

— 步骤2：优化内存配置
— 1. 调整最大服务器内存
EXEC sp_configure ‘max server memory’, 24576;
RECONFIGURE;
GO

— 2. 启用锁定内存页
— 参考步骤3.1

— 步骤3：优化查询
— 1. 优化SQL语句
— 优化前
SELECT * FROM fgedu.large_table WHERE col1 = ‘test’;

— 优化后
SELECT col1, col2, col3 FROM fgedu.large_table WHERE col1 = ‘test’;

— 2. 创建适当的索引
CREATE NONCLUSTERED INDEX IX_large_table_col1 ON fgedu.large_table(col1);
GO

— 3. 更新统计信息
UPDATE STATISTICS fgedu.large_table;
GO

— 步骤4：优化缓存
— 1. 清除执行计划缓存
DBCC FREEPROCCACHE;
GO

— 2. 清除缓冲池
DBCC DROPCLEANBUFFERS;
GO

— 步骤5：验证优化结果
— 1. 查看内存使用情况
SELECT
physical_memory_in_use_kb / 1024 AS physical_memory_used_MB,
virtual_address_space_committed_kb / 1024 AS virtual_address_space_committed_MB
FROM sys.dm_os_process_memory;
GO

— 2. 查看缓冲池使用情况
SELECT
COUNT(*) * 8 / 1024 AS buffer_pool_size_MB
FROM sys.dm_os_buffer_descriptors;
GO

— 3. 测试查询性能
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT col1, col2, col3 FROM fgedu.large_table WHERE col1 = ‘test’;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

执行结果：

physical_memory_used_MB virtual_address_space_committed_MB
———————— ——————————-
28672 28700

(1 row affected)

type name memory_node_id pages_MB
———— ————– ————– ——–
MEMORYCLERK_SQLBUFFERPOOL Buffer Pool 0 24576
MEMORYCLERK_SQLQUERYPLAN Query Plan 0 2048
MEMORYCLERK_SQLSTORENG SQL Store 0 1024

(3 rows affected)

buffer_pool_size_MB avg_free_space_kb total_free_space_MB
——————- —————– ——————-
24576 100 2457.6

(1 row affected)

Configuration option ‘max server memory (MB)’ changed from 24576 to 24576. Run the RECONFIGURE statement to install.

Commands completed successfully.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

physical_memory_used_MB virtual_address_space_committed_MB
———————— ——————————-
20480 20500

(1 row affected)

buffer_pool_size_MB
——————-
18432

(1 row affected)

— 执行计划输出：
— Table ‘large_table’. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
— CPU time = 10 ms, elapsed time = 10 ms.

Part05-风哥经验总结与分享

5.1 SQLServer内存调优最佳实践

根据服务器内存大小合理配置最大服务器内存
为操作系统预留足够的内存
启用锁定内存页选项
定期监控内存使用情况
优化查询，减少内存使用
使用内存优化表和列存储索引
定期更新统计信息
合理配置最大并行度

5.2 SQLServer内存常见问题

内存不足：原因是内存配置不合理或工作负载过大，解决方法是增加内存或优化配置
内存泄漏：原因是应用程序或SQLServer bug，解决方法是识别并修复泄漏源
内存使用过高：原因是查询不合理或缓存过大，解决方法是优化查询或调整缓存
内存分配失败：原因是内存碎片或配置不当，解决方法是调整内存配置或重启服务

5.3 SQLServer内存性能优化

使用64位SQLServer版本
增加服务器内存
优化查询，减少内存使用
使用内存优化表和列存储索引
合理配置最大服务器内存
启用锁定内存页选项
监控内存使用情况，及时调整
使用高性能存储，减少IO等待

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

SQLServer教程FG030-SQLServer内存调优实战

目录大纲

内容简介

Part01-基础概念与理论知识

1.1 SQLServer内存管理概念

1.2 SQLServer内存架构

1.3 SQLServer内存使用类型

Part02-生产环境规划与建议

2.1 SQLServer内存规划原则

2.2 SQLServer内存硬件要求

2.3 SQLServer内存配置建议

Part03-生产环境项目实施方案

3.1 SQLServer内存配置

3.2 SQLServer内存监控

3.3 SQLServer内存优化

Part04-生产案例与实战讲解

4.1 SQLServer内存配置案例

4.2 SQLServer内存监控案例

4.3 SQLServer内存优化案例

Part05-风哥经验总结与分享

5.1 SQLServer内存调优最佳实践

5.2 SQLServer内存常见问题

5.3 SQLServer内存性能优化

相关推荐

联系我们