環境與版本

作業系統:Window10 64x 版本:2004

資料庫產品版本:Microsoft SQL Server Developer (64-bit) v18.4

資料庫版本編號:15.0.2070.41

查詢資料庫中索引破碎狀態

工作上大量運用到SQL SERVER ,當資料庫中的索引碎裂(index fragmentation)程度過高時,索引的效率就會大大降低,為了避免這個問題發生,就必須定時替資料庫健檢(維護資料庫),也就是進行索引重建(rebuild)或索引重組(reorganize)。

SELECT 指令搭配 sys.dm_db_index_physical_stats 這個動態管理函示(DMF, Dynamic Management Function) 可以查出資料庫中所有索引的碎裂狀態,如下 T-SQL 語法:

-- =============================================
-- Author:		Steven玄
-- ALTER date:  20210120
-- Description:	查詢資料表內的索引破碎化百分比情形   
-- =============================================

SELECT OBJECT_NAME(dt.object_id)    as [TableName]   ,	--資料表名稱
       si.name                      as [IndexName]   ,	--索引名稱
       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
       FROM    sys.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

索引重建與索引重組的判斷時機如下:

索引重組的時機

ALTER INDEX REORGANIZE
  • 檢查 External fragmentation 部分
  • 當 avg_fragmentation_in_percent 的值介於 10 到 15 之間
  • 檢查 Internal fragmentation 部分
  • 當 avg_page_space_used_in_percent 的值介於 60 到 75 之間

索引重建的時機

ALTER INDEX REBUILD WITH (ONLINE = ON) 
  • 檢查 External fragmentation 部分
  • 當 avg_fragmentation_in_percent 的值大於 15
  • 檢查 Internal fragmentation 部分
  • 當 avg_page_space_used_in_percent 的值小於 60

索引破碎修復產生執行字串

自動找出資料庫內哪些索引需要被重建或重組,且把 ALTER INDEX 的 T-SQL語法寫好

-- =============================================
-- Author:		Steven玄
-- ALTER date:  20210120
-- Description:	依照破碎程度分類重組或重建並產生修復執行字串   
-- =============================================
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 as [修復語法],
       avg_fragmentation_in_percent,
	   CASE
              WHEN ps.avg_fragmentation_in_percent > 15			--破碎程度 判斷使用重組() 還是使用 重建()
              THEN 'REBUILD'
              ELSE 'REORGANIZE'
       END as [判斷重組或重建]
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
              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
WHERE  ps.avg_fragmentation_in_percent > 10						--需要進行重組或重建的破碎程度 條件
   AND ix.name IS NOT NULL

參考或引用資料:

藉由重新組織或重建索引來解決索引片段:https://docs.microsoft.com/zh-tw/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

MS SQL Server 索引重建或重組標準與T-SQL語法:https://dotblogs.com.tw/cheng_syun/2018/04/10/120246

讓 SQL Server 告訴你有哪些索引應該被重建或重組:https://blog.miniasp.com/post/2009/01/18/Let-SQL-Server-Tell-You-Which-Indexes-to-Rebuild-or-Reorganize

建立索引(1)-叢集與非叢集索引:https://vito-note.blogspot.com/2013/05/blog-post_5510.html

Steven玄

謝謝您觀看本站內容!! 😅 西元93年台灣男,軟體前、後、資料庫工程師 和 多元收入實踐,程式設計、網站系統規劃、商業策略分析規劃、多元收入研究,目前在網站開發公司擔任工程師。

This Post Has One Comment

  1. s0341969

    請問這修復建議多久跑一次呢???
    每天跑合適嗎?? 謝謝S

發佈留言