排查专用 SQL 池查询速度缓慢的问题

项目
11/20/2024

适用对象：Azure Synapse Analytics

本文可帮助你识别 Azure Synapse Analytics 专用 SQL 池上查询的常见性能问题的原因并应用缓解措施。

按照步骤排查问题，或通过 Azure Data Studio 执行笔记本中的步骤。前三个步骤将指导你收集遥测数据，其中描述了查询的生命周期。本文末尾的参考有助于分析收集的数据中的潜在机会。

注意

尝试打开此笔记本之前，请确保在本地计算机上安装 Azure Data Studio。若要安装它，请转到了解如何安装 Azure Data Studio。

在 Azure Data Studio 中打开笔记本

重要

大多数报告的性能问题都是由以下原因引起的：

过时的统计信息
不正常的聚集列存储索引（CCIs）

若要节省故障排除时间，请确保已创建统计信息并重新生成最新和 CCIS。

步骤 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_percentage QID。

注意： 请谨慎使用此筛选器，因为它也可能筛选掉其他会话阻止的某些查询。

步骤 2：确定查询花费时间的位置

运行以下脚本以查找可能导致查询性能问题的步骤。使用下表中所述的值更新脚本中的变量。将 @ShowActiveOnly 值更改为 0，以获取分布式计划的全貌。记下StepIndexPhase结果集中标识的慢步和Description值。

参数	说明
`@QID`	`request_id`步骤 1 中获取的值
`@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_id`步骤 1 中获取的值
`@StepIndex`	`StepIndex`步骤 2 中标识的值
`@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：诊断和缓解

编译阶段问题

根据Description步骤 2 中获取的值，请查看下表中有关部分的详细信息。

说明常见原因

Compilation Concurrency 已阻止：编译并发

Resource Allocation (Concurrency) 已阻止：资源分配

说明	常见原因
`Compilation Concurrency`	已阻止：编译并发
`Resource Allocation (Concurrency)`	已阻止：资源分配

如果查询处于步骤 1 中标识的“正在运行”状态，但步骤 2 中没有步骤信息，请检查最适合你的方案的原因，以获取下表中的详细信息。

场景	常见原因
语句包含复杂的联接筛选器逻辑，或在子句中 `WHERE` 执行联接	复杂的查询或较旧的 JOIN 语法
语句是长时间运行的 `DROP TABLE` 或 `TRUNCATE TABLE` 语句	长时间运行的 DROP TABLE 或 TRUNCATE TABLE
CCIS 的删除或打开行百分比较高（请参阅优化聚集列存储索引）	不正常的 CCIS （通常）

分析步骤 1 中的结果集，了解在查询提交缓慢后立即执行的一个或多个CREATE STATISTICS语句。从下表中检查最适合方案的原因。

场景常见原因

意外创建的统计信息自动创建统计信息的延迟

统计信息创建在 5 分钟后失败自动创建统计信息超时

场景	常见原因
意外创建的统计信息	自动创建统计信息的延迟
统计信息创建在 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 以强制立即清理以前删除或截断的表。

不正常的 CCIS （通常）

聚集列存储索引（CCI）运行状况不佳需要额外的元数据，这可能导致查询优化器花费更多时间来确定最佳计划。为了避免这种情况，请确保所有 CCIS 都处于良好状态。

缓解措施

在专用 SQL 池中评估和更正聚集列存储索引运行状况。

自动创建统计信息的延迟

默认情况下， AUTO_CREATE_STATISTICS ON自动创建统计信息选项有助于确保查询优化器能够做出良好的分布式计划决策。但是，自动创建过程本身可以使初始查询花费的时间比执行相同的后续执行时间长。

缓解措施

如果首次执行查询需要创建统计信息，则需要在执行查询之前手动创建统计信息。

自动创建统计信息超时

默认情况下， AUTO_CREATE_STATISTICS ON自动创建统计信息选项有助于确保查询优化器能够做出良好的分布式计划决策。统计信息的自动创建是在响应 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 中获取的值。如果在给定步骤的几个分发版中明显更高，请执行以下步骤：
1. 针对每个表运行以下命令，检查字段中引用 TSQL 的每个表的数据分布是否关联 step_id ：
```
DBCC PDW_SHOWSPACEUSED(<table>);
```
2. 如果<最小行值>/<最大行值>> 0.1，请转到数据倾斜（已存储）。
3. 否则，请转到外部测试版数据倾斜。

不准确的估计

使统计信息保持最新状态，以确保查询优化器生成最佳计划。当估计的行计数明显小于实际计数时，需要维护统计信息。

缓解措施

创建/更新统计信息。

未缓存的复制表

如果已创建复制表，并且无法正确预热复制的表缓存，则由于额外的数据移动或创建欠佳的分布式计划，意外的性能会降低。

缓解措施

在 DML 操作后预热复制的缓存。
如果有频繁的 DML 操作，请将表的分布更改为 ROUND_ROBIN。

不匹配的数据类型/大小

联接表时，请确保联接列的数据类型和大小匹配。否则，会导致不必要的数据移动，从而减少 CPU、IO 和网络流量到剩余工作负荷的可用性。

缓解措施

重新生成表以更正没有相同数据类型和大小的相关表列。

即席外部表查询

针对外部表的查询旨在将数据批量加载到专用 SQL 池中。针对外部表的即席查询可能会因外部因素（如并发存储容器活动）而遭受可变持续时间。

缓解措施

先将数据加载到专用 SQL 池中，然后查询加载的数据。

数据倾斜（存储）

数据倾斜意味着数据不会均匀分布到分布区。分布式计划的每个步骤都需要完成所有分发，然后才能转到下一步。当数据偏斜时，无法实现处理资源（如 CPU 和 IO）的全部潜力，从而导致执行时间变慢。

缓解措施

查看有关分布式表的指南，以帮助你选择更合适的分布列。

正在进行的数据倾斜

外部数据倾斜是数据倾斜（已存储）问题的变体。但是，这不是磁盘上倾斜的数据分布。特定筛选器或分组数据的分布式计划的性质会导致类型 ShuffleMoveOperation 操作。此操作生成要下游使用的倾斜输出。

缓解措施

请确保已创建统计信息并更新。
更改列的顺序 GROUP BY ，以使用较高基数列领先。
如果联接涵盖多个列，则创建多列统计信息。
向查询添加查询提示 OPTION(FORCE_ORDER) 。
重构查询。

等待类型问题

如果上述任何问题都不适用于查询，则步骤 3 数据提供了确定哪些等待类型（in 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 日志 IO

不正常的 CCIs

不正常的 CCIs 有助于增加 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()函数。

通过

排查专用 SQL 池查询速度缓慢的问题

步骤 1：标识request_id（即 QID）

步骤 2：确定查询花费时间的位置

步骤 3：查看步骤详细信息

步骤 4：诊断和缓解

编译阶段问题

执行阶段问题

等待类型问题

反馈

其他资源