← 技術筆記

DBA筆記-基本維護知識-1.1-Index索引維護

資料庫 · 2026/8/6

這篇文章是我很久以前經營過的一個網誌搬過來的文章,這是第一篇,之後會持續進行。。

雖然年代已經有點久遠,但我覺得有紀念價值,所以還是複製一份過來。

還好那時沒有寫的太認真,份量不多。

文末的語法並不是我發明的,是從我任職的第一間公司學到的,之後我又配合自己的習慣做了一點細部修改。

但類似的語法網路上蠻多的,所以這應該不是什麼稀奇的法寶。


開宗明義的第一課,扣除安裝與連線和一般操作的初步工作,任何稍微進階的DB使用者,第一個會注意也必須注意的維護工作通常就是Index的建立和使用,即使是一個程式設計師,在做效能調整時也大多會碰到這個問題。

這堪稱是DB維護的第一道門。

下列語法參考了各方資料,你也許在別的地方也可以看到類似的語法。

不同的是這裡已經依我的個人習慣改的面目全非了,同樣的,你也可以再依自己的需求去做變化,但基本上下述語法已經列出了常用的主要資料項目與週邊的評估資料,所列出的資料項目在基礎維護上已經夠用,甚至已經有點太多了。

完整語法在文末提供,而該語法查出來的欄位簡列如下,若只是想做基本的索引維護則只需要注意其中一些較關鍵的資訊即可:



語法如下所示:

DECLARE @NAME INT

SET @NAME =DB_ID()

 

SELECT DatabaseName = IxState.DatabaseName,

OwnerName = SCHEMA_NAME(TableData.schema_id),

TableName = OBJECT_NAME(IX.[object_id]) ,

IndexName = IX.name ,

IX.type_desc,is_primary_key,

lookups = UsageState.system_lookups+UsageState.user_lookups,

seeks = UsageState.system_seeks+UsageState.user_seeks,

SCANS = UsageState.system_scans+UsageState.user_scans,

UPDATES = UsageState.system_updates+UsageState.user_updates,

IxState.avg_fragmentation_in_percent,

IxState.fragment_count,IxState.page_count,

IxState.avg_fragment_size_in_pages,

--[BUILD] script

'USE [' +IxState.DatabaseName +'] ALTER INDEX [' + IX.name + '] ON ['

+ SCHEMA_NAME(TableData.schema_id) + '].[' + OBJECT_NAME(IX.[object_id])  + ']

  REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,ALLOW_ROW_LOCKS

  = ON, ALLOW_PAGE_LOCKS  = ON,SORT_IN_TEMPDB = OFF, ONLINE = ON )'

  COLLATE DATABASE_DEFAULT AS BUILD,

--[REORGANIZE] script

'USE [' +IxState.DatabaseName +'] ALTER INDEX [' + IX.name + '] ON ['

+ SCHEMA_NAME(TableData.schema_id) + '].[' + OBJECT_NAME(IX.[object_id])  +

']  REORGANIZE WITH ( LOB_COMPACTION = ON ) ' COLLATE DATABASE_DEFAULT AS [REORGANIZE],

--Suggest scipt by case

CASE WHEN IxState.fragment_count >=10000 THEN 'USE [' +IxState.DatabaseName +

'] ALTER INDEX [' + IX.name + '] ON [' + SCHEMA_NAME(TableData.schema_id)+ '].['

+ OBJECT_NAME(IX.[object_id]) +

']  REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,SORT_IN_TEMPDB = OFF, ONLINE = ON )'

ELSE 'USE [' +IxState.DatabaseName +'] ALTER INDEX [' + IX.name + '] ON ['

+ SCHEMA_NAME(TableData.schema_id)+ '].[' + OBJECT_NAME(IX.[object_id]) + ']  REORGANIZE WITH ( LOB_COMPACTION = ON ) '    

  COLLATE DATABASE_DEFAULT END AS SUGGEST

FROM (

  SELECT DB_NAME(database_id) AS DatabaseName     ,

  database_id,

  object_id                   ,

  index_id                    ,

  avg_fragmentation_in_percent,

  fragment_count,

  avg_fragment_size_in_pages,

  page_count,INDEX_TYPE_DESC

  FROM sys.dm_db_index_physical_stats(@NAME, NULL, NULL, NULL, NULL)

  WHERE  index_type_desc<>'HEAP' AND alloc_unit_type_desc='IN_ROW_DATA'

)AS  IxState

JOIN sys.indexes AS IX on IX.index_id = IxState.index_id AND IX.[object_id] = IxState.[object_id]

INNER JOIN sys.tables AS TableData ON TableData.object_id = IX.object_id AND TableData.is_ms_shipped = 0

LEFT JOIN sys.dm_db_index_usage_stats  AS UsageState ON UsageState.[object_id] = IX.[object_id]

          AND UsageState.index_id = IX.index_id AND IxState.database_id = UsageState.database_id

ORDER BY OBJECT_NAME(IX.[object_id]) ,IX.type_desc,IX.name

 

留言

還沒有留言,來當第一個吧。

寫留言