数据仓库的 RDBMS 性能优化指南
轉：http://www.microsoft.com/china/technet/prodtechnol/sql/2000/maintain/c2061.asp
數據倉庫的 RDBMS 性能優化指南
本性能優化指南旨在幫助數據庫管理員和開發人員配置 Microsoft? SQL Server? 2000，以獲得最佳的性能，并幫助找出造成關系數據庫（包括用于數據倉庫的數據庫）性能低下的原因。本指南還就如何裝載、索引和編寫查詢以訪問 SQL Server 中存儲的數據提供了指導原則和最佳做法。另外還介紹了多種可用于分析性能特征的 SQL Server 工具。
簡介
Microsoft SQL Server 7.0 中引入了一項重大改進：一個在很大程度上可以自行配置、自行優化和自行管理的數據庫引擎。在 SQL Server 7.0 面世之前，大多數數據庫服務器都會耗費數據庫管理員大量的時間和精力，他們必須手動優化服務器配置以獲得最佳性能。實際上，很多競爭性數據庫產品現在仍要求管理員手動配置和優化他們的數據庫服務器。這是許多客戶改用 SQL Server 的主要原因。SQL Server 2000 是在 SQL Server 7.0 奠定的堅實基礎上更上層樓的產品。SQL Server 的目標是：通過實現數據庫引擎自行優化并允許 DBA 自動完成管理任務，使 DBA 不必手動配置和不斷優化數據庫服務器。
盡管現在仍可以手動配置和調整一些 sp_configure 選項，但建議數據庫管理員盡量不要這樣做，而是讓 SQL Server 自動配置和優化。對于這種調整能力，SQL Server 7.0 具有已被廣泛認可且經過實踐證明的成績記錄，SQL Server 2000 對此方案進行了顯著的改進。環境中不斷變化的條件可能會對數據庫性能產生負面影響，所以，讓 SQL Server 自行優化，就可以使數據庫服務器進行動態調整以適應這些變化。
性能優化的基本原則
您可以采取許多措施來管理數據庫的性能。SQL Server 2000 提供了幾種工具來幫助您完成這些任務。
管理性能
讓 SQL Server 完成大多數優化任務。
SQL Server 2000 經過了重大改進，可以創建基本上能夠進行自動配置和自行優化的數據庫服務器。利用 SQL Server 自動優化設置這一優點，可幫助 SQL Server 即使在用戶負荷和查詢隨時間不斷變化的情況下也能以最高性能運行。
管理 RAM 緩存。
RAM 是一種有限的資源。任何數據庫服務器環境的一個主要部分就是管理隨機存取存儲器 (RAM) 緩沖區緩存。訪問 RAM 緩存中的數據比訪問磁盤中同樣的信息要快得多。但是，RAM 資源是有限的。如果可以將數據庫 I/O（針對物理磁盤子系統的輸入/輸出操作）減少到最低所需的數據集和索引頁，這些頁將在 RAM 中停留更長時間。如果流入緩沖區緩存的不必要的數據和索引信息過多，會將有價值的頁很快排擠出去。性能優化的主要目標是減少 I/O，以使緩沖區緩存得到最充分的利用。
創建和維護合適的索引。
對于所有的數據庫查詢，維護最小 I/O 的一個關鍵因素是確保創建和維護合適的索引。
對大數據集和索引進行分區。
要減少總體 I/O 爭用現象并改善并行操作，請考慮對表數據和索引進行分區。本章介紹了使用 SQL Server 2000 實現和管理分區的多種方法。
監視磁盤 I/O 子系統的性能。
物理磁盤子系統必須為數據庫服務器提供足夠的 I/O 處理能力，以使數據庫服務器在運行時不會出現磁盤排隊現象。磁盤排隊現象會導致性能不佳。本文介紹了如何檢測磁盤 I/O 問題以及如何解決這些問題。
優化應用程序和查詢。
當數據庫服務器通過給定的應用程序為來自成百上千個連接的請求提供服務時，這種優化變得尤為重要。由于應用程序通常會確定
要在數據庫服務器上執行的 SQL 查詢，所以應用程序開發人員一定要了解 SQL Server 的結構基礎知識以及如何充分利用 SQL Server 的索引來最小化 I/O。
優化活動數據。
在許多商業智能數據庫中，絕大多數數據庫活動都會涉及最近一個月或一個季度的數據（多達 80% 的數據庫活動都可能是因最近裝載的數據而發生的）。要保持良好的數據庫總體性能，在對這些數據進行裝載、索引和分區時，所采取的方式一定要能夠為這些數據提供最佳的數據訪問性能。
利用 SQL Server 性能工具
SQL 事件探查器和索引優化向導
SQL 事件探查器可用于監視和記錄 SQL Server 的工作負荷。然后，可以將記錄下來的工作負荷可以提交到索引優化向導，這樣，在必要的時候就可以更改索引來幫助提高性能。SQL 事件探查器和索引優化向導可幫助管理員實現優化的索引。定期使用這些工具可使 SQL Server 保持良好的性能，即使查詢工作負荷隨時間而變化也是如此。
SQL 查詢分析器和圖形執行計劃
在 SQL Server 2000 中，SQL 查詢分析器提供了“圖形執行計劃”，使用這種方法可以方便地分析有問題的 SQL 查詢。“統計 I/O”是 SQL 查詢分析器的另一個重要功能，本章后面將會進行介紹。
系統監視器對象
SQL Server 提供了一組系統監視器對象和計數器，可以為監視和分析 SQL Server 的運行狀況提供信息。本章介紹了要監視的關鍵計數器。
影響性能的配置選項
最大異步 IO
SQL Server 7.0 中的手動配置選項最大異步 I/O 在 SQL Server 2000 中已經實現了自動化。以前，最大異步 I/O 用于指定在一次檢查點操作過程中，SQL Server 7.0 可以向 Microsoft Windows NT? 4.0 和 Windows? 2000 同時提交的磁盤 I/O 請求的數量。Windows 接下來又將這些請求提交到物理磁盤子系統。此配置設置實現自動化后，SQL Server 2000 就能夠以動態方式自動維護最佳的 I/O 吞吐量。
注意 Windows 98 不支持異步 I/O，因此在該平臺上不支持最大異步 I/O 選項。
數據庫恢復模型
SQL Server 2000 引入了在數據庫級別對事務記錄方式進行配置的功能。選定的模型會對性能產生很大的影響，尤其是在數據裝載過程中。恢復模型有三種：“完全”、“大容量日志記錄的”和“簡單”。新數據庫的恢復模型是在新數據庫創建時從模型數據庫繼承的。在創建數據庫之后，可以更改它的模型。
“完全恢復”可為將數據庫恢復到以前的時間點提供最大的靈活性。
“大容量日志記錄的恢復”可為某些大規模操作（例如，創建索引或大容量復制）提供較高的性能并占用較少的日志空間。這種恢復的缺點是與時間點恢復相比不夠靈活。
“簡單恢復”提供的性能最高，占用的日志空間最少，但在系統發生故障時丟失數據的風險很大。在使用“簡單恢復”模型時，數據只能恢復到上次（最近一次）進行完全數據庫或差異備份時的狀態。在這一模型中，由于事務在日志中會從檢查點處截斷，因此不能使用事務日志備份來恢復事務。這會產生丟失數據的可能。在不再需要日志空間以從服務器故障（活動事務）進行恢復之后，日志空間就會被截斷并重新使用。
經驗豐富的管理員可以使用這個恢復模型功能來大大加快數據裝載和大容量操作的速度。不過，根據所選模型不同，丟失數據的可能性也各不相同。
重要說明 在選擇某種恢復模型之前，必須仔細考慮會遇到的風險。
每種恢復模型致力于滿足不同的需要。請根據所選的模型權衡利弊。權衡的結果需對性能、空間利用率（磁盤或磁帶）和防范數據丟失的保護措施等方面加以綜合考慮。當您選擇恢復模型時，需要結合以下幾個方面的業務需求作出決定：
大規模操作（例如，創建索引或大容量裝載）的性能
數據丟失的可能性（例如，丟失已提交的事務）
事務日志空間占用大小
備份和恢復過程的難易
根據所執行的操作不同，一種模型可能會比另一種模型更適合。在選擇一種恢復模型之前，請考慮它將帶來的影響。下表提供了一些幫助性信息。
恢復模型
?優點
?丟失所做工作的可能性
?是否恢復到時間點？
?
簡單
?可高性能地完成大容量復制操作。
可回收日志空間，使空間要求保持較低的水平。
?自最近的數據庫備份或差異備份以來所做的更改必須重做
?可以恢復到任何備份的結束點。此后的更改必須重做。
?
完全
?不會因數據文件丟失或損壞而丟失所做的工作。
可恢復到任意時間點（例如，發生應用程序或用戶錯誤之前的那一刻）。
?通常沒有風險。
如果日志受到損壞，則必須重做自最近的日志備份以來所做的更改。
?可恢復到任意時間點。
?
大容量日志記錄的
?可高性能地完成大容量復制操作。
大容量操作使用最小的日志空間。
?如果日志受到損壞，或者自最近的日志備份以來出現過大容量操作，則必須重做自上次備份以來所做的更改。
除此之外，不會丟失所做的任何工作。
?可以恢復到任何備份的結束點。此后所做的更改必須重做。
?
有關多實例的注意事項
SQL Server 2000 中還引入了在一臺計算機上運行 SQL Server 的多個實例的功能。默認情況下，SQL Server 的每個實例會動態地獲取和釋放內存，以針對實例的工作負荷的變化進行調整。當 SQL Server 2000 有多個實例，而每個實例都獨立地自動調整內存使用量時，性能優化會變得很復雜。大多數高端的業務智能客戶通常只在每臺計算機上安裝一個 SQL Server 實例，因此對于他們來說，通常不需要考慮這項功能。但是，隨著計算機個體變得越來越大（Windows 2000 Datacenter Server 最多支持 64 GB RAM 和 32 個 CPU），在有些生產環境中，可能會出現對多個實例的需求。那些利用擴展內存支持的實例需要特別關注。
擴展內存支持
一般情況下，SQL Server 2000 會根據需要動態地獲取和釋放內存，所以管理員通常不需要指定應該為 SQL Server 分配多少內存。但是，SQL Server 2000 企業版和 SQL Server 2000 開發人員版引入了對使用 Microsoft Windows 2000 Address Windowing Extensions (AWE) 的支持。這樣，SQL Server 2000 就可以對更多的內存進行尋址（對于 Windows 2000 Advanced Server 最多約 8 GB；對于 Windows 2000 Datacenter Server 最多約 64 GB）。在配置了擴展內存的情況下，必須將訪問擴展內存的每個實例配置為靜態分配它將使用的內存。
注意 只有當您運行 Windows 2000 Advanced Server 或 Windows 2000 Datacenter Server 時，才可使用這項功能。
使用 Windows 2000 的注意事項
要利用 AWE 內存，必須使用已分配了 Windows 2000 的“內存中鎖定頁”特權的 Windows 2000 帳戶運行 SQL Server 2000 數據庫引擎。SQL Server 安裝程序將自動授權 MSSQLServer 服務帳戶使用內存中鎖定頁選項。如果您從命令提示符使用 Sqlservr.exe 來啟動 SQL Server 2000 的實例，必須使用 Windows 2000 的“組策略”實用工具 (Gpedit.msc) 將這一權限手動分配給交互操作的用戶帳戶，否則的話，如果 SQL Server 不作為服務運行就將無法使用 AWE 內存。
啟用“內存中鎖定頁”選項
在開始菜單上，單擊運行，然后在打開框中輸入 gpedit.msc。
在組策略樹窗格中，展開計算機配置，然后展開 Windows 設置。
展開安全性設置，然后展開本地策略。
選擇用戶權限分配文件夾。
策略將會顯示在詳細信息窗格中。
在詳細信息窗格中，雙擊內存中鎖定頁。
在本地安全性策略設置對話框中，單擊添加。
在選擇用戶或組對話框中，添加一個有權運行 Sqlservr.exe 的帳戶。
要使 Windows 2000 Advanced Server 或 Windows 2000 Datacenter Server 能夠支持 4 GB 以上的物理內存，必須將 /pae 參數添加到 Boot.ini 文件中。
對于內存不超過 16 GB 的計算機，您可以在 Boot.ini 文件中使用 /3gb 參數。這就使 Windows 2000 Advanced Server 和 Windows 2000 Datacenter Server 能夠允許用戶應用程序通過 3 GB 的虛擬內存來對擴展內存進行尋址，并且為操作系統本身保留 1 GB 虛擬內存。
如果計算機上的物理內存超過 16 GB，則 Windows 2000 操作系統本身會需要 2 GB 虛擬內存地址空間用于系統開銷。因此，它只能支持將 2 GB 虛擬地址空間用于應用程序開銷。對于物理內存超過 16 GB 的系統，一定要在 Boot.ini 文件中使用 /2gb 參數。
注意 如果您意外使用了 /3gb 參數，Windows 2000 將無法對 16 GB 以上的任何內存進行尋址。
使用 SQL Server 2000 的注意事項
要使 SQL Server 2000 的實例能夠使用 AWE 內存，請使用 sp_configure 來設置啟用 awe 選項。然后，重新啟動 SQL Server 以激活 AWE。由于 AWE 支持會在 SQL Server 啟動過程中啟用，并在 SQL Server 關閉前一直保持啟用狀態，所以，在 AWE 處于使用狀態時，SQL Server 會通過向 SQL Server 錯誤日志發送“已啟用 Address Windowing Extension”消息來通知用戶。
當您啟用 AWE 內存時，SQL Server 2000 的實例不會動態管理地址空間的大小。因此，當您啟用 AWE 內存并啟動 SQL Server 2000 的實例時，根據最大服務器內存的設置方式不同，會出現下列情況之一。
如果已經設置了最大服務器內存并且計算機上至少有 3 GB 可用內存，則該實例獲取在最大服務器內存中指定的內存量。如果計算機上可用的內存量小于最大服務器內存（但是大于 3 GB），則該實例獲取幾乎所有的可用內存，并且可能最多只留下 128 MB 可用內存。
如果尚未設置最大服務器內存并且計算機上至少有 3 GB 可用內存，則該實例幾乎獲取所有的可用內存，并且可能最多只留下 128 MB 可用內存。
如果計算機上的可用內存不足 3 GB 而且內存是動態分配的，則無論為啟用 awe 設置了什么參數，SQL Server 都將以非 AWE 模式運行。
當在 32 GB 系統上分配 SQL Server AWE 內存時，Windows 2000 可能至少需要 1 GB 可用內存來管理 AWE。因此，如果在啟動 SQL Server 的實例時已啟用了 AWE，建議您不要使用默認的最大服務器內存設置，而應將它限制在 31 GB 或更小。
有關故障轉移群集和多實例的注意事項
如果您在使用 AWE 內存的同時使用 SQL Server 2000 故障轉移群集或者運行多個實例，則必須確保正在運行的所有 SQL Server 實例的最大服務器內存設置的值的總和小于可用的物理 RAM 量。對于故障轉移，您必須考慮任何候選存活節點上物理 RAM 的最小量。如果故障轉移節點上的物理內存比初始節點上的少，則 SQL Server 2000 的實例可能無法啟動或者啟動時用的內存比其在初始節點上的少。
sp_configure 選項
“并行度的成本閾值”選項
使用并行度的成本閾值選項可指定 SQL Server 創建和執行并行計劃所用的閾值。只有當為同一個查詢執行串行計劃的預計成本高于在并行度的成本閾值中設置的值時，SQL Server 才為查詢創建和執行并行計劃。成本是指對特定的硬件配置執行串行計劃時預計需要的時間（以秒為單位）。只針對對稱多處理器 (SMP) 設置并行度的成本閾值。
通常，并行計劃對較長的查詢有利；性能上的優勢可以補償初始化、同步以及終止計劃所需的額外時間。在混合執行短查詢和長查詢時，通常會使用并行度的成本閾值選項。短查詢執行串行計劃，而長查詢使用并行計劃。并行度的成本閾值的值決定哪些查詢被視為短查詢，從而只執行串行計劃。
在有些情況下，即使查詢的成本計劃小于當前的并行度的成本閾值值，也可以選擇并行計劃。這是因為就并行度的成本閾值而言，使用并行計劃還是串行計劃要根據完全優化完成之前提供的預計成本來決定。
并行度的成本閾值選項可以設置為從 0 到 32767 的任何值。默認值是 5（以毫秒為單位）。如果計算機只有一個處理器，或者如果由于關系掩碼配置選項的值而使得 SQL Server 只能使用一個 CPU，或者如果最大并行度選項設置為 1，那么，SQL Server 會忽略并行度的成本閾值。
“最大并行度”選項
最大并行度選項用于限制在執行并行計劃時使用的處理器數（最多 32 個）。默認值是 0，此時使用可用的實際數量的 CPU。將最大并行度選項設置為 1 可強制取消生成并行計劃。如果將該值設置為大于 1 的數字，則可以限制在執行單個查詢時使用的最大處理器數。如果將該值指定為大于可用 CPU 數量的數字，則使用可用的實際數量的 CPU。
注意 如果未將關系掩碼選項設成默認值，則在對稱多處理器 (SMP) 系統上可供 SQL Server 使用的 CPU 數可能會受到限制。
對于在 SMP 計算機上運行的服務器，很少改變最大并行度。如果計算機只有一個處理器，則會忽略最大并行度值。
“優先級提升”選項
優先級提升選項用于指定 SQL Server 是否應當以高于同一臺計算機上其他進程的調度優先級運行。如果將該選項設置為 1，SQL Server 將在 Windows 調度程序中按照 13 的優先級基數運行。默認值是 0，表示優先級基數 7。優先級提升選項只應當用在 SQL Server 專用且具有 SMP 配置的計算機上。
注意 如果將優先級提升得太高，則可能會使基本操作系統和網絡功能的資源不足，從而造成關閉 SQL Server 或使用服務器上的其他 Windows 任務等問題。
在某些情況下，如果將優先級提升設置為默認值以外的任何值，可能會導致在 SQL Server 錯誤日志中記錄以下通訊錯誤：
Error: 17824, Severity: 10, State: 0 Unable to write to ListenOn
connection '<servername>', loginname '<login ID>', hostname '<hostname>'
OS Error: 64, The specified network name is no longer available.
錯誤 17824 指出在嘗試寫入客戶端時 SQL Server 遇到連接問題。如果客戶端已停止響應，或者客戶端已經重新啟動，則這些通訊問題可能是由網絡問題引起的。但是，錯誤 17824 不一定表示網絡問題，而可能只是設置優先級提升選項的結果。
“設置工作集大小”選項
設置工作集大小選項用于為 SQL Server 保留等于服務器內存設置的物理內存空間。服務器內存設置由 SQL Server 根據工作負荷和可用資源自動配置。它將在最小服務器內存和最大服務器內存之間顯著變化。設置設置工作集大小的意思是：即使在 SQL Server 空閑時，另一個進程可以更容易地使用 SQL Server 頁，操作系統也不嘗試換出這些頁。
如果您要允許 SQL Server 動態使用內存，請不要設置設置工作集大小。在將設置工作集大小設置為 1 之前，請將最小服務器內存和最大服務器內存設置為同一個值（希望 SQL Server 使用的內存量）。
輕量池和關系掩碼選項將在本章后面的“要監視的關鍵性能計數器”一節討論。
優化磁盤 I/O 性能
如果您配置的 SQL Server 將只包含幾 GB 數據，而且不負擔繁重的讀寫活動，可以不必考慮磁盤 I/O 以及通過平衡硬盤驅動器間的 SQL Server I/O 活動來實現最大性能等事項。但是，要創建大型 SQL Server 數據庫來包含數百 GB 甚至 TB 的數據，并且/或者要能夠負擔繁重的讀/寫活動，則有必要進行相應的配置，以平衡多個硬盤驅動器間的負載，從而最大程度地提高 SQL Server 的磁盤 I/O 性能。
優化傳輸速度
對于數據庫性能優化來說，最重要的方面之一就是優化 I/O 性能。當然，SQL Server 也不例外。除非運行 SQL Server 的計算機有足夠的 RAM 來容納整個數據庫，否則 I/O 性能將由磁盤 I/O 子系統處理 SQL Server 讀寫數據的速度來確定。
因為傳輸速度、I/O 吞吐量和可能影響 I/O 性能的其他因素不斷改善，所以我們將不針對應當從存儲系統中期望看到哪種速度給出具體數字。為了更好地理解可期望獲得的性能，建議您與首選的硬件供應商協作確定期望的最優性能。
我們必須要強調的是順序 I/O 操作（通常又稱為“序列”或“按磁盤順序”）與非順序 I/O 操作之間的差異。我們還希望大家注意預讀可能對 I/O 操作產生的顯著影響。
順序和非順序磁盤 I/O 操作
有必要解釋以下這些術語相對于磁盤驅動器的含義。通常，一個硬盤驅動器由一組驅動器盤片組成。每個盤片都提供用于讀取/寫入操作的表面。一組帶有讀取/寫入磁頭的臂用于在這些盤片之間移動，并且從盤片的表面讀取數據或者向其中寫入數據。就 SQL Server 而言，有關硬盤驅動器的以下兩點很重要，需要記住。
第一，讀取/寫入磁頭和相關的磁盤臂需要移動，以定位到 SQL Server 請求的硬盤驅動器盤片上的位置并針對其進行操作。如果數據不按位置順序分布到硬盤驅動器盤片上，則硬盤驅動器需要花更多的時間來移動磁盤臂（尋道時間）和旋轉讀/寫頭（旋轉滯后時間）來找到數據。這與按位置順序分布時的情形完全不同，在該情況下，所需的全部數據都位于硬盤驅動器盤片的一個連續物理扇區上，因此磁盤臂和讀取/寫入磁頭在執行所需的磁盤 I/O 時移動量很少。
非順序和順序情形的時間相差很大：每次非順序尋道大約花費 50 毫秒，而順序尋道則大約需要兩三毫秒。請注意，這些時間是大致估計值，并且將根據以下因素而有所變化：非順序數據在磁盤上的分布距離、硬盤盤片可以旋轉的速度 (RPM) 以及硬盤驅動器的其他物理屬性。主要的一點是順序 I/O 有助于提高性能，而非順序 I/O 會降低性能。
第二，一定要記住，讀寫 8 KB 與讀寫 64 KB 所需的時間幾乎一樣多。在 8 KB 到大約 64 KB 的范圍內，磁盤臂以及讀/寫頭的移動（尋道時間和旋轉滯后時間）仍然占一次磁盤 I/O 傳輸操作所需時間的大部分。因此，從數學角度講，在需要傳輸 64 KB 以上的 SQL Server 數據時，因為 64 KB 與 8 KB 的傳輸速度基本上一樣，但每次傳輸所處理的 SQL Server 數據卻是后者的 8 倍，所以最好嘗試盡可能多地執行 64 KB 磁盤傳輸操作。請記住，預讀管理器在 64 KB 區塊（稱作 SQL Server 擴展盤區）中執行它的磁盤操作。日志管理器也以較大的 I/O 大小執行順序寫入。要記住的主要一點是，如果充分利用預讀管理器并將 SQL Server 日志文件與不按順序訪問的其他文件分開，會改善 SQL Server 性能。
根據經驗，大多數硬盤驅動器處理順序 I/O 操作時所提供的性能是處理非順序 I/O 操作時的 2 倍。即，需要非順序 I/O 的操作所花費的時間是執行順序 I/O 操作的兩倍。因此，要盡可能避免可能導致數據庫中出現隨機 I/O 的情況。雖然應當盡量按順序執行 I/O 操作，但是像頁拆分或者數據無序這樣的情形還是可能會導致出現非順序 I/O。
為了促使執行順序 I/O，一定要避免出現導致頁拆分的情形。設計一個精心安排的數據裝載策略也會有所幫助。您可以通過利用可分隔數據和索引的分區策略來促使在磁盤上按順序分布數據。一定要設置作業以定期檢查數據和索引中是否有碎片，并且在數據碎片太多時，使用 SQL Server 隨付的實用工具來對數據重新排序。有關執行這些操作的更多信息將在本章的稍后部分介紹。
注意 因為事務日志數據總是以不超過 32 KB 的大小按順序寫入日志文件中，所以日志通常不是主要的考慮事項。
RAID
RAID（廉價磁盤冗余陣列）是一種存儲技術，通常用于大于幾 GB 的數據庫。RAID 既具有性能優點又具有容錯優點。多個 RAID 控制器和磁盤配置會在成本、性能和容錯之間提供平衡。本主題簡單介紹了將 RAID 技術用于 SQL Server 數據庫的情況，并討論了各種配置以及平衡方案。
性能。硬件 RAID 控制器將從 Windows NT 4.0 與 Windows 2000 和應用程序（例如 SQL Server）中讀取/寫入的所有數據劃分為很多切片（通常是 16–128 KB），這些切片隨后會分布到所有參與 RAID 陣列的磁盤上。按這種方式將數據拆散到各個物理驅動器上會有這樣的效果：讀取/寫入 I/O 工作負荷會平均分布到所有參與 RAID 陣列的物理硬盤驅動器上。這樣就會提高磁盤 I/O 性能，因為參與 RAID 陣列的硬盤在總體上保持相同程度的閑忙，而不會因 I/O 請求分布不均而導致某些磁盤成為瓶頸。
容錯。 RAID 還通過使用鏡像和奇偶校驗這兩種方法來防止硬盤出現故障并因此造成數據丟失。
鏡像通過將信息寫入另一組（鏡像）驅動器上來實現。如果在有鏡像時丟失了驅動器，則可以通過更換有故障的驅動器并重建鏡像集來重建丟失驅動器上的數據。大多數 RAID 控制器都會提供在 Windows NT 4.0 和 Windows 2000 以及 SQL Server 聯機的情況下更換故障驅動器并重新鏡像的功能。這樣的 RAID 系統通常被稱作能夠“熱插拔”的驅動器。
鏡像有一個優點：如果需要容錯，它所實現的性能是 RAID 選項中最佳的。請切記，SQL Server 每次寫入鏡像集時，都會執行兩個磁盤 I/O 操作，對于鏡像集的每一面各執行一個這樣的操作。另一個優點是：進行鏡像比實現奇偶校驗 RAID 提供的容錯更多。鏡像能夠使系統在至少一個驅動器發生故障后繼續運行，而且，在鏡像集中多達半數的驅動器都有故障的情況下，也許還能夠支持系統，而不是強制系統管理員關閉服務器并從文件備份中恢復。
鏡像的缺點是成本高。鏡像的磁盤成本是：每個需要用來存放數據的驅動器都額外需要一個驅動器。這實際上就會使存儲成本增加一倍，對于數據倉庫來說，存儲器通常是所需的最昂貴的組件之一。RAID 1 及其混合 RAID 0+1（有時稱作 RAID 10 或 0/1）都是通過鏡像實現的。
奇偶校驗是這樣實現的：計算有關寫入磁盤的數據的恢復信息，然后將該奇偶校驗信息寫入構成 RAID 陣列的其他驅動器。如果某個驅動器發生故障，一個新驅動器就會插入 RAID 陣列，并通過提取寫入其他驅動器上的恢復信息（奇偶校驗），使用這些信息重新生成故障驅動器上的數據，從而恢復故障驅動器上的數據。RAID 5 及其混合通過奇偶校驗實現。
奇偶校驗的優點是成本低。要用 RAID 5 保護任意數量的驅動器，只需要另外增加一個驅動器。奇偶校驗信息會均勻分布在參與 RAID 5 陣列的所有驅動器上。奇偶校驗的缺點是性能和容錯能力。由于在計算和寫入奇偶校驗時會額外帶來成本，因此 RAID 5 對于每次寫入都需要四個磁盤 I/O 操作，而鏡像只需兩個磁盤 I/O 操作。鏡像和奇偶校驗的讀取 I/O 操作成本是相同的。但是，讀取操作通常會發生在有故障的驅動器上，此后，必須將陣列脫機，而且必須從備份介質中執行恢復，以恢復數據。
一般經驗：一定要在所需的任意多個磁盤上進行條帶化，以實現可靠的磁盤 I/O 性能。系統監視器將會指出在特定的 RAID 陣列上是否存在磁盤 I/O 瓶頸。請準備根據需要添加磁盤，并將數據重新分布到 RAID 陣列和/或小型計算機系統接口 (SCSI) 通道中，以平衡磁盤 I/O 并最大限度地提高性能。
硬件 RAID 控制器板載緩存的效果
許多硬件 RAID 控制器都有某種形式的讀取和/或寫入緩存。對 SQL Server 來說，這種可用的緩存功能可以顯著增強磁盤子系統高效處理 I/O 的能力。這些基于控制器的緩存機制的原理是：收集來自主機服務器 (SQL Server) 的較小的并且有可能是非順序的 I/O 請求，然后嘗試在幾毫秒內將它們與其他 I/O 請求合成一批，這樣，成批的 I/O 就可以形成較大 (32–128 KB) 并且有可能是順序 I/O 請求，以便發送到硬盤驅動器。
按順序的和較大的 I/O 請求有利于提高性能，請遵循這一原則，因為在硬盤驅動器能夠向 RAID 控制器提供固定數量 I/O 的情況下，這樣有助于產生更大的磁盤 I/O 吞吐量。硬盤每秒能夠處理更多的 I/O，并不是 RAID 控制器的緩存功能有多么神奇，而是因為 RAID 控制器緩存使用了某種組織方式來排列傳入的 I/O 請求，從而能夠盡可能充分地利用好基礎硬盤固定的 I/O 處理能力。
這些 RAID 控制器通常用某種形式的后備電源來保護它們的緩存機制。這種后備電源可以幫助在斷電時，將寫入緩存中的數據保留一段時間（可能是幾天）。如果數據庫服務器也由不間斷電源 (UPS) 支持，在斷電時，RAID 控制器會有更多的時間和機會將數據刷新到磁盤中。雖然服務器的 UPS 不直接影響性能，但它的確可以為 RAID 控制器緩存所提供的性能改進提供保護。
RAID 級別
如上所述，在 RAID 的各個級別中，RAID 1 和 RAID 0+1 提供最佳的數據保護和最佳性能，但是就所需的磁盤而言會需要更多的成本。當硬盤成本不是限制因素時，就兼顧性能和容錯而言，RAID 1 或 RAID 0+1 是最佳選擇。
RAID 5 的成本比 RAID 1 或 RAID 0+1 低，但是它提供的容錯和寫入性能較差。RAID 5 的寫入性能大約只是 RAID 1 或 RAID 0+1 的一半，這是因為 RAID 5 讀取和寫入奇偶校驗信息需要額外的 I/O。
使用 RAID 0 可實現最佳磁盤 I/O 性能（磁盤條帶化沒有容錯保護）。因為 RAID 0 不提供容錯保護，所以決不應當將它用在生產環境中，也不建議將它用在開發環境中。RAID 0 通常只用于基準檢驗或測試。
許多 RAID 陣列控制器都通過物理硬盤驅動器提供 RAID 0+1（又稱作 RAID 1/0 和 RAID 10）選項。RAID 0+1 是一種混合 RAID 解決方案。在較低級別，該控制器像普通的 RAID 1 那樣鏡像所有的數據。在較高級別，它（像 RAID 0 一樣）將數據條帶化到所有的驅動器上。因此，RAID 0+1 提供最大的保護（鏡像）和高性能（條帶化）。因為這些條帶化和鏡像操作由 RAID 控制器進行管理，所以它們對于 Windows 和 SQL Server 是透明的。RAID 1 和 RAID 0+1 之間的區別在硬件控制器級別上。對于給定的存儲量，RAID 1 和 RAID 0+1 需要相同數量的驅動器。有關以 RAID 0+1 方式實現特定 RAID 控制器的更多信息，請與生產該控制器的硬件供應商聯系。
下面的插圖顯示了 RAID 0、RAID 1、RAID 5 和 RAID 0+1 之間的區別。
如果您的瀏覽器不支持嵌入式框架，請單擊此處在單獨的頁中查看。
注意 在上面的插圖中，為了容納相當于四個磁盤的數據，RAID 1（和 RAID 0+1）需要八個磁盤，而 Raid 5 只需要五個磁盤。一定要咨詢您的存儲器供應商，以了解有關他們特定的 RAID 實現的更多信息。
0 級
因為該級別使用名為條帶集的磁盤文件系統，所以又將它稱作磁盤條帶。數據被劃分成多個塊并按固定順序分布到陣列中的所有磁盤上。RAID 0 將多個操作分布到多個磁盤上，以便可以同時獨立地執行這些操作，從而改善了讀取/寫入性能。RAID 0 類似于 RAID 5，但是 RAID 5 還提供容錯功能。
下面的插圖顯示的是 RAID 0。
?
1 級
因為該級別使用名為鏡像集的磁盤文件系統，所以又將它稱作磁盤鏡像。磁盤鏡像可提供一個與所選磁盤完全相同的冗余副本。寫入主磁盤的所有數據都會寫入鏡像磁盤。RAID 1 提供了容錯功能，而且通常可以改進讀取性能（但是可能會降低寫入性能）。下面的插圖顯示的是 RAID 1。
?
2 級
該級別通過使用將奇偶校驗分布到所有磁盤上的糾錯方法來添加冗余。它還利用磁盤條帶策略將一個文件分成多個字節并將該文件分布到多個磁盤上。與鏡像 (RAID 1) 相比，該策略在磁盤利用率和讀取/寫入性能方面只帶來了很小的改進。RAID 2 不如其他 RAID 級別效率高，通常不使用它。
3 級
該級別使用與 RAID 2 相同的條帶化方法，但是糾錯方法只需一個磁盤用于奇偶校驗數據。磁盤空間的使用情況因數據磁盤的數量而異。RAID 3 在讀取/寫入性能方面提供一些改進。RAID 3 也極少使用。
4 級
該級別使用的條帶數據塊或段比 RAID 2 或 RAID 3 大得多。與 RAID 3 一樣，糾錯方法只需一個磁盤用于奇偶校驗數據。它將用戶數據與糾錯數據分開。RAID 4 不如其他 RAID 級別效率高，通常不使用。
5 級
該級別又稱作具有奇偶校驗的條帶化，它是新設計中最常用的策略。與 RAID 4 相似，它將數據以大塊形式條帶化到陣列中的磁盤上。不同之處在于它在所有磁盤之間寫入奇偶校驗的方式。數據冗余通過奇偶校驗信息提供。數據和奇偶校驗信息會在磁盤陣列上排列，所以這兩種信息總是位于不同的磁盤上。與磁盤鏡像 (RAID 1) 相比，具有奇偶校驗的條帶化可提供更好的性能。但是，當條帶成員丟失時（例如，當磁盤發生故障時），讀取性能會下降。RAID 5 是最常用的 RAID 配置之一。下面的插圖顯示的是 RAID 5。
?
Level 10 (1+0)
該級別又稱作具有條帶化的鏡像。該級別使用條帶化的磁盤陣列，而該陣列又鏡像到另一組相同的條帶化磁盤。例如，可使用四個磁盤創建一個條帶化的陣列。然后，條帶化的磁盤陣列使用另一組（四個）條帶化的磁盤進行鏡像。RAID 10 提供磁盤條帶化帶來的性能益處以及鏡像帶來的磁盤冗余。在所有的 RAID 級別中，RAID 10 提供的讀取/寫入性能最高，代價是使用的磁盤數量是其他級別的兩倍。下面的插圖顯示的是 RAID 10。
如果您的瀏覽器不支持嵌入式框架，請單擊此處在單獨的頁中查看。
聯機 RAID 擴展
使用該功能可以在 SQL Server 保持聯機的情況下動態地給物理 RAID 陣列添加磁盤。增加的磁盤驅動器會自動集成到 RAID 存儲器中。添加磁盤驅動器的方法是：將它們安裝到被稱為熱插拔驅動器插槽或熱插拔插槽的物理位置。許多硬件供應商都提供了能夠實現該功能的硬件 RAID 控制器。數據會均勻地在所有驅動器（包括新添加的驅動器）之間均勻地重新進行條帶化，而且不需要關閉 SQL Server 或 Windows。
您可以通過在磁盤陣列盒中的熱插拔插槽中保留空位來利用該功能。如果 SQL Server 經常因 I/O 請求而使 RAID 陣列負擔過重（這可以由與該 RAID 陣列相關聯的 Windows 邏輯驅動器盤符的磁盤隊列長度來指示），則可能需要在 SQL Server 仍在運行時，將一個或多個新的硬盤驅動器安裝到熱插拔插槽中。RAID 控制器會將一些現有的 SQL Server 數據移到這些新驅動器上，以便數據均勻地分布到 RAID 陣列中的所有驅動器上。然后，新驅動器的 I/O 處理能力（每個驅動器每秒 75 個非順序/150 個順序 I/O）會添加到 RAID 陣列的總體 I/O 處理能力中。
系統監視器和 RAID
在系統監視器（在 Microsoft Windows NT? 4.0 中為性能監視器）中，可以獲取邏輯磁盤驅動器和物理磁盤驅動器的信息。邏輯磁盤和物理磁盤的區別在于，在系統監視器中，邏輯磁盤與 Windows 讀作邏輯驅動器盤符的內容相關聯，物理磁盤與 Windows 讀作一個物理硬盤的內容相關聯。
在 Windows NT 4.0 中，默認情況下，性能監視器的所有磁盤計數器都是處于關閉狀態的，因為它們可能會對性能略有影響。在 Windows 2000 中，默認情況下，物理磁盤計數器處于打開狀態，邏輯磁盤計數器處于關閉狀態。Diskperf.exe 是一個 Windows 命令，它控制可在系統監視器中查看的計數器的類型。
在 Windows 2000 中，要獲取邏輯驅動器或存儲卷的性能計數器數據，您必須在命令提示符下鍵入 diskperf -yv，然后按 Enter 鍵。這會導致用于收集磁盤性能數據的磁盤性能統計驅動程序報告邏輯驅動器或存儲卷的數據。在默認情況下，操作系統使用 diskperf -yd 命令來獲取物理驅動器的數據。
在 Windows 2000 中，Diskperf.exe 的語法如下所示：
diskperf [-y[d|v] | -n[d|v]] [\\computername]
參數
(none)
報告磁盤性能計數器是否處于啟用狀態并標識啟用的計數器。
-y
將系統設置為在計算機重新啟動時啟動所有的磁盤性能計數器。
-yd
在計算機重新啟動時啟用物理驅動器的磁盤性能計數器。
-yv
在計算機重新啟動時啟用邏輯驅動器或存儲卷的磁盤性能計數器。
-n
將系統設置為在計算機重新啟動時禁用所有的磁盤性能計數器。
-nd
禁用物理驅動器的磁盤性能計數器。
-nv
禁用邏輯驅動器的磁盤性能計數器。
\\computername
指定要查看或設置要使用的磁盤性能計數器的計算機。
在 Windows NT 4.0 和更低版本中，diskperf –y 用于監視不使用 Windows NT 軟件 RAID 的硬盤驅動器或者硬盤驅動器與 RAID 控制器的集和。在使用 Windows 軟件 RAID 時，請使用 diskperf –ye，以便系統監視器將在 Windows NT 條帶集之間正確地報告物理計數器。當結合使用 diskperf –ye 和 Windows NT 條帶集時，邏輯計數器所報告的信息將不正確并且應當被忽略。如果必須將邏輯磁盤計數器信息與 Windows NT 條帶集結合使用，請使用 diskperf –y。
在使用 diskperf –y 時，邏輯磁盤計數器會被正確地報告給 Windows NT 條帶集，但是物理磁盤計數器所報告的信息將不正確并且應當被忽略。
注意 diskperf 命令要在重新啟動了 Windows 之后才會起作用（Windows 2000 和 Windows NT 4.0 及更低版本均是如此）。
有關監視硬件 RAID 的注意事項
因為 RAID 控制器將多個物理硬盤驅動器作為一個 RAID 鏡像集或條帶集提供給 Windows，所以 Windows 就會像讀取一個物理磁盤那樣讀取該分組。實際底層硬盤驅動器活動的最終抽象視圖會導致性能計數器報告可能會起誤導作用的信息。
從優化性能的角度看，知道一個 RAID 陣列關聯了多少物理硬盤驅動器是很重要的。在確定 Windows 和 SQL Server 發送給每個物理硬盤驅動器的磁盤 I/O 請求數時，將需要此信息。將系統監視器報告為與某個硬盤驅動器相關聯的磁盤 I/O 請求數除以該 RAID 陣列中已知的實際物理硬盤驅動器數。
要粗略估計 RAID 陣列中每個硬盤驅動器的 I/O 活動，一定還要將系統監視器報告的磁盤寫入 I/O 數乘以 2（RAID 1 和 0+1）或 4 (RAID 5)。這將更精確地給出發送到物理硬盤驅動器的實際 I/O 請求數，因為正是在這個物理級別應用硬盤驅動器的 I/O 能力。但是，當硬盤 RAID 控制器使用緩存功能時，此方法無法精確計算硬盤驅動器 I/O，因為緩存功能會極大地影響對硬盤驅動器進行的直接 I/O。
在監視磁盤活動時，最好將重點關注磁盤隊列，而不是每個磁盤的實際 I/O。磁盤 I/O 的速度取決于驅動器的傳輸速率，而這種速率是無法調整的。除了購買更快或更多的驅動器外，您沒有什么其他措施，所以，關心實際發生的 I/O 量沒有什么意義。但是，您又希望避免出現過多的磁盤隊列。大量的磁盤隊列表明您的 I/O 有問題。因為 Windows 不能讀取 RAID 陣列中物理驅動器的數量，所以很難精確估計每個物理磁盤的磁盤隊列。通過將磁盤隊列長度除以參與所觀察的邏輯驅動器的硬件 RAID 磁盤陣列的物理驅動器數，可以確定大致的近似值。對于 SQL Server 文件所在的硬盤驅動器，努力使磁盤隊列數少于兩個，是最理想的。
軟件 RAID
Windows 2000 支持軟件 RAID，在不使用硬件 RAID 控制器時，軟件 RAID 通過操作系統來提供鏡像集和條帶集（具有或不具有容錯功能），從而提供容錯功能。您可以使用操作系統過程來設置 RAID 0、RAID 1 或 RAID 5 功能。多數大型數據倉庫都使用硬件 RAID，但是，如果您的安裝規模相對較小，或者您選擇不實現硬件 RAID，那么，軟件 RAID 可以帶來一些數據訪問和容錯方面的優點。
軟件 RAID 確實會占用一些 CPU 資源，因為 Windows 必須管理通常由硬件 RAID 控制器為您管理的 RAID 操作。因此，在磁盤驅動器數相同的情況下，Windows 軟件 RAID 提供的性能會比硬件 RAID 低幾個百分點，尤其是當系統處理器的使用率因其他目的而接近 100% 時。通過降低 I/O 瓶頸的可能性，與沒有軟件 RAID 時相比，Windows 軟件 RAID 通常將幫助一組驅動器為 SQL Server I/O 提供更好的服務。如果使用軟件 RAID，SQL Server 應該能夠更好地利用 CPU，因為服務器通常等待 I/O 請求完成的時間會減少。
磁盤 I/O 并行度
為了改善存儲在多個磁盤驅動器上的大型 SQL Server 數據庫的性能，一個有效的方法是創建磁盤 I/O 并行機制，該機制同時對多個磁盤驅動器進行讀寫操作。RAID 通過硬件和軟件實現磁盤 I/O 并行度。下一個主題討論使用分區來組織 SQL Server 數據以進一步增加磁盤 I/O 并行度。
使用分區來提高性能
對于存儲在多個磁盤驅動器上的 SQL Server 數據庫，可通過對數據進行分區以增加磁盤 I/O 并行度來改善性能。
可使用多種方法來進行分區。分區的創建和管理方法包括配置存儲子系統（磁盤、RAID 分區）和在 SQL Server 中應用各種數據配置機制（例如，文件、文件組、表和視圖）。雖然本節重點介紹一些與性能相關的分區功能，但是第 18 章“在 SQL Server 2000 數據倉庫中使用分區”也特別介紹了分區主題。
創建磁盤 I/O 并行度的最簡單方法是，使用硬件分區并創建一個為所有的 SQL Server 數據庫文件（事務日志文件除外，它們總是應當存儲在從物理上分開且僅專用于日志文件的磁盤驅動器上）提供服務的驅動器池。驅動器池可以是一個 RAID 陣列，它在 Windows 中呈現為一個物理驅動器。可以使用多個 RAID 陣列和 SQL Server 文件/文件組來設置較大的池。可以將一個 SQL Server 文件與每個 RAID 陣列相關聯，并將這些文件組合成一個 SQL Server 文件組。然后，可基于該文件組構建一個數據庫，以便將數據均勻地分布到所有的驅動器和 RAID 控制器上。驅動器池方法依賴 RAID 在所有的物理驅動器之間劃分數據，這樣有助于確保在數據庫服務器操作過程中對該數據進行并行訪問。
該驅動器池方法簡化了 SQL Server I/O 的性能優化，因為數據庫管理員知道只有一個物理位置可供創建數據庫對象。可監視單個驅動器池的磁盤隊列情況，必要時可向該池中添加更多的硬盤驅動器以防出現磁盤排隊現象。一般情況下，無法確定數據庫哪些部分的利用率最高，此時使用該方法有助于優化性能。最好不要只是因為 SQL Server 可能要用 5% 的時間來對另一磁盤分區進行 I/O 而將總體可用 I/O 能力的一部分隔離到該磁盤分區上。“單個驅動器池”方法有助于使所有可用的 I/O 能力對于 SQL Server 操作“始終”可用。它還允許 I/O 操作分布到最大數量的可用磁盤上。
SQL Server 日志文件始終 都應該從物理上分散到不同的硬盤驅動器，與所有其他 SQL Server 數據庫文件分開。對于管理多個繁忙數據庫的極其繁忙的 SQL Server 來說，每個數據庫的事務日志文件應當在物理上互相分離，以減少爭用現象。
由于事務日志記錄主要是順序寫入 I/O，所以將日志文件分開往往會顯著提高 I/O 性能。包含日志文件的磁盤驅動器可以非常高效地執行這些順序寫入操作，但前提是這些操作不被其他 I/O 請求中斷。有時，將需要在 SQL Server 操作（例如，復制、回滾和延遲更新）過程中讀取事務日志。有些實現通過將新數據幾乎實時地裝載到數據倉庫中，將復制用作其數據轉換實用工具的前端。參與復制的 SQL Server 的管理員需要確保所有用于事務日志文件的磁盤都有足夠的 I/O 處理能力，以便處理除正常日志事務寫入之外需要發生的讀取操作。
物理上分割的文件和文件組需要額外的管理工作。事實證明，為了隔離和改善對非常活動的表或索引的訪問而進行分割時，這些額外的工作是值得的。下面列出了一些益處：
對于特定對象的 I/O 需求，可以進行更準確的評估，而如果所有數據庫對象都放在一個大驅動器池中，進行這種評估就不那么容易了。
使用文件和文件組對數據和索引進行分區，可以增強管理員創建粒度更細的備份和恢復策略的能力。
文件和文件組可用于維護數據在磁盤上的順序放置，從而減少或消除非順序的 I/O 活動。如果數據裝載到數據倉庫的可用時間窗口要求并行執行處理以滿足最終期限，則該功能就變得尤其重要。
在數據庫開發和基準檢驗階段，可能適于對文件和文件組進行物理分割，這樣可收集數據庫 I/O 信息并將其應用于生產數據庫服務器環境的容量計劃。
有關對象分區的注意事項
可以在不同的硬盤驅動器、RAID 控制器和 PCI 通道（或者三者的組合）之間分隔以下方面的 SQL Server 活動：
事務日志
tempdb
數據庫
表
非聚集索引
注意 在 SQL Server 2000 中，Microsoft 增強了分布式分區視圖，使用這種視圖可以創建聯合數據庫（通常稱作擴展），這種數據庫會將資源負荷和 I/O 活動分布到多個服務器上。聯合數據庫適于某些高端聯機分析處理 (OLTP) 應用程序，但是建議不要使用該方法來解決數據倉庫的需求。
使用硬件 RAID 控制器、RAID 熱插拔驅動器和聯機 RAID 擴展功能可以輕松實現對 SQL Server I/O 活動的物理分割。最靈活的方法是排列 RAID 控制器，讓單獨的 RAID 通道與上述不同活動方面相關聯。同樣，應當將每個 RAID 通道連接到一個單獨的 RAID 熱插拔機柜，以便充分利用聯機 RAID 擴展功能（如果可通過 RAID 控制器使用該功能）。隨后，Windows 邏輯驅動器盤符將會與每個 RAID 陣列相關聯，并且 SQL Server 文件會基于已知的 I/O 使用模式在不同的 RAID 陣列之間被分隔開。
使用這種配置，有可能將與每個活動相關聯的磁盤隊列重新與一個不同的 RAID 通道及其驅動器機柜相關聯。如果某個 RAID 控制器及其驅動器陣列機柜均支持聯機 RAID 擴展功能，而且機柜中有熱插拔硬盤驅動器的插槽，則只需向 RAID 陣列中添加更多的驅動器，直到系統監視器報告該 RAID 陣列的磁盤隊列已經達到可接受的程度（對于 SQL Server 文件最好少于兩個），即可解決該 RAID 陣列的磁盤隊列問題。這可以在 SQL Server 聯機時完成。
分離事務日志
維護事務日志文件的存儲設備應該在物理上與數據文件所在的設備分開。根據您的數據庫恢復模型設置不同，大多數更新活動既產生數據設備活動又產生日志活動。如果將這兩個活動設置為共享同一個設備，則要執行的操作將爭用同一個有限資源。大多數安裝都受益于將這些競爭 I/O 活動分開。
分離 tempdb
SQL Server 會在每個服務器實例上創建一個名為 tempdb 的數據庫，以供服務器用作各種不同活動的共享工作區，這些活動包括：臨時表、排序、處理子查詢、生成聚合以支持 GROUP BY 或 ORDER BY 子句、使用 DISTINCT 的查詢（必須創建臨時工作表才能刪除重復行）、游標，以及哈希聯接。通過將 tempdb 分割到其自己的 RAID 通道上，我們使 tempdb I/O 操作能夠與它們的相關事務的 I/O 操作并行發生。由于 tempdb 實際上是一個草稿區域，而且更新頻繁，所以 RAID 5 對于 tempdb 并不是好的選擇，而 RAID 1 或 0+1 提供的性能更好。雖然 Raid 0 不提供容錯功能，但可以考慮將它用于 tempdb，因為每次重新啟動數據庫服務器時都會重新生成 tempdb。RAID 0 使用最少的物理驅動器為 tempdb 帶來了最佳的 RAID 性能，但在生產環境中將 RAID 0 用于 tempdb 時主要的顧慮是：如果有物理驅動器（包括用于 tempdb 的驅動器）出現故障，就可能影響到 SQL Server 的可用性。如果將 tempdb 放在具備容錯能力的 RAID 配置上，就可以避免這一點。
要移動 tempdb 數據庫，請使用 ALTER DATABASE 命令更改與 tempdb 相關聯的 SQL Server 邏輯文件名的物理文件位置。例如，要將 tempdb 以及與之相關聯的日志移到新文件位置 E:\mssql7 和 C:\temp，請使用以下命令：
alter database tempdb modify file (name='tempdev',filename=
'e:\mssql7\tempnew_location.mDF')
alter database tempdb modify file (name='templog',filename=
'c:\temp\tempnew_loglocation.mDF')
與用戶數據庫相比，master 數據庫 msdb 和model 數據庫在生產過程中很少使用，因此，在考慮優化 I/O 性能時，通常不必考慮它們。master 數據庫通常只用于添加新登錄、數據庫、設備和其他系統對象。
數據庫分區
可以使用文件和/或文件組對數據庫進行分區。文件組只是為管理目的而將多個單獨的文件組合在一起的命名集合。一個文件不能是多個文件組的成員。表、索引、text、ntext 和 image 數據都可以與一個特定的文件組相關聯。這就是說，它們所有的頁都是從該文件組中的文件中分配而來的。下面介紹三種類型的文件組。
主文件組
該文件組包含主數據文件以及未放到另一個文件組中的所有其他文件。系統表的所有頁都是從主文件組分配的。
用戶定義的文件組
該文件組是使用 CREATE DATABASE 或 ALTER DATABASEfilegroup 語句中的 FILEGROUP 關鍵字或者在 SQL Server 企業管理器中的屬性對話框上指定的任何文件組。
默認文件組
默認文件組包含在創建時未指定文件組的所有表和索引的頁。在每個數據庫中，每次只能有一個文件組是默認文件組。如果未指定默認文件組，則主文件組就是默認文件組。
文件和文件組對于控制數據和索引的位置以及消除設備爭用現象很有用。有相當一部分安裝還將文件和文件組用作一種比數據庫粒度更細的機制，以便對它們的數據庫備份/恢復策略進行更多的控制。
水平分區（表）
水平分區將一個表分割成多個表，每個表都包含相同的列數，但是行數會減少。怎樣對表進行水平分區要根據分析數據的方式而定。根據一般經驗，在對表進行分區時，應當使查詢引用的表盡可能少。否則，用于在查詢時按邏輯合并表的 UNION 查詢就會過多，從而會影響性能。
例如，假定企業要求規定：我們要將十年來不斷滾動的事務數據存儲到我們數據倉庫的中央事實表中。我們公司十年來的事務數據意味著數據會超過十億行。數量達到十億的任何內容管理起來都會很困難。現在，請考慮每年我們都必須除去第十年的數據，然后裝載最新一年的數據。
管理員通常采用的方法是：創建十個獨立但結構相同的表，每個表中存放一年的數據。然后，管理員在這十個表的基礎上定義一個聯合視圖，以便讓最終用戶看到所有數據都放在一個表中。實際上并非如此。針對該視圖執行的任何查詢都被優化成只搜索指定的年份（和相應的表）。不過，管理員確實獲得了管理能力。現在，管理員能夠以粒度方式單獨管理每年的數據。每年的數據都可以單獨裝載、索引或維護。添加新年份就是這樣簡單：除去該視圖，除去包含第十年數據的表，裝載和索引新年份的數據，然后重新定義新視圖以包括新年份的數據。
當您在多個表或多個服務器之間對數據進行分區時，只訪問部分數據的查詢運行得更快，因為要掃描的數據比較少。如果這些表位于不同的服務器上，或者在一臺具有多個處理器的計算機上，還可以并行掃描查詢所涉及的每個表，從而改善查詢性能。另外，維護任務（例如，重建索引或備份表）的執行速度會更快。
通過使用分區視圖，數據仍顯示為一個表，而且在查詢數據時可以不必手動引用相應的基礎表。如果滿足下列任一條件，分區視圖就可以進行更新。有關分區視圖及其限制的詳細信息，請參閱“SQL Server 聯機叢書”。
在該視圖上用可支持 INSERT、UPDATE 和 DELETE 語句的邏輯定義了 INSTEAD OF 觸發器。
該視圖以及 INSERT、UPDATE 和 DELETE 語句遵循為可更新的分區視圖定義的規則。
分離非聚集索引
索引駐留在 B 型樹結構中，通過使用 ALTER DATABASE 命令來設置一個不同的文件組，這些索引可以與它們的相關數據庫表分開（聚集索引除外）。在下面的示例中，第一個 ALTER DATABASE 創建一個文件組。第二個 ALTER DATABASE 向新創建的文件組中添加一個文件。
alter database testdb add filegroup testgroup1
alter database testdb add file (name = 'testfile',
filename = 'e:\mssql7\test1.ndf') to filegroup testgroup1
在創建了文件組及其關聯的文件之后，可以在創建索引時指定該文件組，從而使用該文件組來存儲索引。
create table test1(col1 char(8))
create index index1 on test1(col1) on testgroup1
SP_HELPFILE 會將有關給定數據庫中文件和文件組的信息反饋回來。SP_HELP <表名> 的輸出結果中有一節，該節提供有關表的索引及其文件組關系的信息。
sp_helpfile
sp_help test1
并行數據檢索
SQL Server 在具有多個處理器的計算機上運行時可以并行掃描數據。如果一個表在包含多個文件的文件組中，則可以對該表執行多個并行掃描。只要按順序訪問某個表，就會創建一個獨立線程來并行讀取每個文件。例如，如果完全掃描在包含四個文件的文件組上創建的表，將會使用四個獨立線程來并行讀取數據。因此，為每個文件組創建多個文件會有助于提高性能，因為這樣會使用獨立的線程來并行掃描每個文件。同樣，當某個查詢聯接著不同文件組上的表時，可以并行讀取每個表，從而改進查詢性能。
另外，表中的任何 text、ntext 或 image 列都可以在除基表所在文件組以外的文件組上創建。
最終，文件過多會導致并行線程過多，進而導致磁盤 I/O 子系統中出現瓶頸，這時就會達到飽和點。通過使用系統監視器來監視 PhysicalDisk 對象和磁盤隊列長度計數器，可以確定這些瓶頸。如果磁盤隊列長度計數器大于 3，請考慮減少文件數量。
為了通過使用多個文件并行訪問數據來提高吞吐量，將盡可能多的數據分布到盡可能多的物理驅動器上是很有益處的。要將數據均勻地分布到所有磁盤上，請首先設置基于硬件的磁盤條帶化，然后根據需要使用文件組將數據分布到多個硬件條帶集上。
并行查詢建議
SQL Server 可自動以并行方式執行查詢。這樣就會對在多處理器計算機上執行查詢進行優化。工作會細分為多個線程（受線程和內存的可用性影響），而不是一個查詢用一個操作系統線程執行，這樣，完成復雜查詢時就會速度更快，效率更高。
SQL Server 中的優化器會為查詢生成計劃并確定將在何時并行執行查詢。確定時會依據以下條件：
計算機是否有多個處理器？
是否有足夠的內存來并行執行查詢？
服務器上的 CPU 負荷是多少？
正在運行哪種類型的查詢？
如果允許 SQL Server 以并行方式運行并行操作（例如 DBCC 和創建索引），對服務器資源的壓力就會變重，而且在執行繁重的并行操作任務時，您可能會看到警告信息。如果服務器錯誤日志中經常出現有關資源不足的警告消息，請考慮使用系統監視器來調查哪些資源（例如，內存、CPU 使用率和 I/O 使用率）可用。
當服務器上有活動用戶時，請不要并行運行大量查詢。請嘗試在沒有負載的時間段中執行維護作業（例如，DBCC 和創建索引）。這些作業可以并行執行。監視磁盤 I/O 性能。觀察系統監視器（在 Windows NT 4.0 中為性能監視器）中的磁盤隊列長度，確定是升級硬盤還是將數據庫重新分布到不同的磁盤上。如果 CPU 的使用率非常高，請升級或添加更多的處理器。
下列服務器配置選項可能會影響查詢的并行執行：
并行度的成本閾值
最大并行度
最大工作線程
查詢調控器成本限制
優化數據負荷
在加速數據裝載活動時，一定要記住多種提示和方法。根據您執行的是初始數據裝載還是增量數據裝載，這些方法可能會有所不同。一般來說，增量裝載更復雜且限制性更強。您選擇的方法可能還基于您無法控制的因素。處理窗口要求、所選存儲配置、服務器硬件的限制等都會影響可供您使用的選項。
在執行初始數據裝載和增量數據裝載時，有一些共同的要點需要記住。下面將詳細討論以下主題：
選擇適當的數據庫恢復模型
使用 bcp、BULK INSERT 或大容量復制 API
控制鎖定行為
并行裝載數據
雜項，其中包括：
繞過引用完整性檢查（約束和觸發器）
裝載預先排序的數據
刪除索引帶來的影響
選擇適當的數據庫恢復模型
我們已在“影響性能的配置選項”一節中討論了數據庫恢復模型。一定要記住所選恢復模型對執行數據裝載所需的時間可能會有很大的影響。這些恢復模型主要控制將寫出到事務日志中的數據量。因為對事務日志執行寫入操作基本上會使工作負荷加倍，所以這非常重要。
日志記錄和最小日志記錄大容量復制操作
在使用完全恢復模型時，由某個大容量數據裝載機制（將在下面討論）執行的所有插入行操作都記錄到事務日志中。對于大型數據裝載，這可能會導致快速填充事務日志。為了幫助防止事務日志的空間不足，可執行最小日志記錄大容量復制操作。是以日志記錄還是以無日志記錄形式執行大容量復制不作為大容量復制操作的一部分來指定；它取決于大容量復制中涉及到的數據庫和表的狀態。如果符合以下所有的條件，將進行無日志記錄的大容量復制：
恢復模型是“簡單”或“大容量日志記錄的”，或者數據庫選項 select into/bulkcopy 設置為真。
目標表未在進行復制。
目標表沒有索引，或者如果目標表有索引，在開始大容量復制時它也是空的。
TABLOCK 提示是在將 eOption 設置為 BCPHINTS 的情況下使用 bcp_control 指定的。
任何不滿足上述條件的向 SQL Server 實例中進行的大容量復制將完全記錄下來。
在執行初始數據裝載時，應當總是在“大容量日志記錄的”或“簡單”恢復模型下運行。對于增量數據裝載，只要數據丟失的可能性很低，就考慮使用“大容量日志記錄的”模型。因為許多數據倉庫基本上都是只讀的或者事務活動的數量很少，所以將數據庫恢復模型設置為“大容量日志記錄的”不會產生任何問題。
使用 bcp、BULK INSERT 或大容量復制 API
SQL Server 內部存在兩個機制，用來解決大容量移動數據的需求。第一個機制是 bcp 實用工具。第二個機制是 BULK INSERT 語句。bcp 是一個命令提示符實用工具，它既將數據復制到 SQL Server 中又從其中復制數據。在 SQL Server 2000 中，bcp 實用工具是用 ODBC 大容量復制應用程序編程接口 (API) 重新編寫的。bcp 實用工具的早期版本是使用 DB-Library 大容量復制 API 編寫的。
BULK INSERT 是 SQL Server 附帶的 Transact-SQL 語句，該語句可從數據庫環境內執行。與 bcp 不同的是，BULK INSERT 只能將數據拉入 SQL Server 中。它不能將數據推出。使用 BULK INSERT 的一個好處在于，它可以使用 Transact-SQL 語句將數據復制到 SQL Server 的實例中，而不必退出解釋器轉到命令提示符中。
第三個選項是大容量復制 API，程序員通常對該選項很感興趣。有了這些 API，程序員就能夠使用 ODBC、OLE DB、SQL-DMO 或者甚至基于 DB 庫的應用程序將數據移入或移出 SQL Server。
所有這些選項都使您能夠對批處理大小進行控制。除非您使用的是小容量數據，否則最好習慣于指定批處理大小以進行恢復。如果未指定批處理大小，則 SQL Server 將所有要裝載的行作為一批提交。例如，您嘗試將 1,000,000 行新數據裝載到某個表中。服務器在處理完第 999,999 行后突然斷電。當服務器恢復時，將需要從數據庫中回滾處理完的 999,999 行，然后再嘗試重新裝載數據。您可以通過將批處理大小指定為 10,000 來大大節省自己的恢復時間，這是由于您已經將 1 到 990,000 行提交到數據庫中，因此將只需回滾 9,999 行（而不是 999,999 行）。同樣，如果未指定批處理大小，則將必須從第 1 行重新啟動裝載處理才能重新裝載數據。如果將批處理大小指定為 10,000 行，則只需從第 990,001 行重新啟動裝載處理，這樣就高效地繞過了已經提交的 990,000 行。
控制鎖定行為
bcp 實用工具和 BULK INSERT 語句接受 TABLOCK 提示，該提示允許用戶指定要使用的鎖定行為。TABLOCK 指定在大容量復制操作過程中將采用大容量更新表級鎖。使用 TABLOCK 可以減少表上對鎖的爭用，從而改進大容量復制操作的性能。當針對單個表處理并行裝載時，該設置有非常重要的含義（將在下一節討論）。
例如，要將 Authors.txt 數據文件中的數據大容量復制到 pubs 數據庫中的 authors2 表中，請指定表級鎖，并從以下命令行提示符執行：
bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "TABLOCK"
或者，您可以從查詢工具（如 SQL 查詢分析器），使用 BULK INSERT 語句來大容量復制數據，如下例所示：
BULK INSERT pubs..authors2 FROM 'c:\authors.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
TABLOCK
)
如果未指定 TABLOCK，除非對于表將 table lock on bulk load 選項設置為 on，否則默認鎖定會使用行級鎖。將 table lock on bulk load 選項與 sp_tableoption 命令一起使用，也可以設置大容量裝載操作過程中表的鎖定行為。
Table lock on bulk load
?表的鎖定行為
?
Off
?使用行級鎖
?
On
?使用表級鎖
?
注意 如果指定了 TABLOCK 提示，則在大容量裝載過程中，它將替代使用 sp_tableoption 聲明的設置。
并行裝載數據
并行裝載 — 非分區表
使用 SQL Server 中的任一大容量數據裝載機制，都可以將數據并行裝載到一個非分區表中。這是通過同時運行多個數據裝載來完成的。在開始裝載之前，需要將要并行裝載的數據拆分成多個獨立文件（大容量插入 API 的數據源）。然后，可同時啟動所有的獨立裝載操作，以便并行裝載數據。
例如，假設您需要為在全球四個地區運作的服務公司裝載合并的數據庫，每個地區每個月都報告寄給客戶的帳單上的報告時間（小時）。對于大型服務組織，這可能表示需要合并大量事務數據。如果這四個報告地區都分別提供獨立文件，則可以使用上面介紹的方法將這四個文件同時裝載到一個表中。
注意 并行處理的并行線程（裝載）的數量不應超過 SQL Server 的可用處理器數。
下面的插圖說明了對非分區表進行的并行裝載。
如果您的瀏覽器不支持嵌入式框架，請單擊此處在單獨的頁中查看。
并行裝載：水平分區（表）
本節重點介紹如何使用水平分區表來提高數據裝載的速度。在上一節中，我們討論了將數據從多個文件裝載到一個（非分區）表中。如果對表進行水平分區，則可以減少設備爭用現象，從而有機會改善數據的連續性并加速裝載過程。雖然上圖顯示的是數據裝載到了表的不同部分中，但這樣的表述可能不準確。如果上述裝載中的所有三個線程是同時處理的，為該表提取的擴展盤區最后就可能會是混合狀態。在數據混合后，可能導致在檢索數據時無法實現最佳性能。這是由于數據不是按物理上連續的順序存儲的，從而可能導致系統使用不連續的 I/O 訪問它。
在該表基礎上生成聚集索引將解決上述問題，因為數據會按連續順序被讀入、按鍵順序排序，并被回寫。但是，讀取、排序、刪除舊數據以及將新排序的數據回寫可能是一項非常耗時的任務（請參閱下面的裝載預先排序的數據）。為避免出現這種混合的情形，請考慮使用文件組在可以存儲大表的位置保留多塊連續空間。許多安裝還使用文件組將索引數據與表數據分開。
為便于闡述，假定有一個數據倉庫分配在一個大型物理分區上。任何對該數據庫并行執行的裝載操作都有可能導致以非連續（混合）狀態存儲受影響的數據/索引頁。將執行哪種操作？任何對數據進行修改的操作都將導致數據變得不連續。為了滿足處理窗口的要求，用戶可能會嘗試并行執行初始數據裝載、增量數據裝載、索引創建、索引維護、插入、更新、刪除等活動。
下面的插圖顯示的是跨多個文件組對表分區。
如果您的瀏覽器不支持嵌入式框架，請單擊此處在單獨的頁中查看。
裝載預先排序的數據
SQL Server 的早期版本提供了一個選項，您可以在創建索引時用它來指定 SORTED_DATA 選項。SQL Server 2000 取消了這個選項。在早期版本中，將該選項指定為 CREATE INDEX 語句的一部分是：它能夠讓您在索引創建過程中避免排序的步驟。默認情況下，在 SQL Server 中創建聚集索引時，表中數據會在處理過程中排序。要在 SQL Server 2000 中獲得同樣的效果，請考慮在大容量裝載數據之前創建聚集索引。SQL Server 2000 中的大容量操作使用了增強的索引維護策略，這樣，對于已具有聚集索引的表，可以改進數據導入性能，而且在導入之后不需要對數據重新排序。
FILLFACTOR 和 PAD_INDEX 對數據裝載的影響
FILLFACTOR 和 PAD_INDEX 將在題為“索引和索引維護”的一節中更完整地介紹。對于 FILLFACTOR 和 PAD_INDEX，都需要記住關鍵的一點：創建索引時，如果將它們保留為默認值設置，可能會導致 SQL Server 為存儲數據而執行比必需數量多的寫入和讀取 I/O 操作。如果數據倉庫中沒有發生多少寫入活動但發生了大量讀取活動，則更是如此。要讓 SQL Server 在一頁數據頁或索引頁中寫入更多的數據，您可以在創建索引時指定特定的 FILLFACTOR。最好在提供覆蓋 FILLFACTOR 值時指定 PAD_INDEX。
初始數據裝載的一般準則
在裝載數據時
刪除索引（唯一的例外可能是在裝載預先排序的數據時，請參閱上文）
使用 BULK INSERT、bcp 或大容量復制 API
使用分區數據文件并行裝載到分區表中
對于每個可用的 CPU 運行一個裝載流
設置“大容量日志記錄的”或“簡單”恢復模型
使用 TABLOCK 選項
在裝載數據之后
創建索引
切換到相應的恢復模型
執行備份
增量數據裝載的一般準則
用索引將數據裝載到適當位置。
應當根據性能和并發要求來確定鎖定粒度 (sp_indexoption)。
除非特別需要保留時點恢復（例如，聯機用戶在大容量裝載過程中修改數據庫），否則請將恢復模型從“完全”更改為“大容量日志記錄的”。讀取操作不應當影響大容量裝載。
索引和索引維護
前面已經討論了服務器硬件設備的 I/O 特征。現在，我們將討論 SQL Server 數據和索引結構在物理上是如何放置在磁盤驅動器上的。如果要在設計完成之后改善性能，則索引位置有可能是影響數據倉庫的一個最大因素。
SQL Server 中的索引類型
雖然 SQL Server 2000 引入了幾種新索引類型，但它們全部都基于兩個核心窗體。這兩個核心窗體的格式是聚集索引或非聚集索引。在 SQL Server 中，數據庫設計人員可以使用以下兩種主要類型的索引：
聚集索引。
非聚集索引。
這兩個主要類型的其他變體包括：
唯一索引。
計算列的索引。
索引視圖。
全文索引。
以下各節將詳細介紹上面提到的每種索引（全文索引除外）。全文索引是一種特殊情況，它與其他數據庫索引不同，本章不對它進行介紹。索引視圖是 SQL Server 2000 中新引入的一種索引，它應該會引起數據倉庫用戶的特別關注。SQL Server 2000 中引入的另一項新功能是按升序或降序創建索引。
索引的工作原理
數據庫中的索引類似于圖書中的索引。在一本書中，您使用索引可以迅速找到信息，而不必讀完全書。在一個數據庫中，數據庫程序使用索引可以找到表中的數據，而不必掃描整個表。書中的索引是一個字詞以及各字詞所在頁碼的列表。數據庫中的索引是表中的值以及各值存儲位置（在表中所在的行）的列表。
索引可以針對表中的一列或一組列創建，并以 B 樹的形式實現。索引包含一個條目以及一個或多個對應于表中每一行的列（搜索鍵）。B 樹根據搜索鍵的排序次序按升序或者降序（視創建索引時所選選項而定）存儲，利用該搜索鍵的任何前導子集，可以高效地搜索到 B 樹。例如，利用以下組合，可以高效地搜索 A、B、C 列的索引：A；A 和 B；A、B 和 C。
當您創建數據庫并優化其性能時，應當為查詢中使用的列創建用來查找數據的索引。在 SQL Server 附帶的 pubs 示例數據庫中，在 employee 表的 emp_id 列有一個索引。當用戶執行的語句根據指定的 emp_id 值在 employee 表中查找數據時，SQL Server 查詢處理器識別 emp_id 列的索引并使用該索引來查找數據。下面的插圖說明了該索引如何存儲每個 emp_id 值并指向表中具有相應值的數據所在的行。
如果您的瀏覽器不支持嵌入式框架，請單擊此處在單獨的頁中查看。
但是，帶索引的表需要在數據庫中占用更多的存儲空間。同樣，用來插入、更新或刪除數據的命令的運行時間以及維護索引所需的處理時間會更長。您在設計和創建索引時，一定要注意：性能益處比存儲空間和處理資源所導致的額外成本更加重要。
索引交集
SQL Server 查詢處理器中有一項獨特的功能：執行索引交集。這是一種特殊形式的索引覆蓋，我們將在以后詳述，但是現在因以下兩個原因而需要提及索引交集。第一，它是一種可能會影響您的索引設計策略的技術。
第二，該技術可能會減少您需要的索引數，從而可以大大節省大型數據庫占用的磁盤空間。
索引交集允許查詢處理器使用多個索引來解決查詢。大多數數據庫查詢處理器在嘗試解決查詢時都只使用一個索引。SQL Server 可以組合給定表或視圖中的多個索引，基于這些索引生成哈希表并利用哈希表來減少給定查詢的 I/O 操作。就本質而言，從索引交集中生成的哈希表變成了覆蓋索引，而且，它提供的 I/O 性能與覆蓋索引提供的相同。在數據庫用戶環境中，很難預先確定將針對該數據庫運行的所有查詢，而索引交集為這種環境提供了更大的靈活性。在這種情況下，較好的策略是針對所有經常會被查詢的列定義單列的非群集索引，并讓索引交集處理來需要覆蓋索引的情形。
下面的示例使用了索引交集：
Create index Indexname1 on Table1(col2)
Create index Indexname2 on Table1(col3)
Select col3 from table1 where col2 = 'value'
在執行上面的查詢時，可以通過組合這些索引來快速高效地解決該查詢。
SQL Server 中的索引結構
SQL Server 中所有的索引在物理上都是基于存儲在 8 KB 索引頁上的 B 樹索引結構構建的。每個索引頁都有一個頁頭，頁頭后面是索引行。每個索引行都包含一個鍵值和一個指向行級索引頁或實際數據行的指針。索引中的每一頁又被稱作一個索引節點。B 樹的頂層節點被稱作根節點。索引中的底層節點被稱作葉節點。根和葉之間的任何索引層都統稱為中級層或節點。每層索引中的頁都在雙向鏈接列表中鏈接在一起。
SQL Server 數據頁和索引頁的大小均為 8 KB。SQL Server 數據頁包含所有與表中某行關聯的數據（文本和圖像數據可能除外）。就文本和圖像數據而言，在默認情況下，包含與該文本或圖像列關聯的行的 SQL Server 數據頁將包含一個指針，該指針指向一個或多個包含該文本或圖像數據的 8 KB 頁的二進制樹（或 B 樹）結構。SQL Server 2000 中的一個新功能是能夠將小型文本和圖像值存儲在行中，這意味著小型文本或圖像列將存儲在數據頁上。因為可以避免提取相應的圖像或文本數據所必需的額外 I/O，所以該功能可以減少 I/O 操作。有關如何將表設置為在行中存儲文本或圖像的信息，請參閱“SQL Server 聯機從書”。
聚集索引
聚集索引對于從表中檢索一定范圍的數據值非常有用。非聚集索引最適于檢索特定行，而聚集索引最適于檢索一定范圍的行。但是，由于每個表只允許使用一個聚集索引，因此按照這個簡單的邏輯來確定要創建哪種類型的索引并不總能成功。對于該問題有一個簡單的物理原因。對于聚集索引 B 樹結構的上部（非葉層），如果像對它們的非聚集索引部分那樣組織，則聚集索引的底層由表的實際 8 KB 數據頁組成。但這種情況有一個例外，那就是在視圖的基礎上創建聚集索引時。因為將在下面介紹索引視圖，所以我們將討論針對實際表創建的聚集索引。在針對表創建聚集索引時，會按與索引搜索鍵相同的順序讀取與該表關聯的數據、對這些數據進行排序，并會在物理上將它們存回數據庫。因為該表的數據只能按照一種順序保存到存儲器中，不會導致重復，所以符合一個聚集的限制。
下圖描述了聚集索引的存儲器。
如果您的瀏覽器不支持嵌入式框架，請單擊此處在單獨的頁中查看。
聚集索引和性能
聚集索引有一些會影響性能的固有特征。
在使用聚集索引根據搜索鍵來檢索 SQL Server 數據時，不需要指針跳轉（會導致硬盤上的位置可能不按順序更改）來檢索關聯的數據頁。這是由于聚集索引的葉層實際上就是關聯的數據頁。
如前所述，葉層（當然也包括表或索引視圖的數據）在物理上會按照與搜索鍵相同的順序進行排序和存儲。因為聚集索引的葉層包含表的實際 8 KB 數據頁，所以整個表的行數據會按照由聚集索引確定的順序以物理方式排列在磁盤驅動器上。這就會在根據聚集索引的值從該表中提取大量行（至少大于 64 KB）時帶來潛在的 I/O 性能優勢，因為使用的是順序磁盤 I/O（除非該表上發生了頁拆分，這種情況將在題為“FILLFACTOR 和 PAD_INDEX”的一節中討論）。正因為如此，所以在檢索大量行時，一定要根據將用于執行范圍掃描的列來對表選取聚集索引。
表中與聚集索引相關聯的行必須按照與索引搜索鍵相同的順序排序和存儲，這一點具有以下意義：
在您創建聚集索引時，表會被復制，表中的數據會被排序，然后，原來的表會被刪除。所以，數據庫中必須有足夠的空閑空間來存放數據的副本。
在默認情況下，會在創建索引時對表中的數據進行排序。但是，如果數據已按正確順序排過序，則會自動跳過排序操作。這樣就可以顯著加快索引創建過程。
將數據裝載到表中時的順序應盡可能與您計劃用于生成聚集索引的搜索鍵的順序相同。對于大表（例如那些通常會成為數據倉庫特征的表），該方法將大大加速索引創建過程，從而縮短您處理初始數據裝載所需的時間。只要表中的行仍保持未創建聚集索引時所排的順序，就可以在除去和重建聚集索引時可以使用該方法。任何行排序有誤，操作都會被取消，會出現相應的錯誤信息，而且不會創建索引。
同樣，針對排過序的數據生成聚集索引時所需要的 I/O 也少得多，這是因為不必復制數據、對數據進行排序、將數據存回數據庫，然后刪除舊表數據，而是會將數據留在原來分配給它的擴展盤區中。索引擴展盤區只是添加到數據庫中來存儲頂層節點和中間節點。
注意 針對大表生成索引的首選方法是：先生成聚集索引，然后生成非聚集索引。這樣，就不會因為數據移動而需要重新生成非聚集索引。在除去所有索引時，首先會除去非聚集索引，最后除去聚集索引。這樣，就不需要重新生成索引。
非聚集索引
非聚集索引最適于根據特定的鍵值，從大型 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 索引頁包含的行數遠遠多于數據頁包含的行數，因為索引頁只包含與該索引相關的列數據，而數據頁包含整行的數據，這一點很重要。
另外，請記住維護窗口的出現頻率，維護窗口允許重建索引，以便更正即將發生的頁拆分。請嘗試只在大多數索引頁和數據頁已填滿數據時再重建索引。如果表的聚集索引選擇得當，則不會經常需要重建索引。如果聚集索引均勻地分布數據，從而所有與表相關的數據頁上都會在該表中插入新行，那么，數據頁將會均勻填充。總體說來，這將在開始發生頁拆分且有必要重建聚集索引之前提供更多的時間。
為了確定用于 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 事件探查器
在工具菜單上選擇 SQL 事件探查器，從 SQL Server 企業管理器中啟動 SQL 事件探查器。
按 CTRL+N 鍵新建一個 SQL 事件探查器跟蹤。在連接到 SQL Server 對話框中，選擇要連接到的服務器。
從下拉列表框中選擇 SQLProfilerTuning 模板。
選中另存為文件或另存為表復選框。另存為表選項將打開連接對話框，在該對話框中，您可以將跟蹤信息保存到探查查詢的服務器以外的其他服務器。如果要將跟蹤的活動同時另存為文件和表，請選中這兩個復選框。如果要另存為 .trc 文件，請指向有效的目標和文件名。如果以前已運行過跟蹤，現在再次運行同一跟蹤，請指向現有的跟蹤表；如果這是第一次將跟蹤活動捕獲到表中，您也可以提供新的表名。單擊確定。
單擊運行。
運行工作負荷若干 (3-4) 次
從 SQL Server 企業管理器或開始菜單啟動 SQL 查詢分析器。
連接到 SQL Server，然后將當前數據庫設為在其中創建測試表的數據庫。
在 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
按 CTRL+E 鍵執行查詢。反復執行該步驟三至四次，生成工作負荷樣本。
停止 SQL 事件探查器
在“SQL 事件探查器”窗口中，單擊紅色方塊，停止該事件探查器跟蹤。
將跟蹤文件或表裝載到索引優化向導中
在 SQL 事件探查器中，選擇工具菜單上的索引優化向導，以啟動該向導。單擊下一步。
選擇要分析的數據庫。單擊下一步。
選擇是否要保留現有索引的選項，或者添加索引視圖。
選擇一種優化模式（快速、適中或徹底）。在“快速”優化模式下，索引優化向導執行分析所需的時間較少，但分析不夠徹底，在“徹底”模式下生成的分析最徹底，但所需的分析時間最長。
要查找用 SQL 事件探查器創建的跟蹤文件/表，請選擇我的工作負荷文件或 SQL Server 跟蹤表。單擊下一步。
在選擇要優化的表對話框中，選擇要分析的表，然后單擊下一步。
索引優化向導將分析跟蹤的工作負荷和表結構，然后在索引建議對話框中確定應創建的正確索引。單擊下一步。
該向導提供了幾個選項：立即創建索引、安排索引創建的時間（之后會自動執行的任務），或者創建包含用于創建索引的命令的 Transact-SQL 腳本。選擇首選項，然后單擊下一步。
單擊完成。
索引優化向導針對樣本數據庫和工作負荷生成的 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]))
同等的圖形化執行計劃輸出
下圖顯示查詢 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 本地記錄性能監視信息的效率往往會更高。如果您必須通過網絡記錄日志信息，可以只記錄最重要的計數器信息。
在性能測試運行期間，將所有可用計數器的信息記錄到某個文件中供以后分析，這不失為一個好做法。這樣，對于任何計數器，以后都可以再作進一步檢查。您可以配置系統監視器將所有計數器記錄到日志文件中，與此同時，在其他某種模式（如圖形模式）下監視最感興趣的計數器。這樣，在性能運行期間，所有信息都會被記錄下來，但您最關注的計數器會以清晰整潔的系統監視器圖形顯示出來。
設置要記錄的系統監視器會話
從 Windows 2000 開始菜單中，指向程序、管理工具，然后單擊性能，打開系統監視器。
雙擊性能日志和警報，然后單擊計數器日志。
現有的日志都會在詳細信息窗格中列出。綠色圖標表示日志正在運行；紅色圖標表示日志已被停止。
右鍵單擊詳細信息窗格的空白區域，然后單擊新日志設置。
在名稱中鍵入日志的名稱，然后單擊確定。
在常規選項卡上，單擊添加。選擇要記錄的計數器。您在此處確定要在會話期間監視的 SQL Server 計數器。
如果要更改默認文件，請在日志文件選項卡上進行更改。
記錄的會話可以設置為按預定義的時間段自動運行。為此，請在調度選項卡上修改調度信息。
注意 要保存日志文件的計數器設置，請用右鍵單擊詳細信息窗格中的文件，然后單擊將設置另存為。然后，指定用來保存這些設置的 .htm 文件。要在新日志中重用已保存的設置，請用右鍵單擊詳細信息窗格，然后單擊新日志設置來自。
啟動已記錄的監視會話
從 Windows 2000 開始菜單中，指向程序、管理工具，然后選擇性能，打開系統監視器。
雙擊性能日志和警報，然后單擊計數器日志。
右鍵單擊要運行的計數器日志，然后選擇啟動。
現有的日志都會在詳細信息窗格中列出。綠色圖標表示日志正在運行；紅色圖標表示日志已被停止。
停止已記錄的監視會話
從 Windows 2000 開始菜單中，指向程序、管理工具，然后選擇性能，打開系統監視器。
雙擊性能日志和警報，然后單擊計數器日志。
右鍵單擊要運行的計數器日志，然后選擇停止。
從已記錄的監視會話向系統監視器裝載數據供分析使用
從 Windows 2000 開始菜單中，指向程序、管理工具，然后選擇性能，打開系統監視器。
單擊系統監視器。
右鍵單擊系統監視器的詳細信息窗格，然后單擊屬性。
單擊源選項卡。
在數據源下，單擊日志文件，然后鍵入文件路徑，或單擊瀏覽，查找所需的日志文件。
單擊時間區間。要在日志文件中指定希望查看的時間區間，請拖動滑動條或滑動條柄，設置相應的開始和結束時間。
單擊數據選項卡，然后單擊添加，打開添加計數器對話框。您在日志配置期間選擇的計數器會顯示出來。您可以在圖形中包括所有這些計數器或其中一部分。
如何使系統監視器記錄的事件與過去的某個時點相關
從系統監視器會話中，右鍵單擊系統監視器的詳細信息窗格，然后單擊屬性。時間區間和滑動條允許您設定要在圖形中查看的開始、當前和結束時間。
需要監視的關鍵性能計數器
有幾個性能計數器提供了有關以下重要方面的信息：內存、分頁、處理器、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 查詢，盡量減少進入緩沖區緩存的頁數量。它包括確保具備正確的索引并在使用這些索引。使用聚集索引可以獲得有效的區間掃描，定義非聚集索引有助于快速定位單行或更小的行集。例如，如果您準備在表中只創建一個索引，并且該索引將用于提取單行或更小的行集，該索引應為聚集索引。從表面上看，聚集索引比非聚集索引的速度快。
其他性能主題
使用星型架構和雪花形架構的數據庫設計
數據倉庫使用維度建模來組織數據，以便進行分析。維度建模會生成星型架構和雪花架構，這樣也就為數據倉庫中經常執行的大量數據讀取操作帶來了性能效率。大量的數據（通常成千上萬行）存儲在事實數據表中，表內各行都很短，這就使存儲需求和查詢時間減到最少。業務事實數據的屬性會非正常化為維度表，以最大程度地減少檢索數據時的表聯接數量。
有關數據倉庫的數據庫設計的討論，請參閱第 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 語句以下列方式組合在一起。
分組方法
?說明
?
批處理
?批處理是以一個單元的形式從應用程序發送到服務器的一組 Transact-SQL 語句，其中可包含一條或多條語句。SQL Server 在執行每個批處理時將其視為單個的可執行單元。
?
存儲過程
?存儲過程是已在服務器上預定義和預編譯的一組 Transact-SQL 語句。存儲過程可以接受參數、返回結果集、返回代碼，還可以將參數輸出到調用應用程序。
?
觸發器
?觸發器是一類特殊的存儲過程。它不由應用程序直接調用，而是每當用戶對表執行指定修改（INSERT、UPDATE 或 DELETE）時執行。
?
腳本
?腳本是存儲在文件中的一組 Transact-SQL 語句。該文件可以用作 osql 實用工具或 SQL 查詢分析器的輸入。然后，這些實用工具執行存儲于該文件中的 Transact-SQL 語句。
?
下面的 SQL Server 功能使您可以對同時使用多個 Transact-SQL 語句的情況進行控制。
功能
?說明
?
控制流語句
?允許您包含條件邏輯。例如，如果國家為加拿大，則執行某一組的 Transact-SQL 語句。如果國家為英國，則執行另一組的 Transact-SQL 語句。
?
變量
?允許您存儲數據，在稍后的 Transact-SQL 語句中用作輸入。例如，您可以編寫這樣一個查詢：每次執行該查詢時，都需要在 WHERE 子句中指定不同的數據值。您可以在編寫該查詢時在 WHERE 子句中使用變量，并編寫相應的邏輯，以使用正確數據填充該變量。存儲過程的參數是一類特殊變量。
?
錯誤處理
?允許您自定義 SQL Server 響應問題的方式。您可以指定在發生錯誤時采取的相應的操作，或顯示對用戶來說比一般的 SQL Server 錯誤信息更有用的自定義錯誤信息。
?
重用執行計劃
如果 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 結構和數據庫優化的信息，同時還提供了完整的命令語法和管理的文檔資料。“SQL 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/prodtechnol/sql/default.mspx
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 年。
該書深入探討數據倉庫的數據庫設計，并解釋了維度建模概念。
轉載于:https://www.cnblogs.com/waiter/archive/2006/11/18/564389.html
總結
以上是生活随笔為你收集整理的数据仓库的 RDBMS 性能优化指南的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MCMC（MH算法）
- 下一篇： oracle 中表,oracle中表操作