SQL Server 表變數對 IO 與記憶體影響實測：tempdb 空間、Buffer Pool、批次結束釋放

表變數（@table）常被認為「只在記憶體」，實測證實會寫入 tempdb、產生 IO、佔用 Buffer Pool，且在批次結束時釋放。本文提供完整測試腳本與結果分析。

測試 1：tempdb 空間變化

USE tempdb; SET NOCOUNT ON;
EXEC sp_spaceused;  -- 插入前

DECLARE @tmp_orders TABLE (list_no INT, id INT);
INSERT INTO @tmp_orders (list_no, id)
SELECT ROW_NUMBER() OVER (ORDER BY Id), Id FROM Test.dbo.Orders;

SELECT TOP 1 name, object_id, type, create_date
FROM sys.objects WHERE type='U' ORDER BY create_date DESC;

EXEC sp_spaceused;  -- 插入後（批次未結束）
GO
EXEC sp_spaceused;  -- GO 後（批次結束）

結果

• 表變數建立+插入後：tempdb 空間**增加 ~9MB**
• sys.objects 可見表變數物件（名稱如 #A1B2C3）
• 批次結束（GO）後：空間**釋放歸零**

測試 2：IO 統計驗證

SELECT db_name(database_id) AS db, *
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), NULL);

• 建立表變量前後對比：資料檔**寫入位元組增加 ~8.3MB**
• 證實表變數**確實產生實體 IO 寫入 tempdb**

測試 3：Buffer Pool 緩存頁面

-- 查詢 tempdb 物件在 Buffer Pool 的緩存頁數
SELECT COUNT(*) AS cached_pages, name, index_id
FROM sys.dm_os_buffer_descriptors bd
JOIN (
    SELECT object_name(object_id) AS name, index_id, allocation_unit_id
    FROM sys.allocation_units au
    JOIN sys.partitions p ON au.container_id = p.hobt_id
    WHERE au.type IN (1,3)
    UNION ALL
    SELECT object_name(object_id), index_id, allocation_unit_id
    FROM sys.allocation_units au
    JOIN sys.partitions p ON au.container_id = p.partition_id
    WHERE au.type = 2
) obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id
ORDER BY cached_pages DESC;

結果

• 表變數資料頁**確實緩存在 Buffer Pool**
• 批次結束後緩存頁面隨空間釋放而淘汰

結論總結

實務啟示

• ✅ 少量資料（<1000 列）表變數效能佳，無重編譯
• ⚠️ 大量資料、複雜查詢、需統計資訊 → **暫存表 (#temp) 更好**
• ✅ 表變數不參與交易回滾（資料保留），適合記錄除錯資訊
• ✅ 避免在長時間執行的批次中建立大表變數，佔用 tempdb/記憶體