De workload van uw toegewezen SQL-pool van Azure Synapse Analytics bewaken met behulp van DMV's

Artikel
06/07/2023

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 WEERGEVEN of CONTROLEmachtiging nodig. Meestal is het verlenen van VIEW DATABASE STATE de voorkeursmachtiging, omdat deze veel beperkender 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. De 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();

Uitvoering van query's bewaken

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

Notitie

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

Hier volgen de stappen voor het onderzoeken van queryuitvoeringsplannen en -tijden voor een bepaalde query.

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 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 tabel sys.dm_pdw_exec_requests 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 uit veel stappen bestaat met verschillende verplaatsingsbewerkingen, kunt u overwegen om de tabeldistributies te optimaliseren om de gegevensverplaatsing 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:

Ga voor SQL-bewerkingen (OnOperation, RemoteOperation, ReturnOperation) verder met STAP 3
Voor gegevensverplaatsingsbewerkingen (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 bevat van de querystap voor alle gedistribueerde databases.

-- 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, kunnen 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 in de gedistribueerde databases onderzoeken

Gebruik de aanvraag-id en de stap-index om informatie op te halen over een stap voor gegevensverplaatsing die wordt uitgevoerd op elke distributie van 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.
Voor de langlopende distributie controleert u de rows_processed kolom om te zien of het aantal rijen dat van die distributie wordt verplaatst, aanzienlijk groter is dan andere. Als dat het zo is, kan deze bevinding duiden op scheeftrekken van uw onderliggende gegevens. Een oorzaak van gegevensverschil is het distribueren naar een kolom met veel NULL-waarden (waarvan de rijen allemaal in dezelfde verdeling terechtkomen). Voorkom trage query's door distributie op deze typen kolommen te vermijden 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);

Wachtende query's bewaken

Als u ontdekt dat uw query geen voortgang maakt omdat deze wacht op een resource, is hier een query die alle resources weergeeft 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 wacht op resources uit een andere query, is de status AcquireResources. Als de query alle vereiste resources bevat, wordt de status Verleend.

Tempdb bewaken

De tempdb database wordt gebruikt voor het opslaan van tussenliggende resultaten tijdens het uitvoeren van de query. Een hoog gebruik van de tempdb database kan leiden tot trage queryprestaties. Voor elke DW100c die is geconfigureerd, wordt 399 GB tempdb aan ruimte toegewezen (DW1000c zou 3,99 TB aan totale tempdb ruimte hebben). Hieronder staan tips voor het controleren van tempdb het gebruik en voor het verminderen van tempdb het gebruik in uw query's.

Tempdb met weergaven bewaken

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 voor alle uitgevoerde query's te bekijken:

-- 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 gegevens tijdens het verplaatsen van gegevens wilt verminderen, moet u ervoor zorgen dat uw tabel gebruikmaakt van tempdb een distributiestrategie die gegevens gelijkmatig distribueert. Gebruik Azure Synapse SQL Distribution Advisor om aanbevelingen te krijgen voor de distributiemethode die geschikt is voor uw workloads. Gebruik de Azure Synapse Toolkit om T-SQL-query's te bewakentempdb.

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

De meest voorkomende beperking is om uw CTAS- of INSERT SELECT-instructie op te splitsen in meerdere laadinstructies, zodat het gegevensvolume de limiet van 399 GB per 100DWUc tempdb niet overschrijdt. U kunt uw cluster ook groter schalen om de tempdb hoeveelheid ruimte die u hebt te vergroten.

Naast CTAS- en INSERT SELECT-instructies kunnen grote, complexe query's die worden uitgevoerd met onvoldoende geheugen, overlopen tempdb waardoor query's mislukken. Overweeg om uit te voeren met een grotere resourceklasse om te voorkomen dat er overloopt op tempdb.

Geheugen bewaken

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

De volgende query retourneert SQL Server geheugengebruik en geheugendruk 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 grootte van uw transactie 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)'

Terugdraaien van transactielogboek bewaken

Als uw query's mislukken of het lang duurt om door te gaan, kunt u controleren en controleren of er transacties 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. De query toont alleen bestanden 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 biedt 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 biedt 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.