Устранение неполадок медленного запроса в выделенном пуле SQL

Статья
11/20/2024

Область применения: Azure Synapse Analytics

В этой статье описаны причины и способы устранения распространенных проблем с производительностью запросов в выделенном пуле SQL Azure Synapse Analytics.

Выполните действия, чтобы устранить проблему или выполнить действия, описанные в записной книжке с помощью Azure Data Studio. Первые три шага пошаговые инструкции по сбору данных телеметрии, описывающей жизненный цикл запроса. Ссылки в конце статьи помогут проанализировать потенциальные возможности, найденные в собранных данных.

Примечание.

Прежде чем пытаться открыть эту записную книжку, убедитесь, что Azure Data Studio установлена на локальном компьютере. Чтобы установить его, перейдите к разделу "Узнайте, как установить Azure Data Studio".

Откройте записную книжку в Azure Data Studio

Внимание

Большинство обнаруженных проблем с производительностью вызваны следующими причинами:

Устаревшая статистика
Неработоспособные кластеризованные индексы columnstore (CCIs)

Чтобы сэкономить время устранения неполадок, убедитесь, что статистика создана и обновлена , а CCIS были перестроены.

Шаг 1. Определение request_id (aka 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 столбец, чтобы определить их.
Рекомендуется отфильтровать все QID, для которых нет значения, resource_allocation_percentage если вы знаете, что целевая инструкция содержится в пакете.

Примечание. Будьте осторожны с этим фильтром, так как он также может отфильтровать некоторые запросы, которые блокируются другими сеансами.

Шаг 2. Определение места выполнения запроса

Выполните следующий сценарий, чтобы найти шаг, который может вызвать проблему производительности запроса. Обновите переменные в скрипте со значениями, описанными в следующей таблице. Измените @ShowActiveOnly значение на 0, чтобы получить полное представление распределенного плана. Запишите StepIndexзначения медленного Phaseшага, 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, ознакомьтесь с соответствующим разделом, чтобы получить дополнительные сведения из следующей таблицы.

Description	Распространенные причины
`Compilation Concurrency`	Заблокировано: параллелизм компиляции
`Resource Allocation (Concurrency)`	Заблокировано: выделение ресурсов

Если запрос находится в состоянии "Выполнение", определенном на шаге 1, но на шаге 2 нет сведений о шаге 2, проверьте причину, которая лучше всего соответствует вашему сценарию, чтобы получить дополнительные сведения из следующей таблицы.

Сценарий	Распространенные причины
Оператор содержит сложную логику фильтра соединения или выполняет соединения в `WHERE` предложении	Сложный синтаксис запроса или более старый синтаксис JOIN
Оператор является длительным `DROP TABLE` или `TRUNCATE TABLE` оператором	Длительное выполнение DROP TABLE или TRUNCATE TABLE
CCIs имеет высокий процент удаленных или открытых строк (см. статью "Оптимизация кластеризованных индексов columnstore")	Неработоспособные CCIs (как правило)

Проанализируйте результирующий набор на шаге 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 , чтобы принудительно очистить ранее удаленные или усеченные таблицы.

Неработоспособные CCIs (как правило)

Для обеспечения работоспособности плохого кластеризованного индекса columnstore (CCI) требуются дополнительные метаданные, что может привести к тому, что оптимизатор запросов займет больше времени, чтобы определить оптимальный план. Чтобы избежать этой ситуации, убедитесь, что все ваши CCIS находятся в хорошем состоянии.

Устранение рисков

Оцените и исправьте работоспособность кластеризованного индекса columnstore в выделенном пуле SQL.

Задержка от автоматического создания статистики

Устранение рисков

Если первое выполнение запроса последовательно требует создания статистики, необходимо вручную создать статистику до выполнения запроса.

Автоматическое создание времени ожидания статистики

Параметр автоматического создания статистики по ON AUTO_CREATE_STATISTICS умолчанию помогает оптимизатору запросов принимать хорошие решения распределенного плана. Автоматическое создание статистики происходит в ответ на инструкцию 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на .

Несогласованный тип и размер данных

При присоединении таблиц убедитесь, что тип данных и размер столбцов соединения совпадают. В противном случае это приведет к ненужным перемещениям данных, которые будут уменьшать доступность ЦП, операций ввода-вывода и сетевого трафика до остальной части рабочей нагрузки.

Устранение рисков

Перестройте таблицы, чтобы исправить связанные столбцы таблиц, которые не имеют идентичных типов данных и размера.

Нерегламентированные запросы внешней таблицы

Запросы к внешним таблицам предназначены для массовой загрузки данных в выделенный пул SQL. Нерегламентированные запросы к внешним таблицам могут страдать от переменных длительности из-за внешних факторов, таких как одновременные действия контейнера хранилища.

Устранение рисков

Сначала загрузите данные в выделенный пул SQL, а затем запросите загруженные данные.

Отклонение данных (сохранено)

Отклонение данных означает, что данные равномерно распределяются по дистрибутивам. Каждый шаг распределенного плана требует завершения всех дистрибутивов перед переходом к следующему шагу. При отклонении данных невозможно достичь полного потенциала ресурсов обработки, таких как ЦП и операций ввода-вывода, что приводит к снижению времени выполнения.

Устранение рисков

Ознакомьтесь с нашим руководством по распределенным таблицам , чтобы помочь вам выбрать более подходящий столбец распространения.

Отклонение данных в полете

Отклонение данных во время полета — это вариант проблемы с отклонением (хранимым) данными. Но это не распределение данных на диске, которое искажено. Характер распределенного плана для определенных фильтров или группированных данных приводит к 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.
Пакетируйте изменения и обновите целевой объект с более частыми строками.

Буферные операции ввода-вывода, другие операции ввода-вывода на диск, операции ввода-вывода журнала Tran

Неработоспособные CCIs

Неработоспособные ccis способствуют увеличению объема операций ввода-вывода, ЦП и памяти, что, в свою очередь, негативно влияет на производительность запросов. Чтобы устранить эту проблему, попробуйте один из следующих методов:

Оцените и исправьте работоспособность кластеризованного индекса columnstore в выделенном пуле SQL.
Запустите и просмотрите выходные данные запроса, указанного в статье Оптимизация кластеризованных индексов columnstore, чтобы получить базовые показатели.
Выполните действия по перестроению индексов, чтобы повысить качество сегментов , нацелив таблицы, участвующие в примере запроса на проблему.

Устаревшая статистика

Устаревшая статистика может привести к возникновению неоптимизованного распределенного плана, который включает в себя больше перемещения данных, чем необходимо. Ненужное перемещение данных увеличивает рабочую нагрузку не только на неактивных данных, но и на ней tempdb. Так как операции ввода-вывода являются общим ресурсом во всех запросах, изменение производительности может влиять на всю рабочую нагрузку.

Чтобы устранить эту ситуацию, убедитесь, что все статистические данные актуальны, и план обслуживания применяется для их обновления для рабочих нагрузок пользователей.

Тяжелые рабочие нагрузки ввода-вывода

Общая рабочая нагрузка может считывать большие объемы данных. Выделенные пулы SQL в Synapse масштабируют ресурсы в соответствии с DWU. Чтобы повысить производительность, рассмотрите один или оба варианта:

Использование большего класса ресурсов для запросов.
Увеличьте вычислительные ресурсы.

ЦП, параллелизм

Сценарий	Меры по снижению риска
Низкая работоспособность CCI	Оценка и исправление работоспособности кластеризованного индекса columnstore в выделенном пуле SQL
Пользовательские запросы содержат преобразования	Перемещение всего форматирования и прочей логики преобразования в процессы ETL, чтобы сохранить отформатированные версии
Неправильное определение приоритета для рабочей нагрузки	Реализация изоляции рабочей нагрузки
Недостаточное количество единиц DWU для рабочей нагрузки	Рассмотрите возможность увеличения вычислительных ресурсов

Сетевой ввод-вывод

Если проблема возникает во время операции на шаге RETURN 2,

Уменьшите число параллельных параллельных процессов.
Масштабируйте наиболее затронутый процесс до другого клиента.

Для всех других операций перемещения данных вероятно, что проблемы с сетью, как представляется, внутренними для выделенного пула SQL. Чтобы быстро устранить эту проблему, выполните следующие действия.

Масштабируйте выделенный пула SQL до DW100c
Верните масштаб до требуемого уровня DWU

SQL CLR

Избегайте частого FORMAT() использования функции, реализуя альтернативный способ преобразования данных (например, CONVERT() со стилем).

Поделиться через

Устранение неполадок медленного запроса в выделенном пуле SQL

Шаг 1. Определение request_id (aka QID)

Шаг 2. Определение места выполнения запроса

Шаг 3. Просмотр сведений о шаге

Шаг 4. Диагностика и устранение рисков

Проблемы этапа компиляции

Проблемы этапа выполнения

Проблемы с типом ожидания

Обратная связь

Дополнительные ресурсы