[SQL SERVER][Maintain]管理交易記錄檔(5)
這篇我來說說常見讓交易記錄檔暴增的第二個原因，
我相信每個DBA都知道索引碎片所帶來的效能影響，
我也看到大部分的DBA都會建立索引維護計劃來查看索引碎片問題，
該作業大部分也都是在完整復原模式下採取 Online Index Rebuild 方法，
我不是說這樣做不好，而是或許你可以考慮利用 Index Reorganization 來取代 Index Rebuild，
除非索引碎片太過離譜才使用 Index Rebuild，
但我個人還是不建議用刪除索引(drop index)，然後在建立索引(create index)的方式來處理(將處理nonclustered index 2次)。
實務上我曾經在一台 8 CPU Server 執行Index Rebuild，
我利用午休時間針對800百萬筆的 Clustered Index 執行平行 Rebuild，
大概整整花了我快 40分鍾時間才重建完成，
但這完成時間只是一個大概(因為影響因素還滿多的)，
如果環境中該資料表有更多的 NonClustered Index、
存在CPU資源競爭或實體(外部)I/O碎片過多的話，
那完成時間可能還要拉更長，而DBA一定不希望看到下午大家開工時，
索引重建作業還沒完成的窘境，
下面我簡單說明一下兩種索引方法，針對交易記錄檔容量所帶來的差異，
如果你的索引相當龐大的話，你將清楚知道為什麼我會這麼說。
重建或重新組織索引建議值
實際碎片臨界值，請依資料庫環境判斷較佳。
ps:實務上我個人設定超過50%才會手動執行 index rebuild，
否則都執行Index Reorganize(>10% and <=50%)。
查詢現有資料庫所有索引碎片(建議透過 sys.dm_db_index_physical_stats DMV 調查)
SELECT ps.database_id, ps.OBJECT_ID,object_name(ps.OBJECT_ID) as 'TableName',
ps.index_id, b.name as 'IndexName',
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
AND b.name is not null
ORDER BY ps.avg_fragmentation_in_percent desc, ps.OBJECT_ID
重建或重新組織索引(建議透過 sys.dm_db_index_physical_stats DMV 調查)SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
CASE
WHEN ps.avg_fragmentation_in_percent > 50 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
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
索引重建(Index Rebuild)
ALTER INDEX ALL ON dbo.mytbl REBUILD
ALTER INDEX idx1 ON dbo.mytbl REBUILD刪除現有索引後再重新建立，由於會配置新頁面，這時將重新排序連續頁面中的索引資料列可以線上或離線執行，而當指定 ALL 時，會在單一交易中卸除和重建資料表的所有索引，
且根據指定或現有的填滿因數設定壓縮頁面，並回收磁碟空間。
簡單整理如下:
1.使用較多CPU和系統資源。
2.單一交易執行，如果索引資料龐大，
即表示是一個長時間交易，Lock資源時間更長，
且交易記錄檔無法重用內部空間。
3.因為重新配置新頁面，所以會增加頁面分割頻率。
4.使用更多的交易記錄檔空間。
假設針對一個10GB的索引執行重建(Index Rebuild)，
那麼將需要大於10GB的交易記錄檔空間才能完成該作業，
因為SQL Server需要一些額外空間來存放rollback記錄。
執行 Index Rebuild 注意事項:
1.資料庫高峰其間，請勿執行Index Rebuild
因為會鎖定資源，造成使用者一些問題，應該選擇資料庫非高鋒時間處理。
2.如果要重新建立叢集索引，請勿使用刪除再建立方式
假設該資料表還有其他的非叢集索引，
那麼該作業將造成非叢集索引執行兩次重建作業，
浪費系統資源並拖長作業完成時間。
3.定期重組實體 Data and Log file減少碎片
在你要執行重建大索引前，可以先確認磁碟碎片問題是否嚴重，
讓實體檔案連續將可大大加快Index Rebuild作業效能速度。
索引重新組織(Index Reorganization)
ALTER INDEX ALL ON dbo.mytbl REORGANIZE
ALTER INDEX idx1 ON dbo.mytbl REORGANIZE
重新調整組織分葉層級頁面的順序，一律是線上執行(online)，
以符合分葉節點的邏輯順序 (由左至右)，
將頁面依序排列將可改善索引掃描的效能，
索引是在現有頁面內重新組織，不會再配置新頁面，
也會根據指定或現有的填滿因數設定壓縮頁面，來回收磁碟空間。
簡單整理如下:
1.使用最低CPU和系統資源。
2.多個短暫交易處理，所以不會長時間Lock資源，
且不會Block其他執行中的查詢或更新。
3.因為不會分配新頁面，所以不會發生頁面分割。
4.使用比 Index Rebuild較少的交易記錄檔空間。
假設針對一個10GB的索引執行重新組織(Index Reorganization)，
那麼只需要少於10GB的交易記錄檔空間即可完成作業，
因為每個page只是重新調整分葉層級葉面順序，
並且使用多各短暫交易來處理，以利重複使用交易記錄檔內部空間。
結論:
不管你在那種復原模式下，
想要減少索引維護作業索產生的交易記錄檔大小，
那就是盡量利用Index Reorganization 來取代 Index Rebuild，
並且只保留真正有用索引。
Note:填滿因素( fill factor )
我這裡不詳細說明填滿因素作用，
但該設定值請一定要依照現有環境資料表讀寫特性多加測試後才比較可靠，
因為填滿因素設定過低將增加頁面分割頻率(這是一項耗用大量資源作業)，
同時也造成碎片過多(增加I/O作業)影響查詢效能和增加Lock資源時間，
但填滿因素設定過高，將導致資料異動效能降低，
所以我前面才說一定要經過測試才可靠( 設定 0 和 100 是一樣的)，
該值作用只適用一開始建立。
下面是我個人填滿因素簡單設定
唯讀或靜態資料表: 設定 100%
異動頻率很低的資料表: 設定 95%
異動頻率一般的資料表: 設定 80%~90%
異動頻率很高的資料表: 設定 50%-70%
參考