檢查資料庫之資料表索引是否應該重建或重組

MSSQL 檢查資料庫之資料表索引是否應該重建或重組

本文是學習筆記，內容來自於 [1] ，摘錄語法重點

目的

索引維護，改善效能

範例

檢查索引狀態

引用 [1] 之範例

SELECT OBJECT_NAME(dt.object_id) as TableName  ,
   si.name,
   dt.avg_fragmentation_in_percent,
   dt.avg_page_space_used_in_percent
FROM
   (SELECT object_id   ,
   index_id,
   avg_fragmentation_in_percent,
   avg_page_space_used_in_percent
   FROMsys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')
   WHERE   index_id <> 0
   ) AS dt --does not return information about heaps
   INNER JOIN sys.indexes si
   ON si.object_id = dt.object_id
  AND si.index_id  = dt.index_id

根據 [1] 之說明，重建或重組之建議條件如下

索引重組的時機

• 檢查 External fragmentation 部分

  當 avg_fragmentation_in_percent 的值介於 10 到 15 之間
  

• 檢查 Internal fragmentation 部分

  當 avg_page_space_used_in_percent 的值介於 60 到 75 之間
  

索引重建的時機

• 檢查 External fragmentation 部分

  當 avg_fragmentation_in_percent 的值大於 15
  

• 檢查 Internal fragmentation 部分

  當 avg_page_space_used_in_percent 的值小於 60
  

重建索引

引用 [1] 之範例, 僅檢查 External Fragmentation

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
   CASE
  WHEN ps.avg_fragmentation_in_percent > 15
  THEN 'REBUILD'
  ELSE 'REORGANIZE'
   END +
   CASE
  WHEN pc.partition_count > 1
  THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
  ELSE ''
   END,
   avg_fragmentation_in_percent
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
  FROMsys.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
WHERE  ps.avg_fragmentation_in_percent > 10
   AND ix.name IS NOT NULL

有需要重建的索引，其重建索引語法會被列出來，複製後執行即可完成索引之維護

參考資料介紹

[1] 介紹索引重建的語法及重建時機

[2] 介紹什麼是索引，其原理，資料結構，索引類型，等等知識。 (含範例)，這篇很詳細，篇末還有索引維謢的線上教學影片

參考資料

[1] The Will will Web - 讓 SQL Server 告訴你有哪些索引應該被重建或重組

[2] TechNet 台灣部落格 - 如何寫出高效能 TSQL - 關於索引不可不知道的事