您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2008 記憶體與 I/O 效能監控完整指南

編輯：關於SqlServer

一、記憶體核心概念（32 位元環境）

使用者模式 vs 核心模式

Windows 採用雙模式保護作業系統核心：核心模式擁有完整硬體存取權，供 OS 核心與驅動程式使用；使用者模式權限受限，供應用程式（含 SQL Server）執行。防止應用程式誤損系統穩定。

物理記憶體 vs 虛擬記憶體

物理記憶體：實際安裝的 RAM 容量。32 位元 CPU 最大定址 4 GB。
虛擬記憶體：RAM + 分頁檔。RAM 不足時將資料移至分頁檔釋放記憶體。
虛擬位址空間 (VAS)：每進程擁有獨立 4 GB VAS（0x00000000~0xFFFFFFFF）。預設低 2 GB 給使用者模式，高 2 GB 給核心模式。

突破 2/4 GB 限制的開關

開關	用途	設定方式
/3GB	將使用者模式 VAS 擴充至 3 GB，核心模式壓縮至 1 GB。適用於 SQL Server 需更大緩衝集區時。	Boot.ini / BCDEdit
/PAE	啟用實體位址擴充，支援 32 位元系統使用高達 64 GB 物理記憶體。	Boot.ini / BCDEdit
AWE (Address Windowing Extensions)	應用程式鎖定實體記憶體頁面，映射至 32 位元 VAS 視窗，突破 2~3 GB 限制。需 SQL Server 啟用「使用 AWE 配置記憶體」。	SSMS 伺服器屬性 → 記憶體頁面

注意：物理記憶體超過 16 GB 時，勿同時啟用 /3GB 與 /PAE，應移除 /3GB 確保核心模式有足夠空間管理頁框資料庫。

二、SQL Server 記憶體管理架構

三層階層

記憶體節點：底層配置器介面，對應 NUMA 節點。僅記憶體 Clerk 可存取。
記憶體 Clerk / 快取 / 集區：各大元件（緩衝集區、計畫快取、CLR 等）建立專屬 Clerk 向節點申請記憶體。
記憶體物件：比 Clerk 粒度更細的堆積配置單位，元件透過 Clerk 的頁面配置器取得頁面再細分。

關鍵 DMV

sys.dm_os_memory_nodes、sys.dm_os_memory_clerks、sys.dm_os_memory_objects
sys.dm_os_memory_cache_* 系列：快取時鐘指標、計數器、雜湊表、條目明細。
sys.dm_os_sys_info、sys.dm_os_sys_memory、sys.dm_os_virtual_address_dump
DBCC MEMORYSTATUS：完整記憶體狀態快照，排查瓶頸首選工具。

三、記憶體壓力監控指標

物件/計數器	建議閾值	說明
Memory: Pages/sec	< 50（持續）	硬分頁錯誤率，過高代表實體記憶體不足。
Memory: Available MBytes	> 總 RAM 10%	可用實體記憶體，過低觸發分頁。
SQLServer:Buffer Manager - Page life expectancy	> 300 秒	頁面在緩衝集區平均停留秒數，過低表示記憶體壓力或讀取頻繁。
SQLServer:Buffer Manager - Buffer cache hit ratio	> 99%	緩衝命中率，低於 97% 需關注。
SQLServer:Memory Manager - Target Server Memory / Total Server Memory	Total 接近 Target	若 Total 長期遠小於 Target，可能受外部壓力限制。

四、實用查詢：緩衝集區分佈與熱門物件

-- 各資料庫在緩衝集區頁面數
SELECT db_name(database_id) AS DB, COUNT(*)*8/1024 AS MB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id ORDER BY MB DESC;

-- 緩衝集區中前 10 大物件（資料表/索引）
SELECT TOP 10 OBJECT_NAME(p.object_id) AS Obj, i.name AS Idx, COUNT(*)*8/1024 AS MB
FROM sys.dm_os_buffer_descriptors bd
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.hobt_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE bd.database_id = DB_ID()
GROUP BY p.object_id, i.name ORDER BY MB DESC;

-- 緩衝集區外大記憶體配置元件（多頁配置器）
SELECT type, SUM(pages_kb)/1024 AS MB
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0 AND type NOT LIKE 'CACHESTORE%'
GROUP BY type ORDER BY MB DESC;

-- 計畫快取佔用前 10 的查詢
SELECT TOP 10 cp.size_in_bytes/1024/1024 AS MB, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.cacheobjtype = 'Compiled Plan'
ORDER BY cp.size_in_bytes DESC;

五、I/O 效能診斷

關鍵指標：吞吐量與 IOPS

吞吐量：MB/秒，取決於陣列架構、光纖通道頻寬、磁碟數量。公式：所需磁碟數 = 目標吞吐量 / (單碟吞吐量 * RAID 效率)。
IOPS：每秒讀寫操作次數，衡量隨機存取能力。取決於演算法、快取命中率、磁碟數。單碟 IOPS 上限：SATA/IDE ~100，SAS/FC 150~200，SSD 萬級。

RAID 等級對 IOPS 影響

RAID	每碟 IOPS 公式（讀/寫比例 R/W）	寫入懲罰
RAID 0	(R+W)/N	無
RAID 1/10	(R+2W)/N	2x
RAID 5	(R+4W)/N	4x

實際每碟所需 IOPS = ((總IOPS*(1-快取命中率)*讀比例) + 總IOPS*寫比例*RAID懲罰) / 磁碟數 / 0.85（85% 利用率安全線）。

部署最佳實踐

作業系統 + SQL Server 二進位檔 → RAID 1
記錄檔 → RAID 1/10（循序寫入、低延遲）
tempdb → RAID 0（高速、可重建）或 RAID 10
資料檔 → RAID 5/6（容量/成本平衡）或 RAID 10（高效能）
資料與記錄檔物理隔離於不同磁碟組。

I/O 監控指標

物件/計數器	建議閾值	說明
PhysicalDisk: Avg. Disk sec/Read/Write	< 10 ms（SSD < 1 ms）	平均延遲，超過 20 ms 明顯影響查詢回應。
PhysicalDisk: Current Disk Queue Length	< 2 * 磁碟數	佇列長度持續偏高表示 I/O 瓶頸。
PhysicalDisk: Disk Reads/Writes/sec	視磁碟規格	實際 IOPS，對比磁碟上限判斷飽和度。

DMV 即時 I/O 分析

-- 檔案層級 I/O 統計
SELECT DB_NAME(vfs.database_id) AS DB, mf.physical_name,
       vfs.num_of_reads, vfs.num_of_bytes_read, vfs.io_stall_read_ms,
       vfs.num_of_writes, vfs.num_of_bytes_written, vfs.io_stall_write_ms,
       (vfs.io_stall_read_ms+vfs.io_stall_write_ms)/NULLIF(vfs.num_of_reads+vfs.num_of_writes,0) AS avg_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) vfs
JOIN sys.master_files mf ON vfs.database_id=mf.database_id AND vfs.file_id=mf.file_id
ORDER BY avg_stall_ms DESC;

-- 正在等待的 I/O 請求
SELECT * FROM sys.dm_io_pending_io_requests;