軟件測試中數據倉庫的 RDBMS 性能優化指南

非聚集索引最適于根據特定的鍵值，從大型 SQL Server 表中提取少數幾個具有良好選擇性的行。如前所述，非聚集索引是由 8 KB 索引頁形成的二進制樹。索引頁二進制樹的底層或葉層包含組成該索引的列中的所有數據。在使用非聚集索引根據鍵值的匹配項從表中檢索信息時，會遍歷索引的 B 樹，直到在索引的葉層找到鍵的匹配項。如果需要表中不構成索引的列，指針就會跳轉。這種指針跳轉將有可能需要針對磁盤執行非順序 I/O 操作。它甚至可能需要從另一磁盤中讀取數據，尤其是在表及其伴隨的索引 B 樹很大時。如果多個指針指向同一個 8 KB 數據頁，對 I/O 性能的影響就會比較小，因為只需將該頁讀入數據緩存一次。如果 SQL 查詢涉及到用非聚集索引進行搜索，則對于對該查詢返回的每一行，至少需要一次指針跳轉。

注意 由于指針每次跳轉都會帶來與之相關的開銷，因此非聚集索引更適于處理從表中只返回一行或幾行的查詢。聚集索引更適于處理需要一系列行的查詢。

下圖說明了非聚集索引的存儲。請注意，添加的葉層指向對應的數據頁。在使用非聚集索引而不是聚集索引來訪問表數據時，添加的指針跳轉就會在那里起作用。有關非聚集索引的更多信息，請參閱“SQL Server 聯機叢書”。

如果您的瀏覽器不支持嵌入式框架，請單擊此處在單獨的頁中查看。

唯一索引

聚集索引和非聚集索引均可用于強制表內的唯一性，方法是在現有表上創建索引時指定 UNIQUE 關鍵字。確保表內唯一性的另一種方法是使用 UNIQUE 約束。如同唯一索引，UNIQUE 約束強制一組列中各值的唯一性。實際上，UNIQUE 約束的賦值自動創建基礎唯一索引，以利于強制該約束。由于唯一性可以作為 CREATE TABLE 語句的一部分來加以定義和記錄，因此，UNIQUE 約束通常優先于單獨唯一索引的創建。

計算列上的索引

SQL Server 2000 引入了在計算列上創建索引的功能。如果查詢是以一般方式提交的，而且會例行提供計算列，但管理員不愿意只是為了允許創建索引而在實際的表列中持續存放數據，在這樣的情況下，使用這項功能就會很方便。在此情況下，只要計算列滿足索引所需的全部條件，就可以通過引用計算列來創建索引。其他限制包括，計算列表達式必須有確定性、精確，并且不得取值為 text、ntext 或 image 數據類型。

確定性

如果要在視圖或計算列上創建索引，視圖和計算列均無法喚醒調用沒有確定性的用戶定義函數。所有函數要么有確定性，要么沒有確定性：

• 無論何時使用一組特定的輸入值調用有確定性的函數，這些函數總是會返回相同的結果。
• 每次用特定的一組輸入值調用沒有確定性的函數時，這些函數返回的結果可能各不相同。

例如，DATEADD 內置函數有確定性，因為對于通過該函數的三個輸入參數傳入的一組給定參數值，它始終返回可預測的結果。GETDATE 沒有確定性。雖然始終用相同的參數值喚醒調用 GETDATE 函數，但每次執行調用返回的值各不相同。

精確

如果滿足下列條件，說明計算列表達式是精確的：

• 它不是 float 數據類型的表達式。
• 它不在自己的定義中使用 float 數據類型。例如，在下面的語句中，列 y 是 int 并且有確定性，但不精確。

  CREATE TABLE t2 (a int, b int, c int, x float,
y AS CASE x
WHEN 0 THEN a
WHEN 1 THEN b
ELSE c
END)


COLUMNPROPERTY 函數的 IsPrecise 屬性報告 computed_column_expression 是否精確。

注意 任何 float 表達式均被視為不精確，不能作為索引的鍵；float 表達式可以在索引視圖中使用，但不能用作鍵。這一規則同樣適用于計算列。任何函數、表達式、用戶定義函數或視圖定義，只要包含任何 float 表達式，包括邏輯表達式（比較），均被視為沒有確定性。

如果在計算列或視圖上創建索引，先前能夠正確執行的 INSERT 或 UPDATE 操作現在可能無法執行。在計算列導致算術錯誤時，可能會發生此類無法執行的情況。例如，雖然下表中的計算列 c 導致算術錯誤，但 INSERT 語句會起作用：

CREATE TABLE t1 (a int, b int, c AS a/b)
GO
INSERT INTO t1 VALUES ('1', '0')
GO


如果改為在創建該表之后在計算列 c 上創建索引，相同的 INSERT 語句將會失敗。

CREATE TABLE t1 (a int, b int, c AS a/b)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
GO
INSERT INTO t1 VALUES ('1', '0')
GO


索引視圖

索引視圖是為了實現快速訪問而將其結果持續存放于數據庫內并創建索引的視圖。與任何其他視圖一樣，索引視圖也依靠基表來提供視圖數據。此類相關性意味著，如果更改為索引視圖提供數據的基表，索引視圖可能變得無效。例如，重命名為視圖提供數據的列會使該視圖無效。為了避免此類問題，SQL Server 支持創建具有架構綁定的視圖。架構綁定禁止對表或列進行任何會使視圖無效的修改。使用視圖設計器創建的索引視圖自動獲得架構綁定，因為 SQL Server 要求該索引視圖具有架構綁定。架構綁定并不是說您不能修改視圖；它的意思是您不能按更改視圖結果集的方式來修改基礎表或視圖。另外，就像計算列上的索引一樣，索引視圖也必須有確定性、精確，且不得包含 text、ntext 或 image 等列。

索引視圖在基礎數據不經常更新的情況下效果最佳。維護索引視圖的成本可能高于維護表索引的成本。如果基礎數據更新頻繁，索引視圖數據的維護成本就可能超過使用索引視圖帶來的性能收益。

索引視圖改進了以下幾類查詢的性能：

• 處理多行的聯接和聚合。
• 許多查詢經常執行的聯接和聚合操作。

  例如，在某個記錄清單的 OLTP 數據庫中，預計許多查詢要聯接 Parts、PartSupplier 和 Suppliers 表。雖然執行這一聯接的每個查詢不一定都會處理許多行，但成千上萬個查詢的聯合處理加在一起仍然是非常龐大的操作。因為不太可能經常更新這些關系，所以通過定義存儲聯接結果的索引視圖，即可改進整個系統的總體性能。

• 決策支持工作負荷。
• 分析系統的特點是存儲不經常更新的概要數據、聚合數據。許多決策支持查詢的特點是進一步聚合數據和聯接許多行。

索引視圖通常不會改進以下幾類查詢的性能：

• 經常寫入的 OLTP 系統。
• 經常更新的數據庫。
• 不涉及聚合或聯接的查詢。
• 鍵基數程度高的數據聚合�；鶖党潭雀咭馕吨�該鍵包含許多不同的值。唯一鍵具有基數可能的最高程度，因為每個鍵的值各不相同。索引視圖通過減少查詢必須訪問的行的數量來改進性能。如果視圖結果集的行數量幾乎與基表的行數量相同，那么使用該視圖幾乎就無任何性能收益可言。例如，對于具有 1,000 行的表考慮使用此查詢：

  SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey


  如果表鍵的基數為 100，使用此查詢的結果生成的索引視圖就只有 100 行。使用該視圖的查詢平均需要的讀取次數為讀取基表次數的十分之一。如果該鍵是一個唯一的鍵，而該鍵的基數是 1000，視圖結果集將返回 1000 行。使用該索引視圖，而不直接讀取基表，查詢不會帶來任何性能改進。

• 展開聯接，這些聯接是結果集大于基表內原始數據的視圖。

您設計的索引視圖應能滿足多個操作。因為，即使未在 FROM 子句中指定索引視圖，優化程序也能使用索引視圖，所以設計完好的索引視圖可以加快許多查詢的處理速度。例如，考慮在以下視圖上創建索引：

CREATE VIEW ExampleView (PriKey, SumColx, CountColx)
AS
SELECT PriKey, SUM(Colx), COUNT_BIG(Colx)
FROM MyTable
GROUP BY PriKey


該視圖不僅滿足直接引用視圖列的查詢，而且可以用來滿足查詢基礎基表并且包含 SUM(Colx)、COUNT_BIG(Colx)、COUNT(Colx) 和 AVG(Colx) 等表達式的查詢。所有此類查詢的速度都會更快，因為它們只需檢索視圖中的少數幾列，而不必讀取基表中的所有列。

在視圖上創建的第一個索引必須是唯一的聚集索引。創建了唯一的聚集索引之后，您可以創建其他非聚集索引。視圖上的索引命名規則與表上的索引命名規則相同。唯一不同的是表名會替換為視圖名。

如果除去視圖，視圖上的所有索引也將被除去。如果除去聚集索引，視圖上的所有非聚集索引也將被除去。非聚集索引可被單獨除去。除去視圖上的聚集索引會刪除存儲的結果集，優化程序會恢復以標準視圖的處理方式來處理該視圖。

雖然在 CREATE UNIQUE CLUSTERED INDEX 語句中僅指定構成聚集索引鍵的列，但視圖的完整結果集存儲于數據庫中。就像在基表的聚集索引中一樣，聚集索引的 B 樹結構只包含鍵列，但數據行包含視圖結果集中的所有列。

注意 SQL Server 2000 的任何版本均可創建索引視圖。在 SQL Server 2000 企業版中，索引視圖將由查詢優化程序自動考慮。要在所有其他版本中使用索引視圖，必須使用 NOEXPAND 提示。

覆蓋索引

覆蓋索引是根據滿足 SQL 查詢（在選擇條件和 WHERE 謂詞兩方面滿足）所需的所有列建立的非聚集索引。覆蓋索引可以節省大量 I/O，從而極大地提升了查詢的性能。但是，您需要平衡考慮新索引的創建成本（及其相關的 B 樹索引結構維護成本）與覆蓋索引帶來的 I/O 性能收益。如果覆蓋索引將會極大地提升某個查詢或某組查詢的性能，而這些查詢經常在 SQL Server 上運行，那么就值得創建覆蓋索引。

下面的示例說明如何使用覆蓋索引交集：

Create index indexname1 on table1(col2,col1,col3)
Select col3 from table1 where col2 = 'value'


執行上述查詢時，只會讀取較少的索引頁，所以能夠迅速從基礎表中檢索到所需的值，從而非常高效地解析該查詢。通常，如果覆蓋索引較�。ň退饕�中所有列的字節數與該表的單行內的字節數相比較而言），并且使用覆蓋索引的查詢確實會經常執行，那么適合使用覆蓋索引。

索引選擇

對索引的選擇會極大地影響生成的磁盤 I/O 數量，進而極大地影響性能。非聚集索引適合于檢索少量的行，而聚集索引適合于區間掃描。下列原則有助于選擇要使用的索引類型：

• 盡量精簡索引（行數和字節數保持最�。�。該原則尤其適用于聚集索引，因為非聚集索引將使用聚集索引作為定位行數據的方法。
• 在非聚集索引中，選擇性非常重要。如果在只有幾個唯一值的大表上創建非聚集索引，使用該非聚集索引不會在數據檢索過程中節省大量 I/O。實際上，使用該索引生成的 I/O 很可能遠遠多于只是執行連續表掃描生成的 I/O。適合使用非聚集索引的對象包括發票號、唯一的客戶號、社會保障號和電話號碼。
• 對于涉及到區間掃描的查詢，或在經常使用列聯接其他表時，聚集索引比非聚集索引的效果好。原因是聚集索引對表數據進行物理排序，允許鍵值上存在連續的 64 KB I/O。適合使用聚集索引的對象包括省、公司分支機構、銷售日期、郵政編碼和客戶地區。

  針對一個表只能創建一個聚集索引；如果典型查詢經常從表的某一列提取大量連續區間，而表的其他列包含唯一值，則在第一列上使用聚集索引，在包含唯一值的列上使用非聚集索引。在每個表上嘗試選擇用來創建聚集索引的最佳列時，要問的關鍵問題是：“是否會有大量查詢需要根據該列的順序來提取大量的行？”。答案視每個用戶環境的具體情況而定。某個公司可能根據日期區間進行大量查詢，而另一個公司可能根據銀行分支機構的區間進行大量查詢。

索引的創建和并行操作

在 SQL Server 2000 企業版和 Developer Edition 中，針對創建索引而建立的查詢計劃允許在配有多個微處理器的計算機上進行并行、多線程的索引創建操作。

SQL Server 在為創建索引操作確定并行度（同時運行的單個線程的總數）時使用的算法與為其他 Transact-SQL 語句確定并行度時使用的算法相同。唯一不同是創建索引的 CREATE INDEX、CREATE TABLE 或 ALTER TABLE 語句不支持 MAXDOP 查詢提示。索引創建的最大并行度取決于最大并行度服務器配置選項，但您不能為各索引創建操作設置不同的 MAXDOP 值。

在 SQL Server 建立創建索引查詢計劃時，并行操作的數量以下面的最小值為準：

• 計算機中微處理器或 CPU 的數量。
• 最大并行度服務器配置選項中指定的數量。
• 尚未超過 SQL Server 線程工作閾值的 CPU 數量。

例如，某臺計算機配有八個 CPU，但最大并行度選項設置為 6，那么為創建索引生成的并行線程不會超過六個。如果在建立索引創建執行計劃時，計算機中的五個 CPU 超過 SQL Server 工作的閾值，執行計劃將只指定三個并行線程。

并行索引創建的主要階段包括：

• 協調線程對表進行快速隨機的掃描，以估計索引鍵的分發情況。協調線程建立鍵邊界，鍵邊界的作用是創建多個鍵區間，鍵區間的數量與并行操作度相等，預計每個鍵區間內包含的行數大致相同。例如，如果表中有四百萬行，而最大并行度選項設置為 4，協調線程將確定分隔四個行集的鍵值，每個行集包含一百萬行。
• 協調線程根據并行操作度分派同等數量的多個線程，然后等待這些線程完成工作。每個線程使用篩選器掃描基表，篩選器只在分配給線程的區間內檢索具有鍵值的行。每個線程為其鍵區間內的行建立索引結構。

在所有并行線程完成工作后，協調線程將多個索引子單元連接到單個索引中。單獨的 CREATE TABLE 或 ALTER TABLE 語句可以具有多個需要創建索引的約束。雖然可以在配有多個 CPU 的計算機上并行執行每個索引創建操作，但此處所述的多個索引創建操作仍然按序執行。

索引維護

在數據庫中創建索引時，查詢使用的索引信息存儲在索引頁中。連續索引頁之間通過指針一頁一頁相互鏈接在一起。對影響索引的數據進行更改時，數據庫中的索引信息會被拆散。重建索引將重新組織索引數據的存儲（如果為聚集索引，還將重新組織表數據的存儲），以便刪除碎片。這樣可以減少為獲得請求數據所需的頁讀取的數目，從而改進磁盤性能。

插入活動或更新會修改聚集索引的搜索鍵值，在執行大量的插入活動或更新時，即會出現碎片。因此，為了防止拆分索引頁和數據頁，您應該嘗試在索引頁和數據頁上保留一定的開放空間，這一點很重要。如果索引頁或數據頁不能再存放任何新行，而且由于該頁中定義的數據的邏輯排序，需要將某一行插入該頁，則會拆分該頁。出現此情況時，SQL Server 需要對一整頁的數據進行分割，將大約一半的數據移到新頁上，這樣，新舊兩頁都能保留一定的開放空間。因為這樣會耗用系統資源和時間，所以建議不要經常這樣做。

最初建立索引時，SQL Server 嘗試將索引的 B 樹結構放在物理上連續的頁上；這樣就能在使用連續 I/O 掃描索引頁時優化 I/O 性能。在發生拆分頁和需要將新頁插入索引的邏輯 B 樹結構時，SQL Server 必須分配新的 8 KB 索引頁。如果在硬盤的其他位置發生此情況，將會破壞索引頁的物理連續特性。這樣會導致 I/O 操作的執行從連續切換為不連續，而且還會極大地降低性能。通過重建索引來恢復索引頁的物理連續順序，應該能解決頁拆分過多的問題。同樣的行為還可能在聚集索引的葉級別上發生，從而影響表的數據頁。

在系統監視器中，尤其要注意“SQL Server：訪問方法 – 頁拆分/秒”。該計數器的非零值表示正在進行頁拆分，應使用 DBCC SHOWCONTIG 作進一步分析。

DBCC SHOWCONTIG 命令也可用來揭示表上是否已進行了過多的頁拆分。掃描密度是 DBCC SHOWCONTIG 提供的關鍵指標。該值應盡量接近 100%，越接近越好。如果該值大大低于 100%，請考慮對出現問題的索引運行維護。

DBCC INDEXDEFRAG

一個索引維護選項要使用 SQL Server 2000 中引入的新語句 (DBCC INDEXDEFRAG)。DBCC INDEXDEFRAG 可以為表和視圖上的聚集索引和非聚集索引整理碎片。DBCC INDEXDEFRAG 在索引的葉級別整理碎片，因此各頁的物理順序與葉節點從左至右的邏輯順序一致，從而改進了索引掃描性能。

DBCC INDEXDEFRAG 還壓縮索引的各頁，它會考慮在創建索引時指定的 FILLFACTOR。由壓縮創建的空頁將被刪除。

如果索引跨越多個文件，DBCC INDEXDEFRAG 一次為一個文件整理碎片。索引頁不會在文件之間遷移。DBCC INDEXDEFRAG 每隔五分鐘向用戶報告一次預計已完成的百分比。在執行過程中，您隨時都可以終止 DBCC INDEXDEFRAG，已完成的所有工作會被保留。

與 DBCC DBREINDEX（或一般的索引建立操作）不同，DBCC INDEXDEFRAG 是聯機操作。它不會長期保持鎖定，因而不會阻止運行查詢或更新。為相對而言沒有碎片的索引整理碎片可以比建立新索引快，因為整理碎片所需的時間與碎片量相關。為非常零碎的索引整理碎片的時間可能比重建索引的時間要長得多。此外，不論數據庫恢復模型設置如何，始終完全記錄碎片整理情況（請參閱 ALTER DATABASE）。為非常零碎的索引整理碎片所生成的日志甚至可能比記錄整個索引創建過程所生成的日志還多。不過，由于碎片整理是作為一系列小事務執行的，因此，如果經常進行日志備份，或者恢復模型設置為 SIMPLE，則不需要大日志。

另外，如果兩個索引在磁盤上交錯存放，則不適合使用 DBCC INDEXDEFRAG，因為 INDEXDEFRAG 會打亂索引頁的位置。要改進索引頁的聚集，請重建索引。出于相同的原因，DBCC INDEXDEFRAG 無法更正頁拆分。對于已按反映搜索鍵的連續順序分配的索引頁，它實質上會進行重新排序。索引頁的次序可能因多種原因而變得不正確，這些原因包括：無序數據裝載、過多的插入、更新、刪除活動，等等。

“SQL Server 聯機叢書”中提供了一段示例代碼，您只需對該代碼稍加修改，即可使用它來自動執行各種索引維護任務。該示例說明如何用一種簡單的方法來對數據庫中碎片量大于聲明閾值的所有索引進行碎片整理。有關更多信息，請參閱“SQL Server 聯機叢書”中的主題“DBCC SHOWCONTIG”。

DBCC DBREINDEX

根據所用語法不同，DBCC DBREINDEX 可以只重建表的某一個指定索引，或者也可以重建表的所有索引。與除去并重新創建各個索引時采用的方法類似，DBCC DBREINDEX 語句也具備能夠在一條語句中重建表的所有索引這一優點。這樣比編寫單獨的 DROP INDEX 和 CREATE INDEX 語句更方便，并且，在重建表的一個或多個索引時，不必知道表結構或任何指定的約束條件。另外，DBCC REINDEX 語句固有原子性。如果要在編寫單獨的 DROP INDEX 和 CREATE INDEX 語句時獲得相同的原子性，必須將多個單獨的命令包含在一個事務內。

與單獨的 DROP INDEX 和 CREATE INDEX 語句相比，DBCC DBREINDEX 會自動利用更多優化方案，在多個非聚集索引引用具有聚集索引的表時尤其如此。DBCC DBREINDEX 也可用于重建強制 PRIMARY KEY 或 UNIQUE 約束的索引，而不必刪除和重新創建約束（因為，如果不先刪除約束，則無法刪除為了強制 PRIMARY KEY 或 UNIQUE 約束而創建的索引）。例如，您可能希望通過在 PRIMARY KEY 約束上重建索引來為索引重新建立給定的填充因子。

DROP_EXISTING

重建索引或整理索引碎片的另一種方法是：除去索引后再重新創建索引。通過刪除舊索引，然后再重新創建相同的索引來重建聚集索引，這種方法很昂貴，因為所有二級索引都依賴指向數據行的聚集鍵。如果只刪除聚集索引，然后再重新創建索引，您可能會不慎導致所有引用非聚集索引被刪除和重新創建兩次。在除去聚集索引時進行第一次除去/重新創建。在重新創建聚集索引時進行第二次除去/重新創建。

為了避免這一開銷，使用 CREATE_INDEX 的 DROP_EXISTING 子句就可以一步完成這一重新創建的過程。采用一個步驟重新創建索引會告訴 SQL Server 您要重新組織現有索引，并避免刪除和重新建相關非聚集索引等不必要的工作。這種方法還有一點明顯的好處：可以使用現有索引中已預先排序的數據，因而不需要執行數據排序。這樣就可以明顯減少重新創建聚集索引的時間和成本。

DROP INDEX / CREATE INDEX

維護索引的最后一種方法是：直接除去索引，然后再重新創建索引。此選項仍在廣泛使用，并且可能是以下人員的首選：熟悉此選項的人員、其處理窗口能夠容納表上所有索引完整的重新創建的人員。使用此方法的缺點是必須手動控制事件，以使事件按照適當的順序發生。在手動除去和重新創建索引時，一定要在除去和重新創建聚集索引之前，除去所有非聚集索引。否則，在創建聚集索引時，將自動創建所有非聚集索引。

手動創建非聚集索引有一個優點：各個非聚集索引可以同時重新創建。不過，您的分區策略可能會影響所生成的索引的物理布局。如果同時在同一個文件（文件組）上重建兩個非聚集索引，這兩個索引的索引頁可能在磁盤上交錯在一起。這可能會打亂數據的存儲順序。如果多個文件（文件組）位于不同的磁盤上，您可以指定單獨的文件（文件組）在創建索引之后保存索引，從而保持了索引頁的順序連續性。

前面提及的有關在預先排序的數據上建立索引的問題在此處同樣適用。在已排序的數據上建立的聚集索引不必執行額外的排序步驟，從而可以極大地減少建立索引所需的時間和處理資源。

FILLFACTOR 和 PAD_INDEX

FILLFACTOR 選項提供了一種方法，用于指定在索引頁和數據頁上保留的開放空間的百分比。CREATE INDEX 的 PAD_INDEX 選項會在非葉級別的索引頁上應用 FILLFACTOR 的設置。如果沒有 PAD_INDEX 選項，FILLFACTOR 主要影響聚集索引的葉級別索引頁。最好同時使用 PAD_INDEX 選項和 FILLFACTOR 選項。

PAD_INDEX 和 FILLFACTOR 用于控制頁拆分。為 FILLFACTOR 指定的最佳值取決于在給定時間段內插入 8 KB 索引頁和數據頁的新數據量。請記住，通常，SQL Server 索引頁包含的行數遠遠多于數據頁包含的行數，因為索引頁只包含與該索引相關的列數據，而數據頁包含整行的數據，這一點很重要。

另外，請記住維護窗口的出現頻率，維護窗口允許重建索引，以便更正即將發生的頁拆分。請嘗試只在大多數索引頁和數據頁已填滿數據時再重建索引。如果表的聚集索引選擇得當，則不會經常需要重建索引。如果聚集索引均勻地分布數據，從而所有與表相關的數據頁上都會在該表中插入新行，那么，數據頁將會均勻填充�？傮w說來，這將在開始發生頁拆分且有必要重建聚集索引之前提供更多的時間。

為了確定用于 PAD_INDEX 和 FILLFACTOR 的適當的值，您需要發出判斷請求。在作決定之前，您應該考慮兩方面：一是在頁上保留大量開放空間，二是可能發生的拆分頁的數量，這兩方面要保持性能上的平衡。如果為 FILLFACTOR 指定的百分比很小，它將在索引頁和數據頁上保留大量開放空間，這樣，為了回答查詢，SQL Server 就需要讀取大量部分填充的頁。對于大量讀取操作而言，如果索引頁和數據頁上的壓縮數據越多，SQL Server 的處理速度會明顯加快。指定過高的 FILLFACTOR 會使各頁上保留的開放空間過少，這樣，各頁很快就會溢出，從而導致頁拆分。

在確定 FILLFACTOR 或 PAD_INDEX 值之前，請記住，在許多數據倉庫環境中，讀取操作的數量往往比寫入操作的數量多得多。不過，如果定期裝載數據，可能就不是這種情況了。許多數據倉庫管理員嘗試對表/索引進行分區和組織，以便容納預計會出現的定期數據裝載。

根據一般經驗，如果預計的寫入量相當于讀取量的一大部分，最佳方法是按可行情況盡高地指定 FILLFACTOR，同時在每個 8 KB 頁上保留足夠的可用空間，以避免經常發生頁拆分，至少要讓 SQL Server 能夠到達重新創建索引所需的下一個可用時間窗。該策略均衡了 I/O 性能（盡量填滿各頁），并且避免了頁拆分（不讓各頁溢出）。如果不寫入 SQL Server 數據庫，FILLFACTOR 應設置為 100%，以便填滿所有索引頁和數據頁，獲得最佳 I/O 性能。

用于分析和優化的 SQL Server 工具

本節提供在表中裝載數據的示例代碼，稍后再用該段代碼說明如何使用 SQL 事件探查器和 SQL 查詢分析器分析和優化性能。

樣本數據和工作負荷

下面的示例說明如何使用 SQL Server 性能工具。首先構造下表：

create table testtable
(nkey1 int identity,
col2 char(300) default 'abc',
ckey1 char(1))


然后，在該表中裝載 20,000 行測試數據。裝載到 nkey1 列的數據適用于非聚集索引。ckey1 列中的數據適用于聚集索引，col2 中的數據只是為了將每行的大小增加 300 個字節而填入的數據。

declare @counter int
set @counter = 1
while (@counter <= 4000)
begin
insert testtable (ckey1) values ('a')
insert testtable (ckey1) values ('b')
insert testtable (ckey1) values ('c')
insert testtable (ckey1) values ('d')
insert testtable (ckey1) values ('e')
set @counter = @counter + 1
end


下列查詢構成了數據庫服務器工作負荷：

select ckey1 from testtable where ckey1 = 'a'
select nkey1 from testtable where nkey1 = 5000
select ckey1,col2 from testtable where ckey1 = 'a'
select nkey1,col2 from testtable where nkey1 = 5000


SQL 事件探查器

優化性能的常用方法通常稱為標記和度量。要驗證為改進性能所做的更改是否確實改進了性能，首先需要建立現有不良性能情況的基線或標記。度量 指建立一些可量化的方法，用來證明性能正在得到改進。

SQL 事件探查器是用來進行標記和度量的工具。它不僅可以捕獲服務器內發生的活動，供您進行性能分析；而且可以稍后再回放該活動。SQL Server 中的回放功能提供了一種有用的回歸測試工具。使用回放功能，您可以方便地確定目前為了改進性能而采取的操作是否能達到預期效果。

回放功能還可以模擬負載或壓力測試。您可以設置多個事件探查器客戶端會話，讓它們同時回放。例如，利用這一功能，管理員可以輕松地捕獲五個并發用戶的活動，然后同時啟動十個回放，模擬有 50 個并發用戶時的系統性能。您還可以跟蹤數據庫活動，然后在正進行修改的數據庫中回放該活動，或在正進行測試的新硬件配置中回放該活動。

請記住，您可以利用 SQL 事件探查器記錄 SQL Server 數據庫中發生的活動�？梢詫� SQL 事件探查器進行配置，讓它監視和記錄對 SQL Server 執行查詢的一個或多個用戶。除了 SQL 語句外，使用該工具還能捕獲各種各樣的性能信息。使用 SQL 事件探查器記錄的某些性能信息包括：I/O 統計信息、CPU 統計信息、鎖定請求、Transact-SQL 和 RPC 統計信息、索引和表掃描、引發的警告和錯誤、數據庫對象的創建/除去、建立連接/斷開連接、存儲過程操作、游標操作，等等。

捕獲供索引優化向導使用的事件探查器信息

SQL 事件探查器和索引優化向導的結合使用，形成了一個功能非常強大的工具組合，它可以幫助數據庫管理員確保在表和視圖上放置正確的索引。SQL 事件探查器可以將查詢的資源耗用情況記錄到三個位置上�？梢詫⑤敵龆ㄏ虻� .trc 文件、SQL Server 表或監視器。之后，索引優化向導從 .trc 文件或 SQL Server 表讀取捕獲的數據。索引優化向導對捕獲的工作負荷中的信息和有關表結構的信息進行分析，然后針對改進性能提出應該創建哪些索引的建議。有了索引優化向導，

您就能自動完成以下任務：為數據庫創建正確的索引、調度稍后進行的索引創建、生成可以手動檢查和執行的 Transact-SQL 腳本。

分析查詢負載需要完成以下步驟：

設置 SQL 事件探查器

1. 在工具菜單上選擇 SQL 事件探查器，從 SQL Server 企業管理器中啟動 SQL 事件探查器。
2. 按 CTRL+N 鍵新建一個 SQL 事件探查器跟蹤。在連接到 SQL Server 對話框中，選擇要連接到的服務器。
3. 從下拉列表框中選擇 SQLProfilerTuning 模板。
4. 選中另存為文件或另存為表復選框。另存為表選項將打開連接對話框，在該對話框中，您可以將跟蹤信息保存到探查查詢的服務器以外的其他服務器。如果要將跟蹤的活動同時另存為文件和表，請選中這兩個復選框。如果要另存為 .trc 文件，請指向有效的目標和文件名。如果以前已運行過跟蹤，現在再次運行同一跟蹤，請指向現有的跟蹤表；如果這是第一次將跟蹤活動捕獲到表中，您也可以提供新的表名。單擊確定。
5. 單擊運行。

運行工作負荷若干 (3-4) 次

1. 從 SQL Server 企業管理器或開始菜單啟動 SQL 查詢分析器。
2. 連接到 SQL Server，然后將當前數據庫設為在其中創建測試表的數據庫。
3. 在 SQL 查詢分析器的查詢窗口中輸入下列查詢：

   select ckey1 from testtable where ckey1 = 'a'
select nkey1 from testtable where nkey1 = 5000
select ckey1,col2 from testtable where ckey1 = 'a'
select nkey1,col2 from testtable where nkey1 = 5000


4. 按 CTRL+E 鍵執行查詢。反復執行該步驟三至四次，生成工作負荷樣本。

停止 SQL 事件探查器

• 在“SQL 事件探查器”窗口中，單擊紅色方塊，停止該事件探查器跟蹤。

將跟蹤文件或表裝載到索引優化向導中

1. 在 SQL 事件探查器中，選擇工具菜單上的索引優化向導，以啟動該向導。單擊下一步。
2. 選擇要分析的數據庫。單擊下一步。
3. 選擇是否要保留現有索引的選項，或者添加索引視圖。
4. 選擇一種優化模式（快速、適中或徹底）。在“快速”優化模式下，索引優化向導執行分析所需的時間較少，但分析不夠徹底，在“徹底”模式下生成的分析最徹底，但所需的分析時間最長。
5. 要查找用 SQL 事件探查器創建的跟蹤文件/表，請選擇我的工作負荷文件或 SQL Server 跟蹤表。單擊下一步。
6. 在選擇要優化的表對話框中，選擇要分析的表，然后單擊下一步。
7. 索引優化向導將分析跟蹤的工作負荷和表結構，然后在索引建議對話框中確定應創建的正確索引。單擊下一步。
8. 該向導提供了幾個選項：立即創建索引、安排索引創建的時間（之后會自動執行的任務），或者創建包含用于創建索引的命令的 Transact-SQL 腳本。選擇首選項，然后單擊下一步。
9. 單擊完成。

索引優化向導針對樣本數據庫和工作負荷生成的 Transact-SQL

/* Created by: Index Tuning Wizard */
/* Date: 9/6/2000 */
/* Time: 4:44:34 PM */
/* Server Name: JHMILLER-AS2 */
/* Database Name: TraceDB */
/* Workload File Name: C:\Documents and Settings\jhmiller\My Documents\trace.trc */
USE [TraceDB]
go
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
go
DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

CREATE CLUSTERED INDEX [testtable1] ON [dbo].[testtable] ([ckey1] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

CREATE NONCLUSTERED INDEX [testtable2] ON [dbo].[testtable] ([nkey1] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION


索引優化向導為樣本表和數據建議的索引正是我們所需要的：在 ckey1 上創建聚集索引，在 nkey1 上創建非聚集索引。ckey1 只有五個唯一值，每個值有 4000 行。假定其中一個樣本查詢 (select ckey1, col2 from testtable where ckey1 = 'a') 需要根據 ckey1 中的某個值來檢索表，則適合在 ckey1 列上創建聚集索引。第二個查詢 (select nkey1, col2 from testtable where nkey1 = 5000) 根據 nkey1 列的值提取一行。因為 nkey1 是唯一的，并且有 20,000 行，所以適合在該列上創建非聚集索引。

在使用了很多表并且要處理很多查詢的實際數據庫服務器環境中，將 SQL 事件探查器和索引優化向導組合使用，功能會非常強大。在數據庫服務器處理典型的一組查詢時，使用 SQL 事件探查器記錄 .trc 文件或跟蹤表。隨后，將跟蹤裝載到索引優化向導中，以確定要建立的正確索引。按照索引優化向導中的提示執行操作，以自動生成索引，或安排索引創建作業在非高峰時間運行。您可能希望定期運行 SQL 事件探查器和索引優化向導的組合（也許每周一次或每月一次），以查看目前在數據庫服務器上執行的查詢是否發生了重大變化，這樣就有可能會需要不同的索引。定期組合使用 SQL 事件探查器和索引優化向導，有助于數據庫管理員在查詢工作負荷不斷變化和數據庫日漸增大的情況下，仍保持 SQL Server 處于最佳運行狀態。

使用 SQL 查詢分析器分析事件探查器中記錄的信息

在信息記錄到 SQL Server 表中之后，可以使用 SQL 查詢分析器來確定系統中哪些查詢消耗資源最多。這樣，數據庫管理員就能集中精力改進那些最需要幫助的查詢。如果將跟蹤數據存儲在表中，您就能方便地對跟蹤數據的子集進行選擇和篩選，從而為優化性能標識出性能最差的查詢。例如，在上面的示例中，Duration 列是您使用 SQLProfiler Tuning 模板自動捕獲的列，它可以用來標識需要最長執行時間（以毫秒計）的查詢。要查找前 10% 的運行時間最長的查詢，您可以運行下面這樣的查詢：

SELECT TOP 10 PERCENT *
FROM [TraceDB].[dbo].[Trace]
ORDER BY Duration DESC


要查找運行時間最長的前五個查詢，您可以運行類似下面的查詢：

SELECT TOP 5 *
FROM [TraceDB].[dbo].[Trace]
ORDER BY Duration DESC


要只將希望用于優化的行放在單獨的表中，請考慮使用下面的 SELECT/INTO 語句：

SELECT TOP 10 PERCENT *
INTO TuningTable
FROM [TraceDB].[dbo].[Trace]
ORDER BY Duration DESC


前面提到的 SQLProfiler Tuning 模板只是針對優化建議的一組預選列和篩選器設置。您可能會發現，您需要捕獲更多的信息。當然，您完全可以創建自己的自定義優化模板，方法是：只需打開預先提供的一個模板，然后用不同的名稱保存即可。許多事件都可被捕獲，包括 I/O 統計信息、鎖定信息，等等。

SQL 查詢分析器

SQL 查詢分析器用于優化查詢。該工具提供了多種類似“統計信息 I/O”的機制和用來解決查詢問題的執行計劃。

統計信息 I/O

SQL 查詢分析器提供了一個選項，利用該選項，您能夠獲得在 SQL 查詢分析器中執行的查詢在 I/O 消耗方面的相關信息。要設置該選項，請在 SQL 查詢分析器的查詢菜單上，選擇當前連接屬性，以顯示當前連接屬性對話框。選中設置 statistics I/O 復選框，然后關閉該對話框。然后，執行查詢并在結果窗格中選擇消息選項卡，查看 I/O 統計信息。

例如，在選中設置 statistics IO 選項時，對前面的“SQL 事件探查器”一節中創建的樣本數據進行以下查詢，將在消息選項卡上返回以下 I/O 信息：

select ckey1, col2 from testtable where ckey1 = 'a'
Table 'testtable'.Scan count 1, logical reads 800, physical reads 62, read-ahead reads
760.


使用統計信息 I/O 是監視查詢優化效果的一種好方法。例如，創建索引優化向導為樣本數據建議的索引，然后再次運行該查詢。

select ckey1, col2 from testtable where ckey1 = 'a'
Table 'testtable'.Scan count 1, logical reads 164, physical reads 4, read-ahead reads
162.


請注意，在可以使用索引時，邏輯讀取和物理讀取的數量會明顯降低。

執行計劃

使用圖形化執行計劃可以顯示有關查詢優化程序所做操作的詳細信息，從而讓您著重關注有問題的 SQL 查詢。

查詢的預計執行計劃可以顯示在 SQL 查詢分析器的“結果”窗格中，方法是：用 CTRL+L 鍵執行 SQL 查詢，或在查詢菜單上選擇顯示預計的執行計劃。各圖標表明了查詢優化程序如果執行了查詢后會執行哪些操作。各箭頭表示查詢的數據流方向。將鼠標指針懸停于操作圖標上方，即可顯示有關每個操作的詳細信息。各個操作圖標下方還注明了每個操作步驟的大致成本。通過此標簽，您可以迅速判斷出查詢中哪項操作是最昂貴的。

您也可以查看查詢的實際執行計劃，方法是在查詢菜單上選擇顯示執行計劃，然后執行查詢。與顯示預計的執行計劃選項相比，顯示執行計劃先執行查詢，然后才顯示用于該查詢的實際執行計劃。

您可以創建執行計劃的文本版本，方法是在查詢菜單上選擇當前連接屬性，然后在該對話框中選中設置 showplan_text 選項。執行查詢時，執行計劃將在結果選項卡中顯示為文本。

您還可以在查詢內設置執行計劃選項，方法是執行以下任一命令：

set showplan_all on
go
set showplan_text on
go


SET SHOWPLAN_ALL 供讀取其輸出的應用程序使用。使用 SET SHOWPLAN_TEXT 返回 Microsoft MS-DOS® 應用程序（如 osql 實用工具）可讀取的輸出。

SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以一組文本行的形式返回信息，這些文本行所形成的分層樹表示 SQL Server 查詢處理器在執行每個語句時所采取的步驟。輸出中反映的每個語句包含一個語句文本行，后面緊接若干行分別描述執行步驟的詳細信息。

執行計劃輸出示例

這些結果是使用前面定義的查詢示例和在 SQL 查詢分析器中執行的“set showplan_text on”得出的。

查詢 1

select ckey1,col2 from testtable where ckey1 = 'a'


基于文本的執行計劃輸出

|--Clustered Index Seek (OBJECT:([TraceDB].[dbo].[testtable].[testtable1]),
SEEK:([testtable].[ckey1]='a') ORDERED FORWARD)


同等的圖形化執行計劃輸出

下圖顯示查詢 1 的圖形化執行計劃。

如果您的瀏覽器不支持嵌入式框架，請單擊此處在單獨的頁中查看。

執行計劃利用 ckey1 列上的聚集索引來解析查詢，正如聚集索引查找所示。

如果從表中刪除了聚集索引，并且再次執行相同的查詢，查詢將恢復使用表掃描。下面的圖形化執行計劃表明該行為變化。

基于文本的執行計劃輸出

|--Table Scan(OBJECT:([TraceDB].[dbo].[testtable]), WHERE:([testtable].[ckey1]=[@1]))