Monitorování úloh vyhrazeného fondu SQL ve službě Azure Synapse Analytics pomocí zobrazení dynamické správy

Tento článek popisuje, jak pomocí zobrazení dynamické správy (DMV) monitorovat úlohy, včetně zkoumání spouštění dotazů ve vyhrazeném fondu SQL.

Oprávnění

K dotazování zobrazení dynamické správy v tomto článku potřebujete oprávnění ZOBRAZIT STAV DATABÁZE nebo ŘÍZENÍ . Udělení STAVU DATABÁZE VIEW je obvykle upřednostňovaným oprávněním, protože je mnohem restriktivnější.

GRANT VIEW DATABASE STATE TO myuser;

Monitorování připojení

Všechna přihlášení k datovému skladu se protokolují do sys.dm_pdw_exec_sessions. Toto zobrazení dynamické správy obsahuje posledních 10 000 přihlášení. Je session_id primární klíč a je přiřazen postupně pro každé nové přihlášení.

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

Monitorování spouštění dotazů

Všechny dotazy spuštěné ve fondu SQL se protokolují do sys.dm_pdw_exec_requests. Toto zobrazení dynamické správy obsahuje posledních 10 000 spuštěných dotazů. Jednoznačně request_id identifikuje každý dotaz a je primárním klíčem pro toto zobrazení dynamické správy. Je request_id přiřazen postupně pro každý nový dotaz a má předponu QID, což je zkratka pro ID dotazu. Dotazování na toto zobrazení dynamické správy pro dané zobrazení session_id zobrazí všechny dotazy pro dané přihlášení.

Poznámka:

Uložené procedury používají více ID požadavků. ID žádostí se přiřazují v sekvenčním pořadí.

Tady je postup, jak prozkoumat plány spouštění dotazů a časy konkrétního dotazu.

Krok 1: Identifikace dotazu, který chcete prozkoumat

-- 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;

Z předchozích výsledků dotazu si poznamenejte ID požadavku dotazu, který chcete prozkoumat.

Dotazy v pozastaveném stavu je možné zařadit do fronty kvůli velkému počtu aktivních spuštěných dotazů. Tyto dotazy se také zobrazí v sys.dm_pdw_waits. V takovém případě vyhledejte čekání, jako je UserConcurrencyResourceType. Informace o limitech souběžnosti najdete v tématu Limity paměti a souběžnosti nebo třídy prostředků pro správu úloh. Dotazy můžou také čekat z jiných důvodů, jako jsou zámky objektů. Pokud váš dotaz čeká na prostředek, přečtěte si téma Zkoumání dotazů čekajících na prostředky dále v tomto článku.

Pokud chcete zjednodušit vyhledávání dotazu v tabulce sys.dm_pdw_exec_requests , pomocí funkce LABEL přiřaďte k dotazu komentář, který je možné vyhledat v sys.dm_pdw_exec_requests zobrazení.

-- 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';

Krok 2: Prozkoumání plánu dotazu

Pomocí ID požadavku načtěte distribuovaný plán SQL (DSQL) dotazu z 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;

Pokud plán DSQL trvá déle, než se čekalo, příčinou může být složitý plán s mnoha kroky DSQL nebo jen jeden krok, který trvá dlouho. Pokud je plán mnoho kroků s několika operacemi přesunutí, zvažte optimalizaci distribucí tabulek, aby se snížil přesun dat. Článek o distribuci tabulek vysvětluje, proč je potřeba přesunout data, aby bylo možné vyřešit dotaz. Článek také vysvětluje některé distribuční strategie pro minimalizaci přesunu dat.

Pokud chcete prozkoumat další podrobnosti o jednom kroku, zkontrolujte operation_type sloupec dlouhotrvajícího kroku dotazu a poznamenejte si index kroku:

• V případě operací SQL (OnOperation, RemoteOperation, ReturnOperation) pokračujte krokem 3.
• Pro operace přesunu dat (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation) pokračujte krokem 4.

Krok 3: Prozkoumání SQL v distribuovaných databázích

Pomocí ID požadavku a indexu kroku načtěte podrobnosti z sys.dm_pdw_sql_requests, které obsahují informace o spuštění kroku dotazu ve všech distribuovaných databázích.

-- 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;

Když je krok dotazu spuštěný, můžete použít nástroj DBCC PDW_SHOWEXECUTIONPLAN k načtení odhadovaného plánu SQL Serveru z mezipaměti plánu SQL Serveru pro krok spuštěný v konkrétní distribuci.

-- 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);

Krok 4: Prozkoumání přesunu dat v distribuovaných databázích

Pomocí ID požadavku a indexu kroku načtěte informace o kroku přesunu dat spuštěného v každé distribuci z 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;

• Ve sloupci total_elapsed_time zkontrolujte, jestli konkrétní distribuce trvá výrazně déle než ostatní při přesunu dat.
• V případě dlouhotrvající distribuce zkontrolujte sloupec a zkontrolujte rows_processed , jestli je počet řádků přesunutých z této distribuce výrazně větší než ostatní. Pokud ano, může toto hledání znamenat nerovnoměrnou distribuci podkladových dat. Jednou z příčin nerovnoměrné distribuce dat ve sloupci s mnoha hodnotami NULL (jejichž řádky budou všechny přistály ve stejném rozdělení). Pokud je to možné, vyhněte se distribuci těchto typů sloupců nebo filtrováním dotazu, abyste eliminovali hodnoty NUL.

Pokud je dotaz spuštěný, můžete pomocí nástroje DBCC PDW_SHOWEXECUTIONPLAN načíst odhadovaný plán SQL Serveru z mezipaměti plánu SQL Serveru pro aktuálně spuštěný krok SQL v rámci konkrétní distribuce.

-- 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);

Monitorování čekajících dotazů

Pokud zjistíte, že váš dotaz neprobíhá, protože čeká na prostředek, tady je dotaz, který zobrazuje všechny prostředky, na které dotaz čeká.

-- 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;

Pokud dotaz aktivně čeká na prostředky z jiného dotazu, stav bude AcquireResources. Pokud dotaz obsahuje všechny požadované prostředky, stav se udělí.

Monitorování databáze tempdb

Databáze tempdb se používá k uchovávání průběžných výsledků během provádění dotazu. Vysoké využití tempdb databáze může vést k nízkému výkonu dotazů. Pro každý nakonfigurovaný DW100c je přiděleno 399 GB tempdb místa (DW1000c by měl celkem 3,99 TB tempdb ). Níže najdete tipy pro monitorování tempdb využití a snížení tempdb využití v dotazech.

Monitorování databáze tempdb pomocí zobrazení

Pokud chcete monitorovat tempdb využití, nejprve nainstalujte zobrazení microsoft.vw_sql_requests ze sady Microsoft Toolkit for SQL Pool. Potom můžete spustit následující dotaz, abyste viděli tempdb využití pro každý uzel pro všechny spuštěné dotazy:

-- 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;

Poznámka:

Přesun dat používá tempdb. Pokud chcete snížit využití během přesunu tempdb dat, ujistěte se, že tabulka používá distribuční strategii, která distribuuje data rovnoměrně. Pomocí nástroje Azure Synapse SQL Distribution Advisor získejte doporučení k metodě distribuce, která je vhodná pro vaše úlohy. Pomocí sady Azure Synapse Toolkit můžete monitorovat tempdb pomocí dotazů T-SQL.

Pokud máte dotaz, který spotřebovává velké množství paměti nebo obdržel chybovou zprávu související s přidělením tempdb, může to být způsobeno velmi velkým příkazem CREATE TABLE AS SELECT (CTAS) nebo INSERT SELECT , který selhává v konečné operaci přesunu dat. Obvykle je možné ji identifikovat jako operaci ShuffleMove v plánu distribuovaného dotazu přímo před posledním příkazem INSERT SELECT. K monitorování operací ShuffleMove použijte sys.dm_pdw_request_steps .

Nejběžnějším zmírněním je přerušení příkazu CTAS nebo INSERT SELECT na více příkazů načítání, aby datový svazek nepřekročil limit 399 GB na limit 100DWUc tempdb . Cluster můžete také škálovat na větší velikost, abyste zvýšili, kolik tempdb místa máte.

Kromě příkazů CTAS a INSERT SELECT můžou složité dotazy spuštěné s nedostatkem paměti přetékat do tempdb příčin selhání dotazů. Zvažte spuštění s větší třídou prostředků, abyste se vyhnuli přelití do tempdb.

Monitorování paměti

Příčinou pomalého výkonu a nedostatku paměti může být paměť. Zvažte škálování datového skladu, pokud při provádění dotazů zjistíte, že využití paměti SQL Serveru dosahuje limitů.

Následující dotaz vrátí využití paměti a zatížení paměti SQL Serveru na uzel:

-- 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)'

Monitorování velikosti transakčního protokolu

Následující dotaz vrátí velikost transakčního protokolu v každé distribuci. Pokud některý ze souborů protokolu dosahuje 160 GB, měli byste zvážit vertikální navýšení kapacity instance nebo omezení velikosti transakce.

-- 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)'

Monitorování vrácení transakčního protokolu zpět

Pokud vaše dotazy selhávají nebo trvá delší dobu, můžete zkontrolovat a monitorovat, jestli se nějaké transakce vrací zpět.

-- 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]

Monitorování načtení PolyBase

Následující dotaz poskytuje přibližný odhad průběhu zatížení. Dotaz zobrazuje pouze soubory, které se právě zpracovávají.

-- 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;

Monitorování blokování dotazů

Následující dotaz poskytuje prvních 500 blokovaných dotazů v prostředí.

--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;

Načtení textu dotazu z čekání a blokování dotazů

Následující dotaz poskytuje text a identifikátor dotazu pro čekající a blokující dotazy, aby bylo snadné řešení potíží.

-- 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;

Další kroky

• Další informace o zobrazení dynamické správy naleznete v tématu Zobrazení systému.