Övervaka arbetsbelastningen för din dedikerade SQL-pool i Azure Synapse Analytics med DMV:er

Artikel
08/28/2023

Den här artikeln beskriver hur du använder dynamiska hanteringsvyer (DMV:er) för att övervaka din arbetsbelastning, inklusive att undersöka frågekörning i en dedikerad SQL-pool.

Behörigheter

Om du vill köra frågor mot DMV:erna i den här artikeln behöver du antingen behörigheten VISA DATABASTILLSTÅND eller KONTROLL . Vanligtvis är beviljandet av VIEW DATABASE STATE den önskade behörigheten eftersom det är mycket mer restriktivt.

GRANT VIEW DATABASE STATE TO myuser;

Övervaka anslutningar

Alla inloggningar till informationslagret loggas till sys.dm_pdw_exec_sessions. Denna DMV innehåller de senaste 10 000 inloggningarna. session_id är den primära nyckeln och tilldelas sekventiellt för varje ny inloggning.

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

Övervaka frågekörning

Alla frågor som körs i SQL-poolen loggas till sys.dm_pdw_exec_requests. Denna DMV innehåller de senaste 10 000 körda frågorna. Unikt request_id identifierar varje fråga och är den primära nyckeln för denna DMV. request_id Tilldelas sekventiellt för varje ny fråga och är prefix med QID, som står för fråge-ID. När du frågar den här DMV:en om en viss fråga session_id visas alla frågor för en viss inloggning.

Anteckning

Lagrade procedurer använder flera begärande-ID:t. Begärande-ID:t tilldelas i sekventiell ordning.

Här följer steg för att undersöka frågekörningsplaner och tidpunkter för en viss fråga.

Steg 1: Identifiera den fråga som du vill undersöka

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

Observera fråge-ID:t för den fråga som du vill undersöka från föregående frågeresultat.

Frågor i inaktiverat tillstånd kan placeras i kö på grund av ett stort antal aktiva frågor som körs. Dessa frågor visas också i sys.dm_pdw_waits. I så fall letar du efter väntetider som UserConcurrencyResourceType. Information om samtidighetsgränser finns i Minnes- och samtidighetsgränser eller Resursklasser för arbetsbelastningshantering. Frågor kan också vänta på andra orsaker, till exempel för objektlås. Om frågan väntar på en resurs kan du läsa Undersöka frågor som väntar på resurser längre ned i den här artikeln.

Om du vill förenkla sökningen av en fråga i tabellen sys.dm_pdw_exec_requests använder du LABEL för att tilldela en kommentar till frågan, som kan sökas upp i sys.dm_pdw_exec_requests vyn.

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

Steg 2: Undersöka frågeplanen

Använd begärande-ID:t för att hämta frågans distribuerade SQL-plan (DSQL) från 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;

När en DSQL-plan tar längre tid än förväntat kan orsaken vara en komplex plan med många DSQL-steg eller bara ett steg som tar lång tid. Om planen är många steg med flera flyttåtgärder bör du överväga att optimera tabelldistributionerna för att minska dataförflyttningen. I artikeln Tabelldistribution förklaras varför data måste flyttas för att lösa en fråga. I artikeln förklaras även vissa distributionsstrategier för att minimera dataflytten.

Om du vill undersöka ytterligare information om ett enda steg kontrollerar du kolumnen i operation_type det långvariga frågesteget och noterar stegindexet:

För SQL-åtgärder (OnOperation, RemoteOperation, ReturnOperation) fortsätter du med STEG 3
För åtgärder för dataflytt (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation) fortsätter du med STEG 4.

Steg 3: Undersöka SQL på de distribuerade databaserna

Använd begärande-ID:t och stegindexet för att hämta information från sys.dm_pdw_sql_requests, som innehåller körningsinformation om frågesteget på alla distribuerade databaser.

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

När frågesteget körs kan DBCC-PDW_SHOWEXECUTIONPLAN användas för att hämta den SQL Server uppskattade planen från SQL Server plancache för steget som körs på en viss distribution.

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

Steg 4: Undersöka dataflytt på de distribuerade databaserna

Använd begärande-ID:t och stegindexet för att hämta information om ett dataförflyttningssteg som körs på varje distribution från 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;

Kontrollera kolumnen total_elapsed_time för att se om en viss distribution tar betydligt längre tid än andra för dataflytt.
För den långvariga distributionen rows_processed kontrollerar du kolumnen för att se om antalet rader som flyttas från den fördelningen är betydligt större än andra. I så fall kan den här sökningen tyda på skevhet i dina underliggande data. En orsak till datasnedvridning är att distribuera på en kolumn med många NULL-värden (vars rader alla hamnar i samma distribution). Förhindra långsamma frågor genom att undvika distribution på dessa typer av kolumner eller filtrera frågan för att eliminera NULL:er när det är möjligt.

Om frågan körs kan du använda DBCC-PDW_SHOWEXECUTIONPLAN för att hämta den SQL Server uppskattade planen från SQL Server plancache för det sql-steg som körs inom en viss distribution.

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

Övervaka väntande frågor

Om du upptäcker att frågan inte förlopp eftersom den väntar på en resurs, här är en fråga som visar alla resurser som en fråga väntar på.

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

Om frågan aktivt väntar på resurser från en annan fråga blir tillståndet AcquireResources. Om frågan har alla nödvändiga resurser kommer tillståndet att beviljas.

Övervaka tempdb

Databasen tempdb används för att lagra mellanliggande resultat under frågekörningen. Hög användning av tempdb databasen kan leda till långsamma frågeprestanda. För varje konfigurerad DW100c allokeras 399 GB tempdb utrymme (DW1000c skulle ha 3,99 TB totalt tempdb utrymme). Nedan visas tips för övervakning av tempdb användning och för att minska tempdb användningen i dina frågor.

Övervaka tempdb med vyer

Om du vill övervaka tempdb användningen installerar du först microsoft.vw_sql_requests-vyn från Microsoft Toolkit för SQL-poolen. Du kan sedan köra följande fråga för att se tempdb användningen per nod för alla körda frågor:

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

Anteckning

Dataflytt använder tempdb. Om du vill minska användningen av tempdb under dataflytten kontrollerar du att tabellen använder en distributionsstrategi som distribuerar data jämnt. Använd Azure Synapse SQL Distribution Advisor för att få rekommendationer om distrbution-metoden som passar dina arbetsbelastningar. Använd Azure Synapse Toolkit för att övervaka tempdb med hjälp av T-SQL-frågor.

Om du har en fråga som förbrukar en stor mängd minne eller har fått ett felmeddelande som rör allokeringen av tempdbkan det bero på att en mycket stor CREATE TABLE AS SELECT-instruktion (CTAS) eller INSERT SELECT-instruktionen körs som misslyckas i den slutliga dataflyttåtgärden. Detta kan vanligtvis identifieras som en ShuffleMove-åtgärd i den distribuerade frågeplanen precis före den slutliga INSERT SELECT. Använd sys.dm_pdw_request_steps för att övervaka ShuffleMove-åtgärder.

Den vanligaste lösningen är att dela upp CTAS- eller INSERT SELECT-instruktionen i flera inläsningsinstruktioner så att datavolymen inte överskrider gränsen på 399 GB per 100DWUc tempdb . Du kan också skala klustret till en större storlek för att öka hur mycket tempdb utrymme du har.

Förutom CTAS- och INSERT SELECT-instruktioner kan stora, komplexa frågor som körs med otillräckligt minne spilla över på tempdb så att frågor misslyckas. Överväg att köra med en större resursklass för att undvika spill till tempdb.

Övervaka minne

Minne kan vara rotorsaken till långsamma prestanda och minnesbrist. Överväg att skala informationslagret om du upptäcker SQL Server minnesanvändningen når sina gränser under frågekörningen.

Följande fråga returnerar SQL Server minnesanvändning och minnesbelastning per nod:

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

Övervaka storleken på transaktionsloggen

Följande fråga returnerar transaktionsloggstorleken för varje distribution. Om en av loggfilerna når 160 GB bör du överväga att skala upp din instans eller begränsa transaktionsstorleken.

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

Övervaka återställning av transaktionslogg

Om dina frågor misslyckas eller tar lång tid att fortsätta kan du kontrollera och övervaka om du har några transaktioner som återställs.

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

Övervaka PolyBase-inläsning

Följande fråga ger en ungefärlig uppskattning av belastningens förlopp. Frågan visar endast filer som för närvarande bearbetas.

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

Övervaka frågeblockering

Följande fråga innehåller de 500 mest blockerade frågorna i miljön.

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

Hämta frågetext från väntande och blockerande frågor

Följande fråga innehåller frågetexten och identifieraren för väntande och blockerande frågor som enkelt kan felsökas.

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

Nästa steg

Mer information om DMV:er finns i Systemvyer.