SQL Server片段收集及維護

標籤：sql片段收集   sql片段維護   

在對錶進而對錶中定義的索引進行資料修改（INSERT、UPDATE 和DELETE 語句）的整個過程中都會出現片段。由於這些修改通常並不在表和索引的行中平均分布，所以每頁的填充度會隨時間而改變。對於掃描表的部分或全部索引的查詢，這種片段會導致附加的頁讀取。從而延緩了資料的並行掃描。

可以通過內建sys.dm_db_index_physical_stats函數瞭解索引的片段情況，此函數返回指定表或視圖的資料和索引的大小和片段資訊。對於索引，針對每個分區中的 B 樹的每個層級，返回與其對應的一行。對於堆，針對每個分區的 IN_ROW_DATA 配置單位，返回與其對應的一行。對於大型物件 (LOB) 資料，針對每個分區的 LOB_DATA 配置單位返回與其對應的一行。

通過sys.dm_db_index_physical_stats 函數擷取片段超過10的表和索引的資訊,代碼如下：

    SET NOCOUNTON;

[email protected] INT;

[email protected]=DB_ID()

SELECTobject_id ,index_id ,partition_number,avg_fragmentation_in_percent

INTO#cc

FROMsys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, ‘LIMITED‘)

WHEREavg_fragmentation_in_percent > 10.0 AND index_id > 0

 

selectt2.name,t2.schema_id,t1.* into #dd from #cc t1,sys.objects t2

wheret1.object_id=t2.object_id

orderby t2.name

 

selectt2.name+‘.‘+t1.name as tablename ,t1.* into #ee

from#dd t1,sys.schemas t2

wheret1.schema_id=t2.schema_id

 

select t1.tablename,t2.nameasindexname,t1.avg_fragmentation_in_percent,

t1.partition_number from #ee t1,sys.indexes t2

wheret1.index_id=t2.index_id and t1.object_id=t2.object_id

orderby t1.name

通過以上代碼，索引或堆的片段層級顯示在 avg_fragmentation_in_percent 列中，對於堆，此值表示堆的區片段。對於索引，此值表示索引的邏輯片段。為了獲得最佳效能，avg_fragmentation_in_percent 的值應儘可能接近零。但是，從0 到 10％ 範圍內的值都可以接受。超過此範圍的，就需考慮進行維護以提高SQL效能：

其中有三種方法可減少片段：

1. 刪除並重新建立叢集索引。重新建立叢集索引將對資料進行重新分配，從而使資料頁填滿。填充度可以使用CREATE INDEX 中的 FILLFACTOR 選項進行配置。

    

2. 片段在10%至30%之間，進行重新組織索引，使用 ALTER INDEX REORGANIZE按邏輯順序重新排序索引的葉級頁。

如：ALTER INDEXindexname on tablename  REORGANIZEwith(online=on)

 

 3.使用 ALTER INDEX REBUILD聯機或離線重建索引.

如：ALTER INDEXindexname on tablename  REBUILD With(FillFactor = 90 , Online= On)

 

註：1.由於分區表不支援聯機重建，因此對分區表僅重建有索引片段的分區；非分區表全表聯機重建，設定填滿因數為90; partition_number>1為分區表

 

本文出自 “什麼是我們的最愛” 部落格，請務必保留此出處http://pxizhi.blog.51cto.com/5283742/1602447

SQL Server片段收集及維護