Microsoft SQL Server 2000 索引磁碟重組最佳實務(上)

Microsoft SQL Server 2000 索引磁碟重組最佳實務

來源：Microsoft TechNet
作者：Mike Ruthruff
時間：February 2003

Summary As Microsoft SQL Server 2000 maintains indexes to reflect
updates to their underlying tables, these indexes can become
fragmented. Depending on workload characteristics, this fragmentation
can ……

——————————————————————————–

摘要 既然SQL Server
2000為了反應資料的更新，需要維護表上的索引，因而這些索引會形成片段。根據工作量的特徵，這些片段會影響對應的工作效能。該白皮書提供能協助你決定
是否需要整理片段以改善效能的資訊。SQL Server 2000提供了一些命令來實現索引的磁碟重組。這裡比較其中兩個命令：DBCC
DBREINDEX 和 DBCC INDEXDEFRAG。

目錄

概述
瞭解片段
整理片段前需要考慮的因素
小規模環境 vs. 大規模環境
決定何時進行索引磁碟重組
DBCC DBREINDEX vs. DBCC INDEXDEFRAG
結論
更多資訊
附錄 A: 測試環境

概述

本白皮書提供在生產環境中，決定是否進行索引的磁碟重組工作以改善工作效能的資訊。另外，本文比較了Microsoft SQL Server
2000中用於索引磁碟重組的兩個命令：DBCC DBREINDEX 和 DBCC
INDEXDEFRAG。這個比較包括不同的資料庫和硬體環境的測試結果。關於測試環境，請見章節"小規模環境 vs.
大規模環境"和附錄A。

注意: 並不是在任何情況下，磁碟重組都會改善效能。每個情境是不同的。也因為如此，所以是否要進行磁碟重組工作要根據分析結果而定。

白皮書敘述索引磁碟重組的重要性以及常規處理流程。下面列舉本文的關鍵觀點：

在索引磁碟重組前，請確保系統資源的一些問題，比如物理磁碟片段，不合理的基礎結構等因素會給效能帶來負面影響。
DBCC SHOWCONTIG可以顯示索引片段數量。當運行該命令時，要特別注意邏輯片段(Logical
Fragmentation)和頁密度(Page Density)兩個指標。
決定是否要磁碟重組，考察工作類型很重要。不是所有情況下，都能從磁碟重組中受益。對讀取比較多的工作類型來說，磁碟I/O是最重要的效能指標。測試顯示
決策支援系統(DSS: Decision Support System)比很多線上交易處理系統(OLTP: Online Transaction
Processing)，從磁碟重組中獲益更多。
片段將影響磁碟效能和SQL Server預讀管理(read-ahead
manager)的效果。Windows效能監控器有幾個關鍵計量可以用來支援這一觀點。
決定是否用 DBCC DBREINDEX 還是 DBCC INDEXDEFRAG 取決於你的需求以及硬體環境。
DBCC DBREINDEX會帶來更新統計(updating statistics)的副作用，而DBCC
INDEXDEFRAG不會。可以通過在執行DBCC INDEXDEFRAG後執行Update
STATISTICS來增加其影響。

瞭解片段

當索引所在頁面的基於主關鍵字的邏輯順序，和資料檔案中的物理順序不匹配時，片段就產生了。所有的葉級頁包含了指向前一個和後一個頁的指標。這樣就
形成一個雙鏈表。理想情況下，資料檔案中頁的物理順序會和邏輯順序匹配。整個磁碟的工作效能在物理順序匹配邏輯順序時將顯著提升。對某些特定的查詢而言，
這將帶來極佳的效能。當物理排序和邏輯排序不匹配時，磁碟的工作效能會變得低效，這是因為磁頭必須向前和向後移動來尋找索引，而不是只象某個單一方向來搜
索。片段會影響I/O效能，不過對於位於SQL Server資料緩衝內的資料頁而言，片段並不會帶來任何影響。

當索引第一次建立時，沒有或者只有極少片段。隨著時間推移，插入，更新和刪除資料，和這些資料相關的索引上的片段就增加了。為了整理片段，SQL
Server提供如下命令：

Create INDEX後的Drop INDEX命令
不帶Drop_EXISTING選項的Create INDEX命令
DBCC INDEXDEFRAG
DBCC DBREINDEX

本文用 DBCC INDEXDEFRAG 和 DBCC DBREINDEX 命令來進行測試。這些命令都可以在線上和離線情境下執行。DBCC
DBREINDEX按照Create INDEX的方式建立索引；因此DBCC
DBREINDEX的執行結果和用Create
INDEX命令的結果很相似。上面所有這些命令的測試結果和功能描述會在本文後面提到。

整理片段前需要考慮的因素

系統資源問題

在索引磁碟重組之前，要確認系統任何效能問題和系統資源限制無關。關於這方面的詳細討論已經超出了本文的範圍，不過有些更常見的資源問題和I/O子
系統效能，記憶體使用量以及CPU使用率相關。關於分析這些類型資源問題的更深入討論，請見本文最後的“更多的資訊”章節。

物理磁碟片段

在某些系統上，磁碟片段會帶來很糟的效能。要確定是否存在磁碟片段，可以使用Microsoft
Windows內建的系統工具，或者第三方提供的工具來分析SQL
Server所在的分區。對於常規的I/O子系統上的規模較小的資料庫，建議在運行索引磁碟重組工具前，先進行磁碟磁碟重組。而對於更智能的磁碟子系統上
的規模較大的資料庫，例如SAN(存放區域網路 storage area networks)環境，磁碟磁碟重組就不是必要的。

執行情況較差的查詢

當考察任何效能相關問題時，你必須能識別出那些查詢執行效率較差。這裡討論的一些資訊在後面也會用到，這些資訊用於決定那些索引片段將被整理。

可以使用SQL Profiler(事件探查器)來識別執行效率差的查詢(關於這方面更多的資訊，請參考SQL
Server線上說明的"SQL Profiler"主題)。運行SQL
Profiler會帶來開銷；不過，只監控下面介紹的一些事件可以收集到必要的資訊，而且對效能的影響儘可能的小(一般來說，小於10%的CPU使用率，
當然有根據情況有些差異)。

SQL
Profiler提供了一個名叫SQLProfilerTSQL_Duration的跟蹤模板，可以捕獲相關的事件。可以很快捷地利用它來識別執行效率較
差的查詢。也可以手工建立SQL Profiler跟蹤來捕獲下述事件：

TSQL: SQLBatchCompleted
Stored Procedures: RPC:Completed

運行SQL
Profiler的時間長度要根據伺服器工作量而定。為了讓跟蹤更有效，需要選擇代表性的任務類型，至少應該選擇那些能顯示效能低下的工作類型。當跟蹤被
捕獲後，檢場追蹤記錄檔中期間那列資料。該列資料以毫秒為單位，表示每個批處理或者查詢運行需要的時間。

標識出引起效能最差的查詢

這裡列舉能夠標識出造成最糟糕效能的查詢的一些建議：

按查詢期間對跟蹤進行分組。將注意力首先放在前10個最差的查詢上。
如果在應用中大量使用了預存程序，考慮使用SQLProfilerSP_Counts模板來標識被調用最多的那些預存程序。將注意力放在被調用最頻繁，同
時也是引起較差效能的預存程序。
將收集的資料放到SQL Server表中。這樣，就可以通過查詢表來對工作效能進行更為詳細的分析(例如，平均已耗用時間，最大已耗用時間，等等)。

基礎結構

當找出已耗用時間最長，效能最差得查詢後，必須確保資料庫基礎架構對於那個查詢來說是最優的。例如確儲存在適當的索引並且被那個查詢正確地使用
了。可以使用查詢分析器來顯示和檢查查詢計劃，以發現在查詢任務中那些索引被用到了。當使用查詢分析器圖形化顯示查詢的執行計畫時，以前的資料會以警告的
方式標識(例如表名會以紅色字型顯示)。在整理片段之前要解決這些問題。

檢查查詢計劃時，要牢記以下建議：

找到執行計畫中開銷較大的步驟。這些步驟是查詢中最耗時的部分。解決這些步驟帶來的問題將會使效能大幅提高。
找出執行索引掃描的步驟。索引掃描是從磁碟重組中獲利最大的部分。注意那些效能較差的查詢索引掃描中用到的索引，在磁碟重組的時候可以集中在這些索引上進
行。

利用SQL Profiler中捕獲的跟蹤資訊，以及手工從查詢計劃中擷取的資訊，就可以使用索引嚮導(Index Tuning
Wizard)來分析工作量。利用索引想到產生的報表來決定是否要對基礎結構做改動。在磁碟重組前做完這些改動。

小規模環境 vs. 大規模環境

這裡做的測試基於兩台伺服器，兩台伺服器之間的I/O子系統相差很大。一台伺服器代表小規模環境，而另一台代表大規模環境。用來解釋測試結果，每台
環境的規格如下。

小規模環境

在小規模環境中，資料庫大小在10GB－20GB之間。資料分布再兩個物理磁碟上，tempdb和資料庫日誌分別在兩個使用RAID
0的額外磁碟上。DSS資料庫包含兩個檔案組，每個檔案組內有一個檔案。OLTP資料庫只包含一個檔案組，檔案組內有一個資料檔案。

大規模環境

Micorosoft和Hitachi Data System系統配合，可以用Hitachi Freedom Storage
Lightning 9900 Series Lightning 9960
system來構建SAN環境，用於儲存資料。用於測試的那個資料庫大小大約為1TB。資料分散在64個物理磁碟上，使用RAID1+0結構。儲存資料的
磁碟由8個LUNs(Logical Unit
Numbers)串連，資料庫包含一個檔案組，該檔案組中包含8個資料檔案。tempdb和資料庫日誌單獨放在一組磁碟上，與資料檔案隔離開，48個磁碟
用於存放tempdb，而日誌分布在8個磁碟上。為了快速備份和恢複有片段的資料庫鏡像，在SAN中維護中有兩個Hitachi
ShadowImage拷貝資料/記錄備份，Lightning
9960系統用於同步線上資料和ShadowImage備份資料。在該環境中，重複在三個片段層級上運行兩次，因為大容量的儲存需要維護每個層級(大約
1.4TB)的備份。

索引磁碟重組對效能的影響

測試結果在後面會詳細討論。但是，雖然磁碟重組對兩個環境(小規模和大規模)環境都帶來負面影響，但是無疑對大規模環境的影響要小得多。因為大規模
環境從SAN中擷取了極高的I/O效能，因此這個結論應該是對的：資料不光分散在多個磁碟上，而且SAN還提供16GB的資料緩衝區。I/O
benchmark測試顯示建立1TB資料量，最大的讀取速度為354 MB/sec, 而小規模環境下只有71 MB/sec。

注意: 這些數值會根據各人的實現步驟和儲存配置而變。

顯然，高效能的I/O子系統對SQL
Server效能十分有利，不過，索引磁碟重組的確會對所有系統帶來效能的提升。當建立資料庫時，要謹慎考慮I/O子系統，並確保儘可能將記錄檔和資料
庫資料檔案隔離開。

決定何時進行索引磁碟重組

決定何時進行索引磁碟重組時，請考慮以下重要的建議：

標識有片段的索引。
瞭解何種任務會從磁碟重組中獲利。
確定查詢的I/O效能。
理解磁碟重組帶來的影響和SQL Server預讀管理器。

下一節中，測試的結果可以用來協助理解這些建議。

使用 DBCC SHOWCONTIG 來標識有片段的索引

在決定何時進行磁碟重組前，必須先確定那些索引有片段。DBCC SHOWCONTIG可以用于衡量索引上的片段程度和頁密度層級(Page
Density level)。

下面是運行 DBCC SHOWCONTIG 後的得到的樣本資訊:

DBCC SHOWCONTIG scanning 'table_1' table…
Table: 'table_1' (453576654); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned…………………………..: 48584
- Extents Scanned…………………………: 6090
- Extent Switches…………………………: 12325
- Avg. Pages per Extent……………………: 8.0
- Scan Density [Best Count:Actual Count]…….: 49.27% [6073:12326]
- Logical Scan Fragmentation ………………: 10.14%
- Extent Scan Fragmentation ……………….: 32.74%
- Avg. Bytes Free per Page…………………: 1125.2
- Avg. Page Density (full)…………………: 86.10%
DBCC SHOWCONTIG scanning 'table_1' table…
Table: 'table_1' (453576654); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned…………………………..: 41705
- Extents Scanned…………………………: 5221
- Extent Switches…………………………: 6094
- Avg. Pages per Extent……………………: 8.0
- Scan Density [Best Count:Actual Count]…….: 85.55% [5214:6095]
- Logical Scan Fragmentation ………………: 7.80%
- Extent Scan Fragmentation ……………….: 6.63%
- Avg. Bytes Free per Page…………………: 877.7
- Avg. Page Density (full)…………………: 83.20%

檢查DBCC SHOWCONTIG運行後的結果時，需要特別留意Logical Scan Fragmentation和Average
Page Density。Logic scan fragmentattion表示索引上亂序的百分比(注意:
該數值和堆和文本索引不相關。所謂堆表示一個沒有叢集索引的表。)。Page density是索引葉級頁填充程度的度量。請尋找SQL
Server線上說明的“DBCC SHOWCONTIG”主題以擷取更多資訊。

分析DBCC SHOWCONTIG的輸出結果

在分析DBCC SHOWCONTIG的輸出結果時，請考慮下面問題：

片段會影響I/O。因此，要集中關注較大的索引，因為這些索引被SQL Server放入緩衝的可能性比較小。通過DBCC
SHOWCONTIG得到的頁數，可以估算出索引的大小(每頁大小為8KB)。一般來說，沒有必要關注那些片段層級小於1,000頁的索引。在測試中，包
含超過10,000頁的索引才會影響效能，特別是包含更多的頁(超過50,000頁)的索引，會引起最大的效能提升。
邏輯掃描片段(logical scan
fragmentation)值太高，會大大降低索引掃描的效能。在測試中，那些邏輯片段大於10%的叢集索引，在磁碟重組後效能得到了提升；對那些大於
20%的叢集索引，效能提升尤其明顯。因此關注那些邏輯片段大於等於20%的索引。注意，對於堆(Index ID=0)來說，該標準是無意義的。
平均頁密度(average page
density)太低，將導致查詢中需要讀取更多的頁。重新組織這些頁，可以提高平均頁密度，從而完成相同的查詢只要讀取較少的頁。一般來說，在第一次載
入資料後，表擁有較高的頁密度。隨著資料的插入，頁密度會降低，從而帶來葉級頁面分割。檢查平均頁密度時，記住該值依賴於建立表時設定的填滿因數取值。
雖然掃描密度(scan
density)可以作為片段層級的參考，不過當索引跨越多個檔案時，該參考無效。因此，當檢查跨越多個檔案的索引時，掃描密度不應該被考慮。

監視片段層級

有規律地監控索引的片段層級是良好的實踐習慣。SQL Server線上說明的"DBCC
SHOWCONTIG"主題中，有一個樣本指令碼，用於自動捕獲和重建片段程度較大的索引。建議每隔一段]
]>