將資料表的資料頁標記為記憶體常駐,SQL Server 不會將其從緩衝池淘汰,實現類似「記憶體資料庫」的讀取速度。適用於小型、高頻存取、延遲敏感的查閱表(代碼表、配置表、權限表)。
-- 取得資料庫與表 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+ 移除。不再建議使用。
-- 釘選
EXEC sp_tableoption 'Department', 'pintable', 'true';
-- 取消
EXEC sp_tableoption 'Department', 'pintable', 'false';
-- 回傳 1 表釘選中,0 未釘選
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.Department'), 'TableIsPinned');
SELECT * FROM Department 強制載入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);
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'D:\BP_Extension.BPE', SIZE = 100 GB);
-- 建立檔案群組在 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;
sp_tableoption 'pintable', 'true' + 手動預熱