SQL Server 資料空間配置分析：快速掌握各表儲存體使用情況

當資料庫檔案成長過快，需要快速找出佔用空間最大的資料表。本文提供一個預存程序，遍歷所有使用者資料表呼叫 sp_spaceused，彙總列數、保留空間、資料空間、索引空間、未使用空間，並依保留空間遞減排序，幫助 DBA 秒級定位膨脹熱點。

完整分析腳本

-- 1. 建立暫存彙總表
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tablespaceinfo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE tablespaceinfo (
    nameinfo VARCHAR(50),
    rowsinfo INT,
    reserved VARCHAR(20),
    datainfo VARCHAR(20),
    index_size VARCHAR(20),
    unused VARCHAR(20)
);
GO

-- 2. 清空並重新統計
TRUNCATE TABLE tablespaceinfo;
GO

DECLARE @tablename VARCHAR(255), @cmdsql VARCHAR(500);
DECLARE cur CURSOR FOR
    SELECT o.name FROM dbo.sysobjects o
    WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1
      AND o.name NOT LIKE N'#%%'
    ORDER BY o.name;

OPEN cur;
FETCH NEXT FROM cur INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        INSERT INTO tablespaceinfo EXEC sp_spaceused @tablename;
    END
    FETCH NEXT FROM cur INTO @tablename;
END;
CLOSE cur; DEALLOCATE cur;
GO

-- 3. 更新使用量並顯示結果 (依保留空間 MB 遞減)
EXEC sp_spaceused @updateusage = 'TRUE';

SELECT *
FROM tablespaceinfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved))) - 2) AS INT) DESC;

輸出欄位說明

進階：直接查詢 DMV (SQL 2005+ 適用)

SELECT
    OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
    OBJECT_NAME(p.object_id) AS TableName,
    p.rows AS RowCount,
    CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(12,2)) AS ReservedMB,
    CAST(SUM(a.used_pages) * 8. / 1024 AS DECIMAL(12,2)) AS UsedMB,
    CAST(SUM(a.data_pages) * 8. / 1024 AS DECIMAL(12,2)) AS DataMB,
    CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8. / 1024 AS DECIMAL(12,2)) AS UnusedMB
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.index_id IN (0,1)  -- 堆叠或叢集索引
GROUP BY p.object_id, p.rows
ORDER BY ReservedMB DESC;

DMV 版本不需游標、效能更佳、單位直接為 MB，建議 SQL 2005 以上環境優先使用。

實務操作建議

• 定期排程：每日離峰時段跑一次，記錄歷史趨勢
• 關注 Top 10：前 10 大表通常佔 80% 空間，優先檢查是否有未清理的 Log/暫存資料
• 索引碎片：index_size 佔比高時檢查碎片率，考慮 REBUILD/REORGANIZE
• 資料壓縮：大型資料倉庫表啟用 PAGE 壓縮可省 40-60% 空間