iT邦幫忙

DAY 10
12

SQL SERVER 2008效能監控與最佳化系列 第 10

Day10_淺談Index與FRAGMENTATION

若Index有FRAGMENTATION的情況會嚴重的影響查詢效能,如何有效的觀察碎裂並維護Index是一個相當重要的課題。
在Day09_淺談INDEX與FillFactor有提到當Index存放資料的Page已滿時,如果新增資料就會產生一個新的Page存放資料,而導致索引的資料不連貫而降低索引效能,此時就會產生外部碎裂(External FRAGMENTATION)的問題。

另外當資料被刪除時,Index存放到Page上的資料也會被刪除,而導致在Page中會有因刪除資料而產生的空間,這些空間會造成索引資料的不連續,此時就會產生內部碎裂(Internal FRAGMENTATION)的問題。

管理FRAGMENTATION可以使用dm_db_index_physical_stats搭配sys.indexes查看index碎裂程度。

以下是察看index碎裂程度的程式碼:

--查看INDEX的碎裂程度
SELECT DB_NAME() DBName,i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [FragmentationPercentage]
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
ORDER BY 3 desc

執行結果:

在上述執行結果我們發現PXML_Store_Demographics的碎裂程度高達98.04,所以我們必須REBUILD INDEX

REBUILD INDEX語法為:

--REBUILD INDEX
ALTER INDEX [PXML_Store_Demographics] ON [Sales].[Store] REBUILD

執行完成後,在重新查看INDEX的碎裂情況:

發現有相當碎裂程度有相當大的改善。

結論:使用dm_db_index_physical_stats搭配sys.indexes觀察Index的碎裂情況,若發現碎裂程度過大可以使用Alter Index來改善。


上一篇
Day09_淺談INDEX與FillFactor
下一篇
Day11_淺談Index與Statistics
系列文
SQL SERVER 2008效能監控與最佳化30

1 則留言

0
kradark
iT邦好手 1 級 ‧ 2011-10-19 01:33:36

williamlukof提到:
內部碎裂(External FRAGMENTATION)

讀者來函,中英對照不符

已修改內部碎裂(Internal FRAGMENTATION),謝謝你的提醒。

我要留言

立即登入留言