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

標籤：parallel   logical   ble   檔案   選項   執行   sql   它的   prim   

-- 建立叢集索引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)最佳計數與實際計數相差較大重建索引。

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