SQL Server Indexes Maintenance 索引維護

索引為何需要維護
1. External Fragmentation
2. Internal Fragmentation
索引維護
參考資料
相關連結

筆記 SQL Server 如何查詢資料庫下的資料表需進行維護，自動產生出索引維護指令以及其目前的碎片化及資料列數，讓管理者判斷是否執行。

索引為何需要維護

索引維護需要維護的原因就是發生 fragmentation (碎片化)，而 fragmentation 的原因有二，分別是 External Fragmentation 以及 Internal Fragmentation。

External Fragmentation

因資料庫進行資料寫入、更新等異動時，資料頁的空間不足，為了加入新的資料會進行資料更新，於是資料分頁 (page split) 產生，造成邏輯順序與實際順序的不一致。

Internal Fragmentation

源自因資料寫入時，需要進行資料分頁 (page split)，而使資料頁存在過多的空白，造成資料頁數量膨脹，使查詢需要讀取更多的資料頁才能完成。

索引的碎片化程度，可以由 avg_fragmentation_in_percent 所觀察。

索引維護

進行 REBUILD 或者 REORGANIZE 的判斷在於 avg_fragmentation_in_percent，示範腳本是以 30 為分界，此外要大於 10 才會顯示 😋

SELECT ix.name,
	  'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE
              WHEN ps.avg_fragmentation_in_percent > 30
              THEN 'REBUILD'
              ELSE 'REORGANIZE'
       END +
       CASE
              WHEN pc.partition_count > 1
              THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
              ELSE ''
       END 'alter_script',
       ps.avg_fragmentation_in_percent,
	   ps.page_count,
	   p.rows
FROM   sys.indexes AS ix
       INNER JOIN sys.tables t
       ON     t.object_id = ix.object_id
       INNER JOIN sys.schemas s
       ON     t.schema_id = s.schema_id
       INNER JOIN
              (SELECT object_id,
                      index_id,
                      avg_fragmentation_in_percent,
                      partition_number,
					            page_count
              FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
              ) ps
       ON     t.object_id = ps.object_id
          AND ix.index_id = ps.index_id
       INNER JOIN
              (SELECT  object_id,
                       index_id ,
                       COUNT(DISTINCT partition_number) AS partition_count
              FROM     sys.partitions
              GROUP BY object_id,
                       index_id
              ) pc
       ON     t.object_id = pc.object_id
          AND ix.index_id = pc.index_id
	   INNER JOIN 
		sys.partitions p ON ix.object_id = p.OBJECT_ID AND ix.index_id = p.index_id
WHERE  ps.avg_fragmentation_in_percent > 10
   AND ix.name IS NOT NULL