【導讀】優化較大的工作負荷會對所優化的服務器產生很大的開銷。開銷是由數據庫引擎優化顧問在優化進程中多次調用查詢優化器導致的。如果除生產服務器以外，再使用一台測試服務器，則可以消除此開銷問題。本文介紹如何用MSSQL 2005減輕生產服務器優化負荷。
優化較大的工作負荷會對所優化的服務器產生很大的開銷。開銷是由數據庫引擎優化顧問在優化進程中多次調用查詢優化器導致的。如果除生產服務器以外，再使用一台測試服務器，則可以消除此開銷問題。
數據庫引擎優化顧問如何使用測試服務器
使用測試服務器的傳統方法是將所有數據從生產服務器復制到測試服務器，優化測試服務器，然後在生產服務器上實現建議。此過程可以消除對生產服務器的性能影響，但這不是最佳解決方案。例如，將大量數據從生產服務器復制到測試服務器可能消耗大量時間和資源。此外，測試服務器硬件很少像生產服務器中部署的硬件那樣功能強大。優化進程依賴於查詢優化器，而它生成的建議部分依賴於基礎硬件。如果測試服務器硬件和生產服務器硬件不相同，數據庫引擎優化顧問建議的質量就會降低。
若要避免出現這些問題，數據庫引擎優化顧問將通過把大部分優化負荷轉移到測試服務器，優化生產服務器上的數據庫。它通過使用生產服務器硬件配置信息，而不是真正地將數據從生產服務器復制到測試服務器，來執行該操作。數據庫引擎優化顧問不會將實際數據從生產服務器復制到測試服務器中。它僅復制元數據和必要的統計信息。
下列步驟概要介紹了用於在測試服務器上優化生產數據庫的過程：
1. 確保兩台服務器上都存在要使用測試服務器的用戶。
開始之前，請確保兩台服務器上都存在要使用測試服務器來優化生產服務器上的數據庫的用戶。這就需要您在測試服務器上創建用戶及其登錄帳戶。如果您在兩台計算機上都是 sysadmin 固定服務器角色成員，將不需要執行此步驟。
2. 優化測試服務器上的工作負荷。
若要優化測試服務器上的工作負荷，必須通過 dta 命令行實用工具使用 XML 輸入文件。在 XML 輸入文件中，在 TuningOptions 父元素下使用 TestServer 子元素指定測試服務器的名稱，並為其他子元素指定值。
在優化進程中，數據庫引擎優化顧問將在測試服務器上創建 Shell 數據庫。若要創建此 Shell 數據庫並對其進行優化，數據庫引擎優化顧問需要在下列情況下調用生產服務器：
a. 數據庫引擎優化顧問將元數據從生產數據庫導入到測試服務器 Shell 數據庫。此元數據包括空表、索引、視圖、存儲過程和觸發器等。這使得對測試服務器 Shell 數據庫執行工作負荷查詢成為可能。
b. 數據庫引擎優化顧問從生產服務器導入統計信息，以便查詢優化器可以准確優化對測試服務器的查詢。
c. 數據庫引擎優化顧問從生產服務器導入指定處理器數和可用內存量的硬件參數，為查詢優化器提供生成查詢計劃所需的信息。
3. 數據庫引擎優化顧問優化完測試服務器 Shell 數據庫後，將生成優化建議。
4. 將通過優化測試服務器得到的建議應用於生產服務器。
注意：在數據庫引擎優化顧問的圖形用戶界面 (GUI) 中不支持測試服務器優化功能。
示例
首先，請確保測試服務器和生產服務器上都存在要執行優化的用戶。
將用戶信息復制到測試服務器後，就可以在數據庫引擎優化顧問 XML 輸入文件中定義測試服務器優化會話。下面的示例 XML 輸入文件說明如何使用數據庫引擎優化顧問指定測試服務器來優化數據庫。
在此示例中，MyDatabaseName 數據庫在 MyServerName 上進行優化。Transact-SQL 腳本(即 MyWorkloadScript.sql)用作工作負荷。此工作負荷包含對 MyDatabaseName 執行的事件。查詢優化器對此數據庫的大部分調用操作(作為優化進程的一部分發生)是由駐留在 MyTestServerName 上的 Shell 數據庫實現的。Shell 數據庫由元數據和統計信息構成。此進程會將優化開銷卸載到測試服務器。數據庫引擎優化顧問使用此 XML 輸入文件生成優化建議時，應只考慮索引 (IDX) 而不考慮分區，並且不需要在 MyDatabaseName 中保留現有的任何物理設計結構。
<?xml version="1.0" encoding="utf-16" ?>
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/dta">
<DTAInput>
<Server>
<Name>MyServerName</Name>
<Database>
<Name>MyDatabaseName</Name>
</Database>
</Server>
<Workload>
<File>MyWorkloadScript.sql</File>
</Workload>
<TuningOptions>
<TestServer>MyTestServerName</TestServer>
<FeatureSet>IDX</FeatureSet>
<Partitioning>NONE</Partitioning>
<KeepExisting>NONE</KeepExisting>
</TuningOptions>
</DTAInput>
</DTAXML>
