Мониторинг рабочей нагрузки выделенного SQL пула Azure Synapse Analytics с использованием динамических административных представлений

Статья
01/22/2025

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

Разрешения

Для выполнения запросов к динамическим административным представлениям в этой статье необходимы разрешения VIEW DATABASE STATE или CONTROL. Более предпочтительно разрешение VIEW DATABASE STATE, так как оно гораздо строже.

GRANT VIEW DATABASE STATE TO myuser;

Мониторинг подключений

Все операции входа в хранилище данных регистрируются в представлении sys.dm_pdw_exec_sessions. Это динамическое представление содержит записи о последних 10 000 операциях входа. session_id является первичным ключом и назначается последовательно для каждого нового сеанса входа.

-- Other Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions where status <> 'Closed' and session_id <> session_id();

Наблюдение за выполнением запросов

Все запросы, выполненные в пуле SQL, логируются в sys.dm_pdw_exec_requests. Это динамическое представление управления содержит информацию о последних 10 000 выполненных запросах. request_id уникально идентифицирует каждый запрос и является основным ключом для этого динамического административного представления. Каждому новому запросу последовательно присваивается request_id, и ему добавляется префикс QID, который обозначает идентификатор запроса. При запросе этого динамического административного представления для заданного session_id представления отображаются все запросы для заданного имени входа.

Примечание.

Хранимые процедуры используют несколько идентификаторов запросов. Идентификаторы запросов присваиваются последовательно.

Вот шаги, которые следует выполнить для изучения планов выполнения запросов и времени их выполнения для конкретного запроса.

Шаг 1. Определение запроса, который вы хотите исследовать

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

-- Find top 10 queries longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

Запишите идентификатор запроса, который вы хотите исследовать. Он указан в приведенных выше результатах запроса.

Запросы с состоянием Приостановлено можно поставить в очередь ввиду большого количества активных выполняющихся запросов. Эти запросы также отображаются в sys.dm_pdw_waits. В этом случае ищите события ожидания, такие как UserConcurrencyResourceType. Сведения об ограничениях параллелизма см. в статье Ограничения памяти и параллелизма или Управление рабочими нагрузками с использованием классов ресурсов. Запросы также могут быть отложены по другим причинам, в том числе из-за блокировки объектов. Если ваш запрос ожидает ресурса, ознакомьтесь с разделом Исследование запросов, ожидающих ресурсов, расположенным далее в этой статье.

Чтобы упростить поиск запроса в таблице sys.dm_pdw_exec_requests , используйте LABEL , чтобы назначить комментарий запросу, который можно найти в представлении sys.dm_pdw_exec_requests .

-- Query with Label
SELECT *
FROM sys.tables
OPTION (LABEL = 'My Query')
;

-- Find a query with the Label 'My Query'
-- Use brackets when querying the label column, as it is a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Шаг 2. Изучение плана запроса

Используйте идентификатор запроса, чтобы извлечь распределённый план SQL (DSQL) запроса из sys.dm_pdw_request_steps.

-- Find the distributed query plan steps for a specific query.
-- Replace request_id with value from Step 1.

SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;

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

Чтобы изучить дополнительные сведения о одном шаге, проверьте operation_type столбец длительного шага запроса и запишите индекс шага:

Для операций SQL (OnOperation, RemoteOperation, ReturnOperation), перейдите к шагу 3.
Для операций перемещения данных (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation), перейдите к шагу 4.

Шаг 3. Изучение SQL в распределенных базах данных

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

-- Find the distribution run times for a SQL step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID####' AND step_index = 2;

Когда выполняется этап запроса, можно использовать DBCC PDW_SHOWEXECUTIONPLAN, чтобы получить из кэша планов SQL Server предполагаемый план выполнения SQL Server для этапа, выполняемого на определенном распределении.

-- Find the SQL Server execution plan for a query running on a specific SQL pool or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

Шаг 4. Изучение перемещения данных в распределенных базах данных

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

-- Find information about all the workers completing a Data Movement Step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID####' AND step_index = 2;

Проверьте столбец total_elapsed_time, чтобы увидеть, занимает ли определенное распределение значительно больше времени на перемещение данных по сравнению с другими.
Для давно работающего распределения проверьте столбец rows_processed, чтобы узнать, намного ли больше количество строк, перемещаемых из этого распределения, по сравнению с другими. Если это так, такой результат может означать отклонение базовых данных. Одна из причин неравномерного распределения данных — распределение по столбцу с большим количеством значений NULL (строки которых будут находиться в одном распределении). Избегайте медленных запросов, избегая распределения данных на этих типах столбцов или фильтруйте ваш запрос, чтобы исключать значения NULL, если это возможно.

Если запрос выполняется, вы можете с помощью команды DBCC PDW_SHOWEXECUTIONPLAN получить из кэша планов SQL Server предполагаемый план для выполняемого шага SQL в определенном распределении.

-- Find the SQL Server estimated plan for a query running on a specific SQL pool Compute or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(55, 238);

Мониторинг ожидающих запросов

Если вы обнаружите, что ваш запрос не выполняется, так как он ожидает ресурса, вот запрос, показывающий все ресурсы, которые ожидает запрос.

-- Find queries
-- Replace request_id with value from Step 1.

SELECT waits.session_id,
      waits.request_id,
      requests.command,
      requests.status,
      requests.start_time,
      waits.type,
      waits.state,
      waits.object_type,
      waits.object_name
FROM   sys.dm_pdw_waits waits
   JOIN  sys.dm_pdw_exec_requests requests
   ON waits.request_id=requests.request_id
WHERE waits.request_id = 'QID####'
ORDER BY waits.object_name, waits.object_type, waits.state;

Если запрос активно ожидает ресурсы из другого запроса, то его состоянием будет AcquireResources. Если запрос содержит все необходимые ресурсы, то его состоянием будет Granted.

Следить за tempdb

База tempdb данных используется для хранения промежуточных результатов во время выполнения запроса. Высокая загрузка tempdb базы данных может привести к снижению производительности запросов. Для каждого настроенного DW100c выделено 399 ГБ tempdb пространства (DW1000c будет иметь 3,99 ТБ общего tempdb пространства). Ниже приведены советы по мониторингу и уменьшению использования tempdb и tempdb в запросах.

Мониторинг tempdb с использованием представлений

Чтобы отслеживать tempdb использование, сначала установите представление microsoft.vw_sql_requests из Microsoft Toolkit для SQL pool. Затем можно выполнить следующий запрос, чтобы просмотреть tempdb использование каждого узла для всех выполненных запросов:

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    exs.login_name AS 'LoginName',
    DB_NAME(ssu.database_id) AS 'DatabaseName',
    (es.memory_usage * 8) AS 'MemoryUsage (in KB)',
    (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
    (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
    (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
    (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)',
    CASE es.is_user_process
    WHEN 1 THEN 'User Session'
    WHEN 0 THEN 'System Session'
    END AS 'SessionType',
    es.row_count AS 'RowCount'
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
    INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
    INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
    INNER JOIN microsoft.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
    LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
    LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

Примечание.

Перемещение данных использует tempdb. Чтобы уменьшить использование tempdb во время перемещения данных, убедитесь, что таблица использует стратегию распределения, которая равномерно распределяет данные. Используйте помощник по распространению Azure Synapse SQL, чтобы получить рекомендации по методу распространения, подходящему для рабочих нагрузок. Используйте набор средств Azure Synapse для мониторинга tempdb с помощью запросов T-SQL.

Если у вас есть запрос, который потребляет большой объем памяти, или вы получили сообщение об ошибке, связанное с выделением tempdb, это может быть связано с очень большой инструкцией CREATE TABLE AS SELECT (CTAS) или INSERT SELECT, которые завершаются сбоем в последней операции перемещения данных. Обычно это может быть операция ShuffleMove в плане распределенного запроса непосредственно перед завершающей инструкцией INSERT SELECT. Используйте sys.dm_pdw_request_steps для мониторинга операций ShuffleMove.

Наиболее распространенным способом устранения рисков является разбиение инструкции CTAS или INSERT SELECT на несколько инструкций загрузки, чтобы объем данных не превышал предел 399 ГБ на 100DWUc tempdb. Вы также можете масштабировать кластер до большего размера, чтобы увеличить объем tempdb пространства.

Помимо инструкций CTAS и INSERT SELECT, большие сложные запросы, выполняемые при недостаточности памяти, могут привести к сбою запросов в tempdb. Рекомендуется использовать более крупный ресурсный класс, чтобы избежать переполнения в tempdb.

Мониторинг памяти

Память может быть основной причиной низкой производительности и проблем с нехваткой памяти. Вы можете рассмотреть возможность масштабирования хранилища данных, если во время выполнения запроса использование памяти SQL Server приближается к своим пределам.

Следующий запрос возвращает использование памяти SQL Server и нехватку памяти на каждом узле:

-- Memory consumption
SELECT
  pc1.cntr_value as Curr_Mem_KB,
  pc1.cntr_value/1024.0 as Curr_Mem_MB,
  (pc1.cntr_value/1048576.0) as Curr_Mem_GB,
  pc2.cntr_value as Max_Mem_KB,
  pc2.cntr_value/1024.0 as Max_Mem_MB,
  (pc2.cntr_value/1048576.0) as Max_Mem_GB,
  pc1.cntr_value * 100.0/pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
-- pc1: current memory
sys.dm_pdw_nodes_os_performance_counters AS pc1
-- pc2: total memory allowed for this SQL instance
JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2
ON pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE
pc1.counter_name = 'Total Server Memory (KB)'
AND pc2.counter_name = 'Target Server Memory (KB)'

Мониторинг размера журнала транзакций

Следующий запрос возвращает размер журнала транзакций для каждого распределения. Если один из файлов журнала достигает размера 160 ГБ, следует рассмотреть вертикальное масштабирование вашего экземпляра или ограничение размера транзакции.

-- Transaction log size
SELECT
  instance_name as distribution_db,
  cntr_value*1.0/1048576 as log_file_size_used_GB,
  pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'

Мониторинг отката журнала транзакций

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

-- Monitor rollback
SELECT
    SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END),
    t.pdw_node_id,
    nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]

Мониторинг нагрузки PolyBase

Следующий запрос позволяет приблизительно оценить текущую нагрузку. В запросе отображаются только те файлы, которые обрабатываются в текущий момент.

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

Мониторинг блокировок запросов

Следующий запрос предоставляет первые 500 заблокированных запросов в среде.

--Collect the top blocking
SELECT
    TOP 500 waiting.request_id AS WaitingRequestId,
    waiting.object_type AS LockRequestType,
    waiting.object_name AS ObjectLockRequestName,
    waiting.request_time AS ObjectLockRequestTime,
    blocking.session_id AS BlockingSessionId,
    blocking.request_id AS BlockingRequestId
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
WHERE
    waiting.state = 'Queued'
    AND blocking.state = 'Granted'
ORDER BY
    ObjectLockRequestTime ASC;

Извлечение текста запроса из ожидающих и блокирующих запросов

Следующий запрос предоставляет текст и идентификатор для ожидающих и блокирующих запросов, что помогает легко устранять неполадки.

-- To retrieve query text from waiting and blocking queries

SELECT waiting.session_id AS WaitingSessionId,
       waiting.request_id AS WaitingRequestId,
       COALESCE(waiting_exec_request.command,waiting_exec_request.command2) AS WaitingExecRequestText,
       waiting.object_name AS Waiting_Object_Name,
       waiting.object_type AS Waiting_Object_Type,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       blocking.object_name AS Blocking_Object_Name,
       blocking.object_type AS Blocking_Object_Type,
       waiting.type AS Lock_Type,
       waiting.request_time AS Lock_Request_Time,
       datediff(ms, waiting.request_time, getdate())/1000.0 AS Blocking_Time_sec
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
       INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request
       ON waiting.request_id = waiting_exec_request.request_id
WHERE waiting.state = 'Queued'
       AND blocking.state = 'Granted'
ORDER BY Lock_Request_Time DESC;

Дополнительные сведения о динамических административных представлениях см. в статье о системных представлениях.

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

Мониторинг рабочей нагрузки выделенного SQL пула Azure Synapse Analytics с использованием динамических административных представлений

Разрешения

Мониторинг подключений

Наблюдение за выполнением запросов

Шаг 1. Определение запроса, который вы хотите исследовать

Шаг 2. Изучение плана запроса

Шаг 3. Изучение SQL в распределенных базах данных

Шаг 4. Изучение перемещения данных в распределенных базах данных

Мониторинг ожидающих запросов

Следить за tempdb

Мониторинг tempdb с использованием представлений

Мониторинг памяти

Мониторинг размера журнала транзакций

Мониторинг отката журнала транзакций

Мониторинг нагрузки PolyBase

Мониторинг блокировок запросов

Извлечение текста запроса из ожидающих и блокирующих запросов

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

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

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

Мониторинг рабочей нагрузки выделенного SQL пула Azure Synapse Analytics с использованием динамических административных представлений

Разрешения

Мониторинг подключений

Наблюдение за выполнением запросов

Шаг 1. Определение запроса, который вы хотите исследовать

Шаг 2. Изучение плана запроса

Шаг 3. Изучение SQL в распределенных базах данных

Шаг 4. Изучение перемещения данных в распределенных базах данных

Мониторинг ожидающих запросов

Следить за tempdb

Мониторинг tempdb с использованием представлений

Мониторинг памяти

Мониторинг размера журнала транзакций

Мониторинг отката журнала транзакций

Мониторинг нагрузки PolyBase

Мониторинг блокировок запросов

Извлечение текста запроса из ожидающих и блокирующих запросов

Связанный контент

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

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