Dynamische beheerweergaven gebruiken om queryprestaties te identificeren en problemen op te lossen

  • 8 minuten

Dynamische beheerweergaven bieden een programmatische ervaring voor het bewaken van de activiteit van de Azure Synapse Analytics SQL-pool met behulp van de Transact-SQL-taal. Met de beschikbare weergaven kunt u niet alleen prestatieknelpunten oplossen en identificeren met de werkbelastingen die op uw systeem werken, maar ze worden ook gebruikt door andere services, zoals Azure Advisor, om aanbevelingen te doen over Azure Synapse Analytics.

Er zijn meer dan 90 dynamische beheerweergaven die query's kunnen uitvoeren op toegewezen SQL-pools om informatie over de volgende gebieden van de service op te halen:

  • Verbindingsgegevens en -activiteit
  • SQL-uitvoeringsaanvragen en -query's
  • Informatie over indexen en statistieken
  • Activiteit voor het blokkeren en vergrendelen van resources
  • Serviceactiviteit voor gegevensverplaatsing
  • Fouten

Hier volgt een voorbeeld van het bewaken van de uitvoering van query's van de SQL-pools van Azure Synapse Analytics. De eerste stap omvat het controleren van de verbindingen met de server eerst, voordat u de uitvoeringsactiviteit van de query controleert. 

Verbindingen bewaken

Alle aanmeldingen bij uw datawarehouse worden geregistreerd bij sys.dm_pdw_exec_sessions. 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();

Queryuitvoering bewaken

Alle query's die in de SQL-pool worden uitgevoerd, worden vastgelegd in sys.dm_pdw_exec_requests. 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, dat staat voor query-id. Als u een query uitvoert op deze DMV voor een bepaalde session_id worden alle query's voor een bepaalde aanmelding weergegeven.

Stap 1

De eerste stap is het identificeren van de query 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 wachtquery met een type 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 it a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Stap 2

Gebruik de aanvraag-id om het gedistribueerde SQL-plan (DSQL) voor query's 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, ziet u de operation_type kolom van de langlopende querystap en noteert u de stapindex:

  • Ga verder met stap 3 voor SQL-bewerkingen: OnOperation, RemoteOperation, ReturnOperation.
  • Ga verder met stap 4 voor bewerkingen voor gegevensverplaatsing: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.

Stap 3

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

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

Dynamische beheerweergaven (DMV) bevatten slechts 10.000 rijen met gegevens. Op intensief gebruikte systemen betekent dit dat gegevens die in deze tabel zijn opgeslagen, verloren kunnen gaan met uren of zelfs minuten als gegevens worden beheerd in een eerste in, eerste uit systeem. Als gevolg hiervan kunt u mogelijk zinvolle informatie verliezen die u kan helpen bij het vaststellen van problemen met queryprestaties op uw systeem. In dit geval moet u de Query Store gebruiken.

U kunt ook aanvullende aspecten van Azure Synapse SQL-pools bewaken, waaronder:

  • Bewakingswachttijden
  • Tempdb bewaken
  • Bewakingsgeheugen
  • Transactielogboek bewaken
  • PolyBase bewaken

U kunt hier informatie bekijken over het bewaken van deze gebieden