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

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 類型是等候類型的最高計數之一。
寫入交易記錄的平均時間（ avg_tlog_io_write_ms tlog_io.sql 結果集中的資料行 ）大約在 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 資料庫的預期 I/O 延遲為 5-7 毫秒。

注意

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

檢查 order_rating_insert_single.sql 工作負載 T-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 資料庫實例執行。您在此練習中完成的部署會使用預設連線類型，這是 Proxy 類型，因為您是在 Azure 外部連線。考慮到從用戶端到伺服器的連線需要來回行程，使用「重新導向」可大幅加快工作負載的速度。
觀察工作負載持續時間。工作負載的執行速度很快，可能難以從先前在此活動中使用的查詢中觀察診斷資料。

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

提示

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

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