Monitorování úlohy vyhrazeného fondu SQL Azure Synapse Analytics pomocí zobrazení dynamické správy

  • Článek

Tento článek popisuje, jak používat zobrazení dynamické správy (DMV) k monitorování úloh, včetně zkoumání provádění dotazů ve vyhrazeném fondu SQL.

Oprávnění

Pokud chcete dotazovat zobrazení dynamické správy v tomto článku, potřebujete oprávnění VIEW DATABASE STATE (STAV DATABÁZE ) nebo CONTROL (ŘÍZENÍ ). Udělení oprávnění VIEW DATABASE STATE je obvykle upřednostňované, 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í na 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 přiřazuje se postupně ke každému novému přihlášení.

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

Monitorování provádě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. Pro request_id každý nový dotaz se přiřadí postupně a má předponu QID, což je id dotazu. Dotazování tohoto zobrazení dynamické správy pro dané session_id přihlášení zobrazí všechny dotazy pro dané přihlášení.

Poznámka

Uložené procedury používají více ID požadavků. ID požadavků se přiřazují v postupném pořadí.

Tady je postup, jak prozkoumat plány a časy provádění dotazů pro konkrétní dotaz.

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ě hledejte čekání, například UserConcurrencyResourceType. Informace o limitech souběžnosti najdete v tématech Omezení paměti a souběžnosti nebo Třídy prostředků pro správu úloh. Dotazy můžou také čekat z jiných důvodů, například kvůli zámkům objektů. Pokud váš dotaz čeká na prostředek, přečtěte si část Zkoumání dotazů čekajících na další prostředky v tomto článku.

Pokud chcete zjednodušit vyhledávání dotazu v sys.dm_pdw_exec_requests tabulce, pomocí funkce LABEL přiřaďte k dotazu komentář, který můžete 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á dlouhou dobu. Pokud plán obsahuje mnoho kroků s několika operacemi přesunu, zvažte optimalizaci distribucí tabulek, abyste omezili přesun dat. Článek o distribuci tabulek vysvětluje, proč je nutné přesunout data, aby bylo možné dotaz vyřešit. Článek také vysvětluje některé strategie distribuce, jak minimalizovat přesun 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.
  • V případě operací přesunu dat (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation) pokračujte KROKEM 4.

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

K načtení podrobností z sys.dm_pdw_sql_requests použijte ID požadavku a index kroku, které obsahují informace o provedení 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ý, je možné použít dbcc PDW_SHOWEXECUTIONPLAN k načtení SQL Server odhadovaného plánu z mezipaměti plánu SQL Server 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ém 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;
  • Zkontrolujte sloupec a total_elapsed_time zjistěte, jestli přesun dat u konkrétní distribuce nebere výrazně déle než jiné.
  • U dlouhotrvající distribuce zkontrolujte rows_processed sloupec a zjistěte, jestli je počet řádků, které se z tohoto rozdělení přesouvají, výrazně vyšší než ostatní. Pokud ano, toto zjištění může znamenat nerovnoměrnou distribuci podkladových dat. Jednou z příčin nerovnoměrné distribuce dat je distribuce ve sloupci s mnoha hodnotami NULL (jehož řádky budou mít stejné rozdělení). Předejděte pomalým dotazům tím, že se vyhnete distribuci u těchto typů sloupců nebo vyfiltrujte dotaz, abyste eliminovali hodnoty NULL, pokud je to možné.

Pokud je dotaz spuštěný, můžete použít DBCC PDW_SHOWEXECUTIONPLAN k načtení SQL Server odhadovaného plánu z mezipaměti plánu SQL Server 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 nepostupuje, protože čeká na prostředek, tady je dotaz, který zobrazí 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, bude stav AcquireResources. Pokud dotaz obsahuje všechny požadované prostředky, bude stav Uděleno.

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ý soubor DW100c je přiděleno 399 GB tempdb místa (dw1000c by měl 3,99 TB celkového tempdb místa). 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 z fondu sady Microsoft Toolkit for SQL. Pak můžete spuštěním následujícího dotazu zobrazit tempdb využití jednotlivých uzlů 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ři přesunu dat se tempdbpoužívá . Pokud chcete omezit využití dat během přesunu tempdb dat, zajistěte, aby vaše tabulka používala distribuční strategii, která distribuuje data rovnoměrně. Pomocí nástroje Azure Synapse SQL Distribution Advisor získejte doporučení k metodě rozdělení vhodné pro vaše úlohy. K monitorování tempdb pomocí dotazů T-SQL použijte sadu nástrojů Azure Synapse Toolkit.

Pokud máte dotaz, který spotřebovává velké množství paměti, nebo pokud jste obdrželi chybovou zprávu související s přidělením tempdb, příčinou může být velmi velký spuštěný příkaz CREATE TABLE AS SELECT (CTAS) nebo INSERT SELECT , který selhává při konečné operaci přesunu dat. To se obvykle dá identifikovat jako operace ShuffleMove v distribuovaném plánu dotazu těsně před finálním PŘÍKAZEM INSERT SELECT. Pomocí sys.dm_pdw_request_steps můžete monitorovat operace Náhodné přemístit.

Nejběžnějším řešením je rozdělit příkaz CTAS nebo INSERT SELECT na více příkazů načtení, aby objem dat nepřekročil limit 399 GB na 100DWUc tempdb . Cluster můžete také škálovat na větší velikost, abyste zvětšili tempdb množství místa.

Kromě příkazů CTAS a INSERT SELECT se velké a složité dotazy spuštěné s nedostatkem paměti můžou přelít do tempdb důsledku 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 nízkého výkonu a problémů s nedostatkem paměti může být paměť. Pokud zjistíte, SQL Server využití paměti během provádění dotazů dosahuje svých limitů, zvažte škálování datového skladu.

Následující dotaz vrátí SQL Server využití paměti a zatížení paměti 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 pro každou 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 trvají dlouhou dobu, můžete zkontrolovat a monitorovat, jestli nedošlo k vrácení nějaké transakce.

-- 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í zatížení PolyBase

Následující dotaz poskytuje přibližný odhad průběhu načítání. Dotaz zobrazí jenom aktuálně zpracovávané soubory.

-- 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 obsahuje 500 blokovaných dotazů s nejvyšším využitím 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 čekajících a blokujících dotazů

Následující dotaz poskytuje text dotazu a identifikátor čekajících a blokujících dotazů pro 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,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       waiting.object_name AS Blocking_Object_Name,
       waiting.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