SQL Server Indexes Maintenance 索引維護


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

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

SQL Server Logo

索引為何需要維護

索引維護需要維護的原因就是發生 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

參考資料

讓 SQL Server 告訴你有哪些索引應該被重建或重組

How to identify and resolve SQL Server Index Fragmentation

SQL Server Performance And Tuning

相關連結

SQL Server 閃電般快速查詢指南⚡