SQL Server 索引優化實戰：空間佔用、填充因子、碎片整理與重建腳本

1. 索引佔用大量空間

測試表 ppp 僅 1 列資料，建立非叢集索引後空間從 16KB 增至 32KB（索引佔 24KB）。

2. 主鍵自動建立叢集索引常被浪費

主鍵鮮少作為查詢條件，叢集索引應給予範圍查詢、排序、聯結高頻欄位。

3. 填充因子 設定建議

• 填充因子大 → 頁面疏鬆、插入快、查詢慢（頁面分裂少但掃描多）。
• 填充因子小 → 頁面緊湊、查詢快、插入慢（頻繁分裂）。
• 建議 40~50 平衡；SQL Server 2005+ 預設 0 (同 100)，需手動調整。

4. 定期分析碎片 DBCC SHOWCONTIG

DBCC SHOWCONTIG ('join_product_ty', 'PK_join_product_ty');
-- 關鍵指標：
-- Logical Scan Fragmentation (邏輯掃描碎片) < 10% 為佳
-- Extent Scan Fragmentation (區塊掃描碎片) < 5% 為佳
-- Avg Page Density (平均頁密度) 越接近 100% 越好

5. 定期重建所有索引腳本

DECLARE @table_name sysname, @sql nvarchar(max);
DECLARE cur CURSOR FOR SELECT name FROM sysobjects WHERE xtype='u';
OPEN cur;
FETCH NEXT FROM cur INTO @table_name;
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @sql = 'DBCC DBREINDEX(' + QUOTENAME(@table_name) + ', '''', 0)';
    EXEC (@sql);
    PRINT @table_name + ' 重建完成';
    FETCH NEXT FROM cur INTO @table_name;
END
CLOSE cur; DEALLOCATE cur;
DBCC CHECKDB;

配合維護計畫排程執行，維持索引健康。