SQL Server 表空間佔用查詢指南：sysindexes、sp_spaceused 與現代 DMV 方案對比

方法一：sysindexes 系統表查詢 (SQL Server 2000/2005 相容)

SELECT 
    OBJECT_NAME(id) AS TableName,
    8 * reserved / 1024 AS Reserved_MB,
    RTRIM(8 * dpages / 1024) + ' MB' AS Used_MB,
    8 * (reserved - dpages) / 1024 AS Unused_MB,
    8 * dpages / 1024 - rows / 1024.0 * minlen / 1024 AS Free_MB,
    rows AS RowCount
FROM sysindexes
WHERE indid IN (0, 1)  -- 0=堆表, 1=叢集索引
ORDER BY Reserved_MB DESC;

• reserved：配置頁數 (含數據、索引、未使用)
• dpages：實際使用頁數
• rows：列數 (統計資訊可能過期)
• minlen：最小列長度
• 單位：頁 (8KB)，除以 1024 換算 MB

方法二：sp_spaceused 系統預存程序

單表查詢

EXEC sp_spaceused 'dbo.Orders';

回傳兩個結果集：

1. 表級摘要：name、rows、reserved、data、index_size、unused
2. 索引級明細 (若有多個索引)

批次查詢所有使用者表

CREATE TABLE #SpaceUsed (
    Name SYSNAME,
    Rows BIGINT,
    Reserved VARCHAR(50),
    Data VARCHAR(50),
    IndexSize VARCHAR(50),
    Unused VARCHAR(50)
);

DECLARE @SchemaName SYSNAME, @TableName SYSNAME;
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT s.name, t.name
    FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id;

OPEN cur;
FETCH NEXT FROM cur INTO @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0 BEGIN
    DECLARE @FullName NVARCHAR(500) = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
    DECLARE @SQL NVARCHAR(MAX) = 'EXEC sp_spaceused ' + QUOTENAME(@FullName, '''');
    INSERT INTO #SpaceUsed EXEC (@SQL);
    FETCH NEXT FROM cur INTO @SchemaName, @TableName;
END
CLOSE cur; DEALLOCATE cur;

SELECT 
    Name,
    CAST(REPLACE(Reserved, ' KB', '') AS BIGINT) / 1024.0 AS Reserved_MB,
    CAST(REPLACE(Data, ' KB', '') AS BIGINT) / 1024.0 AS Data_MB,
    CAST(REPLACE(IndexSize, ' KB', '') AS BIGINT) / 1024.0 AS Index_MB,
    CAST(REPLACE(Unused, ' KB', '') AS BIGINT) / 1024.0 AS Unused_MB,
    Rows
FROM #SpaceUsed
ORDER BY Reserved_MB DESC;

DROP TABLE #SpaceUsed;

方法三：現代 DMV 方案 (SQL Server 2005+ 推薦，準確度最高)

sys.dm_db_partition_stats (最常用)

SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    p.partition_number,
    p.row_count,
    (p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count) * 8 / 1024.0 AS Used_MB,
    (p.in_row_reserved_page_count + p.lob_reserved_page_count + p.row_overflow_reserved_page_count) * 8 / 1024.0 AS Reserved_MB,
    CASE 
        WHEN p.in_row_reserved_page_count > 0 
        THEN (p.in_row_reserved_page_count - p.in_row_data_page_count) * 8 / 1024.0 
        ELSE 0 
    END AS Unused_MB
FROM sys.dm_db_partition_stats p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
ORDER BY Reserved_MB DESC;

sys.allocation_units + sys.partitions (更精確，含壓縮/稀疏欄位)

SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    p.partition_number,
    p.rows,
    au.type_desc AS AllocUnitType,
    au.total_pages * 8 / 1024.0 AS Reserved_MB,
    au.used_pages * 8 / 1024.0 AS Used_MB,
    (au.total_pages - au.used_pages) * 8 / 1024.0 AS Unused_MB
FROM sys.allocation_units au
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
ORDER BY Reserved_MB DESC;

三種方法比較

進階：含索引大小明細的完整查詢

SELECT 
    OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
    t.name AS TableName,
    SUM(p.rows) AS TotalRows,
    -- 數據空間 (叢集索引/堆表)
    SUM(CASE WHEN i.index_id IN (0,1) THEN p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count ELSE 0 END) * 8 / 1024.0 AS Data_MB,
    -- 索引空間 (非叢集索引)
    SUM(CASE WHEN i.index_id > 1 THEN p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count ELSE 0 END) * 8 / 1024.0 AS Index_MB,
    -- 總保留空間
    SUM(p.in_row_reserved_page_count + p.lob_reserved_page_count + p.row_overflow_reserved_page_count) * 8 / 1024.0 AS Total_MB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.dm_db_partition_stats p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE t.is_ms_shipped = 0
GROUP BY t.object_id, t.name
ORDER BY Total_MB DESC;

常見問題與注意事項

• 統計資訊過期：sysindexes.rows、sp_spaceused 依賴統計資訊，大量異動後執行 UPDATE STATISTICS 或 DBCC UPDATEUSAGE 校準
• DBCC UPDATEUSAGE (DatabaseName)：修正系統表頁面計數，耗資源，維護窗口執行
• 壓縮表：資料壓縮後 used_pages 反映壓縮後大小，reserved_pages 為配置大小
• 記憶體優化表：不在 dm_db_partition_stats 中，需查 sys.dm_db_xtp_table_memory_stats
• TempDB 表：同上方法適用，但生命週期短，查詢時機關鍵

自動化監控建議

1. 建立定時作業 (Agent Job) 每日執行 DMV 查詢，寫入歷史表
2. 設定閾值告警：單表 > 100GB、總資料庫 > 80% 配置、異常增長率 (>20%/天)
3. 整合至 Grafana/Power BI 儀表板，視覺化容量趨勢
4. 定期輸出 Top 20 大表報表給開發團隊，推動歸檔/分區/壓縮優化