sqlserver性能優(yōu)化之內(nèi)存優(yōu)化詳解
更新時間：2026年03月12日 16:26:41 作者：big狼王
本文介紹了SQL Server內(nèi)存優(yōu)化的詳細(xì)方案,包括核心配置、監(jiān)控手段和高級技術(shù),并提供了腳本示例及配置建議,內(nèi)容涵蓋了內(nèi)存上下限設(shè)置、啟用AWE、緩沖池擴(kuò)展、監(jiān)控關(guān)鍵內(nèi)存指標(biāo)、清理緩存策略、索引優(yōu)化、內(nèi)存優(yōu)化表（In-Memory OLTP）以及自動化維護(hù)與監(jiān)控等
以下是 SQL Server 內(nèi)存優(yōu)化的詳細(xì)方案，結(jié)合核心配置、監(jiān)控手段和高級技術(shù)，提供腳本示例及配置建議。
內(nèi)存優(yōu)化作為sqlserver性能優(yōu)化的方向之一，可以作為了解。
一、核心內(nèi)存配置優(yōu)化
??設(shè)置內(nèi)存上下限??
目標(biāo)??：防止 SQL Server 占用全部系統(tǒng)內(nèi)存，確保操作系統(tǒng)和其他應(yīng)用正常運(yùn)行。
建議配置??：
- max server memory = RAM物理內(nèi)存的 ??75%~80%??（預(yù)留 20%~25% 給 OS 及其他進(jìn)程）
- min server memory = RAM物理內(nèi)存的 ??10%~20%??（避免頻繁內(nèi)存收縮）
??腳本示例??：
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max server memory (MB)', 24576; -- 例如 24GB（適用于 32GB 內(nèi)存服務(wù)器） EXEC sp_configure 'min server memory (MB)', 4096; -- 最小 4GB RECONFIGURE;
????啟用 AWE（大內(nèi)存支持）??
- ??適用場景??：32 位 SQL Server 需使用超過 4GB 內(nèi)存時（64 位無需配置）。
- ??配置步驟??：
EXEC sp_configure 'awe enabled', 1; RECONFIGURE;
??啟用緩沖池擴(kuò)展（Buffer Pool Extension）??
-- 將緩沖池擴(kuò)展至 SSD（需 SQL Server 2014+） ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'F:\SSD_Cache\BP_Extend.bpe', SIZE = 32 GB);
- ??作用??：將冷數(shù)據(jù)緩存至 SSD，減少物理 I/O 壓力，提升讀取性能。
二、緩沖池與緩存管理
??監(jiān)控關(guān)鍵內(nèi)存指標(biāo)??
- ??核心 DMV 查詢??：
-- 實(shí)時內(nèi)存狀態(tài)
SELECT
counter_name AS [指標(biāo)],
cntr_value AS [值(KB)]
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Target Server Memory (KB)', -- 目標(biāo)內(nèi)存
'Total Server Memory (KB)', -- 已用內(nèi)存
'Page life expectancy' -- 頁生命周期（秒），>300 為佳
);
- ??輸出解讀??：
- Page life expectancy 過低（<60s）表明內(nèi)存壓力大，需優(yōu)化或擴(kuò)容。
????清理緩存策略??
??場景??：計(jì)劃緩存過多或測試環(huán)境需重置狀態(tài)時。
??腳本示例??：
DBCC FREEPROCCACHE; -- 清除執(zhí)行計(jì)劃緩存
DBCC DROPCLEANBUFFERS; -- 清除數(shù)據(jù)緩存
DBCC FREESYSTEMCACHE('ALL'); -- 清除系統(tǒng)緩存
- ????注意??：生產(chǎn)環(huán)境謹(jǐn)慎使用，可能引發(fā)短期性能波動。
三、索引優(yōu)化減少內(nèi)存壓力
??定期維護(hù)索引碎片??
- 腳本示例??：
-- 檢查碎片率（>30% 建議重建）
SELECT
index_id,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE avg_fragmentation_in_percent > 30;
四、高級內(nèi)存優(yōu)化技術(shù)
??內(nèi)存優(yōu)化表（In-Memory OLTP）??
適用場景??：高頻讀寫表（如會話狀態(tài)、實(shí)時交易）。
??配置步驟??：
--創(chuàng)建內(nèi)存優(yōu)化文件組：
ALTER DATABASE [DBName]
ADD FILEGROUP [MemOpt_FG] CONTAINS MEMORY_OPTIMIZED_DATA;
--創(chuàng)建容器
ALTER DATABASE [DBName]
ADD FILE (NAME='MemOpt_File', FILENAME='E:\work\develop\sqldata\test') --注意這里是文件夾
TO FILEGROUP [MemOpt_FG];
--創(chuàng)建內(nèi)存優(yōu)化表：
CREATE TABLE [dbo].[MemTable] (
ID INT PRIMARY KEY NONCLUSTERED,
Data NVARCHAR(100)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
--查看內(nèi)存優(yōu)化表
SELECT object_id,
OBJECT_SCHEMA_NAME(object_id) AS schema_name,
name AS table_name
FROM sys.tables
WHERE is_memory_optimized = 1;
USE [SalesDB];
GO
-- 查看文件組及文件
SELECT
fg.name AS FileGroupName,
f.name AS FileName,
f.physical_name AS FilePath
FROM sys.filegroups fg
LEFT JOIN sys.database_files f ON fg.data_space_id = f.data_space_id;
內(nèi)存優(yōu)化表適用于OLTP高頻交易系統(tǒng)、實(shí)時分析與決策的系統(tǒng)、高并發(fā)與資源爭用場景?等。
目前我的感受下來，內(nèi)存優(yōu)化表（MOT），個人認(rèn)為，一般不會用到。
首先，MOT針對的是高頻讀寫的表，那么針對讀，可以使用redis緩存提高查詢性能，在進(jìn)行更新時先更db在刪cache。實(shí)在業(yè)務(wù)數(shù)據(jù)量巨大時，也可以考慮讀寫分離分庫分表，redis 分片集群等。
MOT會把數(shù)據(jù)寫入磁盤的，這樣會很吃內(nèi)存，并且，如果做了MOT還要保證DB和MOT的一致性，如果用了redis，就得保證MOT、redis、db三方的一致性。
鑒于上述，個人認(rèn)為實(shí)在沒啥必要使用內(nèi)存優(yōu)化表。當(dāng)然這里僅限個人認(rèn)為。
五、自動化維護(hù)與監(jiān)控
定期內(nèi)存巡檢腳本??
-- 檢查內(nèi)存分配狀態(tài) DBCC MEMORYSTATUS; -- 輸出緩沖池、計(jì)劃緩存等詳細(xì)信息[4,8](@ref) -- 監(jiān)控內(nèi)存等待事件 SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%MEMORY%';
- 自動化索引維護(hù)計(jì)劃??
- 使用 SQL Agent 定時執(zhí)行索引重建任務(wù)（每周低峰期）。
六、內(nèi)存優(yōu)化臨時表
- 啟用內(nèi)存優(yōu)化臨時表（SQL Server 2019+）?
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
- 作用??：將臨時表自動提升為內(nèi)存優(yōu)化表，減少 TempDB I/O 壓力。
關(guān)鍵注意事項(xiàng)
- 避免過度配置??：max server memory 過高可能導(dǎo)致操作系統(tǒng)內(nèi)存不足引發(fā)崩潰。
- ??監(jiān)控 PLE（Page Life Expectancy）??：持續(xù)低于 300 秒需擴(kuò)容或優(yōu)化查詢。
- ??版本兼容性??：內(nèi)存優(yōu)化表僅支持 SQL Server 2014 及以上版本。
??總結(jié)
以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
SQL Server實(shí)時同步更新遠(yuǎn)程數(shù)據(jù)庫遇到的問題小結(jié)
這篇文章主要介紹了SQL Server實(shí)時同步更新遠(yuǎn)程數(shù)據(jù)庫遇到的問題小結(jié),需要的朋友可以參考下2017-04-04
IN和NOT?IN是比較常用的關(guān)鍵字,為什么要盡量避免呢?這篇文章主要給大家介紹了關(guān)于T-SQL查詢?yōu)楹紊饔?IN和NOT?IN的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-02-02
如果你維護(hù)的數(shù)據(jù)表一方面依靠其他模塊的數(shù)據(jù)表，另一方面又對其他模塊進(jìn)行數(shù)據(jù)支持，并且這些表都是實(shí)時更新的，那么就不能通過頁面調(diào)用SQL語句等方式來更新數(shù)據(jù)表，只能讓數(shù)據(jù)表定時自動更新。2011-04-04
這篇文章主要介紹了sql server刪除過期備份文件的腳本,文中通過代碼示例給大家講解的非常詳細(xì),對大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-10-10
使用linux?CentOS本地部署SQL?Server數(shù)據(jù)庫超詳細(xì)步驟
作為一名Linux愛好者,我們在使用Linux系統(tǒng)的時候,經(jīng)常需要使用到數(shù)據(jù)庫,下面這篇文章主要給大家介紹了關(guān)于使用linux?CentOS本地部署SQL?Server數(shù)據(jù)庫的超詳細(xì)步驟,需要的朋友可以參考下2024-01-01
這篇文章主要介紹了Sql Server中的非聚集索引詳細(xì)介,本文講解了非聚集索引為什么能提速這么快、什么是RID、非聚集索引的運(yùn)行原理等內(nèi)容,需要的朋友可以參考下2015-02-02
SQL SELECT DISTINCT 語句實(shí)例詳解
本文將深入探討 SELECT DISTINCT 語句,詳細(xì)講解它的用法、原理以及常見的應(yīng)用場景,幫助你理解如何精準(zhǔn)地去除重復(fù)數(shù)據(jù),感興趣的朋友跟隨小編一起看看吧2025-06-06
mssql函數(shù)DATENAME使用示例講解(取得當(dāng)前年月日/一年中第幾天SQL語句)
這篇文章主要介紹了mssql函數(shù)DATENAME取得當(dāng)前年月日、一年中第幾天的SQL語句2013-11-11
這篇文章主要介紹了sqlserver中數(shù)據(jù)查詢方法,需要的朋友可以參考下2015-01-01
更改SQL Server數(shù)據(jù)庫的日志恢復(fù)模式是數(shù)據(jù)庫管理中的一個重要操作,本文就來介紹一下SQL Server三種主要的恢復(fù)模式,具有一定的參考價值,感興趣的可以了解一下2024-12-12