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.

Corriger un mauvais modèle de données

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, au prix d’un surcoût CPU.
  • 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 la méthode nodes(), en minuscules : les méthodes du type xml sont sensibles à la casse ;
    • Un paramètre en JSON, parsé avec OPENJSON (niveau de compatibilité 130 au minimum). Le paramètre est en NVARCHAR(MAX), ou, depuis SQL Server 2025, du type natif json ;
    • Un paramètre en chaîne de caractères, parsé avec STRING_SPLIT() (niveau de compatibilité 130 au minimum). L’ordre des éléments renvoyés n’est garanti qu’avec l’argument enable_ordinal et un tri sur la colonne ordinal ;
    • 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

  • Pour optimiser le parallélisme sur l’instance, Configurer les éléments suivants :

  • Depuis SQL Server 2016, vous pouvez configurer le degré de parallélisme par base de données, à l’aide d’une configuration scopée. L’option s’applique à la base courante, d’où le USE (ici sur la base de démonstration PachaDataFormation).

USE [PachaDataFormation];
GO

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

Manque d’index

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

Lorsque les problèmes se posent, retirez du cache le plan de la requête en cause avec DBCC FREEPROCCACHE (plan_handle), ou videz le cache de plans d’une seule base avec ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE. Est-ce que cela résout le problème ? Vous pouvez exécuter ces commandes au lieu de redémarrer un serveur SQL. DBCC FREEPROCCACHE sans argument vide le cache de toute l’instance et force la recompilation de toutes les requêtes : à éviter en production.

Vérifiez les statistiques avec cette 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 réel
    • 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, surtout si la base est à un niveau de compatibilité inférieur à 150 : l’optimiseur y estime alors une seule ligne. Depuis le niveau 150, la compilation différée des variables table utilise leur nombre réel de lignes.
Expertise SQL Server Besoin de services avec SQL Server ? Contactez-moi et parlons de votre projet