sql server 索引闡述系列五 索引參數與碎片

原文: sql server 索引闡述系列五 索引參數與碎片

-- 建立聚集索引
create table [dbo].[pub_stocktest] add  constraint [pk_pub_stocktest] primary key clustered 
(
[sid] asc
)with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, 
online = off, allow_row_locks = on, allow_page_locks = on) on [primary]

-- 建立非聚集索引
 create nonclustered index [ix_model] on [dbo].[pub_stocktest]
(
    [model] asc
)
include (     [name]) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, 
online = off, allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 85) on [primary]      

1.1 Filefactor參數

　　使用Filefactor可以對索引的每個葉子分頁存儲保留一些空間。對于聚集索引，葉級别包含了資料，使用Filefactor來控制表的保留白間，通過預留的空間，避免了新的資料按順序插入時，需騰出空位而進行分頁分隔。

　　Filefactor設定生效注意，隻有在建立索引時才會根據已經存在的資料決定預留的空間大小，如裡需要可以alter index重建索引并重置原來指定的Filefactor值。

　　在建立索引時，如果不指定Filefactor，就采用預設值0 也就是填充滿，可通過sp_configure 來配置全局執行個體。Filefactor也隻就用于葉子級分頁上。如果要在中間層控制索引分頁，可以通過指定pad_index選項來實作.該選擇會通知到索引上所有層次使用相同的Filefactor。Pad_index也隻有索引在建立或重建時有用。

1.2 Drop_existing 參數

　　删除或重建一個指定的索引作為單個事務來處理。該項在重建聚集索引時格外有用，當删除一個聚集索引時，sqlserver會重建每個非聚集索引以便将書簽從聚集索引鍵改為RID。如果再建立或者重建聚集索引，Sql server會再一次重建全部的非聚集索引，如果再建立或重建的聚集索引鍵值相同，可以設定Drop_existing=ON。

1.3 IGNORE_DUP_KEY

　　是指如果一個update或者insert語句影響多行資料，但有一行鍵被發現産生重值時，整個語句就會復原，IGNORE_DUP_KEY=on時産生重複鍵值時不會引起整個語句的復原，重複的行會被舍棄其它的行會被插入或更新。

1.4 Statistics_norecompute

　　選項決定了是否需要自動更新索引上的統計，每個索引維護着該索引首位字段的數值分布的柱狀圖，在查詢執行計劃時，查詢優化器利用這些統計資訊來判斷一個特定索引的有效性。當資料達到一個閥值時，統計值會變。Statistics_norecompute選項允許一個關聯的索引在資料修改時不自動更新統計值。該選擇覆寫了auto_update_statistics的on值。

1.5 ONLINE 　　

　　值預設OFF, 索引操作期間,基礎表和關聯的索引是否可用于查詢和資料修改操作。

　　當值為ON時,能夠繼續對基礎表和索引進行查詢或更新，但在短時間内擷取sch_m架構修改鎖，必須等待此表上的所有阻塞事務完成，在操作期間，此鎖會阻止所有其它事務。

　　當值為OFF時，可以會擷取共享鎖，防止更新基礎表，但允許讀操作。

1.6 MAXDOP

　　索引操作期間替代max degree of parallelism 執行個體配置，預設值為0， 根據目前系統工作負荷使用實際數量的處理器。

1.7 包含性列(included columns)

　　包含列隻在葉級别中出現，不控制索引行的順序，它作用是使葉級别包含更多資訊進而覆寫索引的調優能力，覆寫索引隻出現在非聚集索引中，在葉級别就可以找到滿足查詢的全部資訊。

1.8 on [primary]

　　在建立索引時 create index 最後一個子句允許使用者指定索引被放置在哪裡。可以指定特定的檔案組或預定義的分區方案。預設存放與表檔案組相同一般都是主檔案組中。

1.9限制和索引

　　　　當我們建立主鍵或者唯一性限制時，會建立一個唯一性索引，被建立出來支援限制的索引名稱與限制名稱相同。

　　限制是一個邏輯概念，而索引是一個實體概念，建立索引實際是建立一個占用存儲空間并且在資料修改操作中必須得到維護的實體結構。

　　建立限制就索引内部結構或優化器的選擇來看是沒有差別的。

二 索引碎片　　

　　2.1 SHOWCONTIG 

--   SQLserver 2000使用SHOWCONTIG檢視索引碎片 (已過時)
dbcc SHOWCONTIG (tablename,'indexname')       

　　例如下面查詢一個PUB_StockCollect表下的IX_StockModel索引

　　(1)Page Scanned-掃描頁數：如果你知道行的近似尺寸和表或索引裡的行數，那麼你可以估計出索引裡的頁數。看看掃描頁數，如果明顯比你估計的頁數要高，說明存在内部碎片。

　　(2)Extents Scanned-掃描擴充盤區數：用掃描頁數除以8,四舍五入到下一個最高值。該值應該和DBCC SHOWCONTIG傳回的掃描擴充盤區數一緻。如果DBCC SHOWCONTIG傳回的數高，說明存在外部碎片。碎片的嚴重程度依賴于剛才顯示的值比估計值高多少。 

　　(3)Extent Switches-擴充盤區開關數：該數應該等于掃描擴充盤區數減1。高了則說明有外部碎片。

　　(4)Avg. Pages per Extent-每個擴充盤區上的平均頁數：該數是掃描頁數除以掃描擴充盤區數，一般是8。小于8說明有外部碎片。

　　(5)Scan Density [Best Count:Actual Count]-掃描密度［最佳值:實際值］：DBCC SHOWCONTIG傳回最有用的一個百分比。這是擴充盤區的最佳值和實際值的比率。該百分比應該盡可能靠近100％。低了則說明有外部碎片。

　　(6)Logical Scan Fragmentation-邏輯掃描碎片：無序頁的百分比。該百分比應該在0％到10％之間，高了則說明有外部碎片。

　　(7)Extent Scan Fragmentation-擴充盤區掃描碎片：無序擴充盤區在掃描索引葉級頁中所占的百分比。該百分比應該是0％，高了則說明有外部碎片。

　　(8)Avg. Bytes Free per Page-每頁上的平均可用位元組數：所掃描的頁上的平均可用位元組數。越高說明有内部碎片，不過在你用這個數字決定是否有内部碎片之前，應該考慮fill factor（填充因子）。

　　(9)Avg. Page Density (full)-平均頁密度（完整）：每頁上的平均可用位元組數的百分比的相反數。低的百分比說明有内部碎片。

　　總結：(1)邏輯掃描碎片：越低越好 (2)平均頁密度:80%左右最好，低于%60重建索引，(3)最佳計數與實際計數相差較大重建索引。