統計資料庫空間使用情況與索引使用率的實用腳本

資料庫空間緊迫（最大單庫 400GB、百 GB 級庫數個）往往暗示應用設計存問題：濫建索引、過度冗余、缺乏歷史資料清理機制。以下兩支腳本助你快速掌握空間分佈與索引效益。

1. 取得每張表/索引空間使用情況

WITH paas AS (
    SELECT p.object_id, p.index_id, a.type_desc AS PageType_desc,
           a.total_pages, a.used_pages, a.data_pages
    FROM sys.partitions p
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
),
indexes AS (
    SELECT object_id, index_id, OBJECT_NAME(object_id) AS tbname,
           name AS indexname, type_desc AS tbtype_desc
    FROM sys.indexes
    WHERE object_id >= 100
),
result AS (
    SELECT i.*, p.PageType_desc, p.total_pages, p.used_pages, p.data_pages
    FROM paas p
    INNER JOIN indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
)
SELECT * FROM result ORDER BY total_pages DESC;

2. 統計索引使用率

DECLARE @dbid INT;
SELECT @dbid = DB_ID();

SELECT OBJECT_NAME(s.object_id) AS objectname, s.object_id,
       i.name AS indexname, i.index_id,
       user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE database_id = @dbid
  AND OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC;

第一支腳本整合 sys.partitions 與 sys.allocation_units，輸出各索引的頁類型、總/已用/資料頁數，按總頁數降序排列，快速定位佔空間大戶。第二支腳本查詢 sys.dm_db_index_usage_stats，統計各索引的搜尋、掃描、查找與更新次數，按總操作數升序排列，低使用率索引優先顯示，利於精簡索引策略。