在 64 位元平台上執行 SQL Server 合併作業：經驗學習

經驗學習

發佈日期: 2004 年 5 月 1 日

作者： Mike Ruthruff

**摘要：**本白皮書探討測試案例中所學習到的經驗，在測試案例中，Microsoft 部署並操作 64 位元伺服器，合併先前在不同實體電腦上運作的 Microsoft SQL Server 執行個體。

概觀

決定在單一伺服器上合併多個 SQL Server 資料庫的方式之前，必須先評估多項考量。其中一項重要考量是：使用 64 位元平台或者 32 位元平台，以取得最佳合併結果。在這項考量上，64 位元平台所提供的優勢勝過 32 位元平台。例如，在 32 位元平台上，有些技術限制可能需要使用多重執行個體，但是在 64 位元平台上，若決定使用多重 SQL Server 執行個體，多半是基於資料管理考量而作成的決定，而不是平台所加諸的技術限制。

本白皮書探討測試案例中所學習到的經驗，在測試案例中，Microsoft 部署並操作 64 位元伺服器，合併先前在不同實體電腦上運作的 SQL Server 執行個體。提供您作為參考範例，示範在 64 位元平台上以 Microsoft SQL Server 執行合併作業的方式。本白皮書所包含的確切內容如下：

• 可用來執行資料庫合併的 64 位元組態範例，其中包括伺服器與儲存體組態的討論。

• 在多重 SQL Server 執行個體與單一執行個體之間作比較，包括記憶體微調的最佳實務。

• 如何使用 Windows 系統資源管理員 (WSRM) 在多重 SQL Server 執行個體的環境中管理 CPU 資源之指導。

• 將異質工作負載合併到同一部伺服器上所產生影響的相關資訊。

本文件並無意解答在合併 SQL Server 資料庫規劃階段可能碰到的所有問題。而且本文件中所探討的測試案例結果也並不一定適用於所有的合併環境。如需更深入了解成功地選擇與部署環境以執行 SQL Server 合併作業的必要規劃情形，請參閱＜附錄 C＞所提供的資源＜規劃以 Microsoft SQL Server 2000 執行合併作業＞。如需 Microsoft 為 SQL Server 合併作業所提供服務的相關資訊，請參閱＜附錄 C＞中的資源＜Microsoft 提供的服務＞。

本頁內容

64 位元合併環境組態設定
單一執行個體與多重執行個體
Windows 系統資源管理員與 SQL Server
異質工作負載
結論
附錄 A：多重執行個體記憶體爭用的考量
附錄 B：儲存體組態與效能
附錄 C：參考資料
附錄 D：硬體組態

64 位元合併環境組態設定

主機硬體組態設定

本文件所探討測試案例中使用的主機硬體是 NEC Express5800/1320Xd 64 位元伺服器。這部伺服器的組態設定是 32 1.5 GHz Intel Itanium II 處理器與 64 GB RAM。這部伺服器分成兩個不同的 16 處理器資料分割，每一個資料分割擁有全部記憶體的一半 (每個資料分割各 32 GB)。每個資料分割所設定的組態都是四個 Emulex LP9002 主匯流排介面卡 (HBA)。兩個 16 處理器資料分割之中，只用了一個來測試合併案例。

儲存體組態設定

用於本文件所探討測試案例中的儲存體組態設定是 NEC Storage S4300 磁碟陣列，用來儲存所有 SQL Server 資料檔。除了用來作測試案例以外，本磁碟陣列也同時由 NEC Express5800/1320Xd 的第二個資料分割用來進行其他無關的 SQL Server 工作負載測試，在實際部署中這可能是一般常見情形，有多個主機使用相同的儲存體陣列來執行 SQL Server 工作負載。為了確保執行測試時實體磁碟層級上不會發生爭用現象，NEC Storage S4300 的組態設定方式是讓各資料分割所使用的邏輯單元數 (LUN) 分別存放在不同的實體磁碟上。儲存體上用來執行合併作業部份的 LUN 是分散在總共 56 個實體旋轉軸 (或實體磁碟) 上。大體上說，一共建立了四個 LUN，提供給 Microsoft Windows 使用，總容量大約是 1 TB。＜附錄 B＞中列出用在本文件所探討測試案例中的儲存體組態設定細目。

單一執行個體與多重執行個體

以 64 位元平台執行合併作業的優勢之一就是在單一 SQL Server 執行個體中向上擴充的能力。在 64 位元平台上執行的應用程式與在 32 位元平台上執行的應用程式不同，可以因大型虛擬位址空間而獲益。在 32 位元平台上，應用程式受限於 4 GB 虛擬位址空間，而且要保留 2 GB 供核心使用。64 位元平台上大型虛擬位址空間允許在單一執行個體之內執行更多合併作業，在下列各種案例中很有利：合併眾多資料庫、必須支援大量連線使用者，或是先前由於鎖定與程序快取之類結構需要大量記憶體，已將伺服器分割成多重執行個體。在 32 位元平台上，組態設定選項，如 /3GB、/PAE 與 Address Windowing Extensions (AWE) 確實都允許 SQL Server 利用更大量的記憶體，但是請考慮下列與這些選項相關的警告：

• 在 Microsoft SQL Server 2000 中啟用 AWE 記憶體雖然允許 SQL Server 使用超過 2 GB 或 /3 GB 限制的記憶體，但是額外的記憶體只能用來執行資料快取。供鎖定、快取程序計劃、使用者連線與資料指標使用的記憶體只能放在非 AWE 的記憶體區域。

• 在 SQL Server 2000 中啟用 AWE 記憶體的結果是：SQL Server 會在啟動處理時保留並認可記憶體，而不是根據需求動態地增加記憶體。因而導致 SQL Server 保留住記憶體，一直到處理終止為止。這種記憶體也無法執行分頁作業。

決定使用單一執行個體或多重執行個體時，64 位元平台因為比 32 位元平台擁有更廣大的虛擬位址空間 (理論上有大約 18 EB 的限制)，可以提供更大的彈性空間。目前 SQL Server 2000 (64 位元版) 提供最多 512 GB 記憶體支援，而且不限制資料結構在位址空間中使用者部份的存放位置。

在 SQL Server 合併案例中，決定是否要部署多重 SQL Server 執行個體有許多考量因素。其中包括應用程式相容性、工作負載隔離、維護的彈性、可用性需求與安全性。這些問題全部都可以透過在單一伺服器上，使用多重 SQL Server 執行個體來解決。本文件中＜附錄 C＞的資源＜規劃以 Microsoft SQL Server 2000 執行合併作業＞對各項問題都有更深入的探討。

為了釐清執行多重 SQL Server 執行個體與單一個體效能比較的含意，Microsoft 測試這兩種案例時，使用完全相同的工作負載。但是在檢閱結果以前，務必要了解測試案例中所牽涉各種不同資料庫及其相關聯工作負載的特性。

資料庫與工作負載

Microsoft 在測試合併案例中使用了幾種資料庫與工作負載，都是從先前存放在不同伺服器上真正的實際執行資料庫中加以選取。資料庫是從總共 10 個伺服器中加以選取，每一個伺服器中都包含 1 到 25 個資料庫。總共使用了 63 個資料庫，大小從 2 MB 一直到 85 GB，平均大約是 7 GB。所牽涉全部資料庫的累計資料大小大約是 460 GB。

使用所選取的資料庫，建立了兩種組態案例以供測試：

• 來自個別伺服器的每一組資料庫都儲存到 NEC Express5800/1320Xd 上不同的 SQL Server 執行個體中，結果總共有 10 個 SQL Server 執行個體。

• 先前所有不同執行個體中的全部資料庫都儲存到 NEC Express5800/1320Xd 上單一 SQL Server 執行個體中。

為了建立要對合併伺服器進行測試的工作負載，擷取了 SQL Profiler 追蹤，並為各個不同的執行個體重新執行。對各組態設定執行了完全相同的工作負載，並使 用 Windows 效能監視器在各測試期間監視其效能。SQL Profiler 追蹤檔案中的工作負載各個不同，但多半是線上交易處理 (OLTP) 工作負載，其中主要是包含修改現有資料及插入新資料的交易查詢。這些工作負載的 I/O 特性造成大量記錄磁碟區傳輸量 (因為有大量的小寫入作業)，以及對資料磁碟區進行的一些讀取/寫入作業。對資料檔發出的大半讀取作業都是選擇性的 (也就是，小讀取)。

除了上述工作負載以外，也執行了其他測試，在其中不同 I/O 特性的工作負載是與重新執行的工作負載同時執行，以便在合併的環境中，更清楚地了解對整個系統效能的衝擊。這個工作負載子集包含決策支援系統 (DSS) (也稱為關聯式資料倉庫 (RDW)) 工作負載，是由有平行執行計劃的複雜性查詢組成。此工作負載的 I/O 特性包含對資料磁碟區執行的極其大量讀取活動 (也就是大讀取)、極少量的記錄傳輸量，另外還有針對中等大小資料庫 (25 GB) 執行數項資料庫主控台命令 (DBCC) 作業 (CHECKDB、SHOWCONTIG、DBREINDEX) 的維護工作負載。這兩個額外的工作負載都有更大量的 I/O 需求。使用這兩個工作負載的結果會在本文稍後的＜Tempdb 考量＞與＜異質工作負載＞中探討。

工作負載效能是以可供 SQL Server 使用的幾個不同微調選項，透過 sp_configure 預存程序進行測試。表 1.1 列出用在測試中的設定。

表 1.1 SP_CONFIGURE 預存程序的設定

設定	單一執行個體	多重執行個體
預設值 (除了最高程度的平行處理原則以外，所有測試都使用此設定)。	4	4
最大伺服器記憶體	30,000 (MB)	各執行個體都不相同，但累計總記憶體為 30,000 (MB)。
CPU 相關性	65,535，相當於位元遮罩，包括所有 CPU (1111111111111111)。	每個執行個體都設定為 65,535。

單一執行個體與多重執行個體的效能比較

Microsoft 為工作負載執行的測試顯示，合併到單一 SQL Server 執行個體之中時，效能優勢稍高。完全相同的工作負載在單一執行個體中執行比分散在多重執行個體中執行效能稍高。在所有組態設定下進行測試，都顯示出一致的結果。圖表 1.1 顯示所有工作負載都作用時的每秒平均交易量。本文件稍後會更深入探討各個微調選項與產生的相關結果。

圖表 1.1 單一執行個體與多重執行個體的每區段交易量

請注意，雖然效能很重要，但它也只是決定使用單一執行個體或重執行個體的考量因素之一而已。其他還有很多重要考量因素是在本文件探討範圍之外。請參閱＜附錄 C＞中的＜規劃以 Microsoft SQL Server 2000 執行合併作業＞，取得更多資訊。

在 64 位元平台上管理 SQL Server 記憶體使用量

64 位元平台讓 SQL Server 能夠使用比在 32 位元平台上更大量的記憶體。其結果是，記憶體組態設定很重要，可確保所有 SQL Server 執行個體，以及其他在該伺服器上執行的應用程式都有足夠的記憶體可以正確運作。SQL Server 永遠都會嘗試至少保留 128 MB 實體記憶體可供使用，以確保適當的 Windows 功能運作；但是有一些情況可能會讓 SQL Server 無法做到。

例如，考慮同時執行多重 SQL Server 執行個體的情形。如果讓一個執行個體離線進行維護，同時其他執行個體繼續執行，作用執行個體就有可能增加記憶體耗用量，直到伺服器上的實體記憶體耗盡為止。當離線的執行個體再度上線時，可能沒有足夠的記憶體可用來執行資料快取。這可能會造成一段期間 I/O 活動增加，而執行個體「重新平衡」記憶體使用量，而影響到所有使用 I/O 子系統的執行個體效能。

在測試期間，為了決定每一個 SQL Server 執行個體最理想的最大伺服器記憶體組態設定選項值，就在每一個執行個體上，使用記憶體管理員監視記憶體耗用量：伺服器總記憶體效能監視器計數器。使用記憶體預設值監視執行個體的記憶體耗用量有助按每個執行個體決定可能的記憶體耗用量。

在單一 SQL Server 執行個體之內合併的優勢之一就是記憶體管理包含在單一處理中。在工作負載測試期間，更有效地使用緩衝集區會產生對 I/O 子系統發出較少 I/O 活動，而在最後導致更好的交易處理能力，並能更有效地使用 CPU 資源。一般來說，讓 SQL Server 在單一執行個體之內，由內部管理記憶體耗用量比跨多重執行個體更容易。圖表 1.2 & 1.3 說明在單一執行個體中，與多重執行個體比較下，由 SQL Server 發出的 I/O 降低情形，同時也說明單一與多重執行個體之間處理器使用量的差異。

圖表 1.2 單一執行個體與多重執行個體每秒的磁碟總讀取量

圖表 1.3 單一執行個體與多重執行個體的處理器平均使用量總計

請注意，SQL Server 中記憶體微調選項的行為在 32 位元平台與 64 位元平台之間有些差異。表 1.2 列出這些差異。

表 1.2 32 位元與 64 位元平台之間的記憶體組態設定行為差異

行為	SQL Server 2000 (32 位元)	SQL Server 2000 (64 位元)
如果最大伺服器記憶體選項設定為比目前伺服器記憶體設定的總值低，而且執行 RECONFIGURE，(在不重新啟動服務的情形下) SQL Server 會動態地將記憶體向下調整到最大伺服器記憶體設定值嗎？	會	會
如果已設定最小伺服器記憶體選項，SQL Server 會在處理啟動時取得相當於此設定的實體記憶體嗎？	不會	會 (注意：這種行為在《Microsoft SQL Server 2000 線上叢書》中說明得不正確。)
如果最小伺服器記憶體選項增加到高於目前伺服器記憶體設定的總值，並執行 RECONFIGURE，(在不重新啟動服務的情形下) 執行個體最多會取得最小伺服器記憶體設定值嗎？	不會	會

如表 1.2 所示，在 64 位元平台上設定最小伺服器記憶體選項時，SQL Server 會在處理啟動時，保留並認可記憶體。雖然可以利用這項來確保特定的執行個體在啟動時取得保證的記憶體量，但必須小心確保， SQL Server 執行個體啟動時，伺服器上永遠都會至少有實體記憶體的量可供使用。否則可能會導致過度使用 Windows 分頁檔中的虛擬記憶體，而對整個系統效能有不利的影響。

在 64 位元平台上，微調 SQL Server 中的記憶體耗用量時，請牢記下面各項：

• 若同時執行多重 SQL Server 執行個體，要明確設定各執行個體的最大伺服器記憶體選項，以確保其他 SQL Server 執行個體有足夠的實體記憶體可用。務必也一定要將在伺服器上執行的其他應用程式以及作業系統都考慮在內。若要避免分頁作業，最理想的情形是永遠都在系統上保留至少 128 MB 的實體記憶體。監視記憶體：可用的位元組與記憶體：頁/秒計數器可協助決定系統上可使用的實體記憶體量，以及系統是否執行分頁作業。

• 使用單一 SQL Server 執行個體時，建議您設定最大伺服器記憶體選項，以便讓其他應用程式及作業系統都有足夠的實體記憶體可用。

• 使用多重 SQL Server 執行個體時，應該要執行測試，決定各 SQL Server 執行個體的最理想記憶體選項設定。使用 SQL Server 監視記憶體總使用量：記憶體管理員計數器以及 SQL Server：緩衝區管理員計數器可以提供各執行個體所耗用記憶體的細目。合併以前的記憶體使用量可以用來估計適當的最大伺服器記憶體選項基準。

• 最小伺服器記憶體設定可以用來保證 SQL Server 各執行個體有一定的記憶體量。若要確保伺服器上永遠都會至少有一定的實體記憶體量可供使用，請為各執行個體設定適當的最大伺服器記憶體。否則可能會由於過度使用虛擬記憶體，而導致整個系統效能不佳。

CPU 相關性

在多處理器系統上，SQL Server 與 CPU 資源的互動可以透過設定相關性遮罩與 affinity64 遮罩組態設定選項來控制。設定這些選項的結果會讓 SQL Server 中的執行緒在每次於相同處理器上執行時進行排程。如果不設定這些選項，執行緒可能會在處理器之間移轉。有兩種主要案例，您可能要考慮在其中使用 CPU 相關性選項。

CPU 相關性案例 1：使用相關性，更有效地運用記憶體

許多高檔多處理器系統現在都使用非統一記憶體存取 (NUMA) 或 行動電話多處理 (CMP) 架構。本文件所探討測試中使用的 NEC Express5800/1320Xd 是以 NUMA 架構為基礎。在 NUMA 系統上設定相關性遮罩選項可能會因為本機處理器快取利用更佳而取得效能優勢。請參閱＜附錄 C＞的資源，其中提供有關這些技術的詳細相關資訊。

在此案例測試期間，所有 SQL Server 執行個體的相關性遮罩選項都設定為 65,535，相當於位元遮罩 '1111111111111111' (每一個處理器一個位元)。對所使用的工作負載來說，以這種方式設定 SQL Server 執行個體會導致總處理能力稍微降低 (如上文中圖表 1.1 所示)。但是要注意很重要的一點，使用此設定取得的結果可能會有差異，要取決於各工作負載的特性。建議您要逐一測試，判斷此選項是否對特定工作負載有利。圖表 1.4 與圖表 1.5 顯示在單一與多重 SQL Server 執行個體測試期間，設定相關性遮罩選項的影響。

圖表 1.4 單一與多重執行個體的處理器佇列長度

圖表 1.4 顯示設定及未設定相關性遮罩選項時的處理器佇列長度。處理器佇列長度是已準備執行但在佇列中等待可用處理器的執行緒數目。在測試案例中，處理器佇列長度增加是因為設定相關性遮罩選項的結果。執行緒並非可以在任何不忙碌的 CPU 上執行，而是必須等待特定的處理器釋出才能使用。雖然表中所示處理器佇列長度對所使用的硬體並非不能接受，但處理器使用量降低的相關結果表示整體的 CPU 資源並未充份加以利用。

圖表 1.5 單一與多重執行個體的每秒內容切換次數

圖表 1.5 顯示平均每秒內容切換次數。在處理器上分派不同執行緒時就會產生內容切換，要求將其內容載入以便進行處理。使用相關性遮罩的結果是內容切換次數減少，但是也會導致無法充份利用 CPU 資源。在所執行的全部測試中，對單一 SQL Server 執行個體執行工作負載時，每秒內容切換次數較低。

CPU 相關性案例 2：分配 CPU 資源

一般使用相關性遮罩選項的理由是要將可用的 CPU 限制在特定的 SQL Server 執行個體上。在合併多重執行個體的案例中，這種手法可以用來在多重執行個體之間分配 CPU 資源。但是雖然這是確保特定 CPU 資源只能供特定 SQL Server 執行個體使用，最後可能會導致無法充份利用伺服器上所提供的全部 CPU 資源。管理 CPU 資源有更好的方法，就是使用 Windows 系統資源管理員之類的管理工具。這一章的後面會討論這個選項。

Tempdb 考量

除了先前所提及使用單一或多重 SQL Server 執行個體的考量以外，還要考慮 tempdb 使用情形。tempdb 資料庫是包含在相同 SQL Server 執行個體之內所有資料庫之間共用的資源，因此有可能成為單一爭用點。

因為其使用量差異極大，而且幾乎是依工作負載而定，所以決定 tempdb 的最佳設定方式時，所牽涉的因素極多。在測試案例的組態中，Microsoft 將 tempdb 的資料檔存放在 NEC Storage S4300 之內實體隔離的磁碟上。tempdb 爭用情形是在執行工作負載時，透過於固定間隔輪詢主系統處理來監視。此外，對 tempdb 的I/O 活動與回應時間是使用效能監視器來監視。至於測試案例中的工作負載，並未觀察到有爭用情形。

決定調整 tempdb 資料庫大小的方式也牽涉許多不同的因素。為了執行合併作業，您可以透過在不同伺服器上，監視使用量並查看目前的 tempdb 大小，來估計所需的 tempdb 空間。在本文件所探討的測試案例中，並沒有過去的 tempdb 使用量或大小等的相關資訊可供參考。因此在單一執行個體組態設定中，tempdb 的大小為 30 GB，並允許自動以 20% 累加擴充。tempdb 的儲存體空間規劃為整個資料大小的 20%，大概接近 90。在測試案例中，儲存體組態設定有足夠的儲存空間可在 tempdb 成長時加以容納，結果顯示 tempdb 檔案 為 30 GB 足以供應所有工作負載的需求 (包括同時並行的 DSS 與維護工作負載)。在工作負載測試期間並沒有發現 tempdb 資料檔有自動成長現象，但是觀察到在執行 DSS 與維護工作負載時，若有 OLTP 工作負載同時並行，平均 tempdb I/O 活動會稍多。如需測試中的 tempdb I/O 活動細目，以及所有其他 I/O 活動，請參閱＜附錄 B：儲存體組態設定與效能＞。

有關如何處理 tempdb 組態設定與大小的決定應該包含下列考量：

• 在某些情況下，當 tempdb I/O 的需求量極大，而且搭配組合 tempdb 檔與資料檔會引進負面影響風險，而對其他資料檔所用磁碟的回應時間不利時，隔離 tempdb 資料檔可以提供優勢；但是這種作法也可能導致無法充份利用全部磁碟資源，所提供的彈性空間較小，而且需要更仔細思索儲存體的組態。了解特定應用程式使用 tempdb 的特性，可協助您決定放置資料檔的位置。

• 合併到單一 SQL Server 執行個體之中時，tempdb 有可能成為單一爭用點。了解各個伺服器使用 tempdb 的情形很重要，因為您可以使用這項資訊，作為合併到單一執行個體時的指導。

• 以不同定序合併 SQL Server 2000 資料庫時，可能會遭遇到困難，因為 tempdb 會保留 model 資料庫的預設定序。如需以不同定序合併資料庫的詳細資訊，請參閱《SQL Server 2000 線上叢書》的＜混合定序環境＞主題。

• 在有八個以上處理器的系統上，讓 tempdb 的每個處理器有 .25 到 .50 資料檔，可以取得更佳的延展性，尤其是大量使用暫存物件的工作負載。請參閱＜附錄 C＞中的參考資料，取得更多資訊。

Windows 系統資源管理員與 SQL Server

Windows 系統資源管理員是包含在 Microsoft Windows Server 2003 Enterprise Edition 與 Microsoft Windows Server 2003 Datacenter Edition 之中的服務，可以讓管理員控制將 CPU 與記憶體資源分配給應用程式、服務和處理程序的方式。此處也探討一些 WSRM 功能。如需詳細資訊，請參閱＜附錄 C＞的參考資料以及與 WSRM 一起安裝的 WSRM 說明檔。

建議您使用 WSRM 來管理分配給各 SQL Server 執行個體的 CPU 資源量。但是，雖然 WSRM 提供控制處理器相關性與處理程序記憶體耗用量的功能，並不建議您使用 WSRM 的這些功能來管理 SQL Server 執行個體。微調記憶體耗用量或處理器相關性應該永遠都是使用 SQL Server sp_configure 系統預存程序來進行 (程序中包含相關性遮罩、最小伺服器記憶體與最大伺服器記憶體等設定)。

WSRM 透過變更所管理處理程序的基礎優先權，控制提供給所管理之處理程序使用的 CPU 資源量。下面列出在 SQL Server 中使用 WSRM 時，必須注意的一些一般原則：

• WSRM 監視在伺服器上的全部 CPU 耗用量，然後會在全部 CPU 資源即將耗盡 (超過 75%) 時，調整所管理處理程序的優先權。

• WSRM 是根據由伺服器管理員所設定的預先定義原則來管理處理程序。CPU 配置原則可以在每一個處理程序層級上定義。WSRM 原則定義 CPU 配置的目標值。這些並不是對一個處理程序的固定限制，而是用來在 CPU 資源即將耗盡時，決定哪個處理程序應該給予優先權。

• 在預設情形下，未確切符合原則的任何處理程序都會放在 WSRM 'default' 群組中。預設群組是給予 100-N% 的 CPU 資源，其中 N 代表明確原則的全部配置總和。

• WSRM 提供可將處理程序加入使用者定義的排除清單功能，此時存取 CPU 資源將不會由 WSRM 加以管理。但是除非在特殊情況下，不建議您將處理程序放在此排除清單上，因為這些處理程序將能夠無限制地使用 CPU 資源，而導致 WSRM 在管理處理程序上效率降低。

在探討使用 WSRM 進行測試的各案例以前，應該注意在 WSRM 相關測試期間，/NUMPROC=8 Boot.ini 參數是用來限制 Windows 可見到的 CPU 數量，因此讓工作負載耗盡 NEC Express 5800/1320Xd 的全部 CPU 資源，而導致 WSRM 管理 CPU 使用量。

WSRM 案例 1：將資源限制用於特定 SQL Server 執行個體

WSRM 是在兩個不同案例中加以測試，試圖控制在伺服器上哪個 SQL Server 執行個體擁有存取優先權。在第一個案例中，WSRM 是用來限制給予特定 SQL Server 執行個體的 CPU 資源量，以確保其他執行個體擁有存取 CPU 資源的優先權。工作負載是同時對 11 個 SQL Server 執行個體執行。前 10 個執行個體的工作負載主要是由 OLTP 類型的工作負載所組成。第 11 個執行個體的工作負載是由以平行執行計劃執行複雜性查詢的多重資料流 (DSS 型工作負載) 所組成。設定了 WSRM 原則，明確地給予 DSS 工作負載 10% 的 CPU 配置目標。剩餘的 SQL Server 執行個體是透過預設 WSRM 原則來管理，此原則將剩下的 90% CPU 資源平均分配給所有執行個體。在測試期間，測量了 CPU 使用量與交易處理能力。結果顯示在圖 1.6 和圖 1.7 中。

圖 1.6 SQL Server 執行個體 的每秒交易量

圖 1.6 顯示執行 OLTP 類型工作負載的各 SQL Server 執行個體的每秒交易量。當 WSRM 不管理處理程序時，所有 OLTP 類型工作負載所達到的每秒交易總量大約是 506；而當 WSRM 管理處理程序時，則達到 587。透過 WSRM 限制執行 DSS 型工作負載的 SQL Server 執行個體可使用的 CPU 資源量，OLTP 工作負載可以多取得 14% 的交易處理能力。

圖 1.7 SQL Server 執行個體的 處理器使用量

圖表 1.7 顯示 SQL Server 執行個體的 CPU 使用量。透過啟用 WSRM，執行 DSS 型工作負載的 SQL Server 執行個體平均 CPU 使用量大約從 254% 降低到 206% (因為是在多重處理器系統上所以超過 100%，CPU 使用量可以達到處理器數目乘以 100%)。更明顯的是，執行 DSS 型工作負載之 SQL Server 執行個體的最高 CPU 使用量記錄，從未啟用 WSRM 時的 682% 降到已啟用 WSRM 時的 361%。透過啟用 WSRM 原則，WSRM 能夠確保 OLTP 工作負載對 CPU 資源有優先權，而且這些工作負載可達到的處理能力即使在全部 CPU 資源都已耗盡時，也可以提升。

WSRM 是透過監視整個 CPU 使用量，並動態地調整所管理處理程序的優先權基底，來管理特定處理器可以使用的 CPU 資源。在圖 1.8 中，Windows 效能監視器圖表顯示：

• 處理程序：% Processor Time

• 處理程序：sqlservr.exe 的 Priority Base

• 處理器：% Processor Time (_Total)

  

  圖 1.8 使用 WSRM 管理處理程序優先權

在圖 1.8 中，請注意 WSRM 與所管理處理程序的互動方式。sqlservr#8 執行個體代表執行 DSS 型工作負載的處理程序，sqlservr#3 執行個體代表執行 OLTP 類型工作負載的 SQL Server 執行個體之一。當 CPU 使用量超過 75% 使用率時，WSRM 就調整執行 DSS 工作負載之執行個體的基礎優先權，讓它低於其他 OLTP 執行個體。為了可讀性，並未包括所有相關 SQL Server 處理程序的計數器。

WSRM 案例 2：授予一組 SQL Server 執行個體優先權

在第二個測試案例中，WSRM 是用來授予更多 CPU 資源給使用最多 CPU 使用量的前三個 SQL Server 執行個體。在這個測試中，OLTP 類型工作負載是對總共 10 個 SQL Server 執行個體執行。WSRM 原則是為三個 SQL Server 執行個體定義的，每個執行個體各給予 20% 的 CPU 配置目標。最後七個執行個體是透過預設 WSRM 原則來控制，平均分配剩餘的 40% CPU 資源。在測試期間，測量了 CPU 使用量與交易處理能力。

圖 1.9 和圖 1.10 顯示測試下 10 個 SQL Server 執行個體中每個執行個體的交易處理能力與 CPU 使用量。

圖 1.9 SQL Server 執行個體的 每秒交易量

圖 1.10 SQL Server 執行個體的 CPU 使用量

在圖 1.9 和 1.10 中，OLTP4、OLTP7 和 OLTP8 代表由 WSRM 原則所定義各有 20% CPU 配置目標的三個執行個體。剩餘的執行個體都在 WSRM 預設群組中，並平分剩餘的 40% CPU 資源。以這種方式使用 WSRM 的結果是：所有 SQL Server 執行個體每秒的累計交易量從停用 WSRM 的 627 增加到啟用 WSRM 的 682，代表大約改善了 8%。

兩個測試案例都顯示，必要時，WSRM 能夠成功地管理伺服器上所授予的 CPU 資源優先權，並能充份利用機器上整體的資源。由於 WSRM 是在處理程序層級上管理資源，必須使用多重 SQL Server 執行個體，才能用 WSRM 來管理 SQL Server。

異質工作負載

在本文件的內容中，異質工作負載指的是 I/O 特性極其不同的工作負載。將多個資料庫合併到單一伺服器上，一般不建議將包含極為不同 I/O 特性的工作負載放在同一個伺服器上，更重要的是不要放在共用的 I/O 子系統上。資料庫工作負載可以分成兩大類別：

• 線上交易處理 (OLTP) 工作負載主要是由修改現有資料及插入新資料的交易查詢所組成。此類型工作負載的 I/O 特性是：大量記錄磁碟區傳輸量 (大量的小型寫入作業)，以及對資料磁碟區進行的一些讀取/寫入作業。對資料檔發出的大多數讀取作業都是選擇性的 (也就是小型讀取)。

• 決策支援系統 (DSS) 工作負載，也稱為關聯式資料倉儲 (RDW) 工作負載，是由一般有平行執行計劃的複雜性查詢組成。這些類型工作負載的 I/O 特性是：對資料磁碟區執行極其大量的讀取活動 (也就是大型讀取) 與極少量的記錄傳輸量。

在這裡為了使討論容易進行，已經簡化了工作負載的描述 — 您使用的工作負載可能不是永遠都完全符合這兩類工作負載之一。但是，若要將多個工作負載一起合併到一組共同的磁碟上，了解特定工作負載的 I/O 特性很重要。在同一組實體旋轉軸上混合不同工作負載類型對整個系統的效能有負面影響。

在一些測試案例中，Microsoft 結合 DSS 型工作負載與 OLTP 型工作負載，以決定 DSS 工作負載影響 OLTP 效能的方式。一組測試是將 DSS 資料檔與 OLTP 資料檔存放在相同的實體旋轉軸上執行；另一組測試是將 DSS 資料檔與 OLTP 資料檔存放在不同實體旋轉軸上執行。圖 1.11 顯示這兩種測試的結果。

圖 1.11 異質工作負載效能

如圖 1.11 中的結果顯示，DSS 型工作負載 I/O 的一般結果是較高的磁碟延遲。將 DSS 資料檔隔離在不同實體旋轉軸上時，對 OLTP 資料檔發出的讀取磁碟延遲從 32 微秒 (ms) 降到 13.5 ms。DSS 工作負載的效能取決於讀取處理能力。將資料檔放在不同磁碟上時可維持這種處理能力，且結果不會觀察到任何效能影響。但是，隔離兩種工作負載會導致由 OLTP 工作負載發出的 I/O 平均延遲降低，而最後在測試案例中導致較佳的 OLTP 工作負載效能。混合工作負載的影響可歸結為工作負載的 I/O 特性、所用硬體的容量與儲存體的設計。如果計劃要在單一伺服器上混合工作負載，就應該將所有這些因素都考慮在內。

結論

64 位元平台在有關 SQL Server 合併作業方面，提供勝過 32 位元平台的一些優勢。若決定使用多重 SQL Server 執行個體，多半是基於資料管理考量而作成的決定，而不是因平台加諸其上的技術限制。除了效能以外，決定在單一伺服器上合併多個 SQL Server 資料庫的方式之前，還必須先評估多項考量。本文件只提供作為合併作業方法的範例，而不是用來完成合併作業的快速而固定不變的規則。在適當的規劃與分析下，資料庫的合併作業可以有效率地完成，而能達到管理容易且成本降低的結果。

總結來說，從合併測試案例中學習到的重要經驗如下：

• 在單一 SQL Server 執行個體之內執行合併作業時，可能會取得稍高的效能優勢。但是請務必牢記，效能只是眾多考量之一。建議您檢閱＜附錄 C＞中的參考文件＜規劃以 Microsoft SQL Server 2000 執行合併作業＞。

• 建議的最佳實務是：為 SQL Server 執行個體設定記憶體上限 (使用最大伺服器記憶體選項)。這在 64 位元平台上尤其重要，SQL Server 執行個體在此平台上可能會耗用大量記憶體。在多重執行個體案例中，應該永遠都設定最大伺服器記憶體，因為多重 SQL Server 執行個體之間可能會發生記憶體爭用現象。

• 您可以使用 Windows 系統資源管理員，動態地管理授予 SQL Server 執行個體的 CPU 資源。這可能是考慮使用多重執行個體的理由，因為 WSRM 讓系統管理員能夠授予特定應用程式優先權。必須要有多重 SQL Server 執行個體，才能使用 WSRM 來管理 SQL Server 資源。

• 一般都建議，避免將異質工作負載合併到相同伺服器上，但是並不是永遠都能做到。如果是合併不同 I/O 特性的工作負載，要仔細思索與工作負載隔離相關的 I/O 子系統組態。

附錄 A：多重執行個體記憶體爭用的考量

對工作負載效能的記憶體壓力影響

除了本文件中描述的合併案例以外，使用多重 SQL Server 執行個體的測試案例是在另一部 64 位元系統上執行，其中所含的實體記憶體與 CPU 資源都比 NEC 系統少。結果是 SQL Server 執行個體必須在伺服器上競爭取得可用的記憶體。測試是在有 4 個處理器、配備 12 GB RAM 64 位元的 Itanium 系統上進行，並在測試期間測量對效能的整體影響。

SQL Server 繼續耗用緩衝集區的記憶體，直到伺服器重新啟動、對最大伺服器記憶體設定作了組態變更，或是有來自系統上其他應用程式的記憶體壓力。若要判斷記憶體壓力對工作負載效能的影響，就在另一個 SQL Server 執行個體已在伺服器上取得大部份實體記憶體以後，啟動 SQL Server 執行個體。執行測試以前，也進行兩個執行個體同時都上線的效能基準測試 (案例 A)。在下一個測試 (案例 B) 中，執行個體是以最大伺服器記憶體及最小伺服器記憶體設定為 0 來進行組態設定，結果是 SQL Server 耗用記憶體直到只剩 128 MB 可用實體記憶體為止。在最後測試 (案例 C) 中，已經上線之執行個體所耗用記憶體量限制於可用 12 GB 中的 10 GB，這是透過設定最大伺服器記憶體加以限制。表 A.1 大概描繪出所執行的工作負載案例。

表 A.1 爭用案例

案例	執行個體 1	執行個體 2
案例 A (沒有記憶體壓力的測試基準)	與執行個體 2 同時上線。	與執行個體 1 同時上線。
案例 B	在執行個體 2 之後上線，耗掉最大伺服器記憶體設定的所有實體記憶體，然後閒置。	在執行個體 1 之前上線，耗掉最大伺服器記憶體設定的所有可用 RAM，然後閒置。本案例重複進行兩次，一次將最大伺服器記憶體設定為零，然後再將最大伺服器記憶體設定為 10 GB。
案例 C	在執行個體 2 耗掉最大伺服器記憶體設定之所有實體記憶體之後上線，並以 OLTP 工作負載作用。	在執行個體 1 之前上線，耗掉最大伺服器記憶體設定的所有可用 RAM，然後啟動 OLTP 工作負載。本案例重複進行兩次，一次將最大伺服器記憶體設定為零，然後再將最大伺服器記憶體設定為 10 GB。

圖 A.1 顯示在上述 A、B 和 C 案例中，由工作負載實現的交易處理能力。

圖 A.1 上線之 SQL Server 執行個體的每秒交易量 (在另一個執行個體幾乎耗盡所有實體記憶體以後)

圖 A.1 顯示在另一個執行個體已經耗盡部份可用的記憶體以後，讓一個 SQL Server 執行個體上線對交易處理能力的影響。案例 A 顯示當兩個執行個體同時上線時，可達到的交易處理能力 (兩個執行個體之間沒有記憶體壓力)。案例 B 和案例 C 的藍條代表在另一個執行個體已經將可用的 12 GB 耗掉大約 11.5 GB 時，讓一個執行個體上線的交易處理能力 (原先已上線執行個體的最大伺服器記憶體選項是設定為零或無限制)。案例 B 和案例 C 的紅條代表在另一個執行個體已經將 12 GB 實體記憶體耗掉 10 GB，而只留下少量記憶體可供執行個體上線時，讓一個執行個體上線所達到的交易處理能力 (原先已上線執行個體透過將最大伺服器記憶體選項設定為 10 GB，將記憶體耗用量限制於 12 GB 的 10 GB)。案例 B 和案例 C 顯示執行個體閒置下與執行個體作用下，讓另一個執行個體上線時的差異。

圖 A.1 顯示在限制一個 SQL Server 執行個體的記憶體用量下，讓另一個執行個體上線可能會取得更佳效能。在案例 B 中，於伺服器閒置下讓執行個體上線，可達到的每秒交易量降低 55%。在第一個執行個體上設定上限，留下小部份可用記憶體，可大幅提升效能。在案例 C 中，於伺服器作用下讓執行個體上線差異更大，可達到的每秒交易量降低 203%。

圖 A.2 整個磁碟子系統的每秒磁碟總讀取量 (有多個執行個體爭用記憶體)

如圖 A.2 所示，除了在執行個體上線時，因緩衝集區的記憶體取得延遲而降低交易處理能力以外，也有更多磁碟 I/O 活動，會同時影響兩個 SQL Server 執行個體的效能。後來上線的 SQL Server 執行個體無法快取所要求的資料頁，結果使得磁碟活動增加。透過限制原先已上線執行個體的記憶體耗用量，後來上線的執行個體在快取資料頁時效能更佳，結果使整個系統的 I/O 活動降低。

附錄 B：儲存體組態與效能

本文件所探討的合併測試中，Microsoft 使用 NEC Storage S4300。為了確保實體磁碟層級上不會與其他非測試基準工作負載發生爭用現象，NEC Storage S4300 的組態方式讓各資料分割所使用的邏輯單元數分別存放在不同的實體磁碟上。儲存體上用來執行合併作業的 LUN 是分佈在總共 56 個實體旋轉軸 (或是實體磁碟) 上。大體上說，一共建立了四個 LUN 提供給 Windows 使用，總容量大約是 1 TB。

如圖 B.1 所示，合併測試所用的邏輯裝置 (LD) 是存放在實體隔離的旋轉軸上。各個邏輯裝置號碼都代表實體磁碟機。由於實體旋轉軸是 NEC Storage S4300 上最小的 LUN 元件，很容易就能進行設計達到隔離的目的。透過將資料與記錄隔離的手法，以便將相同 I/O 特性組成群組，而降低記錄寫入延遲。Tempdb 也與資料隔離，以確保資料檔的最佳效能，因為大半工作負載都沒有太多 tempdb 特性相關資訊可用。

圖 B.1 邏輯實體磁碟的組態

表 B.1 顯示各邏輯單元數的使用量細目。

表 B.1 邏輯單元數組態

LUN 數	大小	磁碟區	使用情形	旋轉軸
LD0 – LD5	未使用	未使用	未使用	54
LD6	265 GB	資料1 (132 GB) 資料2 (133 GB)	資料檔	16
LD7	133 GB	記錄1	記錄檔	8
LD8	265 GB	資料3 (132 GB) 資料4 (133 GB)	資料檔	16
LD9	265 GB	資料5 (132 GB) Tempdb1	Tempdb 與備份資料	16

用在 SQL Server 的儲存區域網路 (SAN) 組態可能會有很大的差異，全取決於特定用途案例的特性。在本文件所探討的測試案例中，下列考量會影響儲存體設定的決定：

• 由於案例中的資料庫數目太多，不可能讓每個資料庫將記錄與資料集達到實體隔離。而是在所有記錄、資料與 tempdb 檔之間建立實體隔離邊界，以便將 I/O 特性類似的檔案集合成群組。

• 供資料檔使用的兩個邏輯單位數是透過 Windows 磁碟管理 MMC 嵌入式管理單元分成四個邏輯磁碟區，目的是在備份與還原作業期間，允許更多的平行處理原則，因為備份與還原作業會在每個磁碟區建立一個讀取器與一個寫入器執行緒。然後再將資料檔依照大小平均地分散在所有磁碟區中。同時也能允許使用邏輯磁碟效能監視器的計數器進行更細微的監視。

• 簡化的儲存體組態比嘗試為特定工作負載作微調更有利，因為測試目標是在判斷合併工作負載的行為方式，包括合併 OLTP 與 DSS 型工作負載時之行為方式。在多種部署下，根據 I/O 特性、根據商務用途，或同時根據兩者，將資料檔組成邏輯或實體群組可能比較有利。

• 主機伺服器與 NEC Storage S4300 之間的所有 I/O 傳輸量都是透過 NEC Storage PathManager 3.0 Enterprise for Windows，在四個 HBA 之間進行負載平衡 (以循環配置資源的方式)；NEC Storage PathManager 3.0 Enterprise for Windows 是一項以 Microsoft Multipath I/O (MPIO) 技術為基礎的多路徑軟體解決方案。一般來說，建議使用的是負載平衡解決方案，因為萬一 HBA 失敗時，這樣會更易於進行組態設定與重複作業。主機伺服器與 NEC Storage S4300 上的光纖通道通訊埠之間是進行直接連線。

下圖揭露在測試合併方案期間執行工作負載時，使用 NEC Storage S4300 所達到的 I/O 效能。在這裡提供這些圖表是作為參考，看所測試的組態案例可能會有何種 I/O 效能。

圖 B.2 單一執行個體磁碟處理能力 (OLTP 與 DSS 工作負載同時並行)

圖 B.3 單一執行個體磁碟處理能力 (只有 OLTP 工作負載)

圖 B.4 多重執行個體磁碟處理能力 (OLTP 與 DSS 工作負載同時並行)

圖 B.5 多重執行個體磁碟處理能力 (只有 OLTP 工作負載)

附錄 C：參考資料

NEC Storage S4300 與 Express5800/1320Xd (英文)

http://www.necstorage.com

http://www.necstorage.com/product/san/s4300/index.shtml

規劃以 Microsoft SQL Server 2000 執行合併作業 (英文)

https://www.microsoft.com/technet/prodtechnol/sql/2000/plan/SQL2KCon.mspx

Microsoft 提供的服務 (英文)

https://www.microsoft.com/services/microsoftservices/offe.mspx

Windows 系統資源管理員首頁 (英文)

https://www.microsoft.com/windowsserver2003/technologies/management/wsrm/default.mspx

Microsoft 儲存技術 – Multipath I/O (英文)

https://www.microsoft.com/windowsserversystem/storage/technologies/mpio/default.mspx

Microsoft 知識庫文件編號 271624 INF：使用 DBCC MEMORYSTATUS 監視 SQL Server 的記憶體使用

https://support.microsoft.com/default.aspx?kbid=271624

Microsoft 知識庫文件編號 314546 HOW TO：在執行 SQL Server 的電腦間移動資料庫

https://support.microsoft.com/default.aspx?kbid=314546

Microsoft 知識庫文件編號 328551 FIX：Tempdb 資料庫的並行處理增強功能

https://support.microsoft.com/default.aspx?kbid=328551

附錄 D：硬體組態

伺服器

NEC Express5800/1320Xd

• 32 Intel Itanium II 1.5 GHz 處理器

• 64 GB RAM

• 4 個 Emulex LP9002 PCI 主匯流排介面卡 (HBA)

• 這種組態分成兩個資料分割，各有 16 個處理器和 32 GB RAM。合併測試只使用一個資料分割。

儲存體

NEC Storage S4300 磁碟陣列

• 105 – 15K RPM 36 GB 磁碟

• 16 GB 快取

• 以 RAID 1+0 設定組態

  使用 NEC Storage PathManager 3.0 Enterprise for Windows，以 Microsoft Multipath I/O (MPIO) 技術為基礎的多路徑軟體解決方案，在全部四個主匯流排介面卡 (HBA) 以平衡 I/O 傳輸量

結構開關

無切換 (直接連線)

作業系統

64 位元版 Microsoft Windows Server 2003 Datacenter Edition

資料庫伺服器

Microsoft SQL Server 2000 (64 位元版)