A dedikált AZURE SYNAPSE Analytics SQL-készlet számítási feladatainak monitorozása DMV-k használatával

Ez a cikk azt ismerteti, hogyan használhat dinamikus felügyeleti nézeteket (DMV-ket) a számítási feladatok figyelésére, beleértve a lekérdezések végrehajtásának vizsgálatát egy dedikált SQL-készletben.

Engedélyek

A cikkben szereplő DMV-k lekérdezéséhez AZ ADATBÁZIS ÁLLAPOTA vagy a CONTROL engedély szükséges. A VIEW DATABASE STATE megadása általában az előnyben részesített engedély, mivel sokkal korlátozóbb.

GRANT VIEW DATABASE STATE TO myuser;

Kapcsolatok monitorozása

Az adattárházba való összes bejelentkezést a rendszer sys.dm_pdw_exec_sessions naplózza. Ez a DMV az utolsó 10 000 bejelentkezést tartalmazza. Az session_id az elsődleges kulcs, és sorrendben van hozzárendelve minden új bejelentkezéshez.

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

Lekérdezés végrehajtásának monitorozása

A rendszer az SQL-készleten végrehajtott összes lekérdezést naplózza a sys.dm_pdw_exec_requests. Ez a DMV tartalmazza az utolsó 10 000 végrehajtott lekérdezést. Az request_id egyedileg azonosítja az egyes lekérdezéseket, és ez a DMV elsődleges kulcsa. A request_id sorrendben van hozzárendelve minden új lekérdezéshez, és a QID előtaggal van ellátva, amely a lekérdezésazonosítót jelenti. Ha egy adott session_id bejelentkezéshez lekérdezi ezt a DMV-t, az egy adott bejelentkezés összes lekérdezését megjeleníti.

Megjegyzés

A tárolt eljárások több kérelemazonosítót használnak. A kérelemazonosítók szekvenciális sorrendben vannak hozzárendelve.

Az alábbi lépéseket követve megvizsgálhatja egy adott lekérdezés végrehajtási terveit és idejét.

1. lépés: A vizsgálni kívánt lekérdezés azonosítása

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

A fenti lekérdezési eredmények közül jegyezze fel a vizsgálni kívánt lekérdezés kérelemazonosítóját .

A Felfüggesztett állapotú lekérdezések számos aktív futó lekérdezés miatt várólistára helyezhetők. Ezek a lekérdezések a sys.dm_pdw_waits is megjelennek. Ebben az esetben keressen olyan várakozásokat, mint a UserConcurrencyResourceType. Az egyidejűségi korlátokról további információt a Memória- és egyidejűségi korlátok vagy erőforrásosztályok a számítási feladatok kezeléséhez című témakörben talál. A lekérdezések más okokból is várhatnak, például objektumzárolás esetén. Ha a lekérdezés erőforrásra vár, tekintse meg a jelen cikkben az erőforrásokra várakozó lekérdezések kivizsgálását ismertető cikket.

Ha egyszerűsíteni szeretné egy lekérdezés keresését az sys.dm_pdw_exec_requests táblában, a LABEL használatával rendeljen megjegyzést a lekérdezéshez, amely a nézetben kereshető meg 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. lépés: A lekérdezésterv vizsgálata

A kérésazonosító használatával kérje le a lekérdezés elosztott SQL-(DSQL-) csomagját 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;

Ha egy DSQL-csomag a vártnál több időt vesz igénybe, az ok lehet egy összetett terv, amely sok DSQL-lépést tartalmaz, vagy csak egy lépés hosszú időt vesz igénybe. Ha a terv több áthelyezési művelettel több lépésből áll, érdemes lehet optimalizálni a táblaeloszlásokat az adatáthelyezés csökkentése érdekében. A Táblaterjesztés című cikkből megtudhatja, hogy miért kell áthelyezni az adatokat a lekérdezések megoldásához. A cikk néhány olyan terjesztési stratégiát is ismertet, amely minimalizálja az adatáthelyezési folyamatokat.

Egy lépés további részleteinek vizsgálatához vizsgálja meg a operation_type hosszú ideig futó lekérdezési lépés oszlopát, és jegyezze fel a lépésindexet:

• SQL-műveletek (OnOperation, RemoteOperation, ReturnOperation) esetén folytassa a 3. LÉPÉSsel
• Az adatátviteli műveletek (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation) esetében folytassa a 4. LÉPÉSsel.

3. lépés: Sql vizsgálata az elosztott adatbázisokon

A kérelemazonosító és a lépésindex használatával kérje le a részleteket sys.dm_pdw_sql_requests, amely a lekérdezési lépés végrehajtási adatait tartalmazza az összes elosztott adatbázison.

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

Amikor a lekérdezési lépés fut, a DBCC PDW_SHOWEXECUTIONPLAN segítségével lekérheti a SQL Server becsült tervet az adott elosztáson futó lépés SQL Server tervgyorsítótárából.

-- 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. lépés: Az elosztott adatbázisokon végzett adatáthelyezési vizsgálat

A kérésazonosító és a lépésindex használatával lekérheti az egyes disztribúciókon futó adatáthelyezési lépés adatait 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;

• Ellenőrizze az total_elapsed_time oszlopban, hogy egy adott eloszlás jelentősen tovább tart-e, mint a többi adatáthelyezési folyamat.
• A hosszú ideig futó eloszlás esetében ellenőrizze az rows_processed oszlopban, hogy az adott eloszlásból áthelyezett sorok száma jelentősen nagyobb-e, mint mások. Ha igen, ez a megállapítás a mögöttes adatok eltérését jelezheti. Az adateltérés egyik oka a sok NULL értékkel rendelkező oszlop elosztása (amelynek sorai ugyanabban az eloszlásban fognak landolódni). A lassú lekérdezések elkerülése érdekében kerülje az ilyen típusú oszlopok eloszlását, vagy szűrje a lekérdezést az NULL-ek eltávolításához, ha lehetséges.

Ha a lekérdezés fut, a DBCC PDW_SHOWEXECUTIONPLAN használatával lekérheti a SQL Server becsült tervet a SQL Server terv gyorsítótárából az adott elosztáson belül jelenleg futó SQL-lépéshez.

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

Várakozó lekérdezések monitorozása

Ha azt észleli, hogy a lekérdezés nem halad, mert egy erőforrásra vár, az alábbi lekérdezés az összes olyan erőforrást megjeleníti, amelyekre a lekérdezés várakozik.

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

Ha a lekérdezés aktívan várakozik egy másik lekérdezés erőforrásaira, akkor az állapot AcquireResources lesz. Ha a lekérdezés rendelkezik az összes szükséges erőforrással, akkor az állapot Meg lesz adva.

Tempdb monitorozása

Az tempdb adatbázis a lekérdezés végrehajtása során a köztes eredmények tárolására szolgál. Az adatbázis magas kihasználtsága tempdb lassú lekérdezési teljesítményhez vezethet. Minden konfigurált DW100c esetében 399 GB tempdb terület van lefoglalva (a DW1000c 3,99 TB teljes tempdb területtel rendelkezik). Az alábbiakban tippeket talál a használat monitorozásához tempdb és a lekérdezések használatának csökkentéséhez tempdb .

A tempdb monitorozása nézetekkel

A használat monitorozásához tempdb először telepítse a microsoft.vw_sql_requests nézetet a Microsoft Toolkit for SQL-készletből. Ezután a következő lekérdezés végrehajtásával megtekintheti az tempdb összes végrehajtott lekérdezés csomópontonkénti használatát:

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

Megjegyzés

Az adatáthelyezési szolgáltatás a következőt tempdbhasználja: . Az adatáthelyezési funkció használatának tempdb csökkentése érdekében győződjön meg arról, hogy a tábla olyan terjesztési stratégiát használ, amely egyenletesen osztja el az adatokat. Az Azure Synapse SQL Distribution Advisor használatával javaslatokat kaphat a számítási feladatokhoz megfelelő disztribúciós módszerről. A Azure Synapse eszközkészlet használatával T-SQL-lekérdezésekkel monitorozhattempdb.

Ha olyan lekérdezése van, amely nagy mennyiségű memóriát használ, vagy a lefoglalásával tempdbkapcsolatos hibaüzenetet kapott, annak oka egy nagyon nagy CREATE TABLE AS SELECT (CTAS) vagy az INSERT SELECT utasítás futtatása lehet, amely meghiúsul a végső adatáthelyezési művelet során. Ez általában shuffleMove műveletként azonosítható az elosztott lekérdezéstervben közvetlenül a végső INSERT SELECT előtt. A ShuffleMove-műveletek figyeléséhez használja a sys.dm_pdw_request_steps .

A leggyakoribb kockázatcsökkentés az, hogy a CTAS vagy az INSERT SELECT utasítást több terhelési utasításra bontja, hogy az adatkötet ne lépje túl a 399 GB/100DWUc tempdb korlátot. A fürt nagyobb méretűre is skálázható, hogy növelje a tempdb rendelkezésre álló helyet.

A CTAS és az INSERT SELECT utasítások mellett a nem elegendő memóriával futó nagy, összetett lekérdezések is kiömlhetnek a lekérdezések sikertelenségéhez tempdb . Fontolja meg egy nagyobb erőforrásosztály futtatását, hogy elkerülje a fájlba tempdbvaló kiömlést.

Memória monitorozása

A memória lehet a lassú teljesítmény és a memóriakihasználtság kiváltó oka. Fontolja meg az adattárház skálázását, ha azt tapasztalja, hogy SQL Server memóriahasználat eléri annak korlátait a lekérdezés végrehajtása során.

A következő lekérdezés SQL Server memóriahasználatot és a csomópontonkénti memóriaterhelést adja vissza:

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

Tranzakciónapló méretének monitorozása

Az alábbi lekérdezés az egyes disztribúciók tranzakciónapló-méretét adja vissza. Ha az egyik naplófájl eléri a 160 GB-ot, fontolja meg a példány vertikális felskálázását vagy a tranzakció méretének korlátozását.

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

Tranzakciónapló-visszaállítás monitorozása

Ha a lekérdezések sikertelenek vagy hosszú ideig tartanak a folytatáshoz, ellenőrizheti és figyelheti, hogy vannak-e olyan tranzakciók, amelyek visszagördülnek.

-- 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-terhelés monitorozása

A következő lekérdezés hozzávetőleges becslést ad a terhelés előrehaladásáról. A lekérdezés csak a jelenleg feldolgozott fájlokat jeleníti meg.

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

Lekérdezésblokkolások monitorozása

Az alábbi lekérdezés a környezet 500 blokkolt lekérdezését tartalmazza.

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

Lekérdezés szövegének lekérése a várakozó és blokkoló lekérdezésekből

A következő lekérdezés a lekérdezés szövegét és azonosítóját biztosítja a várakozó és blokkoló lekérdezésekhez a könnyű hibaelhárítás érdekében.

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

Következő lépések

• A DMV-kkel kapcsolatos további információkért lásd: Rendszernézetek.