练习 - 优化应用程序性能

10 分钟

在本练习中，你将观察到新的性能情况，并通过优化应用程序和查询来解决它。

使用 Azure SQL 优化应用程序性能

在某些情况下，将现有应用程序和 SQL 查询工作负载迁移到 Azure 可发现优化和调节查询的机会。

若要支持 AdventureWorks 订单网站的新扩展来提供客户的评级系统，你需要添加一个新表来处理大量并发 INSERT 活动。你在具有 SQL Server 2022 的开发计算机上测试了 SQL 查询工作负载，该计算机配备用于数据库和事务日志的本地 SSD 驱动器。

使用常规用途层级（8 个 vCore）将测试移到 Azure SQL 数据库时，INSERT 工作负载速度变慢。应更改服务目标或服务层级来支持新的工作负载，还是该查看应用程序？

可以在克隆的 GitHub 存储库或下载的 zip 文件的 04-Performance\tuning_applications 文件夹中找到本练习的所有脚本。

为应用程序创建新表

在对象资源管理器中，选择 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 工作负载脚本。

将 -S parameter 替换为你在第一个练习中为服务器名称提供的 unique_id。
将 -P parameter 替换为第一个练习中在数据库部署中提供的密码。
保存对文件所做的更改。

运行工作负载

在 PowerShell 命令提示符下，更改为本模块活动的目录：
```
cd c:<base directory>\04-Performance\tuning_applications
```
使用以下命令运行工作负载：
```
.\order_rating_insert_single.cmd
```
此脚本使用 ostress.exe 程序运行 25 名并发用户，他们运行以下 T-SQL 语句（在脚本 order_rating_insert_single.sql 中）：
```
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 总是为 WRITELOG，且值 > 0。
WRITELOG 等待类型是计数最高的等待类型之一。
写入事务日志的平均时间（tlog_io.sql 结果集中的 avg_tlog_io_write_ms 列）大约为 2 毫秒。

此工作负载在具有 SSD 驱动器的 SQL Server 2022 实例上的持续时间大约为 10-12 秒。使用 Gen5 v8 核心的 Azure SQL 数据库上的总持续时间大约为 25 秒。

等待时间较长的 WRITELOG 等待类型表示刷新到事务日志时存在延迟。每次写入需 2 毫秒的等待时间似乎并不长，但是在本地 SSD 驱动器上，等待时间可能少于 1 毫秒。

确定解决方法

问题不在于日志写入活动的百分比较高。 Microsoft Azure 门户和 sys.dm_db_resource_stats 未显示任何高于 20-25% 的数字（无需查询这些数字）。问题也不在于 IOPS 限制。问题是，此应用程序工作负载对于事务日志写入的低延迟十分敏感，而常规用途层级并不是专为满足此类型的延迟要求。 Azure SQL 数据库的预期 I/O 延迟为 5-7 毫秒。

注意

常规用途 Azure SQL 数据库将近似 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 批处理，从而在 INSERT 迭代中包装 BEGIN TRAN/COMMIT TRAN。

运行修改后的更高效的工作负载

编辑脚本并运行它们来获得更高效的 I/O 性能。可以在 order_rating_insert.sql 脚本中找到修改后的工作负载。

编辑 order_rating_insert.cmd 来准备工作负载脚本，以使用正确的服务器名称和密码。
使用 order_rating_insert.cmd 脚本运行修改后的工作负载（与运行前一个工作负载脚本的方式类似）。

观察新结果

在 SSMS 中查看用于 sqlrequests.sql 的 T-SQL 脚本的结果。请注意，WRITELOG 等待要少得多，这些等待的总等待时间更短。

与以前的执行相比，工作负载的运行速度现在要快得多。该示例显示了针对将在 Azure 内部或外部运行的 SQL 查询优化应用程序。

注意

对于连接类型为“重定向”的 Azure SQL 数据库实例，此工作负载的运行速度甚至可以更快。在本练习中完成的部署使用默认连接类型（即代理类型），因为你已在 Azure 外部连接。考虑到从客户端到服务器的往返，使用重定向可显著提高此类工作负载的速度。
观察工作负载持续时间。工作负载运行很快，因此可能很难观察此活动中之前使用的查询的诊断数据。

“批处理”的概念可帮助大多数应用程序，包括连接到 Azure SQL 的应用。

提示

Azure 上的资源治理可能会影响非常大的事务，症状将为 LOG_RATE_GOVERNOR。在本例中，char(500) 非 NULL 列会填充空格并导致大型事务日志记录。你甚至可通过将该列设为可变长度列来进一步优化性能。

在下一个单元中，你将了解 Azure SQL 中的智能性能。