Optimiser les requêtes SQL

Méthodologie pour optimiser les requêtes dans SQL Server

Quelle méthodologie pouvez-vous utiliser pour optimiser vos requêtes Transact-SQL ?

Hypothèses

Les problèmes de performance de requêtes peuvent provenir de :

  1. Un mauvais modèle de données ;
  2. De requêtes mal écrites qui sont trop longues ;
  3. D’un comportement unitaire et non ensembliste (RBAR) ;
  4. D’exécution de triggers, de transactions trop longues;
  5. D’un manque d’index;
  6. D’attentes, par exemple sur le parallélisme des requêtes;
  7. De problèmes de plans d’exécution, de compilation et de parameter sniffing.

Diagnostiquer les requêtes mal écrites et trop longues

Il est difficile de corriger un mauvais modèle de données. Vous pouvez :

  • Utiliser la compression ROW ou PAGE pour diminuer la taille physique des tables et améliorer les IO.
  • Corriger petit à petit le schéma en utilisant des vues comme couche d’abstraction pour remodéliser les tables sans avoir à modifier le code client. Les mises à jour sur les vues peuvent être reproduites avec des déclencheurs INSTEAD OF.

Diagnostiquer les requêtes mal écrites et trop longues

  • Vérifiez les statistiques d’IO et de temps des requêtes
    • SET STATISTICS IO ON – affiche les statistiques d’IO par table accédée dans la requête. L’unité est la page (8 ko). Les statistiques sont séparées en IO logiques (dans le buffer) et IO physiques (lectures physiques et anticipées).
    • SET STATISTICS TIME ON – affiche les statistiques de temps par requête. Les statistiques sont séparées en temps de compilation (création du plan d’exécution) et d’exécution. Les temps sont le temps CPU et la durée totale de l’exécution, envoi des résultats au client compris.
  • Utilisez le Query Store pour identifier les requêtes les plus consommatrices.
  • Utilisez une session d’évènements étendus :
  • Requêtez la vue dm_exec_query_stats
  • Utilisez, en temps réel, la procédure sp_whoisactive
  • Si vous avez des procédures stockées, utilisez la vue dm_exec_procedure_stats

Comportement unitaire

Trop d’allers-retours entre le client et le serveur pose de grands problèmes de performances. On a appelé ce comportement RBAR : Row By Agonizing Row.

Le comportement souhaité est : obtenir le plus de données possibles dans une requête ensembliste, et effectuer le moins d’allers-retours possibles. Comme quand vous faites vos courses : Vous achetez tout en une fois au supermarché, et vous revenez chez vous avec toutes vos courses.

  • Cherchez, avec une session d’évènements étendus sans filtre de coût de requête, des appels répétitifs de requêtes semblables, par exemple des suites d’inserts ou des SELECT unitaires semblables avec des changements de paramètres.
  • Requêtez la vue dm_exec_query_stats en cherchant des requêtes peu consommatrices mais exécutées très souvent (triez sur la colonne execution_count)
  • Utilisez le Query Store pour identifier les requêtes les plus exécutées.

Pour cela :

  • Envoyez des tableaux à vos procédures stockées. Vous avez plusieurs méthodes :
    • Un paramètre XML, parsé avec NODES() ;
    • Un paramètre en JSON (NVARCHAR(MAX)), parsé avec OPENJSON ;
    • Un paramètre en chaîne de caractères, parsé avec STRING_SPLIT() ;
    • Un paramètre en type table ;
  • Vérifiez toujours le comportement de votre application avec une session d’évènements étendus sur le serveur.
  • En Entity Framework, assurez-vous que vous utilisez le bon loading.

Parallélisme

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;

Blocages dus à trop de verrouillage

Les blocages sont des attentes sur des verrous posés par d’autres sessions.

Y a-t-il des blocages ?

Exécution de triggers, de transactions trop longues

  • Identifiez le coût de vos déclencheurs à l’aide de la vue de diagnostic sys.dm_exec_triggers_stats
  • Cherchez les blocages éventuels avec les outils de la section précédente.
  • Si vous avez des procédures stockées, utilisez la vue dm_exec_procedure_stats
  • Si vous soupçonnez des déclencheurs, des procédures stockées ou des appels de fonctions, créez une session d’évènements étendus avec l’évènement sp_statement_completed – code pour Azure à adapter pour on prem

Manque d’index

Problèmes de plans d’exécution, de compilation et de parameter sniffing

Lorsque les problèmes se posent, videz le cache de plans à l’aide de la commande suivante : DBCC FREEPROCCACHE. Est-ce que cela résout le problème ? Vous pouvez exécuter cette commande au lieu de redémarrer un serveur SQL.

Vérifiez les statistiques : - requête de diagnostic

Erreur d’estimation de cardinalité

  • un signe classique : la requête se dégrade avec le temps. Planifiez un recalcul des statistiques plus régulièrement.
    • vérifiez les statistiques avec ce script, regardez les tables qui ont eu beaucoup de modifications.
    • regardez le plan d’exécution actuel
    • Utilisez plan explorer
    • lancez un recalcul avec UPDATE STATISTICS
    • Activez l’ancien moteur d’estimation de cardinalité.
    • ajoutez l’option dans la requête OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
    • regardez si on utilise des variables de type table

[Besoin de services avec SQL Server ? Contactez-moi]