对专用 SQL 池的慢查询进行故障排除
适用于：Azure Synapse Analytics
本文可帮助你确定原因，并针对 Azure Synapse Analytics 专用 SQL 池上的查询的常见性能问题应用缓解措施。
按照步骤排查问题，或通过 Azure Data Studio 执行笔记本中的步骤。 前三个步骤将引导你完成收集遥测数据，其中描述了查询的生命周期。 本文末尾的参考资料有助于分析在收集的数据中找到的潜在机会。
注意
在尝试打开此笔记本之前，请确保在本地计算机上安装了 Azure Data Studio。 若要安装它，请转到 了解如何安装 Azure Data Studio。
步骤 1：确定request_id (（又称 QID) ）
request_id需要慢速查询的 ，才能研究查询速度缓慢的潜在原因。 使用以下脚本作为起点来标识要排除故障的查询。 确定慢速查询后，记下 request_id 该值。
-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [status] NOT IN ('Completed','Failed','Cancelled')
AND session_id <> session_id()
-- AND [label] = '<YourLabel>'
-- AND resource_allocation_percentage is not NULL
ORDER BY submit_time DESC;
-- Find top 10 longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;
若要更好地定位慢速查询，请在运行脚本时使用以下提示：
按
submit_time DESC或total_elapsed_time DESC排序，使运行时间最长的查询出现在结果集的顶部。OPTION(LABEL='<YourLabel>')在查询中使用 ，然后筛选label列以标识它们。当你知道目标语句包含在批处理中时，请考虑筛选掉任何没有值的
resource_allocation_percentageQID。注意： 请谨慎使用此筛选器，因为它也可能筛选出一些被其他会话阻止的查询。
步骤 2：确定查询花费时间的位置
运行以下脚本以查找可能导致查询性能问题的步骤。 使用下表中所述的值更新脚本中的变量。 将 @ShowActiveOnly 值更改为 0 以获取分布式计划的全貌。 记下 StepIndex从结果集中标识的慢速步骤的 、 Phase和 Description 值。
| 参数 | 说明 |
|---|---|
@QID |
步骤 request_id1 中获取的值 |
@ShowActiveOnly |
0 - 显示查询的所有步骤 1 - 仅显示当前活动步骤 |
DECLARE @QID VARCHAR(16) = '<request_id>', @ShowActiveOnly BIT = 1;
-- Retrieve session_id of QID
DECLARE @session_id VARCHAR(16) = (SELECT session_id FROM sys.dm_pdw_exec_requests WHERE request_id = @QID);
-- Blocked by Compilation or Resource Allocation (Concurrency)
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
'Blocked waiting on '
+ MAX(CASE WHEN waiting.type = 'CompilationConcurrencyResourceType' THEN 'Compilation Concurrency'
WHEN waiting.type LIKE 'Shared-%' THEN ''
ELSE 'Resource Allocation (Concurrency)' END)
+ MAX(CASE WHEN waiting.type LIKE 'Shared-%' THEN ' for ' + REPLACE(waiting.type, 'Shared-', '')
ELSE '' END) AS [Description],
MAX(waiting.request_time) AS [StartTime], GETDATE() AS [EndTime],
DATEDIFF(ms, MAX(waiting.request_time), GETDATE())/1000.0 AS [Duration],
NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount], NULL AS [TSQL]
FROM sys.dm_pdw_waits waiting
WHERE waiting.session_id = @session_id
AND ([type] LIKE 'Shared-%' OR
[type] in ('ConcurrencyResourceType', 'UserConcurrencyResourceType', 'CompilationConcurrencyResourceType'))
AND [state] = 'Queued'
GROUP BY session_id
-- Blocked by another query
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
'Blocked by ' + blocking.session_id + ':' + blocking.request_id + ' when requesting ' + waiting.type + ' on '
+ QUOTENAME(waiting.object_type) + waiting.object_name AS [Description],
waiting.request_time AS [StartTime], GETDATE() AS [EndTime],
DATEDIFF(ms, waiting.request_time, GETDATE())/1000.0 AS [Duration],
NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount],
COALESCE(blocking_exec_request.command, blocking_exec_request.command2) AS [TSQL]
FROM sys.dm_pdw_waits waiting
INNER JOIN sys.dm_pdw_waits blocking
ON waiting.object_type = blocking.object_type
AND waiting.object_name = blocking.object_name
INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request
ON blocking.request_id = blocking_exec_request.request_id
WHERE waiting.session_id = @session_id AND waiting.state = 'Queued'
AND blocking.state = 'Granted' AND waiting.type != 'Shared'
-- Request Steps
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, step_index AS [StepIndex],
'Execution' AS [Phase], operation_type + ' (' + location_type + ')' AS [Description],
start_time AS [StartTime], end_time AS [EndTime],
total_elapsed_time/1000.0 AS [Duration], [status] AS [Status],
CASE WHEN estimated_rows > -1 THEN estimated_rows END AS [EstimatedRowCount],
CASE WHEN row_count > -1 THEN row_count END AS [ActualRowCount],
command AS [TSQL]
FROM sys.dm_pdw_request_steps
WHERE request_id = @QID
AND [status] = CASE @ShowActiveOnly WHEN 1 THEN 'Running' ELSE [status] END
ORDER BY StepIndex;
步骤 3：查看步骤详细信息
运行以下脚本以查看上一步中标识的步骤的详细信息。 使用下表中所述的值更新脚本中的变量。 将 @ShowActiveOnly 值更改为 0 以比较所有分布计时。 记下 wait_type 可能导致性能问题的分布的值。
| 参数 | 说明 |
|---|---|
@QID |
步骤 request_id1 中获取的值 |
@StepIndex |
步骤 StepIndex2 中标识的值 |
@ShowActiveOnly |
0 - 显示给定 StepIndex 值的所有分布1 - 仅显示给定 StepIndex 值的当前活动分布 |
DECLARE @QID VARCHAR(16) = '<request_id>', @StepIndex INT = <StepIndex>, @ShowActiveOnly BIT = 1;
WITH dists
AS (SELECT request_id, step_index, 'sys.dm_pdw_sql_requests' AS source_dmv,
distribution_id, pdw_node_id, spid, 'NativeSQL' AS [type], [status],
start_time, end_time, total_elapsed_time, row_count
FROM sys.dm_pdw_sql_requests
WHERE request_id = @QID AND step_index = @StepIndex
UNION ALL
SELECT request_id, step_index, 'sys.dm_pdw_dms_workers' AS source_dmv,
distribution_id, pdw_node_id, sql_spid AS spid, [type],
[status], start_time, end_time, total_elapsed_time, rows_processed as row_count
FROM sys.dm_pdw_dms_workers
WHERE request_id = @QID AND step_index = @StepIndex
)
SELECT sr.step_index, sr.distribution_id, sr.pdw_node_id, sr.spid,
sr.type, sr.status, sr.start_time, sr.end_time,
sr.total_elapsed_time, sr.row_count, owt.wait_type, owt.wait_time
FROM dists sr
LEFT JOIN sys.dm_pdw_nodes_exec_requests owt
ON sr.pdw_node_id = owt.pdw_node_id
AND sr.spid = owt.session_id
AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests'
AND sr.status = 'Running') -- sys.dm_pdw_sql_requests status
OR (sr.source_dmv = 'sys.dm_pdw_dms_requests'
AND sr.status not LIKE 'Step[CE]%')) -- sys.dm_pdw_dms_workers final statuses
WHERE sr.request_id = @QID
AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests' AND sr.status =
CASE WHEN @ShowActiveOnly = 1 THEN 'Running' ELSE sr.status END)
OR (sr.source_dmv = 'sys.dm_pdw_dms_workers' AND sr.status NOT LIKE
CASE WHEN @ShowActiveOnly = 1 THEN 'Step[CE]%' ELSE '' END))
AND sr.step_index = @StepIndex
ORDER BY distribution_id
步骤 4：诊断和缓解
编译阶段问题
根据步骤
Description2 中获取的值，检查下表中的详细信息的相关部分。说明 常见原因 Compilation Concurrency已阻止：编译并发 Resource Allocation (Concurrency)已阻止：资源分配 如果查询处于步骤 1 中确定的“正在运行”状态，但步骤 2 中没有步骤信息，检查最适合你的方案的原因，以便从下表中获取更多信息。
应用场景 常见原因 语句包含复杂的联接筛选器逻辑或在 子句中 WHERE执行联接复杂查询或较旧的 JOIN 语法 语句是长时间运行的 DROP TABLE或TRUNCATE TABLE语句长时间运行的 DROP TABLE 或 TRUNCATE TABLE CCI 具有较高百分比的已删除或打开行 (请参阅 优化聚集列存储索引) 不正常的 CCI (通常) 分析 步骤 1 中的结果集，了解提交速度缓慢的查询后立即执行的一个或多个
CREATE STATISTICS语句。 从下表中检查最适合你的方案的原因。应用场景 常见原因 意外创建的统计信息 自动创建统计信息的延迟 5 分钟后统计信息创建失败 自动创建统计信息超时
已阻止：编译并发
并发编译块很少发生。 但是，如果遇到此类块，则表示在短时间内提交了大量查询，并且已排队开始编译。
缓解
减少并发提交的查询数。
已阻止：资源分配
被阻止进行资源分配意味着查询正在等待执行，具体取决于：
- 根据与用户关联的资源类或工作负荷组分配授予的内存量。
- 系统或工作负荷组上的可用内存量。
- (可选) 工作负荷组/分类器重要性。
缓解
复杂查询或较旧的 JOIN 语法
你可能会遇到这样的情况：默认查询优化器方法被证明是无效的，因为编译阶段需要很长时间。 如果查询：
- 涉及大量联接和/或子查询 (复杂查询) 。
- 利用子句中的
FROM联接器 (而不是 ANSI-92 样式联接) 。
尽管这些方案不典型，但可以选择尝试替代默认行为，以减少查询优化器选择计划所需的时间。
缓解
- 使用 ANSI-92 样式联接。
- 添加查询提示：
OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))。 有关详细信息，请参阅 FORCE ORDER 和基数估计 (SQL Server) 。 - 将查询分解为多个不太复杂的步骤。
长时间运行的 DROP TABLE 或 TRUNCATE TABLE
为了提高执行时间效率， DROP TABLE 和 TRUNCATE TABLE 语句会将存储清理延迟到后台进程。 但是，如果工作负荷在短时间内执行大量 DROP/TRUNCATE TABLE 语句，则元数据可能会变得拥挤，并导致后续 DROP/TRUNCATE TABLE 语句执行缓慢。
缓解
确定维护时段，停止所有工作负荷，并运行 DBCC SHRINKDATABASE 以强制立即清理以前删除或截断的表。
不正常的 CCI (通常)
(CCI) 运行状况不佳的聚集列存储索引需要额外的元数据，这可能导致查询优化器花费更多时间来确定最佳计划。 若要避免这种情况，请确保所有 CCI 都处于良好运行状态。
缓解
自动创建统计信息的延迟
默认情况下， AUTO_CREATE_STATISTICSON自动创建统计信息选项有助于确保查询优化器可以做出良好的分布式计划决策。 但是，自动创建过程本身会使初始查询花费的时间比后续执行的时间更长。
缓解
如果首次执行查询始终需要创建统计信息，则需要在执行查询之前 手动创建统计信息 。
自动创建统计信息超时
默认情况下， AUTO_CREATE_STATISTICSON自动创建统计信息选项有助于确保查询优化器可以做出良好的分布式计划决策。 统计信息的自动创建是响应 SELECT 语句的，并且需要 5 分钟的阈值才能完成。 如果数据大小和/或要创建的统计信息数需要超过 5 分钟的阈值，则会放弃统计信息的自动创建，以便查询可以继续执行。 创建统计信息失败可能会对查询优化器生成高效分布式执行计划的能力产生负面影响，从而导致查询性能不佳。
缓解
手动 创建统计信息， 而不是依赖于标识的表/列的自动创建功能。
执行阶段问题
使用下表分析 步骤 2 中的结果集。 确定方案并检查详细信息的常见原因以及可能的缓解步骤。
应用场景 常见原因 EstimatedRowCount/ActualRowCount< 25%估算不准确 值 Description指示BroadcastMoveOperation和查询引用复制的表。未缓存的复制表 1. @ShowActiveOnly= 0
2. 观察到 ()step_index步骤数过高或意外。
3. 联接器列的数据类型在表之间不相同。数据类型/大小不匹配 1. 该值 Description指示HadoopBroadcastOperation、HadoopRoundRobinOperation或HadoopShuffleOperation。
2.total_elapsed_time给定step_index的值在执行之间不一致。临时外部表查询 检查在
total_elapsed_time步骤 3 中获取的值。 如果在给定步骤的几个分发版中明显高于此值，请执行以下步骤：
未缓存的复制表
如果已创建复制表，并且无法正确预热复制的表缓存，则由于额外的数据移动或创建不理想的分布式计划，将导致意外性能不佳。
缓解
- 在 DML 操作后为复制的缓存预热。
- 如果存在频繁的 DML 操作，请将表的分布更改为
ROUND_ROBIN。
数据类型/大小不匹配
联接表时，请确保联接列的数据类型和大小匹配。 否则，这将导致不必要的数据移动，降低 CPU、IO 和网络流量到工作负载其余部分的可用性。
缓解
重新生成表以更正数据类型和大小不同的相关表列。
临时外部表查询
针对外部表的查询旨在将数据大容量加载到专用 SQL 池中。 由于外部因素（例如并发存储容器活动），针对外部表的临时查询可能会遭受不同的持续时间。
缓解
首先将数据加载到专用 SQL 池 ，然后查询加载的数据。
存储) (数据倾斜
数据倾斜意味着数据在分布中不均匀分布。 分布式计划的每个步骤都需要完成所有分发，然后再转到下一步。 数据倾斜时，无法实现处理资源（如 CPU 和 IO）的全部潜力，从而导致执行时间变慢。
缓解
查看分布式 表指南 ，以帮助你选择更合适的分布列。
未完成数据倾斜
实时数据倾斜是 存储) 问题 (数据倾斜的 变体。 但是，倾斜的不是磁盘上数据的分布。 特定筛选器或分组数据的分布式计划的性质会导致 ShuffleMoveOperation 类型操作。 此操作会生成一个倾斜的输出，供下游使用。
缓解
- 确保 统计信息已创建并处于最新状态。
- 将列的顺序
GROUP BY更改为具有较高基数列的前导。 - 如果联接涵盖多个列，则创建多列统计信息。
- 向查询添加查询提示
OPTION(FORCE_ORDER)。 - 重构查询。
等待类型问题
如果上述常见问题均不适用于查询， 则步骤 3 数据将有机会确定哪些等待类型 (， wait_type 并且 wait_time) 干扰运行时间最长的步骤的查询处理。 存在大量等待类型，并且由于类似的缓解措施，它们被分组到相关类别中。 按照以下步骤查找查询步骤的等待类别：
wait_type确定步骤 3 中花费的时间最多的 。- 在等待 类别映射表中找到等待 类型，并确定它包含的等待类别。
- 从以下列表中展开与等待类别相关的部分，以获取建议的缓解措施。
编译
按照以下步骤缓解编译类别的等待类型问题：
- 为有问题的查询中涉及的所有对象重新生成索引。
- 更新有问题查询中涉及的所有对象的统计信息。
- 再次测试有问题的查询以验证问题是否仍然存在。
如果问题仍然存在，则：
使用以下 项创建.sql 文件：
SET QUERY_DIAGNOSTICS ON; <Your_SQL>; SET QUERY_DIAGNOSTICS OFF;打开命令提示符窗口并运行以下命令：
sqlcmd −S <servername>.database.windows.net −d <databasename> −U <username> −G −I −i .\<sql_file_name>.sql −y0 −o .\<output_file_name>.txt在>文本编辑器中打开<output_file_name.txt。 找到分发级别执行计划并将其复制粘贴 (行（以
<ShowPlanXML>步骤 2 中确定的运行时间最长的步骤) 开头）粘贴到扩展名为 .sqlplan 的单独文本文件中。注意： 分布式计划的每个步骤通常都记录了 60 个分发级别执行计划。 确保正在准备和比较同一分布式计划步骤中的执行计划。
步骤 3 查询经常显示一些分布区，这些分布区花费的时间比其他分布时间长得多。 在 SQL Server Management Studio 中，将分发级别执行计划 (从) 长时间运行的分发创建的 .sqlplan 文件与快速运行的分发进行比较，以分析差异的潜在原因。
锁定、工作线程
- 请考虑更改经常发生小更改的表，以利用行存储索引而不是 CCI。
- 对更改进行批处理，并在频率较低的基础上使用更多行更新目标。
缓冲区 IO、其他磁盘 IO、Tran Log IO
不正常的 CCI
不正常的 CCI 会导致 IO、CPU 和内存分配增加，进而对查询性能产生负面影响。 若要缓解此问题，请尝试以下方法之一：
- 评估和更正专用 SQL 池中的聚集列存储索引运行状况。
- 运行并查看 优化聚集列存储索引 中列出的查询输出以获取基线。
- 按照步骤 重新生成索引 以提高段质量，针对示例问题查询中涉及的表。
过时的统计信息
过时的统计信息可能会导致生成未优化分布式计划，其中涉及的数据移动超出必要范围。 不必要的数据移动不仅会增加静态数据的工作负荷，还会增加 对 的 tempdb工作负荷。 由于 IO 是跨所有查询的共享资源，因此整个工作负荷都会感受到性能影响。
若要纠正这种情况，请确保所有 统计信息都是最新的，并且已制定维护计划，以便针对用户工作负载更新统计信息。
繁重的 IO 工作负载
整个工作负载可能正在读取大量数据。 Synapse 专用 SQL 池根据 DWU 缩放资源。 为了获得更好的性能，请考虑以下任一或两者：
CPU、并行度
| 应用场景 | 缓解 |
|---|---|
| 不良 CCI 运行状况 | 评估和更正专用 SQL 池中的聚集列存储索引运行状况 |
| 用户查询包含转换 | 将所有格式设置和其他转换逻辑移动到 ETL 进程，以便存储格式化版本 |
| 工作负载优先级不正确 | 实现 工作负载隔离 |
| 工作负荷的 DWU 不足 | 考虑 增加计算资源 |
网络 IO
如果在步骤 2 中的操作期间RETURN发生此问题，
- 减少并发并行进程的数量。
- 将受影响最大的进程横向扩展到另一个客户端。
对于所有其他数据移动操作，网络问题很可能是专用 SQL 池的内部问题。 若要尝试快速缓解此问题，请执行以下步骤：
- 将专用 SQL 池扩展到 DW100c
- 缩减到所需的 DWU 级别
SQL CLR
通过实现转换数据 (的替代方法（例如，CONVERT()使用样式) ），避免频繁使用 FORMAT() 函数。
反馈
即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
提交和查看相关反馈