Uw toegewezen SQL-poolworkload van Azure Synapse Analytics bewaken met behulp van DMV's

Artikel
10/25/2024

In dit artikel wordt beschreven hoe u dynamische beheerweergaven (DMV's) gebruikt om uw workload te bewaken, inclusief het onderzoeken van de uitvoering van query's in een toegewezen SQL-pool.

Machtigingen

Als u een query wilt uitvoeren op de DMV's in dit artikel, hebt u de machtiging DATABASESTATUS of CONTROL WEERGEVEN nodig. Meestal is het verlenen van VIEW DATABASE STATE de voorkeursmachtiging omdat deze veel restrictiever is.

GRANT VIEW DATABASE STATE TO myuser;

Verbindingen bewaken

Alle aanmeldingen bij uw datawarehouse worden geregistreerd bij sys.dm_pdw_exec_sessions. Deze DMV bevat de laatste 10.000 aanmeldingen. Dit session_id is de primaire sleutel en wordt opeenvolgend toegewezen voor elke nieuwe aanmelding.

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

Queryuitvoering bewaken

Alle query's die in de SQL-pool worden uitgevoerd, worden vastgelegd in sys.dm_pdw_exec_requests. Deze DMV bevat de laatste 10.000 uitgevoerde query's. De request_id unieke identificatie van elke query en is de primaire sleutel voor deze DMV. De request_id opdracht wordt opeenvolgend toegewezen voor elke nieuwe query en wordt voorafgegaan door QID, dat staat voor query-id. Als u een query uitvoert op deze DMV voor een bepaalde session_id aanvraag, worden alle query's voor een bepaalde aanmelding weergegeven.

Notitie

Opgeslagen procedures maken gebruik van meerdere aanvraag-id's. Aanvraag-id's worden in opeenvolgende volgorde toegewezen.

Hier volgen de stappen die u moet volgen om queryuitvoeringsplannen en -tijden voor een bepaalde query te onderzoeken.

Stap 1: De query identificeren die u wilt onderzoeken

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

Noteer in de voorgaande queryresultaten de aanvraag-id van de query die u wilt onderzoeken.

Query's met de status Onderbroken kunnen in de wachtrij worden geplaatst vanwege een groot aantal actieve actieve query's. Deze query's worden ook weergegeven in de sys.dm_pdw_waits. Zoek in dat geval naar wachttijden zoals UserConcurrencyResourceType. Zie Geheugen- en gelijktijdigheidslimieten of resourceklassen voor workloadbeheer voor informatie over gelijktijdigheidslimieten . Query's kunnen ook wachten op andere redenen, zoals voor objectvergrendelingen. Als uw query wacht op een resource, raadpleegt u Query's onderzoeken die wachten op resources verderop in dit artikel.

Als u het opzoeken van een query in de sys.dm_pdw_exec_requests tabel wilt vereenvoudigen, gebruikt u LABEL om een opmerking toe te wijzen aan uw query, die in de sys.dm_pdw_exec_requests weergave kan worden opgezoekd.

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

Stap 2: Het queryplan onderzoeken

Gebruik de aanvraag-id om het gedistribueerde SQL-plan (DSQL) van de query op te halen uit 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;

Wanneer een DSQL-plan langer duurt dan verwacht, kan de oorzaak een complex plan zijn met veel DSQL-stappen of slechts één stap die lang duurt. Als het plan veel stappen met verschillende verplaatsingsbewerkingen bevat, kunt u overwegen om de tabeldistributies te optimaliseren om de verplaatsing van gegevens te verminderen. In het artikel Tabeldistributie wordt uitgelegd waarom gegevens moeten worden verplaatst om een query op te lossen. In het artikel worden ook enkele distributiestrategieën uitgelegd om gegevensverplaatsing te minimaliseren.

Als u meer informatie over één stap wilt onderzoeken, inspecteert u de operation_type kolom van de langlopende querystap en noteert u de stapindex:

Voor SQL-bewerkingen (OnOperation, RemoteOperation, ReturnOperation) gaat u verder met STAP 3
Voor bewerkingen voor gegevensverplaatsing (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation), gaat u verder met STAP 4.

Stap 3: SQL onderzoeken op de gedistribueerde databases

Gebruik de aanvraag-id en de stapindex om details op te halen uit sys.dm_pdw_sql_requests, die uitvoeringsinformatie van de querystap op alle gedistribueerde databases bevat.

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

Wanneer de querystap wordt uitgevoerd, kan DBCC-PDW_SHOWEXECUTIONPLAN worden gebruikt om het geschatte SQL Server-plan op te halen uit de SQL Server-plancache voor de stap die wordt uitgevoerd op een bepaalde distributie.

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

Stap 4: Gegevensverplaatsing op de gedistribueerde databases onderzoeken

Gebruik de aanvraag-id en de stapindex om informatie op te halen over een stap voor gegevensverplaatsing die wordt uitgevoerd op elke distributie uit 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;

Controleer de total_elapsed_time kolom om te zien of een bepaalde distributie aanzienlijk langer duurt dan andere voor gegevensverplaatsing.
Controleer voor de langdurige distributie de rows_processed kolom om te zien of het aantal rijen dat van die distributie wordt verplaatst aanzienlijk groter is dan andere. Zo ja, dan kan deze bevindingen duiden op scheeftrekken van uw onderliggende gegevens. Eén oorzaak voor scheeftrekken van gegevens is het distribueren van een kolom met veel NULL-waarden (waarvan rijen allemaal in dezelfde verdeling terechtkomen). Voorkom trage query's door distributie van deze typen kolommen te voorkomen of door uw query te filteren om NULL's indien mogelijk te elimineren.

Als de query wordt uitgevoerd, kunt u DBCC PDW_SHOWEXECUTIONPLAN gebruiken om het geschatte SQL Server-plan op te halen uit de SQL Server-plancache voor de momenteel actieve SQL-stap binnen een bepaalde distributie.

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

Wachtquery's bewaken

Als u ontdekt dat uw query geen voortgang maakt omdat deze wacht op een resource, ziet u hier een query waarin alle resources worden weergegeven waarop een query wacht.

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

Als de query actief op resources van een andere query wacht, wordt de status AcquireResources. Als de query alle vereiste resources heeft, wordt de status Verleend.

Tempdb bewaken

De tempdb database wordt gebruikt voor het opslaan van tussenliggende resultaten tijdens het uitvoeren van query's. Hoog gebruik van de tempdb database kan leiden tot trage queryprestaties. Voor elke geconfigureerde DW100c wordt 399 GB tempdb ruimte toegewezen (DW1000c zou 3,99 TB aan totale tempdb ruimte hebben). Hieronder ziet u tips voor het bewaken tempdb van het gebruik en voor het verlagen van tempdb het gebruik in uw query's.

Tempdb bewaken met weergaven

Als u het gebruik wilt controleren tempdb , installeert u eerst de weergave microsoft.vw_sql_requests vanuit de Microsoft Toolkit voor SQL-pool. Vervolgens kunt u de volgende query uitvoeren om het tempdb gebruik per knooppunt te bekijken voor alle uitgevoerde query's:

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

Notitie

Gegevensverplaatsing maakt gebruik van de tempdb. Als u het gebruik van tempdb gegevensverplaatsing wilt verminderen, moet u ervoor zorgen dat uw tabel een distributiestrategie gebruikt die gegevens gelijkmatig distribueert. Gebruik Azure Synapse SQL Distribution Advisor om aanbevelingen te krijgen voor de distrbution-methode die geschikt is voor uw workloads. Gebruik de Azure Synapse Toolkit om te controleren tempdb met behulp van T-SQL-query's.

Als u een query hebt die een grote hoeveelheid geheugen verbruikt of een foutbericht hebt ontvangen met betrekking tot de toewijzing, tempdbkan dit worden veroorzaakt door een zeer grote CREATE TABLE AS SELECT (CTAS) of INSERT SELECT-instructie die mislukt in de laatste bewerking voor gegevensverplaatsing. Dit kan meestal worden geïdentificeerd als een ShuffleMove-bewerking in het gedistribueerde queryplan vlak voor de laatste INSERT SELECT. Gebruik sys.dm_pdw_request_steps om ShuffleMove-bewerkingen te bewaken.

De meest voorkomende beperking is het verbreken van uw CTAS- of INSERT SELECT-instructie in meerdere belastingsinstructies, zodat het gegevensvolume de limiet van 399 GB per 100DWUc-limiet tempdb niet overschrijdt. U kunt uw cluster ook schalen naar een grotere grootte om te vergroten hoeveel tempdb ruimte u hebt.

Naast CTAS- en INSERT SELECT-instructies kunnen grote, complexe query's die worden uitgevoerd met onvoldoende geheugen, overlopen waardoor tempdb query's mislukken. Overweeg om te worden uitgevoerd met een grotere resourceklasse om overloop in tempdbte voorkomen.

Geheugen bewaken

Geheugen kan de hoofdoorzaak zijn voor trage prestaties en geheugenproblemen. Overweeg om uw datawarehouse te schalen als u merkt dat het geheugengebruik van SQL Server de limieten bereikt tijdens het uitvoeren van query's.

De volgende query retourneert geheugengebruik en geheugendruk van SQL Server per knooppunt:

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

Grootte van transactielogboek bewaken

De volgende query retourneert de grootte van het transactielogboek voor elke distributie. Als een van de logboekbestanden 160 GB bereikt, kunt u overwegen uw exemplaar omhoog te schalen of de transactiegrootte te beperken.

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

Het terugdraaien van transactielogboeken bewaken

Als uw query's mislukken of lange tijd in beslag nemen, kunt u controleren of er transacties zijn die worden teruggezet.

-- 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-belasting bewaken

De volgende query biedt een geschatte schatting van de voortgang van uw belasting. In de query worden alleen bestanden weergegeven die momenteel worden verwerkt.

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

Queryblokkeringen bewaken

De volgende query bevat de top 500 geblokkeerde query's in de omgeving.

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

Querytekst ophalen uit wachtende en blokkerende query's

De volgende query bevat de querytekst en id voor de wachtende en blokkerende query's om eenvoudig problemen op te lossen.

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

Volgende stappen

Zie Systeemweergaven voor meer informatie over DMV's.

Delen via