Partager via

Résoudre les problèmes liés à une requête lente sur un pool SQL dédié

  • Article

S’applique à : Azure Synapse Analytics

Cet article vous aide à identifier les raisons et à appliquer des atténuations pour les problèmes de performances courants liés aux requêtes sur un pool SQL dédié Azure Synapse Analytics.

Suivez les étapes pour résoudre le problème ou exécuter les étapes du notebook via Azure Data Studio. Les trois premières étapes vous guident tout au long de la collecte de données de télémétrie, qui décrit le cycle de vie d’une requête. Les références à la fin de l’article vous aident à analyser les opportunités potentielles trouvées dans les données collectées.

Note

Avant d’essayer d’ouvrir ce notebook, vérifiez qu’Azure Data Studio est installé sur votre ordinateur local. Pour l’installer, accédez à Découvrir comment installer Azure Data Studio.

Ouvrir un notebook dans Azure Data Studio

Important

La plupart des problèmes de performances signalés sont causés par :

  • Statistiques obsolètes
  • Index columnstore non sains (CCIs)

Pour gagner du temps de résolution des problèmes, assurez-vous que les statistiques sont créées et à jour et que les CCI ont été reconstruites.

Étape 1 : Identifier le request_id (aka QID)

La request_id requête lente est nécessaire pour rechercher des raisons potentielles pour une requête lente. Utilisez le script suivant comme point de départ pour identifier la requête à résoudre. Une fois la requête lente identifiée, notez la request_id valeur.

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [status] NOT IN ('Completed','Failed','Cancelled')
AND session_id <> session_id()
-- AND [label] = '<YourLabel>'
-- AND resource_allocation_percentage is not NULL
ORDER BY submit_time DESC;

-- Find top 10 longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

Pour mieux cibler les requêtes lentes, utilisez les conseils suivants lorsque vous exécutez le script :

  • Triez en fonction de l’une ou de l’autre submit_time DESC total_elapsed_time DESC des requêtes les plus longues présentes en haut du jeu de résultats.

  • Utilisez-les OPTION(LABEL='<YourLabel>') dans vos requêtes, puis filtrez la label colonne pour les identifier.

  • Envisagez de filtrer les QID qui n’ont pas de valeur lorsque resource_allocation_percentage vous savez que l’instruction cible est contenue dans un lot.

    Remarque : soyez prudent avec ce filtre, car il peut également filtrer certaines requêtes qui sont bloquées par d’autres sessions.

Étape 2 : Déterminer où la requête prend du temps

Exécutez le script suivant pour rechercher l’étape qui peut entraîner le problème de performances de la requête. Mettez à jour les variables dans le script avec les valeurs décrites dans le tableau suivant. Remplacez la @ShowActiveOnly valeur par 0 pour obtenir l’image complète du plan distribué. Notez les StepIndexvaleurs , Phaseet Description les valeurs de l’étape lente identifiée à partir du jeu de résultats.

Paramètre Description
@QID Valeur obtenue à l’étape request_id 1
@ShowActiveOnly 0 - Afficher toutes les étapes de la requête
1 - Afficher uniquement l’étape active actuellement		 
DECLARE @QID VARCHAR(16) = '<request_id>', @ShowActiveOnly BIT = 1; 
-- Retrieve session_id of QID
DECLARE @session_id VARCHAR(16) = (SELECT session_id FROM sys.dm_pdw_exec_requests WHERE request_id = @QID);
-- Blocked by Compilation or Resource Allocation (Concurrency)
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked waiting on '
       + MAX(CASE WHEN waiting.type = 'CompilationConcurrencyResourceType' THEN 'Compilation Concurrency'
                  WHEN waiting.type LIKE 'Shared-%' THEN ''
                  ELSE 'Resource Allocation (Concurrency)' END)
       + MAX(CASE WHEN waiting.type LIKE 'Shared-%' THEN ' for ' + REPLACE(waiting.type, 'Shared-', '')
             ELSE '' END) AS [Description],
   MAX(waiting.request_time) AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, MAX(waiting.request_time), GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount], NULL AS [TSQL]
FROM sys.dm_pdw_waits waiting
WHERE waiting.session_id = @session_id
   AND ([type] LIKE 'Shared-%' OR
      [type] in ('ConcurrencyResourceType', 'UserConcurrencyResourceType', 'CompilationConcurrencyResourceType'))
   AND [state] = 'Queued'
GROUP BY session_id 
-- Blocked by another query
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked by ' + blocking.session_id + ':' + blocking.request_id + ' when requesting ' + waiting.type + ' on '
   + QUOTENAME(waiting.object_type) + waiting.object_name AS [Description],
   waiting.request_time AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, waiting.request_time, GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount],
   COALESCE(blocking_exec_request.command, blocking_exec_request.command2) AS [TSQL]
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
WHERE waiting.session_id = @session_id AND waiting.state = 'Queued'
   AND blocking.state = 'Granted' AND waiting.type != 'Shared' 
-- Request Steps
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, step_index AS [StepIndex],
       'Execution' AS [Phase], operation_type + ' (' + location_type + ')' AS [Description],
       start_time AS [StartTime], end_time AS [EndTime],
       total_elapsed_time/1000.0 AS [Duration], [status] AS [Status],
       CASE WHEN estimated_rows > -1 THEN estimated_rows END AS [EstimatedRowCount],
       CASE WHEN row_count > -1 THEN row_count END AS [ActualRowCount],
       command AS [TSQL]
FROM sys.dm_pdw_request_steps
WHERE request_id = @QID
   AND [status] = CASE @ShowActiveOnly WHEN 1 THEN 'Running' ELSE [status] END
ORDER BY StepIndex;

Étape 3 : Passer en revue les détails de l’étape

Exécutez le script suivant pour passer en revue les détails de l’étape identifiée à l’étape précédente. Mettez à jour les variables dans le script avec les valeurs décrites dans le tableau suivant. Remplacez la @ShowActiveOnly valeur par 0 pour comparer tous les minutages de distribution. Notez la wait_type valeur de la distribution qui peut entraîner le problème de performances.

Paramètre Description
@QID Valeur obtenue à l’étape request_id 1
@StepIndex Valeur identifiée à l’étape StepIndex 2
@ShowActiveOnly 0 - Afficher toutes les distributions pour la valeur donnée StepIndex
1 - Afficher uniquement les distributions actuellement actives pour la valeur donnée StepIndex		 
DECLARE @QID VARCHAR(16) = '<request_id>', @StepIndex INT = <StepIndex>, @ShowActiveOnly BIT = 1;
WITH dists
AS (SELECT request_id, step_index, 'sys.dm_pdw_sql_requests' AS source_dmv,
       distribution_id, pdw_node_id, spid, 'NativeSQL' AS [type], [status],
       start_time, end_time, total_elapsed_time, row_count
    FROM sys.dm_pdw_sql_requests
    WHERE request_id = @QID AND step_index = @StepIndex
    UNION ALL
    SELECT request_id, step_index, 'sys.dm_pdw_dms_workers' AS source_dmv,
       distribution_id, pdw_node_id, sql_spid AS spid, [type],
       [status], start_time, end_time, total_elapsed_time, rows_processed as row_count
    FROM sys.dm_pdw_dms_workers
    WHERE request_id = @QID AND step_index = @StepIndex
   )
SELECT sr.step_index, sr.distribution_id, sr.pdw_node_id, sr.spid,
       sr.type, sr.status, sr.start_time, sr.end_time,
       sr.total_elapsed_time, sr.row_count, owt.wait_type, owt.wait_time
FROM dists sr
   LEFT JOIN sys.dm_pdw_nodes_exec_requests owt
      ON sr.pdw_node_id = owt.pdw_node_id
         AND sr.spid = owt.session_id
         AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests'
                 AND sr.status = 'Running') -- sys.dm_pdw_sql_requests status
              OR (sr.source_dmv = 'sys.dm_pdw_dms_requests'
                     AND sr.status not LIKE 'Step[CE]%')) -- sys.dm_pdw_dms_workers final statuses
WHERE sr.request_id = @QID
      AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests' AND sr.status =
               CASE WHEN @ShowActiveOnly = 1 THEN 'Running' ELSE sr.status END)
           OR (sr.source_dmv = 'sys.dm_pdw_dms_workers' AND sr.status NOT LIKE
                  CASE WHEN @ShowActiveOnly = 1 THEN 'Step[CE]%' ELSE '' END))
      AND sr.step_index = @StepIndex
ORDER BY distribution_id

Étape 4 : Diagnostiquer et atténuer

Problèmes de phase de compilation

Bloqué : concurrence de compilation

Les blocs de compilation d’accès concurrentiel se produisent rarement. Toutefois, si vous rencontrez ce type de bloc, cela signifie qu’un grand nombre de requêtes ont été envoyées dans un court délai et ont été mis en file d’attente pour commencer la compilation.

Atténuations

Réduisez le nombre de requêtes envoyées simultanément.


Bloqué : allocation de ressources

Étant bloqué pour l’allocation de ressources, votre requête attend d’être exécutée en fonction des points suivants :

  • Quantité de mémoire accordée en fonction de l’affectation de groupe de ressources ou de groupe de charge de travail associée à l’utilisateur.
  • Quantité de mémoire disponible sur le groupe de charge de travail ou système.
  • (Facultatif) L’importance du classifieur ou du groupe de charge de travail.

Atténuations

  • Attendez la fin de la session bloquante.
  • Évaluez le choix de la classe de ressources. Pour plus d’informations, consultez les limites d’accès concurrentiel.
  • Évaluez s’il est préférable de tuer la session de blocage.
Requête complexe ou syntaxe JOIN antérieure

Vous pouvez rencontrer une situation où les méthodes d’optimiseur de requête par défaut sont inefficaces, car la phase de compilation prend beaucoup de temps. Elle peut se produire si la requête :

  • Implique un grand nombre de jointures et/ou de sous-requêtes (requête complexe).
  • Utilise des jointures dans la FROM clause (et non des jointures de style ANSI-92).

Bien que ces scénarios soient atypiques, vous avez des options pour tenter de remplacer le comportement par défaut afin de réduire le temps nécessaire à l’optimiseur de requête pour choisir un plan.

Atténuations

  • Utilisez des jointures de style ANSI-92.
  • Ajouter des indicateurs de requête : OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Pour plus d’informations, consultez FORCE ORDER and Cardinality Estimation (SQL Server).
  • Décomposez la requête en plusieurs étapes moins complexes.
TABLE DROP OU TRUNCATE TABLE longue

Pour améliorer l’efficacité du temps d’exécution, les DROP TABLE instructions TRUNCATE TABLE reportent le nettoyage du stockage vers un processus en arrière-plan. Toutefois, si votre charge de travail effectue un nombre élevé d’instructions dans un délai court, il est possible que les métadonnées soient encombrées et provoque l’exécution lente des instructions suivantesTRUNCATE TABLEDROP/.DROP/TRUNCATE TABLE

Atténuations

Identifiez une fenêtre de maintenance, arrêtez toutes les charges de travail et exécutez DBCC SHRINKDATABASE pour forcer un nettoyage immédiat des tables précédemment supprimées ou tronquées.


Cci non saines (généralement)

L’intégrité de l’index columnstore en cluster médiocre nécessite des métadonnées supplémentaires, ce qui peut entraîner la prise de temps de l’optimiseur de requête pour déterminer un plan optimal. Pour éviter cette situation, assurez-vous que toutes vos CCI sont en bonne santé.

Atténuations

Évaluez et corrigez l’intégrité de l’index columnstore cluster dans un pool SQL dédié.


Retard de la création automatique des statistiques

L’option AUTO_CREATE_STATISTICSde création automatique de statistiques est ON par défaut pour vous assurer que l’optimiseur de requête peut prendre de bonnes décisions de plan distribué. Toutefois, le processus de création automatique lui-même peut prendre plus de temps que les exécutions suivantes de la même requête.

Atténuations

Si la première exécution de la requête nécessite constamment la création de statistiques, vous devez créer manuellement des statistiques avant l’exécution de la requête.


Créer automatiquement des délais d’expiration des statistiques

L’option AUTO_CREATE_STATISTICSde création automatique de statistiques est ON par défaut pour vous assurer que l’optimiseur de requête peut prendre de bonnes décisions de plan distribué. La création automatique de statistiques se produit en réponse à une instruction SELECT et a un seuil de 5 minutes à terminer. Si la taille des données et/ou le nombre de statistiques à créer nécessitent plus de 5 minutes, la création automatique de statistiques sera abandonnée afin que la requête puisse poursuivre l’exécution. L’échec de création des statistiques peut avoir un impact négatif sur la capacité de l’optimiseur de requête à générer un plan d’exécution distribué efficace, ce qui entraîne des performances de requête médiocres.

Atténuations

Créez manuellement les statistiques au lieu de vous appuyer sur la fonctionnalité de création automatique pour les tables/colonnes identifiées.

Problèmes de phase d’exécution

  • Utilisez le tableau suivant pour analyser le jeu de résultats à l’étape 2. Déterminez votre scénario et vérifiez la cause courante pour obtenir des informations détaillées et les étapes d’atténuation possibles.

    Scénario Cause courante
    EstimatedRowCount/ActualRowCount< 25% Estimations inexactes
    La Description valeur indique BroadcastMoveOperation et la requête fait référence à une table répliquée. Tables répliquées non mises en cache
    1. @ShowActiveOnly = 0
    2. Un nombre élevé ou inattendu d’étapes (step_index) est observé.
    3. Les types de données de colonnes de jointure ne sont pas identiques entre les tables.    		 Type/taille de données incompatibles
    1. La Description valeur indique HadoopBroadcastOperation, HadoopRoundRobinOperation ou HadoopShuffleOperation.
    2. La total_elapsed_time valeur d’une donnée step_index est incohérente entre les exécutions.    		 Requêtes de table externe ad hoc

  • Vérifiez la valeur obtenue à l’étape total_elapsed_time 3. S’il est beaucoup plus élevé dans quelques distributions dans une étape donnée, procédez comme suit :

    1. Vérifiez la distribution des données pour chaque table référencée dans le TSQL champ associé step_id en exécutant la commande suivante sur chacun d’eux :

      DBCC PDW_SHOWSPACEUSED(<table>);

    2. Si <la valeur> minimale des lignes/<la valeur>> maximale des lignes est 0,1, accédez à Data skew (stocké).

    3. Sinon, accédez à l’asymétrie des données en vol.

Estimations inexactes

Disposez de vos statistiques à jour pour vous assurer que l’optimiseur de requête génère un plan optimal. Lorsque le nombre de lignes estimé est nettement inférieur au nombre réel, les statistiques doivent être conservées.

Atténuations

Créer/mettre à jour des statistiques.


Tables répliquées non mises en cache

Si vous avez créé des tables répliquées et que vous ne parvenez pas à réchauffer correctement le cache de table répliqué, des performances inattendues médiocres entraînent des déplacements de données supplémentaires ou la création d’un plan distribué non optimal.

Atténuations

  • Réchauffez le cache répliqué après les opérations DML.
  • S’il existe des opérations DML fréquentes, remplacez la distribution de la table ROUND_ROBINpar .
Type/taille de données incompatibles

Lors de la jointure de tables, assurez-vous que le type de données et la taille des colonnes de jointure correspondent. Dans le cas contraire, les mouvements de données inutiles diminueront la disponibilité du processeur, des E/S et du trafic réseau vers le reste de la charge de travail.

Atténuations

Régénérez les tables pour corriger les colonnes de table associées qui n’ont pas de type et de taille de données identiques.


Requêtes de table externe ad hoc

Les requêtes sur des tables externes sont conçues avec l’intention de charger en bloc des données dans le pool SQL dédié. Les requêtes ad hoc sur des tables externes peuvent subir des durées variables en raison de facteurs externes, tels que les activités de conteneur de stockage simultanées.

Atténuations

Chargez d’abord les données dans le pool SQL dédié, puis interrogez les données chargées.


Asymétrie des données (stockée)

L’asymétrie des données signifie que les données ne sont pas distribuées uniformément entre les distributions. Chaque étape du plan distribué nécessite que toutes les distributions soient terminées avant de passer à l’étape suivante. Lorsque vos données sont asymétriques, le potentiel complet des ressources de traitement, telles que le processeur et les E/S, ne peut pas être atteint, ce qui entraîne des temps d’exécution plus lents.

Atténuations

Passez en revue nos conseils pour les tables distribuées afin d’aider votre choix d’une colonne de distribution plus appropriée.


Asymétrie des données en cours de vol

L’asymétrie des données en cours de vol est une variante du problème d’asymétrie des données (stocké). Mais ce n’est pas la distribution de données sur le disque qui est asymétrique. La nature du plan distribué pour des filtres particuliers ou des données groupées entraîne une ShuffleMoveOperation opération de type. Cette opération produit une sortie asymétrique à consommer en aval.

Atténuations

  • Assurez-vous que les statistiques sont créées et à jour.
  • Modifiez l’ordre de vos GROUP BY colonnes à diriger avec une colonne de cardinalité supérieure.
  • Créez des statistiques à plusieurs colonnes si les jointures couvrent plusieurs colonnes.
  • Ajoutez un indicateur OPTION(FORCE_ORDER) de requête à votre requête.
  • Refactorisez la requête.

Problèmes de type d’attente

Si aucun des problèmes courants ci-dessus ne s’applique à votre requête, les données d’étape 3 permettent de déterminer quels types d’attente (dans wait_type et wait_time) interfèrent avec le traitement des requêtes pour l’étape la plus longue. Il existe un grand nombre de types d’attente, et ils sont regroupés en catégories connexes en raison d’atténuations similaires. Procédez comme suit pour localiser la catégorie d’attente de votre étape de requête :

  1. Identifiez l’étape wait_type 3 qui prend le plus de temps.
  2. Recherchez le type d’attente dans la table de mappage des catégories d’attente et identifiez la catégorie d’attente dans laquelle elle est incluse.
  3. Développez la section relative à la catégorie d’attente dans la liste suivante pour les atténuations recommandées.
Compilation

Procédez comme suit pour atténuer les problèmes de type d’attente de la catégorie Compilation :

  1. Régénérer des index pour tous les objets impliqués dans la requête problématique.
  2. Mettez à jour les statistiques sur tous les objets impliqués dans la requête problématique.
  3. Testez à nouveau la requête problématique pour vérifier si le problème persiste.

Si le problème persiste, effectuez les actions suivantes :

  1. Créez un fichier .sql avec :

    SET QUERY_DIAGNOSTICS ON; <Your_SQL>; SET QUERY_DIAGNOSTICS OFF;

  2. Ouvrez une fenêtre d’invite de commandes et exécutez la commande suivante :

    sqlcmd −S <servername>.database.windows.net −d <databasename> −U <username> −G −I −i .\<sql_file_name>.sql −y0 −o .\<output_file_name>.txt

  3. Ouvrez <output_file_name>.txt dans un éditeur de texte. Recherchez et copiez les plans d’exécution au niveau de la distribution (lignes commençant par <ShowPlanXML>) de l’étape la plus longue identifiée à l’étape 2 dans des fichiers texte distincts avec une extension .sqlplan.

    Remarque : chaque étape du plan distribué aura généralement enregistré 60 plans d’exécution au niveau de la distribution. Vérifiez que vous préparez et comparez les plans d’exécution à partir de la même étape de plan distribué.

  4. La requête Étape 3 révèle fréquemment quelques distributions qui prennent beaucoup plus de temps que d’autres. Dans SQL Server Management Studio, comparez les plans d’exécution au niveau de la distribution (à partir des fichiers .sqlplan créés) d’une distribution de longue durée à une distribution rapide pour analyser les causes potentielles des différences.

Verrou, thread de travail
  • Envisagez de modifier des tables qui subissent des modifications fréquentes et petites pour utiliser un index de magasin de lignes au lieu de cci.
  • Effectuez un lot de vos modifications et mettez à jour la cible avec plus de lignes sur une base moins fréquente.
E/S de mémoire tampon, autres E/S de disque, E/S du journal tran

Cci non saines

Les ccis non saines contribuent à une augmentation des E/S, du processeur et de l’allocation de mémoire, ce qui, à son tour, a un impact négatif sur les performances de la requête. Pour atténuer ce problème, essayez l’une des méthodes suivantes :

Statistiques obsolètes

Les statistiques obsolètes peuvent entraîner la génération d’un plan distribué non optimisé, ce qui implique plus de déplacement de données que nécessaire. Le déplacement inutile des données augmente la charge de travail non seulement sur vos données au repos, mais également sur le tempdb. Étant donné que les e/s sont une ressource partagée pour l’ensemble des requêtes, l’impact sur les performances peut être considéré par l’ensemble de la charge de travail.

Pour remédier à cette situation, assurez-vous que toutes les statistiques sont à jour et qu’un plan de maintenance est en place pour les maintenir à jour pour les charges de travail des utilisateurs.

Charges de travail d’E/S lourdes

Votre charge de travail globale peut lire de grandes quantités de données. les pools de SQL de Synapse dédiés dimensionnent les ressources conformément aux DWU. Pour obtenir de meilleures performances, envisagez soit les deux :

PROCESSEUR, parallélisme
Scénario Limitation des risques
Intégrité ICC médiocre Évaluer et corriger l’intégrité de l’index columnstore en cluster dans un pool SQL dédié
Les requêtes utilisateur contiennent des transformations Déplacer toute la mise en forme et toute autre logique de transformation dans les processus ETL afin que les versions mises en forme soient stockées
Charge de travail mal hiérarchisée Implémenter l’isolation de la charge de travail
DWU insuffisant pour la charge de travail Envisagez d’augmenter les ressources de calcul

E/S réseau

Si le problème se produit pendant une RETURN opération à l’étape 2,

  • Réduisez le nombre de processus parallèles simultanés.
  • Effectuez un scale-out du processus le plus impacté vers un autre client.

Pour toutes les autres opérations de déplacement de données, il est probable que les problèmes réseau semblent internes au pool SQL dédié. Pour tenter d’atténuer rapidement ce problème, procédez comme suit :

  1. Mettez à l’échelle votre pool de SQL dédié vers DW100c
  2. Redimensionner au niveau de DWU souhaité
SQL CLR

Évitez l’utilisation fréquente de la FORMAT() fonction en implémentant une autre façon de transformer les données (par exemple, CONVERT() avec un style).