Monitorowanie obciążenia dedykowanej puli SQL usługi Azure Synapse Analytics przy użyciu widoków DMV

W tym artykule opisano sposób używania dynamicznych widoków zarządzania (DMV) do monitorowania obciążenia, w tym badania wykonywania zapytań w dedykowanej puli SQL.

Uprawnienia

Aby wykonać zapytanie dotyczące widoków DMV w tym artykule, musisz mieć uprawnienie WYŚWIETL STAN BAZY DANYCH lub KONTROLĘ . Zazwyczaj przyznanie widoku STANU BAZY danych jest preferowanym uprawnieniem, ponieważ jest znacznie bardziej restrykcyjne.

GRANT VIEW DATABASE STATE TO myuser;

Monitorowanie połączeń

Wszystkie logowania do magazynu danych są rejestrowane w sys.dm_pdw_exec_sessions. Ten dynamiczny widok zarządzania zawiera ostatnie 10 000 identyfikatorów logowania. Jest session_id to klucz podstawowy i jest przypisywany sekwencyjnie dla każdego nowego identyfikatora logowania.

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

Monitorowanie wykonywania zapytań

Wszystkie zapytania wykonywane w puli SQL są rejestrowane w sys.dm_pdw_exec_requests. Ten dynamiczny widok zarządzania zawiera wykonane ostatnie 10 000 zapytań. Unikatowo request_id identyfikuje każde zapytanie i jest kluczem podstawowym dla tego dynamicznego widoku zarządzania. Element request_id jest przypisywany sekwencyjnie dla każdego nowego zapytania i ma prefiks QID, co oznacza identyfikator zapytania. Wykonywanie zapytań dotyczących tego dynamicznego widoku zarządzania dla danego session_id elementu powoduje wyświetlenie wszystkich zapytań dotyczących danego identyfikatora logowania.

Uwaga

Procedury składowane używają wielu identyfikatorów żądań. Identyfikatory żądań są przypisywane w kolejności sekwencyjnej.

Poniżej przedstawiono kroki, które należy wykonać, aby zbadać plany wykonywania zapytań i czasy dla określonego zapytania.

Krok 1. Identyfikowanie zapytania, które chcesz zbadać

-- 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 powyższych wyników zapytania zanotuj identyfikator żądania zapytania, które chcesz zbadać.

Zapytania w stanie Wstrzymanie można kolejkować z powodu dużej liczby aktywnych uruchomionych zapytań. Te zapytania są również wyświetlane w sys.dm_pdw_waits. W takim przypadku wyszukaj oczekiwania, takie jak UserConcurrencyResourceType. Aby uzyskać informacje na temat limitów współbieżności, zobacz Limity pamięci i współbieżności lub Klasy zasobów na potrzeby zarządzania obciążeniami. Zapytania mogą również czekać z innych powodów, takich jak blokady obiektów. Jeśli zapytanie czeka na zasób, zobacz Badanie zapytań oczekujących na zasoby w dalszej części tego artykułu.

Aby uprościć wyszukiwanie zapytania w tabeli sys.dm_pdw_exec_requests, użyj etykiety, aby przypisać komentarz do zapytania, który można wyszukać w sys.dm_pdw_exec_requests widoku.

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

Krok 2. Badanie planu zapytania

Użyj identyfikatora żądania, aby pobrać rozproszony plan SQL (DSQL) zapytania 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;

Jeśli plan DSQL trwa dłużej niż oczekiwano, przyczyną może być złożony plan z wieloma krokami DSQL lub tylko jednym krokiem, który zajmuje dużo czasu. Jeśli plan obejmuje wiele kroków z kilkoma operacjami przenoszenia, rozważ optymalizację rozkładów tabel w celu zmniejszenia przenoszenia danych. W artykule Dotyczącym dystrybucji tabel wyjaśniono, dlaczego dane muszą zostać przeniesione, aby rozwiązać zapytanie. W tym artykule wyjaśniono również niektóre strategie dystrybucji w celu zminimalizowania przenoszenia danych.

Aby zbadać dalsze szczegóły dotyczące jednego kroku, sprawdź operation_type kolumnę długotrwałego kroku zapytania i zanotuj indeks kroku:

• W przypadku operacji SQL (OnOperation, RemoteOperation, ReturnOperation) wykonaj krok 3
• W przypadku operacji przenoszenia danych (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation), przejdź do kroku 4.

Krok 3. Badanie bazy danych SQL w rozproszonych bazach danych

Użyj identyfikatora żądania i indeksu kroku, aby pobrać szczegóły z sys.dm_pdw_sql_requests, który zawiera informacje o wykonaniu kroku zapytania we wszystkich rozproszonych bazach danych.

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

Po uruchomieniu kroku zapytania można użyć PDW_SHOWEXECUTIONPLAN DBCC do pobrania szacowanego planu programu SQL Server z pamięci podręcznej planu programu SQL Server dla kroku uruchomionego w określonej dystrybucji.

-- 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. Badanie przenoszenia danych w rozproszonych bazach danych

Użyj identyfikatora żądania i indeksu kroku, aby pobrać informacje o kroku przenoszenia danych uruchomionym w każdej dystrybucji 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;

• Sprawdź kolumnę, total_elapsed_time aby sprawdzić, czy określony rozkład trwa znacznie dłużej niż inne w przypadku przenoszenia danych.
• W przypadku długotrwałej dystrybucji sprawdź kolumnę rows_processed , aby sprawdzić, czy liczba wierszy przenoszonych z tej dystrybucji jest znacznie większa niż inne. Jeśli tak, to odkrycie może wskazywać na niesymetryczność danych bazowych. Jedną z przyczyn niesymetryczności danych jest dystrybucja w kolumnie z wieloma wartościami NULL (których wiersze będą wylądować w tym samym rozkładzie). Zapobiegaj powolnym zapytaniom, unikając dystrybucji tych typów kolumn lub filtrowania zapytania w celu wyeliminowania listy NUL, gdy jest to możliwe.

Jeśli zapytanie jest uruchomione, możesz użyć PDW_SHOWEXECUTIONPLAN DBCC, aby pobrać szacowany plan programu SQL Server z pamięci podręcznej planu programu SQL Server dla aktualnie uruchomionego kroku SQL w ramach określonej dystrybucji.

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

Monitorowanie oczekujących zapytań

Jeśli okaże się, że zapytanie nie postępuje, ponieważ oczekuje na zasób, oto zapytanie zawierające wszystkie zasoby, na które czeka zapytanie.

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

Jeśli zapytanie aktywnie czeka na zasoby z innego zapytania, stanem będzie AcquireResources. Jeśli zapytanie ma wszystkie wymagane zasoby, stan zostanie udzielony.

Monitorowanie bazy danych tempdb

Baza tempdb danych służy do przechowywania wyników pośrednich podczas wykonywania zapytania. Wysokie wykorzystanie tempdb bazy danych może prowadzić do spowolnienia wydajności zapytań. Dla każdego skonfigurowanego magazynu DW100c przydzielone jest 399 GB tempdb miejsca (DW1000c będzie mieć 3,99 TB całkowitego tempdb miejsca). Poniżej przedstawiono porady dotyczące monitorowania tempdb użycia i zmniejszania tempdb użycia zapytań.

Monitorowanie bazy danych tempdb za pomocą widoków

Aby monitorować tempdb użycie, najpierw zainstaluj widok microsoft.vw_sql_requests z puli Microsoft Toolkit for SQL. Następnie można wykonać następujące zapytanie, aby wyświetlić tempdb użycie na węzeł dla wszystkich wykonanych zapytań:

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

Uwaga

Przenoszenie danych używa elementu tempdb. Aby zmniejszyć użycie tempdb podczas przenoszenia danych, upewnij się, że tabela korzysta ze strategii dystrybucji, która równomiernie dystrybuuje dane. Skorzystaj z usługi Azure Synapse SQL Distribution Advisor , aby uzyskać zalecenia dotyczące metody rozdzielania dostosowanej do obciążeń. Użyj zestawu narzędzi Azure Synapse Toolkit do monitorowania tempdb przy użyciu zapytań języka T-SQL.

Jeśli masz zapytanie korzystające z dużej ilości pamięci lub odebrano komunikat o błędzie związany z alokacją tempdbelementu , może to być spowodowane bardzo dużym poleceniem CREATE TABLE AS SELECT (CTAS) lub instrukcją INSERT SELECT , która kończy się niepowodzeniem podczas ostatniej operacji przenoszenia danych. Zazwyczaj można to zidentyfikować jako operację ShuffleMove w rozproszonym planie zapytania bezpośrednio przed ostatecznym INSERT SELECT. Użyj sys.dm_pdw_request_steps do monitorowania operacji ShuffleMove.

Najczęstszym ograniczeniem ryzyka jest podzielenie instrukcji CTAS lub INSERT SELECT na wiele instrukcji ładowania, aby wolumin danych nie przekraczał limitu 399 GB na 100DWUc tempdb . Możesz również skalować klaster do większego rozmiaru, aby zwiększyć ilość tempdb miejsca.

Oprócz instrukcji CTAS i INSERT SELECT duże, złożone zapytania uruchomione z niewystarczającą ilością pamięci mogą powodować niepowodzenie tempdb zapytań. Rozważ uruchomienie z większą klasą zasobów, aby uniknąć rozlania do tempdbklasy .

Monitorowanie pamięci

Pamięć może być główną przyczyną problemów z niską wydajnością i brakiem pamięci. Rozważ skalowanie magazynu danych, jeśli okaże się, że użycie pamięci programu SQL Server osiągnie limity podczas wykonywania zapytania.

Następujące zapytanie zwraca użycie pamięci programu SQL Server i wykorzystanie pamięci na węzeł:

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

Monitorowanie rozmiaru dziennika transakcji

Poniższe zapytanie zwraca rozmiar dziennika transakcji w każdej dystrybucji. Jeśli jeden z plików dziennika osiągnie 160 GB, rozważ skalowanie wystąpienia w górę lub ograniczenie rozmiaru transakcji.

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

Monitorowanie wycofywania dziennika transakcji

Jeśli zapytania kończą się niepowodzeniem lub trwają długo, możesz sprawdzić i monitorować, czy istnieją transakcje wycofywania.

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

Monitorowanie obciążenia programu PolyBase

Poniższe zapytanie zawiera przybliżone oszacowanie postępu ładowania. Zapytanie pokazuje tylko pliki, które są obecnie przetwarzane.

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

Monitorowanie blokowania zapytań

Poniższe zapytanie zawiera 500 zablokowanych zapytań w środowisku.

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

Pobieranie tekstu zapytania z oczekujących i blokujących zapytań

Poniższe zapytanie zawiera tekst zapytania i identyfikator oczekujących i blokujących zapytania, które ułatwiają rozwiązywanie problemów.

-- 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,
       waiting.object_name AS Waiting_Object_Name,
       waiting.object_type AS Waiting_Object_Type,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       blocking.object_name AS Blocking_Object_Name,
       blocking.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;

Następne kroki

• Aby uzyskać więcej informacji na temat widoków DMV, zobacz Widoki systemowe.