Použití zobrazení dynamické správy k identifikaci a řešení potíží s výkonem dotazů

  • 8 min

Zobrazení dynamické správy poskytují programové prostředí pro monitorování aktivity fondu SQL služby Azure Synapse Analytics pomocí jazyka Transact-SQL. Poskytnutá zobrazení umožňují nejen řešit potíže s výkonem a identifikovat kritické body výkonu s úlohami pracujícími ve vašem systému, ale používají je také jiné služby, jako je Azure Advisor, k poskytování doporučení k Azure Synapse Analytics.

Existuje více než 90 zobrazení dynamické správy, která se můžou dotazovat na vyhrazené fondy SQL a načítat informace o následujících oblastech služby:

  • Informace o připojení a aktivita
  • Požadavky a dotazy na spouštění SQL
  • Informace o indexu a statistikách
  • Blokování a zamykání prostředků
  • Aktivita služby přesunu dat
  • Chyby

Následuje příklad monitorování provádění dotazů fondů SQL služby Azure Synapse Analytics. Prvním krokem je kontrola připojení k serveru před kontrolou aktivity provádění dotazů. 

Monitorování připojení

Všechna přihlášení k datovému skladu se protokolují do sys.dm_pdw_exec_sessions. Session_id je 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. Request_id jednoznačně identifikuje každý dotaz a je primárním klíčem pro toto zobrazení dynamické správy. Request_id se přiřadí 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 danou session_id zobrazí všechny dotazy pro dané přihlášení.

Krok 1

Prvním krokem je 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 čeká dotaz s typem 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ý můžete vyhledat v zobrazení 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 it a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Krok 2

Id požadavku použijte k načtení plánu distribuovaného SQL (DSQL) dotazů 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, operation_type sloupec dlouhotrvajícího kroku dotazu a poznamenejte si index kroku:

  • Pokračujte krokem 3 pro operace SQL: OnOperation, RemoteOperation, ReturnOperation.
  • Pokračujte krokem 4 pro operace přesunu dat: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.

Krok 3

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 pomocí PDW_SHOWEXECUTIONPLAN DBCC načíst odhadovaný plán 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

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 pro přesun dat trvá určitá distribuce déle než jiná.
  • V případě dlouhotrvající distribuce zkontrolujte sloupec rows_processed a zkontrolujte, jestli je počet řádků přesunutých z této distribuce 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);

Zobrazení dynamické správy (DMV) obsahuje pouze 10 000 řádků dat. U silně využívaných systémů to znamená, že data uložená v této tabulce můžou být ztracena s hodinami nebo dokonce minutami, protože data se spravují v prvním a prvním systému. V důsledku toho můžete potenciálně ztratit smysluplné informace, které vám můžou pomoct s diagnostikou problémů s výkonem dotazů ve vašem systému. V takovém případě byste měli použít úložiště dotazů.

Můžete také monitorovat další aspekty fondů Azure Synapse SQL, mezi které patří:

  • Čekání monitorování
  • Monitorování databáze tempdb
  • Monitorování paměti
  • Monitorování transakčního protokolu
  • Monitorování PolyBase

Tady si můžete prohlédnout informace o monitorování těchto oblastí.