練習 - 最佳化應用程式效能

10 分鐘

在本練習中，您將進行觀察新效能案例，並最佳化應用程式及查詢來解決此問題。

使用 Azure SQL 最佳化應用程式效能

在某些情況下，將現有應用程式及 SQL 查詢工作負載移轉至 Azure 可能會發現最佳化與調整查詢的機會。

若要為 AdventureWorks 訂單支援新的網站延伸模組，以提供客戶的評等系統，則需要針對一大組並行的 INSERT 活動新增新資料表。您已在具有 SQL Server 2022 (使用 SSD 磁碟放置資料庫和交易記錄) 的開發電腦上測試 SQL 查詢工作負載。

當使用一般用途層 (8 個虛擬核心) 將測試移至 Azure SQL Database 時，INSERT 工作負載會變慢。您是否該變更服務目標或層級，以支援新的工作負載或查看應用程式？

此練習中的所有指令碼都可在您複製的 GitHub 存放庫中的 04-Performance\tuning_applications 資料夾，或您下載的 ZIP 檔案中找到。

為應用程式建立新的資料表

在 [物件總管] 中，選取 [AdventureWorks] 資料庫。使用 [檔案] > [開啟] > [檔案] 來開啟 order_rating_ddl.sql 指令碼，以在 AdventureWorks 資料庫中建立資料表。您的 [查詢編輯器] 視窗看起來應類似下列文字：

DROP TABLE IF EXISTS SalesLT.OrderRating;
GO
CREATE TABLE SalesLT.OrderRating
(OrderRatingID int identity not null,
SalesOrderID int not null,
OrderRatingDT datetime not null,
OrderRating int not null,
OrderRatingComments char(500) not null);
GO

選取 [執行] 以執行指令碼。

載入查詢以監視查詢執行

現在讓我們載入一些動態管理檢視 (DMV) 的 T-SQL 查詢，以觀察作用中查詢、等候及 I/O 的查詢效能。將這些所有查詢載入 AdventureWorks 資料庫的內容中。

在 [物件總管] 中，選取 [AdventureWorks] 資料庫。使用 [檔案] > [開啟] > [檔案] 來開啟 sqlrequests.sql 指令碼，以查看作用中的 SQL 查詢。您的 [查詢編輯器] 視窗看起來應類似下列文字：

SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
ON er.session_id = es.session_id
AND es.is_user_process = 1;

在 [物件總管] 中，選取 [AdventureWorks] 資料庫。使用 [檔案] > [開啟] > [檔案] 來開啟 top_waits.sql 指令碼，以依計數查看排名最前面的等候類型。您的 [查詢編輯器] 視窗看起來應類似下列文字：
```
SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;
```
在 [物件總管] 中，選取 [AdventureWorks] 資料庫。使用 [檔案] > [開啟] > [檔案] 來開啟 tlog_io.sql 指令碼，以觀察交易記錄寫入的延遲。您的 [查詢編輯器] 視窗看起來應類似下列文字：
```
SELECT io_stall_write_ms/num_of_writes as avg_tlog_io_write_ms, * 
FROM sys.dm_io_virtual_file_stats
(db_id('AdventureWorks'), 2);
```

準備用於執行的工作負載指令碼

開啟並編輯工作負載指令碼 order_rating_insert_single.cmd。

以您在第一個練習中取得的伺服器名稱 unique_id 取代 -S parameter。
以您在第一個練習中進行資料庫部署時提供的密碼取代 -P parameter。
儲存對檔案所做的變更。

執行工作負載

從 PowerShell 命令提示字元中，變更為此模組活動的目錄：
```
cd c:<base directory>\04-Performance\tuning_applications
```
使用下列命令執行工作負載：
```
.\order_rating_insert_single.cmd
```
此指令碼會使用 ostress.exe 程式，以透過執行下列 T-SQL 陳述式 (在 order_rating_insert_single.sql 指令碼中) 來執行 25 位並行使用者：
```
DECLARE @x int;
SET @x = 0;
WHILE (@x < 500)
BEGIN
SET @x = @x + 1;
INSERT INTO SalesLT.OrderRating
(SalesOrderID, OrderRatingDT, OrderRating, OrderRatingComments)
VALUES (@x, getdate(), 5, 'This was a great order');
END
```
您可從此指令碼看出，其並不是來自網站的實際資料描繪。但其確實會模擬內嵌至資料庫的多個訂單評等。

觀察 DMV 和工作負載效能

現在，在先前載入的 SQL Server Management Studio (SSMS) 中執行查詢，以觀察效能。執行 sqlrequests.sql、top_waits.sql 及 tlog_io.sql 的查詢。

使用這些查詢，即可觀察下列事實：

許多要求的 wait_type 經常是值 > 0 的 WRITELOG。
WRITELOG 等候類型是其中一個計數最高的等候類型。
寫入交易記錄 (tlog_io.sql 結果集中的 avg_tlog_io_write_ms 資料行) 的平均時間大約是 2 毫秒。

在具有 SSD 磁碟機的 SQL Server 2022 執行個體上，此工作負載的持續時間大約會在 10-12 秒左右。在使用 Gen5 v8 核心的 Azure SQL Database 上，總持續時間大約為 25 秒。

等候時間較長的 WRITELOG 等候類型表示排清交易記錄時有延遲。每次寫入耗費 2 毫秒的等候時間看起來似乎不多，但在本機 SSD 磁碟機上，這些等候時間可低於 1 毫秒。

決定解決方式

問題並不在於記錄寫入活動的高百分比。 Azure 入口網站及 sys.dm_db_resource_stats 不會顯示高於 20-25% 的任何數字 (您不需對其進行查詢)。問題也不是 IOPS 限制。問題在於，此應用程式工作負載對交易記錄寫入的低延遲很敏感，而一般用途層則不是針對此類型的延遲需求所設計。 Azure SQL Database 的預期 I/O 延遲為 5-7 毫秒。

注意

一般用途 Azure SQL Database 文件的 I/O 延遲平均為 5-7 (寫入) 及 5-10 (讀取)。因此您可能會遇到更接近這些數字的延遲。一般用途 Azure SQL 受控執行個體的延遲也很類似。如果應用程式對 I/O 延遲非常敏感，請考慮業務關鍵層。

檢查工作負載 T-SQL 指令碼 order_rating_insert_single.sql。每個 INSERT 都是單一交易認可，其需要進行交易記錄排清。

每個插入只有一次認可並不夠，但應用程式不會在本機 SSD 上受到影響，因為每次的認可都非常快速。業務關鍵定價層 (服務目標或 SKU) 提供延遲較低的本機 SSD 磁碟機。您可以最佳化應用程式，使工作負載不會對交易記錄檔的 I/O 延遲過於敏感。

您可變更工作負載的 T-SQL 批次，以 BEGIN TRAN/COMMIT TRAN 包裝 INSERT 反覆項目。

執行修改得更有效率的工作負載

編輯指令碼並加以執行，以查看更有效率的 I/O 效能。您可以在 order_rating_insert.sql 指令碼中找到修改過的工作負載。

編輯 order_rating_insert.cmd 來準備工作負載指令碼，以使用正確的伺服器名稱和密碼。
使用 order_rating_insert.cmd 指令碼執行修改過的工作負載，如同執行前一個工作負載指令碼的方式。

觀察新的結果

在 SSMS 中查看 sqlrequests.sql 的 T-SQL 指令碼結果。請注意，WRITELOG 等待的時間明顯更少，且整體等候時間較其他工作負載少。

相較於先前的執行，現在工作負載的執行速度變得更快。這是調整應用程式以在 Azure 內部或外部執行 SQL 查詢的範例。

注意

針對重新導向連線類型的 Azure SQL Database 執行個體，此工作負載也可執行得更快。您在本練習中已完成的部署會使用預設連線類型，因為您是在 Azure 外部連線，所以其會是 Proxy 類型。考慮到從用戶端到伺服器的連線需要來回行程，使用「重新導向」可大幅加快工作負載的速度。
觀察工作負載持續時間。工作負載的執行速度很快，可能難以從先前在此活動中使用的查詢中觀察診斷資料。

「批次」的概念對大部分應用程式都有用，包括連線至 Azure SQL 的應用程式。

提示

Azure 上的資源控管可能會影響非常大型的交易，且徵兆會是 LOG_RATE_GOVERNOR。在此範例中，char(500) 非 Null 資料行會填補空格，並產生大型交易記錄的記錄。您可將該資料行設為長度可變的資料行，以進一步最佳化效能。

在下一個單元中，您將了解 Azure SQL 中的智慧效能。