降低生產伺服器的微調負載
微調大量工作負載會對所微調的伺服器產生重大負擔。負擔來自於 Database Engine Tuning Advisor 在微調處理中,對查詢最佳化工具所發出的大量呼叫。若您在生產伺服器之外使用測試伺服器,即可解決這個負擔問題。
Database Engine Tuning Advisor 使用測試伺服器的方式
測試伺服器的傳統使用方式是,將生產伺服器上所有的資料都複製到測試伺服器上,然後微調測試伺服器,再於生產伺服器上實作建議項目。這項程序可避免對生產伺服器造成效能上的影響,但卻不是最好的解決方案。例如,從生產伺服器複製大量資料到測試伺服器,會耗用大量的時間與資源。此外,測試伺服器硬體不太可能和生產伺服器所部署的硬體一樣強大。微調處理所依賴的是查詢最佳化工具,而它所產生的建議有部份卻是依據基礎硬體。若測試伺服器與生產伺服器硬體不完全相同,Database Engine Tuning Advisor 建議的品質就會受到影響。
為避免這些問題,Database Engine Tuning Advisor 在對生產伺服器的資料庫進行微調時,會將大部份的微調負載卸載到測試伺服器上。它的作法是使用生產伺服器硬體組態資訊,而不實際將生產伺服器的資料複製到測試伺服器。Database Engine Tuning Advisor 不會從生產伺服器複製實際資料到測試伺服器。它只會複製中繼資料與必要的統計資料。
下列步驟將概略說明在測試伺服器上微調生產資料庫的程序:
- 確定要使用測試伺服器的使用者,同時存在於兩部伺服器上。
開始之前,請先確定要使用測試伺服器來微調生產伺服器資料庫的使用者,同時存在於兩部伺服器上。為此,您必須建立使用者及其在測試伺服器上的登入。若您在兩部電腦上都是系統管理員 (sysadmin) 固定伺服器角色的成員,即可略過此步驟。 - 在測試伺服器上微調工作負載。
若要在測試伺服器上微調工作負載,您必須透過 dta 命令列公用程式使用 XML 輸入檔。在 XML 輸入檔中,以 TuningOptions 父項目下的 TestServer 子項目指定測試伺服器的名稱,並指定其他子項目的值。
在微調的過程中,Database Engine Tuning Advisor 會在測試伺服器上建立 Shell 資料庫。為建立此 Shell 資料庫並加以微調,Database Engine Tuning Advisor 會針對下列項目,對生產伺服器發出呼叫:- Database Engine Tuning Advisor 會將生產伺服器的中繼資料,匯入測試伺服器的 Shell 資料庫。此中繼資料中包含了空白資料表、索引、檢視、預存程序、觸發程序等,如此即可對測試伺服器的 Shell 資料庫執行工作負載查詢。
- Database Engine Tuning Advisor 會從生產伺服器匯入統計資料,讓查詢最佳化工具能夠準確地將測試伺服器上的查詢最佳化。
- Database Engine Tuning Advisor 會匯入硬體參數,以指定生產伺服器的處理器與可用記憶體數量,進而為查詢最佳化工具提供在產生查詢計劃時所需的資訊。
- 在 Database Engine Tuning Advisor 完成測試伺服器 Shell 資料庫的微調後,會產生微調建議。
- 將微調測試伺服器之後所得到的建議,套用到生產伺服器上。
下圖說明測試伺服器與生產伺服器的案例:
附註: |
---|
Database Engine Tuning Advisor 圖形使用者介面 (GUI) 中不支援測試伺服器微調功能。 |
範例
首先,請確定要執行微調的使用者,同時存在於測試伺服器與生產伺服器上。
將使用者資訊複製到測試伺服器後,您可以在 Database Engine Tuning Advisor XML 輸入檔中定義用於測試伺服器的微調工作階段。以下 XML 輸入檔範例將說明,如何以 Database Engine Tuning Advisor 指定測試伺服器的資料庫微調作業。
在此範例中,MyDatabaseName
資料庫會在 MyServerName
上進行微調。並使用 Transact-SQL 指令碼 MyWorkloadScript.sql
作為工作負載。此工作負載中包含了針對 MyDatabaseName
而執行的事件。在微調過程中,查詢最佳化工具對此資料庫所產生的大多數呼叫,都由位於 MyTestServerName
中的 Shell 資料庫處理。Shell 資料庫中含有中繼資料與統計資料,此程序會將微調負擔卸載到測試伺服器上。當 Database Engine Tuning Advisor 使用此 XML 輸入檔產生微調建議時,應只會考量索引 (<FeatureSet>IDX</FeatureSet>
),而不考量資料分割,也不需在 MyDatabaseName
中保存任何現有的實體設計結構。
<?xml version="1.0" encoding="utf-16" ?>
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://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>