使用 Database Engine Tuning Advisor 分析 Amazon RDS for SQL Server 資料庫執行個體上的資料庫工作負載

Database Engine Tuning Advisor 為 Microsoft 提供的一項用戶端應用程式，可分析資料庫工作負載並可根據您執行的查詢，為 Microsoft SQL Server 資料庫建議一組理想的索引。如同 SQL Server Management Studio，您可以從與您執行 SQL Server 的 Amazon RDS 資料庫執行個體連接的用戶端電腦執行 Tuning Advisor。用戶端電腦可以是在您自己網路內的現場部署執行的本機電腦，也可以是在與 Amazon RDS 資料庫執行個體相同區域中執行的 Amazon EC2 Windows 執行個體。

此小節顯示如何擷取工作負載供 Tuning Advisor 分析。這是擷取工作負載偏好的程序，因為 Amazon RDS 會限制主機對 SQL Server 執行個體的存取。如需詳細資訊，請參閱 Microsoft 文件中的 Database Engine Tuning Advisor。

若要使用 Tuning Advisor，您必須向建議程式提供名為工作負載的內容。工作負載是一組 Transact-SQL 陳述式，它會對您想要調校的一或多個資料庫執行。在調校資料庫時，Database Engine Tuning Advisor 使用追蹤檔案、追蹤資料表、Transact-SQL 指令碼或 XML 檔案做為工作負載輸入。使用 Amazon RDS 時，工作負載可以是用戶端電腦上的檔案，或可供用戶端電腦存取之 Amazon RDS for SQL Server 資料庫上的資料庫資料表。檔案或資料表必須包含對您要調校之資料庫的查詢 (採用適合重播的格式)。

若要讓 Tuning Advisor 更有效，工作負載應該盡可能實際。您可以透過對資料庫執行個體執行追蹤來產生工作負載檔案或資料表。執行追蹤時，您可以在資料庫執行個體上模擬負載或使用一般負載執行應用程式。

有兩個類型的追蹤：用戶端和伺服器端。您可以輕鬆設定用戶端追蹤，且可以在 SQL Server Profiler 中即時監看擷取的追蹤事件。伺服器端追蹤的設定較複雜，因此需要編寫一些 Transact-SQL 指令碼。此外，因為會將追蹤寫入 Amazon RDS 資料庫執行個體上的檔案，因此追蹤會耗用儲存空間。務必追蹤執行伺服器端追蹤所使用的儲存空間，因為資料庫執行個體可能進入儲存空間滿載的狀態，因此如果儲存空間用完將不再可用。

針對用戶端追蹤，在 SQL Server Profiler 中擷取了足夠數量的追蹤資料時，接著您可以透過將追蹤儲存到您本機電腦上的檔案或可供用戶端電腦存取的資料庫執行個體上的資料庫資料表，藉此產生工作負載檔案。使用用戶端追蹤的主要缺點是該追蹤在繁重負載的情況下，可能不會擷取所有查詢。這可能會削弱 Database Engine Tuning Advisor 所執行分析的有效性。如果需要在繁重負載下執行追蹤，並想要確保它可擷取追蹤工作階段期間的每個查詢，您應該使用伺服器端追蹤。

針對伺服器端追蹤，您必須將資料庫執行個體上的追蹤檔案放入合適的工作負載檔案中，或您可以在追蹤完成之後將追蹤儲存至資料庫執行個體上的資料表。您可以使用 SQL Server Profiler 將追蹤儲存至本機電腦上的檔案，或是從資料庫執行個體上的追蹤資料表讀取 Tuning Advisor。

在 SQL Server 資料庫執行個體上執行用戶端追蹤

在 SQL Server 資料庫執行個體上執行用戶端追蹤

1. 啟動 SQL Server Profiler。它安裝在 SQL Server 執行個體資料夾的 Performance Tools 資料夾。您必須載入或定義追蹤定義範本才能開始用戶端追蹤。

2. 在 SQL Server Profiler File (檔案) 選單中，選擇 New Trace (新增追蹤)。在 Connect to Server (連接至伺服器) 對話方塊中，輸入您想要對其執行追蹤之資料庫的資料庫執行個體端點、連接埠、主要使用者名稱和密碼。

3. 在 Trace Properties (追蹤屬性) 對話方塊中，輸入追蹤名稱並選擇追蹤定義範本。應用程式隨附了預設的範本 TSQL_Replay。您可以編輯此範本來定義追蹤。在 Trace Properties (追蹤屬性) 對話方塊的 Events Selection (事件選取) 標籤下編輯事件和事件資訊。

   如需追蹤定義範本和使用 SQL Server Profiler 來指定用戶端追蹤的詳細資訊，請參閱 Microsoft 文件中的 Database Engine Tuning Advisor。

4. 開始用戶端追蹤，並在對資料庫執行個體執行追蹤時即時監看 SQL 查詢。

5. 完成追蹤時從 File (檔案) 功能表選取 Stop Trace (停止追蹤)。將結果儲存為檔案或儲存為資料庫執行個體上的追蹤資料表。

在 SQL Server 資料庫執行個體上執行伺服器端追蹤

編寫指令碼來建立伺服器端追蹤的操作可能很複雜，並且不在此文件的範圍。此小節包含您可以用作範例的範例指令碼。利用用戶端追蹤時，目標是要建立您可以使用 Database Engine Tuning Advisor 來開啟的工作負載檔案或追蹤資料表。

下列是會開始伺服器端追蹤並將詳細資料擷取至工作負載檔案之縮減的範例指令碼。此追蹤最初是將檔案儲存至 D:\RDSDBDATA\Log 目錄中的 RDSTrace.trc，並且每 100 MB 輪替，因此後續的追蹤檔案名為 RDSTrace_1.trc、RDSTrace_2.trc 等等。

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc = 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
 . . .
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

下列範例是停止追蹤的指令碼。請注意，先前的指令碼建立的追蹤會繼續執行，直到您明確停止追蹤或該程序用完磁碟空間為止。

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END 

您可以將伺服器端追蹤結果儲存至資料庫資料表，並透過使用 fn_trace_gettable 函數，將資料庫資料表用作 Tuning Advisor 的工作負載。下列命令會將 D:\rdsdbdata\Log 目錄中名為 RDSTrace.trc 之所有檔案的結果 (包括所有輪替檔案 (像是 RDSTrace_1.trc)) 載入目前資料庫中名為 RDSTrace 的資料表。

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default); 

若要將特定輪替檔案儲存至資料表，例如 RDSTrace_1.trc 檔案，請指定輪替檔案的名稱並將 1 替換，而非使用預設值做為傳遞至 fn_Trace_gettable 的最後一個參數。

SELECT * INTO RDSTrace_1
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1); 

使用追蹤執行 Tuning Advisor

建立追蹤做為本機檔案或資料庫資料表後，接著您可以對資料庫執行個體執行 Tuning Advisor。使用 Tuning Advisor 搭配 Amazon RDS 與使用獨立式、遠端 SQL Server 執行個體時是相同的程序。您可以在用戶端機器上使用 Tuning Advisor UI 或從命令列使用 dta.exe 公用程式。在這兩個情況下，使用 Tuning Advisor 時，您都必須使用資料庫執行個體的端點連接至 Amazon RDS 資料庫執行個體，並提供您的主要使用者名稱和主要使用者密碼。

下列程式碼範例示範對具有 dta.cnazcmklsdei.us-east-1.rds.amazonaws.com 端點的 Amazon RDS 資料庫執行個體使用 dta.exe 命令列公用程式。此範例包括主要使用者名稱 admin 和主要使用者密碼 test，要調整的範例資料庫命名為名為 C:\RDSTrace.trc 的機器。範例命令列程式碼也會指定名為 RDSTrace1 的追蹤工作階段，並將名為 RDSTrace.sql 之本機機器的輸出檔案表示為 SQL 輸出指令碼，將 RDSTrace.txt 表示為結果檔案，以及將 RDSTrace.xml 表示為分析的 XML 檔案。在名為 RDSTraceErrors 的 RDSDTA 資料庫上也指定了一個錯誤資料表。

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors 

這是相同的範例命令列程式碼，除了輸入工作負載是名為 RDSTrace (位於 RDSDTA 資料庫) 的遠端 Amazon RDS 執行個體上的資料表。

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

如需 dta 公用程式命令列參數的完整清單，請參閱 Microsoft 文件中的 dta 公用程式。