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

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

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

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

該執行計劃使用表掃描來解析查詢 1。要從小表檢索信息，最有效的方法是使用表掃描。但在大表上，由執行計劃指明的表掃描實際是一種警告，它說明表需要最好的索引，或者現有索引的統計信息需要更新。您可以使用 UPDATE STATISTICS 命令在表或索引上更新統計信息。如果啟發式頁與基礎索引值的同步差異過大，SQL Server 將自動更新索引。例如，如果您從 testtable 中刪除了所有包含 ckey1 值等于“b”的行，然后，沒有先更新統計信息就運行查詢。最好讓 SQL Server 自動維護索引統計信息，因為它有助于確保查詢始終能夠使用完好的索引統計信息。如果使用 ALTER DATABASE 語句將 AUTO_UPDATE_STATISTICS 數據庫選項設為 OFF，則 SQL Server 不會自動更新統計信息。

查詢 2

select nkey1,col2 from testtable where nkey1 = 5000


基于文本的執行計劃輸出

--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([TraceDB].[dbo].[testtable]))
|--Index Seek(OBJECT:([TraceDB].[dbo].[testtable].[testtable2]),
SEEK:([testtable].[nkey1]=Convert([@1])) ORDERED FORWARD)


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

下面兩圖顯示查詢 2 的圖形化執行計劃。

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

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

查詢 2 的執行計劃在 nkey1 列上使用非聚集索引。這是由 nkey1 列上的 Index Seek 操作指明的。Bookmark Lookup 操作指明 SQL Server 需要執行指針跳轉，從表的索引頁跳轉到數據頁，以檢索請求的數據。需要執行指針跳轉的原因是查詢要求查找 col2 列，而非聚集索引內不包含該列。

查詢 3

select nkey1 from testtable where nkey1 = 5000


基于文本的執行計劃輸出

|--Index Seek(OBJECT:([TraceDB].[dbo].[testtable].[testtable2]),
SEEK:([testtable].[nkey1]=Convert([@1])) ORDERED FORWARD)


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

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

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

查詢 3 的執行計劃使用 nkey1 上的非聚集索引作為覆蓋索引。請注意，該查詢不需要執行 Bookmark Lookup 操作。原因是該查詢（SELECT 和 WHERE 子句）所需的全部信息都由非聚集索引提供。這就是說，非聚集索引頁中不需要有指向數據頁的指針跳轉。與需要書簽查找的情況相比，I/O 有所減少。

系統監視

系統監視器提供大量的有關數據庫服務器執行期間所發生的 Windows 和 SQL Server 操作的信息。

在系統監視器的圖形模式下，請注意最大和最小值。因為過大和過小的數據點都會使平均值失真，所以對過于強調平均值的情況一定要小心。研究圖形的形狀并與最小和最大值比較，以便準確地理解行為。使用 BACKSPACE 鍵，用一條白線突出顯示計數器。

您可以使用系統監視器在日志文件中記錄所有可用的 Windows 和 SQL Server 系統監視器對象/計數器，而同時以交互方式查看系統監視器（圖表模式）。采樣間隔的設置決定了日志文件增大的速度。日志文件可能很快變大（例如，如果打開所有計數器，采樣間隔設為 15 秒，日志文件在 1 小時內就能達到 100 兆字節）。測試服務器上最好有足夠的空閑千兆字節來存儲這些類型的文件。不過，如果保留空間對您很重要，請嘗試采用較長的日志間隔，以免系統監視器過于頻繁地對系統采樣。請嘗試 30 或 60 秒。這樣，系統監視器會以合理的頻率對所有計數器重新采樣，同時又能保持較小的日志文件大小。

系統監視器也會耗用少量 CPU 資源和磁盤 I/O 資源。如果系統沒有多余的備用磁盤 I/O 和/或 CPU，請考慮從另一臺計算機運行系統監視器，然后通過網絡監視 SQL Server。在通過網絡監視時，請只使用圖形模式。與通過局域網發送信息相比，在 SQL Server 本地記錄性能監視信息的效率往往會更高。如果您必須通過網絡記錄日志信息，可以只記錄最重要的計數器信息。

在性能測試運行期間，將所有可用計數器的信息記錄到某個文件中供以后分析，這不失為一個好做法。這樣，對于任何計數器，以后都可以再作進一步檢查。您可以配置系統監視器將所有計數器記錄到日志文件中，與此同時，在其他某種模式（如圖形模式）下監視最感興趣的計數器。這樣，在性能運行期間，所有信息都會被記錄下來，但您最關注的計數器會以清晰整潔的系統監視器圖形顯示出來。

設置要記錄的系統監視器會話

1. 從 Windows 2000 開始菜單中，指向程序、管理工具，然后單擊性能，打開系統監視器。
2. 雙擊性能日志和警報，然后單擊計數器日志。
3. 現有的日志都會在詳細信息窗格中列出。綠色圖標表示日志正在運行；紅色圖標表示日志已被停止。
4. 右鍵單擊詳細信息窗格的空白區域，然后單擊新日志設置。
5. 在名稱中鍵入日志的名稱，然后單擊確定。
6. 在常規選項卡上，單擊添加。選擇要記錄的計數器。您在此處確定要在會話期間監視的 SQL Server 計數器。
7. 如果要更改默認文件，請在日志文件選項卡上進行更改。
8. 記錄的會話可以設置為按預定義的時間段自動運行。為此，請在調度選項卡上修改調度信息。

注意 要保存日志文件的計數器設置，請用右鍵單擊詳細信息窗格中的文件，然后單擊將設置另存為。然后，指定用來保存這些設置的 .htm 文件。要在新日志中重用已保存的設置，請用右鍵單擊詳細信息窗格，然后單擊新日志設置來自。

啟動已記錄的監視會話

1. 從 Windows 2000 開始菜單中，指向程序、管理工具，然后選擇性能，打開系統監視器。
2. 雙擊性能日志和警報，然后單擊計數器日志。
3. 右鍵單擊要運行的計數器日志，然后選擇啟動。
4. 現有的日志都會在詳細信息窗格中列出。綠色圖標表示日志正在運行；紅色圖標表示日志已被停止。

停止已記錄的監視會話

1. 從 Windows 2000 開始菜單中，指向程序、管理工具，然后選擇性能，打開系統監視器。
2. 雙擊性能日志和警報，然后單擊計數器日志。
3. 右鍵單擊要運行的計數器日志，然后選擇停止。

從已記錄的監視會話向系統監視器裝載數據供分析使用

1. 從 Windows 2000 開始菜單中，指向程序、管理工具，然后選擇性能，打開系統監視器。
2. 單擊系統監視器。
3. 右鍵單擊系統監視器的詳細信息窗格，然后單擊屬性。
4. 單擊源選項卡。
5. 在數據源下，單擊日志文件，然后鍵入文件路徑，或單擊瀏覽，查找所需的日志文件。
6. 單擊時間區間。要在日志文件中指定希望查看的時間區間，請拖動滑動條或滑動條柄，設置相應的開始和結束時間。
7. 單擊數據選項卡，然后單擊添加，打開添加計數器對話框。您在日志配置期間選擇的計數器會顯示出來。您可以在圖形中包括所有這些計數器或其中一部分。

如何使系統監視器記錄的事件與過去的某個時點相關

• 從系統監視器會話中，右鍵單擊系統監視器的詳細信息窗格，然后單擊屬性。時間區間和滑動條允許您設定要在圖形中查看的開始、當前和結束時間。

需要監視的關鍵性能計數器

有幾個性能計數器提供了有關以下重要方面的信息：內存、分頁、處理器、I/O 和磁盤活動。

監視內存

默認情況下，SQL Server 會根據可用系統資源動態更改其內存需求。如果 SQL Server 需要更多內存，它會查詢操作系統，以確定是否有可用的空閑物理內存，并使用可用內存。如果 SQL Server 當前不需要分配給它的內存，它將向操作系統釋放內存。不過，動態使用內存的選項會被服務器配置選項替代，這些選項是最小服務器內存、最大服務器內存和設置工作集大小。有關更多信息，請參閱“SQL Server 聯機叢書”。

要監視由 SQL Server 使用的內存量，請檢查下列性能計數器：

• 進程：工作集
• SQL Server：緩沖管理器：緩存命中率
• SQL Server：緩沖管理器：全部頁
• SQL Server：內存管理器：總的服務器內存 (KB)

工作集計數器顯示由進程使用的內存量。如果該數字一直低于 SQL Server 配置使用的內存量（由服務器選項最小服務器內存和最大服務器內存設置），說明為 SQL Server 配置的內存比它實際需要的內存多。否則，使用設置工作集大小服務器選項調整工作集的大小。

緩存命中率計數器是特定于應用程序的；不過，該比率達到或超過 90% 比較理想。請增多內存，直到該值穩定地達到 90% 以上，這樣就表明數據緩存滿足了 90% 以上的數據請求。

如果與計算機中的物理內存量相比，總的服務器內存 (KB) 計數器值一直較高，說明需要更多內存。

強制分頁

如果內存：頁/秒大于零或內存：頁讀取/秒大于五，說明 Windows 正在使用磁盤來解決內存引用（強制分頁錯誤）。它耗用磁盤 I/O + CPU 資源。內存：頁/秒清楚地指明了 Windows 正在執行的分頁量，以及數據庫服務器當前的 RAM 配置是否夠用。系統監視器中的強制分頁信息有一個子集記錄的是 Windows 為了解決內存引用而必須讀取分頁文件的次數/秒，它由內存：頁讀取/秒表示。如果內存：頁讀取/秒大于 5，則對性能不利。

為了避免分頁，SQL Server 自動內存優化將嘗試動態調整 SQL Server 對內存的使用。每秒讀取少量頁是正常的，但如果分頁過多，則需采取更正措施。

如果 SQL Server 自動優化內存，您可以選擇添加更多 RAM 或從數據庫服務器中刪除其他應用程序，以幫助內存：頁/秒達到合理水平。

如果 SQL Server 內存是在數據庫服務器上手動配置的，則需要減少指定給 SQL Server 的內存，從數據庫服務器中刪除其他應用程序，或向數據庫服務器添加更多 RAM。

保持內存：頁/秒為零或接近零，對數據庫服務器性能有利。這就是說，Windows 及其所有應用程序（包括 SQL Server）不會為滿足內存請求中的任何數據而轉到分頁文件，所以服務器上的 RAM 是充足的。頁/秒略大于零尚可接受，但請記住，每次從分頁文件（而非 RAM）檢索數據時，都將遭受相對較高的性能懲罰（磁盤 I/O）。

對與 Windows 分頁文件相關的所有驅動器間的內存：頁輸入/秒與邏輯磁盤：磁盤讀取/秒以及內存：頁輸出/秒與邏輯磁盤：磁盤寫入/秒進行比較是很有用的，因為通過它們可以知道真正與分頁而非其他應用程序（即 SQL Server）相關的磁盤 I/O 量。隔離分頁文件 I/O 活動的另一種便捷方式是確保分頁文件與所有其他 SQL Server 文件不在同一組驅動器上。將分頁文件與 SQL Server 文件隔開也對磁盤 I/O 性能有利，因為它允許與分頁相關的磁盤 I/O 和與 SQL Server 相關的磁盤 I/O 并行執行。

軟分頁

如果內存：分頁錯誤/秒大于零，說明 Windows 正在分頁，但計數器中既有強制分頁，也有軟分頁。我們已在上一節討論過強制分頁。軟分頁表示數據庫服務器上的應用程序正在請求的內存頁仍然位于 RAM 以內，但已位于 Windows 工作集之外。內存：分頁錯誤/秒有助于獲得正在發生的軟分頁量。系統中沒有稱為“軟分頁錯誤/秒”的計數器。您可以使用此公式計算每秒鐘發生的軟分頁錯誤數：內存：分頁錯誤/秒 - 內存：頁輸入/秒 = 軟分頁錯誤/秒

要確定導致過多分頁的是否是 SQL Server 而不是其他進程，請監視 SQL Server 進程的進程：分頁錯誤/秒計數器，并注意相關 Sqlservr.exe 實例的分頁錯誤數/秒是否接近內存：頁/秒的數值。

與硬分頁錯誤相比，軟分頁錯誤對性能的不利影響通常要小一些，因為它們耗用 CPU 資源。硬分頁錯誤耗用磁盤 I/O 資源。獲得良好性能的最佳環境是杜絕任何類型的錯誤。

注意 在 SQL Server 第一次訪問它所有的數據緩存頁時，對每一頁的第一次訪問均會導致軟分頁錯誤。在 SQL Server 第一次啟動和第一次使用數據緩存時，不必擔心最初的軟分頁錯誤。

監視處理器

您的目標應該是：盡可能充分地利用所有分配給服務器的處理器，以獲得最佳性能，而同時又避免因過于繁忙而出現處理器瓶頸。性能優化所面臨的挑戰是：如果 CPU 不是瓶頸，總有其他東西是瓶頸（很可能是磁盤子系統），因而浪費了 CPU 容量。通常，CPU 是最難擴展的資源（某些配置特定的級別除外，例如，許多最新系統上的 4 CPU 或 8 CPU），因此，如果繁忙系統上的 CPU 使用率超過 95%，說明系統運行良好。同時，您應該監視事務的響應時間，確保響應時間合理；如果響應時間不合理，而 CPU 使用率超過 95%，則可能說明可用 CPU 資源承擔的工作負荷過多，您要么增加 CPU 資源，要么減少或優化工作負荷。

請查看系統監視器計數器處理器：% 處理器時間，確保每個 CPU 上的處理器使用率一直低于 95%。系統：處理器隊列長度是 Windows 系統上所有 CPU 的處理器隊列。如果每個 CPU 的系統：處理器隊列長度大于二，則說明出現了 CPU 瓶頸。在檢測到 CPU 瓶頸時，您需要向服務器添加處理器，或減少系統上的工作負荷。減少工作負荷的方法是：通過優化查詢或改進索引來減少 I/O，從而減少 CPU 使用率。

在懷疑出現 CPU 瓶頸時需要監視的另一個系統監視器計數器是系統：上下文切換/秒，因為它指明了 Windows 和 SQL Server 必須從在一個線程上執行切換到在另一個線程上拖動的頻率（次數/秒）。它耗用 CPU 資源。上下文切換是多線程、多處理器環境的正常組件，但過多的上下文切換會降低系統性能。應對方法是在有處理器隊列時，只關注上下文切換。

如果觀察處理器隊列，則將上下文切換級別用作 SQL Server 性能優化的尺度。如果看起來是上下文切換導致出現瓶頸，您可以考慮兩種方法：使用關系掩碼選項，使用基于纖程的調度。

使用關系掩碼選項可以提高重負荷下運行的對稱多處理器 (SMP) 系統（微處理器數量超過四個）的性能。您可以使線程與特定處理器相關，并指定 SQL Server 將使用的處理器。您還可以使用關系掩碼選項設置來阻止 SQL Server 活動使用某些處理器。在更改關系掩碼的設置之前，請記住，Windows 會將與 NIC 的相關延遲進程調用 (DPC) 活動分配給系統中編號最高的處理器。在安裝并激活了多個 NIC 的系統中，另外每增加一個卡的活動，就會分配給下一個編號最高的處理器。例如，安裝了兩個 NIC 的八處理器系統將每個 NIC 的 DPC 分配給處理器 7 和處理器 6（從 0 開始計數）。在使用輕量池選項時，SQL Server 切換到基于纖程的調度模式，而不是默認的基于線程的調度模式。纖程本質上是輕量線程。使用命令 sp_configure 'lightweight pooling',1 可啟用基于纖程的調度。

通過監視處理器隊列和上下文切換，您可以監視設置關系掩碼和輕量池的值之后產生的效果。某些情況下，這些設置非但不會改進性能，反而會使性能下降。另外，除非系統中有四個或更多個處理器，否則它們一般不會帶來很大的收益。DBCC SQLPERF (THREADS) 提供了映射回 SPID 的有關 I/O、內存以及 CPU 使用率的更多信息。執行下面的 SQL 查詢可調查當前最耗用 CPU 時間的使用者：

select * from master.sysprocesses order by cpu desc


監視處理器隊列長度

如果系統：處理器隊列長度大于二，說明服務器的處理器收到的工作請求多于它們能夠以一個組的方式集體處理的請求。因此，Windows 需要將這些請求放在隊列中。

某些處理器隊列說明 SQL Server 的總體 I/O 性能良好。如果沒有處理器隊列，并且 CPU 使用率低，說明系統某處可能出現了性能瓶頸，最有可能的地方就是磁盤子系統。處理器隊列中留有合理的工作請求量，這說明 CPU 并不空閑，系統的其余部分也與 CPU 保持同步。

根據一般經驗，理想的處理器隊列數是數據庫服務器中的 CPU 數乘以二。

如果處理器隊列數明顯高于該值，可能表明服務器遇到了 CPU 瓶頸，您需要進行調查。過多的處理器隊列會耗用查詢執行時間。多個不同活動可能導致出現處理器隊列。消除強制分頁和軟分頁有助于節省 CPU 資源。其他有助于減少處理器隊列的方法包括：優化 SQL 查詢、挑選更好的索引以減少磁盤 I/O（從而減少 CPU 使用量）、在系統中添加更多 CPU（處理器）。

監視 I/O

磁盤寫入字節/秒和磁盤讀取字節/秒計數器表明磁盤的數據吞吐量，以每個邏輯驅動器或物理驅動器每秒的字節數計。請仔細地將這些數字與磁盤讀取/秒和磁盤寫入/秒均衡比較。不要看到較低的字節數/秒，就相信磁盤 I/O 子系統不忙。

監視與 SQL Server 文件相關的所有驅動器的磁盤隊列長度，然后確定哪些文件與過多的磁盤隊列相關。

如果系統監視器表明某些驅動器不如其他驅動器繁忙，則可將 SQL Server 文件從出現瓶頸的驅動器移到不太繁忙的驅動器。這樣有助于將磁盤 I/O 活動更均勻地分布到各個硬盤。如果將一個大型驅動器池用于 SQL Server 文件，磁盤隊列的解決方法是在池中添加更多的物理驅動器，從而增大驅動器池的 I/O 容量。

出現磁盤隊列可能表明某個 SCSI 通道中的 I/O 請求數量已達到飽和。系統監視器無法直接確定該情況是否屬實。存儲器供應商通常會另外提供工具，以幫助監視 RAID 控制器所服務的 I/O 數量，以及控制器是否在對 I/O 請求進行排隊。如果 SCSI 通道上連接了許多磁盤驅動器（十個或更多個），并且所有驅動器都以全速執行 I/O，則更有可能發生該情況。應對此情況的解決方案是：將一半磁盤驅動器連接到另一個 SCSI 通道或 RAID 控制器，以平衡該 I/O。通常，在 SCSI 通道之間重新平衡驅動器需要重建 RAID 陣列以及完全備份/還原 SQL Server 數據庫文件。

磁盤時間百分比

在系統監視器中，物理磁盤：% 磁盤時間和邏輯磁盤：% 磁盤時間計數器監視磁盤因讀取/寫入活動而處于繁忙狀態的時間百分比。如果 % 磁盤時間計數器很高（超過 90%），請檢查當前磁盤隊列長度計數器，以查看有多少系統請求正在等待磁盤訪問。等待 I/O 的請求數量應該始終不超過構成物理磁盤的軸數量的 1.5 至 2 倍。大多數磁盤只有一個軸，然而不昂貴的磁盤冗余陣列 (RAID) 設備通常有多個軸。硬件 RAID 設備在系統監視器中顯示為一個物理磁盤；通過軟件創建的 RAID 設備顯示為多個實例。

磁盤隊列長度

監視過長的磁盤隊列是一項重要任務。

要監視磁盤隊列長度，您需要觀察多個系統監視器磁盤計數器。要啟用這些計數器，請從 Windows 2000 或 Windows NT 命令窗口運行 diskperf –y 命令，然后重新啟動計算機。

出現磁盤隊列的物理硬盤驅動器將在彌補 I/O 處理的同時阻止磁盤 I/O 請求。這些驅動器上的 SQL Server 響應時間也不如從前。此操作會耗用查詢執行時間。

如果使用 RAID，為了計算每個物理驅動器的磁盤隊列，您需要了解有多少個物理硬盤驅動器與每個 Windows 視為單個物理驅動器的驅動器陣列相關。為了了解每個物理驅動器保存 SQL Server 數據的具體方式，以及每個 SCSI 通道上分發的 SQL Server 數據量，請向硬件專家咨詢，讓他們來解釋 SCSI 通道和物理驅動器分發。

通過系統監視器查看磁盤隊列的選擇有多種。邏輯磁盤計數器與通過磁盤管理器分配的邏輯驅動器盤符相關，而物理磁盤計數器與磁盤管理器視為一個物理磁盤設備的內容相關。請注意，磁盤管理器視為一個物理設備的驅動器可能是一個硬盤驅動器，也可能是一個包含多個硬盤驅動器的 RAID 陣列。當前磁盤隊列長度是對磁盤隊列的即時度量，而平均磁盤隊列長度是采樣期間磁盤隊列度量的平均值。如果指明出現以下任一情況，請加以注意：

• 邏輯磁盤：平均磁盤隊列長度 > 2
• 物理磁盤：平均磁盤隊列長度 > 2
• 邏輯磁盤：當前磁盤隊列長度 > 2
• 物理磁盤：當前磁盤隊列長度 > 2

這些建議的度量適于每個物理硬盤驅動器。如果 RAID 陣列與磁盤隊列度量相關，則需要用該度量除以 RAID 陣列中的物理硬盤驅動器的數量，以確定每個物理硬盤驅動器的磁盤隊列。

注意 在保存 SQL Server 日志文件的物理硬盤驅動器或 RAID 陣列上，磁盤隊列不是有用的度量方法，因為日志管理器不會對多個針對 SQL Server 日志文件的 I/O 請求進行排隊。

了解 SQL Server 技術內幕

了解 SQL Server 2000 的一些技術內幕有助于您管理數據庫的性能。

工作線程

SQL Server 維護著一個 Windows 線程池，這些線程的作用是為成批提交到數據庫服務器的 SQL Server 命令提供服務。sp_configure 選項最大工作線程的設置規定了可以為所有傳入的命令批提供服務的線程（在 SQL Server 術語中稱為工作線程）的總數。如果主動提交命令批的連接數大于指定的最大工作線程數，將在主動提交命令批的連接之間共享工作線程。許多安裝都適合使用默認值 255。請注意，大部分連接大多數的時間都在等待從客戶端接收命令批。

從 SQL Server 緩沖區緩存中寫出 8 KB 臟頁的任務主要由工作線程來完成。為了獲得最佳性能，工作線程會異步調度它們的 I/O 操作。

惰性寫入器

惰性寫入器是在緩沖管理器內運行的 SQL Server 系統進程。惰性寫入器刷新臟的舊緩沖（必須先將這些緩沖內所含的更改寫入磁盤，隨后才能將緩沖重新用于其他不同的頁）批，然后將它們提供給用戶進程。該活動有助于生成和維護可用的空閑緩沖，它們是大小為 8 KB，不含任何數據，可以重新使用的數據緩存頁。在惰性寫入器將每個 8 KB 緩存緩沖區刷新到磁盤上時，緩存頁的標識會被初始化，這樣，其他數據就可以寫入空閑的緩沖區。惰性寫入器在磁盤 I/O 量少時工作，從而將該活動對其他 SQL Server 操作的影響減到最小。

SQL Server 自動配置和管理空閑緩沖水平。性能計數器 SQL Server：緩沖管理器：惰性寫入/秒指明了物理寫出到磁盤的 8 KB 頁的數量。請監視 SQL Server：緩沖管理器：可用頁，查看該值是否下降。最佳狀態是：惰性寫入器使該計數器在所有 SQL Server 操作之間保持水平，這意味著惰性寫入器與用戶對空閑緩沖的需求保持同步。如果系統監視器對象 SQL Server：緩沖管理器：可用頁的值達到零，說明用戶負載有時需要較高水平的空閑緩沖，而惰性寫入器無法提供這一水平的空閑緩沖。

如果惰性寫入器難以使空閑緩沖保持穩定或至少保持在零以上，說明磁盤子系統可能無法提供足夠的磁盤 I/O 性能。要證明是否確實如此，請將空閑緩沖水平的下降與磁盤隊列作比較。解決辦法是向數據庫服務器磁盤子系統添加更多物理磁盤驅動器，以提高磁盤 I/O 處理能力。

在系統監視器中監視當前的磁盤隊列水平，方法是查看邏輯磁盤或物理磁盤的性能計數器平均磁盤隊列長度或當前磁盤隊列長度，確保與任何 SQL Server 活動相關的每個物理驅動器的磁盤隊列小于 2。對于使用硬件 RAID 控制器和磁盤陣列的數據庫服務器，記住用“邏輯/物理磁盤”計數器報告的數字除以與該邏輯驅動器盤符或物理硬盤驅動器盤符（依據磁盤管理器的報告）相關的實際硬盤數量，因為 Windows 和 SQL Server 不知道與 RAID 控制器相連的物理硬盤驅動器的實際數量。為了正確地解釋系統監視器報告的磁盤隊列數量，一定要知道與 RAID 陣列控制器相關的驅動器數量。

有關更多信息，請參閱“SQL Server 聯機叢書”。

檢查點

SQL Server 的每個實例需要定期確保將所有臟日志和數據頁刷新到磁盤。這稱為檢查點。在重新啟動 SQL Server 的實例時，使用檢查點可以減少從故障中恢復所需的時間和資源。在檢查點期間，臟頁（進入緩沖區緩存后已經過修改的緩沖區緩存頁）會被寫入 SQL Server 數據文件。在檢查點處寫入磁盤的緩沖仍然包含數據頁，用戶可以讀取或更新該頁，而不必從磁盤重新讀取，這一點與惰性寫入器創建的空閑緩沖不同。

檢查點邏輯嘗試讓工作線程和惰性寫入器負責大部分的臟頁寫出工作。為此，如有可能，檢查點邏輯在寫出臟頁之前，嘗試額外多等待一個檢查點。這樣，工作線程和惰性寫入器就有更多的時間來寫出臟頁。某些情況下，檢查點邏輯在寫出臟頁之前需要額外多等一段時間，有關這些情況的詳細信息，請參閱“SQL Server 聯機叢書”中的主題“檢查點和日志的活動部分”。要請住的重點是，檢查點邏輯會嘗試通過等待額外的檢查點在更長的時間段內均衡 SQL Server 磁盤 I/O 活動。

在有大量的數據頁需要從緩存刷新到磁盤上時，為了使檢查點操作更有效，SQL Server 將要刷新的數據頁按照它們在磁盤上出現的順序進行排序。這有助于盡量減少磁盤在緩存刷新過程中的來回移動，并在可能的情況下使用連續磁盤 I/O。檢查點進程也向磁盤子系統異步提交 8 KB 磁盤 I/O 請求。這樣，SQL Server 就能更快地完成對所需磁盤 I/O 請求的提交，因為檢查點進程不必等待磁盤子系統發回指明已將數據實際寫入磁盤的報告。

重要的一點是要監視與 SQL Server 數據文件相關的硬盤驅動器上的磁盤隊列，確定 SQL Server 目前發送的 I/O 請求是否超過磁盤的實際處理能力；如果情況屬實，必須提高磁盤子系統的磁盤 I/O 能力，使它能夠處理負載。

日志管理器

像所有其他主流 RDBMS 產品一樣，SQL Server 也可以確保在發生中斷 SQL Server 聯機狀態的事件（例如，斷電、磁盤驅動器有故障、數據中心起火，等等）時，數據庫上執行的所有寫入活動（插入、更新和刪除）不會丟失。SQL Server 日志記錄進程有助于確�？苫謴托�。在完成任何隱式（單個 SQL 查詢）或顯式事務（所定義的發出 BEGIN TRAN/COMMIT 或 ROLLBACK 命令序列的事務）之前，日志管理器必須從磁盤子系統收到信號，表明與該事務相關的所有數據更改均已成功寫入相關的日志文件。這一規則可以確保：如果 SQL Server 因某種原因而突然關機，而檢查點和惰性寫入器尚未將寫入數據緩存的事務刷新到數據文件，那么，SQL Server 可以在啟動后讀取和重新應用事務日志�；謴褪侵阜�務器停機之后讀取事務日志以及向 SQL Server 數據應用事務。

由于在每個事務完成時，SQL Server 必須等待磁盤子系統完成對 SQL Server 日志文件的 I/O，所以包含 SQL Server 日志文件的磁盤要有足夠的磁盤 I/O 處理能力來承受預期的事務負載，這一點很重要。

SQL Server 日志文件的相關磁盤隊列的監視方法與 SQL Server 數據庫文件的相關磁盤隊列的監視方法不同。請使用系統監視器計數器 SQL Server：數據庫 <數據庫實例>：日志刷新等待時間和 SQL Server：數據庫 <數據庫實例>：日志刷新等待/秒來查看磁盤子系統上是否有處于等待完成狀態的日志寫入器請求。

具備緩存功能的控制器性能最高，但除非該控制器能夠確保將它負責的數據最終寫入磁盤，甚至在電源故障時也能最終寫入磁盤，否則不得將它用于包含日志文件的磁盤。有關具備緩存功能的控制器的更多信息，請參閱本章的“硬件 RAID 控制器板載緩存的效果”一節。

預讀管理

SQL Server 2000 為大規模連續讀取表掃描等活動提供了自動管理功能。預讀管理完全自行配置和自行優化，并且與

SQL Server 查詢處理器的操作緊密地結合在一起。預讀管理用于大表掃描、大索引區間掃描、探測聚集索引和非聚集索引二進制樹，以及其他情況。原因是預讀采用的是 64 KB I/O，與 8 KB I/O 相比，64 KB I/O 能使磁盤子系統達到更大的磁盤吞吐量。如果需要檢索大量數據，SQL Server 就使用預讀來獲得最大的吞吐量。

SQL Server 使用簡單有效的索引分配映射表 (IAM) 存儲結構，該結構支持預讀管理。IAM 是 SQL Server 用于記錄擴展盤區位置的機制，即每 64 KB 擴展盤區包含八頁數據或索引信息。每個 IAM 頁是包含緊密打包（位映射）信息的 8 KB 頁，這些信息指明哪些擴展盤區包含所需的數據。IAM 頁的壓縮特性加快了它們的讀取速度，經常使用的 IAM 頁還可以保留在緩沖區緩存中。

預讀管理可以將來自查詢處理器的查詢信息與需要從 IAM 頁讀取的所有擴展分區的位置信息組合在一起，從而構成多個連續的讀取請求。連續的 64 KB 磁盤讀取提供優異的磁盤 I/O 性能。SQL Server：緩沖管理器：預讀頁/秒性能計數器提供有關預讀管理的有效性及效率的信息。

SQL Server 2000 企業版根據現有內存量動態調整預讀頁的最大數量。在 SQL Server 2000 的所有其他版本中，該值固定不變。SQL Server 2000 企業版的另一改進之處是通常所說的“旋轉木馬式掃描”，它允許多個任務共享整個表掃描。如果 SQL 語句的執行計劃要求掃描表中的數據頁，并且關系數據庫引擎檢測到已經為另一個執行計劃掃描過該表，那么數據庫引擎在第二次掃描的當前位置將第二次掃描加入到第一次掃描中。數據庫引擎每次讀取一頁，并將每一頁的所有行同時傳遞到這兩個執行計劃。此操作會一直進行，直至到達表的結尾。此時，第一個執行計劃具有完整的掃描結果，但第二個執行計劃仍必須檢索在它加入正在進行的掃描之前所發生的數據頁。然后，為第二個執行計劃執行的掃描會折返回表的第一個數據頁，并且向前掃描至它加入第一次掃描的位置。用這種方式可以組合任意數量的掃描；數據庫引擎將一直在所有數據頁之間循環，直到完成所有掃描。

有關預讀管理需要注意一點，那就是過多的預讀會對總體性能不利，因為它在緩存內填入不需要的數據頁，占用本應用于其他用途的 I/O 和 CPU。對于這一點，只能通過一般的性能優化來解決：優化所有 SQL 查詢，盡量減少進入緩沖區緩存的頁數量。它包括確保具備正確的索引并在使用這些索引。使用聚集索引可以獲得有效的區間掃描，定義非聚集索引有助于快速定位單行或更小的行集。例如，如果您準備在表中只創建一個索引，并且該索引將用于提取單行或更小的行集，該索引應為聚集索引。從表面上看，聚集索引比非聚集索引的速度快。

其他性能主題

使用星型架構和雪花形架構的數據庫設計

數據倉庫使用維度建模來組織數據，以便進行分析。維度建模會生成星型架構和雪花架構，這樣也就為數據倉庫中經常執行的大量數據讀取操作帶來了性能效率。大量的數據（通常成千上萬行）存儲在事實數據表中，表內各行都很短，這就使存儲需求和查詢時間減到最少。業務事實數據的屬性會非正�；癁榫S度表，以最大程度地減少檢索數據時的表聯接數量。

有關數據倉庫的數據庫設計的討論，請參閱第 17 章“數據倉庫設計注意事項”。

在 Transact-SQL 查詢中使用等價運算符

在 SQL 查詢中使用非等價運算符將強制數據庫使用表掃描來對非等價對象取值。如果經常對非常大的表運行這些查詢，將會生成高 I/O。包含“NOT”運算符（!=、<>、!<、!>）的 WHERE 子句（如 WHERE <column_name> != some_value）將生成高 I/O。

如果需要運行此類查詢，請嘗試更改查詢的結構，從其中消除 NOT 關鍵字。例如：

不使用：

select * from tableA where col1 != "value"


嘗試使用：

select * from tableA where col1 < "value" and col1 > "value"


減少行集大小和通訊開銷

使用 Microsoft ActiveX® 數據對象 (ADO)、遠程數據對象 (RDO) 和數據訪問對象 (DAO) 數據庫 API 等易用界面的 SQL 數據庫程序員需要考慮他們生成的結果集。

ADO、RDO 和 DAO 為程序員提供了極好的數據庫開發界面，程序員即使沒有太多的 SQL 編程經驗也能實現豐富的 SQL 行集功能。如果程序員仔細考慮他們的應用程序返回到客戶端的數據量，并且跟蹤 SQL Server 索引的位置以及 SQL Server 數據的安排方式，就能避免性能問題。SQL 事件探查器、索引優化向導和圖形化的執行計劃都是非常有用的工具，它們可以幫助程序員精確定位和修復出現問題的查詢。

在使用游標邏輯時，請選擇最適合您的處理類型的游標。不同類型的游標開銷也不同。您應該了解所要執行的是何種類型的操作（只讀、只向前處理，等等），然后選擇相應的游標類型。

尋找各種機會來減少返回的結果集的大小，方法包括在選擇列表中消除不需要返回的列、只返回所需的行。這有助于減少 I/O 和 CPU 消耗。

使用多個語句

通過在數據庫上執行處理，您可以減少結果集的大小，并避免在客戶端和數據庫服務器之間進行不必要的網絡通訊。為了執行無法用單個 Transact-SQL 語句執行的處理，SQL Server 允許您將多個 Transact-SQL 語句以下列方式組合在一起。

下面的 SQL Server 功能使您可以對同時使用多個 Transact-SQL 語句的情況進行控制。

重用執行計劃

如果 SQL Server 能夠利用先前查詢的現有執行計劃，則可以提高性能。要促使 SQL Server 重用執行計劃，開發人員可以做的工作有很多。Transact-SQL 語句應根據以下原則編寫。

• 使用對象的完全限定名，例如表和視圖。

  例如，請不要這樣編寫 SELECT 語句：

  SELECT * FROM Shippers WHERE ShipperID = 3


  而應使用 SQLBindParameter ODBC 函數（以使用 ODBC 為例）：

  SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3


• 使用參數化查詢，并提供參數值，而不要指定存儲過程參數值或直接在搜索條件謂詞中指定值。使用 sp_executesql 中的參數替代，或使用 ADO、OLE DB、ODBC 和 DB-Library API 的參數綁定。

  例如，請不要這樣編寫 SELECT 語句：

  SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3


  而應使用 SQLBindParameter ODBC 函數（以使用 ODBC 為例），將參數標記 (?) 綁定到程序變量，并按下面這樣編寫 SELECT 語句：

  SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = ?


• 在 Transact-SQL 腳本、存儲過程或觸發器中，使用 sp_executesql 執行 SELECT 語句：

  DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
/* Build the SQL string. */
SET @SQLString =
N'SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = @ShipID'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@ShipID int'
/* Execute the string. */
SET @IntVariable = 3
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ShipID = @IntVariable


  如果要避免創建和維護單獨的存儲過程的開銷，則可使用 sp_executesql。

對多個批處理重用執行計劃

如果多個并發應用程序將用一組已知參數執行同一個批處理，請將該批處理實現為將由這些應用程序調用的存儲過程。

在 ADO、OLE DB 或 ODBC 應用程序將會多次執行同一個批處理時，請使用執行該批處理的 PREPARE/EXECUTE 模型。使用綁定到程序變量的參數標記來提供所需的全部輸入值，例如，在 UPDATE VALUES 子句或搜索條件謂詞中使用的表達式。

維護列中的統計信息

SQL Server 允許創建與某個列中值的分布有關的統計信息，即使該列不是索引的一部分也不成問題。查詢處理器可以使用該統計信息來確定評估查詢的最佳策略。在您創建索引時，SQL Server 會自動存儲與索引列中的值的分布有關的統計信息。除索引列外，如果 AUTO_CREATE_STATISTICS 數據庫選項設置為 ON（默認設置），只要在謂詞中使用了某個列，即使該列不在索引中，SQL Server 也會自動創建該列的統計信息。

隨著列中數據的更改，索引和列的統計信息將會過時，從而導致查詢優化程序所做的有關如何處理查詢的決策也不如以前理想。隨著表中數據的更改，SQL Server 會定期地自動更新此統計信息。采樣是在數據頁中隨機進行的，而且是從統計信息所需的表中或列上最小的非聚集索引中采樣。在從磁盤讀取了數據頁之后，該數據頁上的所有行都會用來更新統計信息。更新統計信息的頻率由列或索引中的數據量以及發生更改的數據量決定。

例如，某個表中包含 10,000 行，如果其中的 1,000 個索引值發生了更改，這時就可能需要更新該表的統計信息，因為這 1,000 個值可能代表了表中很大一部分數據。但是，對于包含 1000 萬個索引條目的表而言，其中 1000 個索引值發生了更改就沒有太大關系，因此可能不會自動更新統計信息。不過，SQL Server 始終會確保對最小數量的行進行采樣；始終會對小于 8 MB 的表通過完全掃描來收集統計信息。

注意 使用 SQL 查詢分析器以圖形方式顯示查詢的執行計劃時，將以示警的形式（表名用紅色文字顯示）指出統計信息過時或缺少統計信息。另外，使用 SQL 事件探查器監視缺少的列統計信息事件類，可以發現什么時候缺少統計信息。

通過使用 sp_createstats 系統存儲過程，您可以使用單個語句，在當前數據庫內的所有用戶表中的所有適合的列上很輕松地創建統計信息。不適于創建統計信息的列包括：不確定的或不精確的計算列，或是數據類型為 image、text 和 ntext 的列。

如果手動創建統計信息，則您可以創建包含多個列密度（列組合重復的平均數）的統計信息。例如，某個查詢包含以下子句：

WHERE a = 7 and b = 9


同時在兩列（a 和 b）上創建手動統計信息可以使 SQL Server 更好地預估查詢，因為統計信息也包含 a 和 b 列組合的非重復值的平均數。這樣，SQL Server 就可以利用 col1 上建立的索引（此情況下最好為聚集索引），而不必進行表掃描。有關如何創建列統計信息的信息，請參閱“SQL Server 聯機叢書”中的主題“創建統計信息”。

查找更多信息

• “SQL Server 聯機叢書”提供了有關 SQL Server 結構和數據庫優化的信息，同時還提供了完整的命令語法和管理的文檔資料�！癝QL Server 聯機叢書”可以從 SQL Server 安裝介質安裝到任何 SQL Server 客戶端或服務器計算機上。
• 有關 Microsoft SQL Server 的最新信息，包括有關 SQL Server 的技術論文，請訪問下面的 Microsoft SQL Server Web 站點：
  • http://www.microsoft.com/china/sql/
  • http://www.microsoft.com/china/technet/sql/
  • http://msdn.microsoft.com/sqlserver/
• http://www.sqlmag.com 中有以期刊的形式提供信息的外部資源。您可以在其中找到許多優化和調整提示、代碼示例、概述 SQL Server 內部工作原理的見解深刻的文章，以及其他有價值的信息。
• Delaney、Kalen 和 Soukup, Ron 合著。Inside Microsoft SQL Server 2000（Microsoft SQL Server 2000 技術內幕），Microsoft Press，2001

  該書是對上一版本（Inside Microsoft SQL Server 7.0 (Microsoft SQL Server 7.0 技術內幕)）的更新，其中納入了 SQL Server 2000 的信息。該書深入探討了 SQL Server 的許多內部概念。

• Kimball, Ralph 著。The Data Warehouse Lifecycle Toolkit（數據倉庫生存期工具箱），John Wiley 和 Sons 合著，1998 年。

  該書深入探討數據倉庫的數據庫設計，并解釋了維度建模概念

文章來源于領測軟件測試網 http://www.kjueaiud.com/