25、Azure SQL 数据库监控与调试指南

Azure SQL 数据库监控与调试指南

1. 监控与调试的重要性

Azure SQL 及其底层的 SQL Server 查询引擎以其低管理门槛而闻名，能让应用程序在不同数据规模和形态下，多数情况下都能实现良好的性能和可靠性。不过，对于应用开发者而言，理解 Azure SQL 查询处理的原理和内部机制，以及掌握开发和生产阶段用于监控和排查性能问题的工具与功能，是十分重要的。通常有两个主要场景需要我们掌握这些能力：
1. 了解系统在特定工作负载下的表现 ：在负载测试或生产期间，查看主要的性能和可用性指标，确保系统正常运行并能应对特定工作负载。
2. 深入调查特定活动 ：若上述步骤的结果显示出一些关键问题，就需要深入调查可能发生或已经发生的特定活动。

第一个场景是一个持续、长期的监控过程，对于确保解决方案的稳定运行至关重要。Azure 平台上有一个通用的基础架构 Azure Monitor，可收集给定解决方案所有组件发出的诊断信息。所有服务都可配置为异步向 Azure Monitor 发送日志和指标，Azure Monitor 会根据需求将其导向近实时（通过 Azure 门户可视化指标或推送到 Azure Event Hub 进行进一步处理和警报）和长期分析（如 Azure Blob 存储或日志分析）选项。更多详细信息可查看官方文档： https://aka.ms/asdbmto 。

第二个场景则与即时诊断和故障排除有关。Azure SQL 引擎具备广泛的检测功能，在特定问题发生时，开发者可利用服务各内部子系统发出的丰富诊断信息，了解单个查询的执行情况，以及内存管理或资源治理等子系统的运行状况。

2. 动态管理视图（DMVs）

动态管理视图（和函数）可用于对 Azure SQL 端点执行 T - SQL 查询，并返回用于监控 Azure SQL 服务器或数据库实例健康状况、诊断问题和调整性能的状态信息。SQL Server 引擎高度可检测，会捕获从与底层操作系统和硬件集成到查询执行等各子系统的大量信息。这些细节存储在数据库进程空间的内存结构中，可通过系统视图和函数层，使用具有相应权限的登录连接执行常规 T - SQL 命令进行查询。例如，可通过以下命令授予对给定实例的访问权限：

GRANT VIEW DATABASE STATE TO database_user;

Azure SQL 启用了一部分动态管理视图来诊断性能问题，这些问题可能由阻塞或长时间运行的查询、资源瓶颈、糟糕的查询计划等引起。在 SQL Server 实例和 Azure SQL 托管实例中，动态管理视图返回服务器状态信息；在 Azure SQL 数据库中，仅返回当前逻辑数据库的信息。

虽然有数百个这样的视图和函数，但主要可分为以下三类：
- 数据库相关的动态管理视图 （以 sys.dm_db_* 为前缀）
- 执行相关的动态管理视图 （以 sys.dm_exec_* 为前缀）
- 事务相关的动态管理视图 （以 sys.dm_tran_* 为前缀）

2.1 基本使用案例

2.1.1 查看当前资源利用率

要了解运行应用程序的实例的当前资源利用率，可打开首选的客户端工具（如 Azure Data Studio 或 SQL Server Management Studio），连接到数据库并执行以下查询：

SELECT * FROM sys.dm_db_resource_stats ORDER BY end_time DESC;

此视图将返回过去一小时内数据库实例资源消耗的关键指标，粒度为 15 秒。若任何指标接近 100%，可选择升级数据库服务或计算层级，或者更深入地分析资源利用率，以提高工作负载效率。

2.1.2 查找高 CPU 消耗查询

假设 CPU 利用率达到最大值，下一步可查看哪些查询消耗的 CPU 最多，以确定是否有改进的方法。为演示目的，先清除测试实例的过程缓存，避免受到过去运行的大量查询的干扰，然后执行一个简单查询：

--!!! Test purposes only, don’t do it in production!!!--
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
-- Execute a simple query
SELECT * FROM [Sales].[Orders] WHERE CustomerID=832

接着，在同一连接或不同连接上，运行以下针对 sys.dm_exec_* DMVs 的诊断查询：

--- Returning TOP 25 CPU consuming queries for this database
SELECT TOP 25
     qs.query_hash,
     qs.execution_count,
     REPLACE(REPLACE(LEFT(st.[text], 512), CHAR(10),''), CHAR(13),'') AS query_text,
     qs.total_worker_time,
     qs.min_worker_time,
     qs.total_worker_time/qs.execution_count AS avg_worker_time,
     qs.max_worker_time,
     qs.min_elapsed_time,
     qs.total_elapsed_time/qs.execution_count AS avg_elapsed_time,
     qs.max_elapsed_time,
     qs.min_logical_reads,
     qs.total_logical_reads/qs.execution_count AS avg_logical_reads,
     qs.max_logical_reads,
     qs.min_logical_writes,
     qs.total_logical_writes/qs.execution_count AS avg_logical_writes,
     qs.max_logical_writes,
     CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS missing_index,
     qs.creation_time,
     qp.query_plan,
     qs.*
FROM
     sys.dm_exec_query_stats AS qs
CROSS APPLY
     sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY
     sys.dm_exec_query_plan(plan_handle) AS qp
WHERE
     st.[dbid]=db_id() and st.[text] NOT LIKE '%sys%'
ORDER BY
     qs.total_worker_time DESC
OPTION (RECOMPILE);

该查询将返回消耗 CPU 时间最多的前 25 个查询的相关信息，包括查询哈希、执行次数、查询文本、CPU 时间、逻辑读写等指标。通过更改排序列，可查看执行频率高或读写数据页多的查询。

3. 执行计划

在前面的示例中，通过 DMVs 检索的列中有一个 XML 片段，它返回了查询执行计划。在 SQL Server Management Studio 中点击该列，会打开一个新窗口，显示查询执行计划的图形表示，展示了 Azure SQL 引擎为生成返回给客户端的结果所采取的详细步骤。

Azure SQL 有一个内存池，用于存储执行计划和数据缓冲区。执行 Transact - SQL 语句时，数据库引擎会先检查计划缓存，若存在完全匹配的执行计划则重用，避免重新编译的开销；若不存在，则会在合理时间内尝试生成资源利用率最低的新执行计划。

理解执行计划的创建和执行过程，对于确保应用程序生成的工作负载与数据模型和索引策略相匹配，或进行必要的优化至关重要。许多现代数据库使用更专业的术语“有向无环图（DAG）”来代替“执行计划”，例如在 Apache Spark 中，DAG 和执行计划基本是同一概念。

执行计划在 Management Studio 中的步骤通常应从右向左、从上到下解释执行顺序。例如，在之前的查询中，第一步是使用 Orders 表 CustomerID 列上的非聚集索引进行查找操作，找到 CustomerID 为 832 的行。由于该索引可能不包含选择列表中的所有列，下一步会对第一步检索到的每一行执行查找操作，使用聚集索引键从聚集索引中读取其他列。

将鼠标悬停在某个操作符上，可获取该步骤的详细信息，如执行模式（行或批处理）、对象的底层存储类型（行存储与列存储）以及相关的 CPU 和 IO 成本。例如，可发现 Key Lookup 操作符占整个查询成本的 99%，且执行了 127 次。

作为开发者，可通过只选择真正需要的列，并将这些列添加到第一个操作符使用的非聚集索引中，来提高查询效率。这样可消除昂贵的 Key Lookup 操作符，降低整体查询处理成本。优化后的查询计划不再包含 Key Lookup 操作符，整体子树成本降至原来的 1/100，执行时间不到原查询的一半，逻辑页读取从 369 降至 5，减少了存储读取的 IO 操作和缓冲区池的空间占用。

在客户端工具（如 SQL Server Management Studio 或 Azure Data Studio）中运行查询时，可通过选择“包含实际执行计划”工具栏按钮（在 Management Studio 中使用 Ctrl + M 快捷键，或在 Data Studio 中点击“Explain”按钮）获取执行计划信息。更多关于 Azure SQL 引擎创建和执行查询计划的详细信息，可查看官方文档： https://aka.ms/qpag 。

4. 查询存储

之前提到，代表 Azure SQL 数据库状态并为动态管理视图和函数提供数据的所有内部数据结构都存储在数据库进程内存中，因此实例重启或发生计划内/外故障转移时，所有诊断信息都会丢失。自 SQL Server 2016 引入查询存储功能后，Azure SQL 默认可在重启后保留大部分诊断信息，因为整个云数据库群集都启用了查询存储。

查询存储功能可帮助开发者了解查询计划的选择和性能，简化性能故障排除过程，能快速找出因查询计划更改导致的性能差异。它会自动捕获查询、计划和运行时统计信息的历史记录，并按时间窗口分隔数据，让开发者查看数据库使用模式，了解服务器上查询计划何时发生变化。查询存储就像一个飞行数据记录器，持续收集与查询和计划相关的编译和运行时信息，相关数据存储在内部表中，并通过一组视图呈现给用户。

查询存储主要包含三个部分：
1. 计划存储 ：包含执行计划的详细信息。
2. 运行时统计信息存储 ：存储执行统计信息。
3. 等待统计信息存储 ：包含等待统计的历史数据。

Azure SQL 中特定查询的执行计划可能会因统计信息、架构、索引等变化而改变。过程缓存只存储最新的执行计划，在内存压力下计划可能会被逐出。若新生成的执行计划不理想，很难确定原因。查询存储可保留同一查询的多个执行计划版本，帮助找出问题所在，还可强制查询处理器使用特定执行计划，即“计划强制”，无需更改应用程序中的查询语法。

查询存储会收集 DML 语句（如 SELECT、INSERT、UPDATE、DELETE、MERGE 和 BULK INSERT）的计划。在性能故障排除过程中，系统等待状态的统计信息也是重要的信息来源。在查询存储出现之前，等待统计信息通常只能在数据库实例级别获取，且难以关联到特定查询。

4.1 查询存储的数据收集过程

查询首次编译时，查询文本和初始计划会发送到查询存储，若查询重新编译则会更新。创建新计划时，会作为新条目添加到查询中，并保留之前的计划及其运行时执行统计信息。每次查询执行时，运行时统计信息会发送到查询存储，并在当前活动时间间隔内按计划级别聚合。

在编译和检查重新编译阶段，Azure SQL 会检测查询存储中是否有应应用于当前运行查询的计划，若强制计划与缓存中的计划不同，则会重新编译查询，这对用户应用程序是完全透明的。

4.2 查询存储的配置与视图

查询存储在 Azure SQL 中默认启用且不可关闭。默认配置针对连续数据收集进行了优化，但开发者仍可控制一些配置选项，如最大存储大小（默认 100MB）或用于聚合查询执行统计信息的时间间隔（默认 60 分钟）。若无特殊需求，建议大多数情况下使用默认设置。

查询存储的内部信息通过一系列视图呈现，可用于诊断查询，了解 Azure SQL 在特定工作负载下的行为。以下是一些主要视图及其用途：
| 视图名称 | 用途 |
| — | — |
| sys.query_store_query_text | 报告针对数据库执行的唯一查询文本，批处理中的每个语句都会生成一个单独的查询文本条目。 |
| sys.query_context_settings | 展示查询执行时影响计划的设置的唯一组合。 |
| sys.query_store_query | 显示在查询存储中单独跟踪和强制的查询条目。 |
| sys.query_store_plan | 返回带有编译时统计信息的查询估计计划，存储的计划等同于使用 SET SHOWPLAN_XML ON 获得的计划。 |
| sys.query_store_runtime_stats_interval | 显示在自动生成的时间窗口（间隔）内聚合的执行计划的运行时统计信息，可使用 ALTER DATABASE SET 语句中的 INTERVAL_LENGTH_MINUTES 控制间隔大小。 |
| sys.query_store_runtime_stats | 报告执行计划的聚合运行时统计信息，捕获的指标以四种统计函数形式呈现：平均值、最小值、最大值和标准差。 |

以下是一些查询示例：
- 返回数据库中最后执行的十个查询 ：

SELECT TOP 10 qt.query_sql_text, q.query_id,
    qt.query_text_id, p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;

• 返回过去一小时内执行时间最长的查询 ：

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time >= DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

通过使用这些视图和查询，开发者可快速获取工作负载执行的详细信息，从而进行性能优化和故障排除。

5. 监控与调试的综合应用

在实际开发和运维过程中，需要综合运用上述介绍的监控和调试工具，以确保 Azure SQL 数据库的性能和稳定性。以下是一个综合应用的示例流程：

5.1 持续监控

• 使用 Azure Monitor ：配置 Azure SQL 服务将日志和指标异步发送到 Azure Monitor。通过 Azure 门户可视化指标，设置关键指标的阈值，当指标超过阈值时触发警报。同时，可将数据推送到 Azure Event Hub 进行进一步处理和深入分析。
• 定期查看 DMVs ：定期执行查询查看数据库资源利用率，如 sys.dm_db_resource_stats 。若发现资源利用率接近 100%，则进一步分析具体的资源瓶颈。

5.2 性能问题排查

• 查找高消耗查询 ：当发现性能问题时，使用 DMVs 查找消耗 CPU 时间、逻辑读写等资源较多的查询。例如，执行查询找出前 25 个高 CPU 消耗的查询。

--- Returning TOP 25 CPU consuming queries for this database
SELECT TOP 25
     qs.query_hash,
     qs.execution_count,
     REPLACE(REPLACE(LEFT(st.[text], 512), CHAR(10),''), CHAR(13),'') AS query_text,
     qs.total_worker_time,
     qs.min_worker_time,
     qs.total_worker_time/qs.execution_count AS avg_worker_time,
     qs.max_worker_time,
     qs.min_elapsed_time,
     qs.total_elapsed_time/qs.execution_count AS avg_elapsed_time,
     qs.max_elapsed_time,
     qs.min_logical_reads,
     qs.total_logical_reads/qs.execution_count AS avg_logical_reads,
     qs.max_logical_reads,
     qs.min_logical_writes,
     qs.total_logical_writes/qs.execution_count AS avg_logical_writes,
     qs.max_logical_writes,
     CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS missing_index,
     qs.creation_time,
     qp.query_plan,
     qs.*
FROM
     sys.dm_exec_query_stats AS qs
CROSS APPLY
     sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY
     sys.dm_exec_query_plan(plan_handle) AS qp
WHERE
     st.[dbid]=db_id() and st.[text] NOT LIKE '%sys%'
ORDER BY
     qs.total_worker_time DESC
OPTION (RECOMPILE);

• 分析执行计划 ：获取高消耗查询的执行计划，通过 SQL Server Management Studio 或 Azure Data Studio 查看图形化的执行计划。分析执行计划中的操作符和成本，找出可能的性能瓶颈。例如，若发现 Key Lookup 操作符成本较高，可考虑优化查询和索引。

5.3 优化与调整

• 查询优化 ：根据执行计划的分析结果，对查询进行优化。如只选择需要的列，避免使用 SELECT * ；调整查询条件和排序规则等。
• 索引优化 ：若执行计划显示缺少索引或索引使用不当，可添加或修改索引。例如，将查询中常用的列添加到非聚集索引中，减少 Key Lookup 操作。
• 使用查询存储进行计划强制 ：若发现查询计划不稳定或性能下降，可使用查询存储的计划强制功能，指定使用性能较好的执行计划。

5.4 验证与持续改进

• 验证优化效果 ：在实施优化措施后，再次执行查询并查看性能指标，验证优化效果。可对比优化前后的执行时间、逻辑读写等指标。
• 持续监控与改进 ：持续监控数据库性能，根据监控结果不断调整和优化查询和索引，确保数据库始终保持良好的性能。

6. 注意事项与最佳实践

6.1 注意事项

• DMV 权限 ：执行 DMVs 查询需要相应的权限，如 VIEW DATABASE STATE 或 VIEW SERVER STATE 。确保用户具有执行查询的权限。
• 计划缓存清理 ：在生产环境中，不要随意清理计划缓存，因为这可能会导致查询重新编译，影响性能。只有在测试环境中为了模拟新的查询执行情况时才进行清理。

--!!! Test purposes only, don’t do it in production!!!--
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

• 查询存储配置 ：虽然查询存储默认配置已针对连续数据收集进行了优化，但在某些特殊情况下，可能需要调整最大存储大小和统计信息聚合时间间隔。调整时要谨慎，避免影响数据收集和分析的准确性。

6.2 最佳实践

• 定期备份 ：定期备份数据库，以防止数据丢失。可使用 Azure SQL 提供的备份功能，设置合理的备份策略。
• 使用参数化查询 ：在编写查询时，尽量使用参数化查询，避免 SQL 注入攻击，同时提高查询的可重用性和性能。
• 监控与日志记录 ：建立完善的监控和日志记录机制，及时发现和解决性能问题。定期审查监控数据和日志，总结经验教训，不断优化数据库性能。

7. 总结

通过合理使用 Azure SQL 提供的监控和调试工具，如动态管理视图（DMVs）、执行计划和查询存储，开发者可以深入了解数据库的运行状态，找出性能瓶颈，并进行有效的优化和调整。在实际应用中，需要综合运用这些工具，建立持续监控和优化的机制，以确保 Azure SQL 数据库的高性能和稳定性。同时，遵循注意事项和最佳实践，避免因不当操作导致的性能问题和安全风险。

以下是一个简单的 mermaid 流程图，展示了监控与调试的综合应用流程：

graph LR
    A[持续监控] --> B[性能问题排查]
    B --> C[优化与调整]
    C --> D[验证与持续改进]
    D --> A

通过这个流程图，可以清晰地看到监控与调试是一个循环的过程，需要不断地进行监控、排查、优化和验证，以确保数据库的性能始终处于良好状态。