萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 表駐留記憶體實戰:DBCC PINTABLE、sp_tableoption 與驗證方法完整指南

什麼是表釘選?

將資料表的資料頁標記為記憶體常駐,SQL Server 不會將其從緩衝池淘汰,實現類似「記憶體資料庫」的讀取速度。適用於小型、高頻存取、延遲敏感的查閱表(代碼表、配置表、權限表)。

方法一:DBCC PINTABLE(舊版、已棄用)

-- 取得資料庫與表 ID
DECLARE @db_id INT = DB_ID('MyDB');
DECLARE @tbl_id INT = OBJECT_ID('dbo.Department');

-- 釘選
DBCC PINTABLE (@db_id, @tbl_id);

-- 取消釘選
DBCC UNPINTABLE (@db_id, @tbl_id);

⚠️ SQL Server 2005+ 標記為棄用,2012+ 移除。不再建議使用。

方法二:sp_tableoption 'pintable'(相容性較好,同樣棄用)

-- 釘選
EXEC sp_tableoption 'Department', 'pintable', 'true';

-- 取消
EXEC sp_tableoption 'Department', 'pintable', 'false';

驗證是否生效

-- 回傳 1 表釘選中,0 未釘選
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.Department'), 'TableIsPinned');

關鍵行為細節(官方文檔)

  1. 不會立即讀入記憶體:僅標記,首次查詢讀入後才常駐
  2. 手動預熱:釘選後執行 SELECT * FROM Department 強制載入
  3. 更新仍寫入磁碟:WAL 機制保證持久性,記憶體頁面同步更新
  4. 大表風險:佔用過多緩衝池導致其他查詢效能下降,甚至 OOM
  5. sysadmin 才能解除大表釘選:若填滿緩衝池,需重啟 SQL Server 才能 UNPIN

現代替代方案(SQL Server 2014+)

1. In-Memory OLTP (Hekaton) - 記憶體優化表

CREATE TABLE dbo.Department_Memory (
    DeptID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
    Name NVARCHAR(50) NOT NULL,
    Budget MONEY
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
  • 真正無鎖、無閂、原生編譯存儲程序
  • 資料完整在記憶體,Checkpoint 寫入磁碟
  • 需規劃記憶體容量、備份策略

2. Buffer Pool Extension (BPE) - 緩衝池擴展至 SSD

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON 
(FILENAME = 'D:\BP_Extension.BPE', SIZE = 100 GB);
  • 將熱頁面自動溢寫至快速 SSD,透明擴展緩衝池
  • 無需程式碼變更,適用唯讀/讀多寫少工作負載

3. 將表放入專用檔案群組 + 置於高速儲存

-- 建立檔案群組在 NVMe 磁碟
ALTER DATABASE MyDB ADD FILEGROUP FG_HotData;
ALTER DATABASE MyDB ADD FILE (NAME='HotData', FILENAME='E:\SQLData\HotData.ndf') TO FILEGROUP FG_HotData;

-- 重建表至該檔案群組
CREATE UNIQUE CLUSTERED INDEX PK_Department ON Department(DeptID)
WITH (DROP_EXISTING=ON) ON FG_HotData;

列出所有釘選表

SELECT 
    TABLE_SCHEMA, TABLE_NAME,
    OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'TableIsPinned') AS IsPinned
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'TableIsPinned') = 1;

總結建議

  1. 舊版相容維護:sp_tableoption 'pintable', 'true' + 手動預熱
  2. 新專案:In-Memory OLTP 取代釘選,獲得真正記憶體級效能
  3. 唯讀熱點表:Buffer Pool Extension 或 檔案群組置於 NVMe 低成本提升
  4. 嚴禁釘選大表、高寫入表、未評估記憶體容量的表
copyright © 萬盛學電腦網 all rights reserved