SQL Server

SQL Server Performance Tuning : une checklist pratique

Une méthode reproductible et fondée sur des preuves pour diagnostiquer les problèmes de performances SQL Server, des blocages et requêtes aux plans, statistiques et index.

Par JaviPublié 12 min de lecture

Écran de serveur affichant SQL

L’optimisation des performances est une investigation, pas une collection de correctifs. « La base de données est lente » décrit un symptôme, mais ne précise ni le périmètre, ni la période, ni la charge, ni la ressource concernée. Agir immédiatement en ajoutant un index, en vidant le cache des plans, en augmentant la mémoire ou en interrompant une session peut masquer les faits et créer un autre problème.

L’objectif pratique est de passer d’un symptôme général à un constat reproductible : quelle charge a ralenti, à quel moment, sur quoi elle a attendu, quel volume de travail elle a effectué, quel plan elle a utilisé et ce qui a changé. La checklist suivante suit cette progression.

  1. 1Système lentLe symptôme signalé, pas encore un diagnostic
  2. 2PérimètrePériode, base, application, utilisateurs touchés
  3. 3Requêtes activesCe qui s’exécute et ce qui le fait attendre
  4. 4BlocagesTrouver la tête de la chaîne de blocage
  5. 5Requêtes coûteusesCPU, lectures, écritures, durée et fréquence
  6. 6PlanPlan réel, lignes estimées et réelles
  7. 7Statistiques / indexEntrées de cardinalité et chemins d’accès
  8. 8ModificationUne variable comprise, avec une possibilité de retour arrière
  9. 9Nouvelle mesureComparer à la référence enregistrée
Parcours de diagnostic des problèmes de performances SQL Server. Chaque étape précise le périmètre avant toute modification, puis la boucle se termine par une nouvelle mesure.

Commencer par les faits, pas par des suppositions

Commencez par noter la période de l’incident, fuseau horaire compris. Si possible, identifiez la base, l’application, le login et l’hôte concernés. Demandez-vous si toutes les requêtes étaient lentes ou si une seule opération l’était, si la latence s’accompagnait d’échecs et si le problème est toujours en cours.

Établissez une petite référence comprenant le CPU, les lectures logiques, les lectures physiques, les écritures, la durée, les attentes et les blocages. Ces indicateurs répondent à des questions différentes. Un CPU élevé peut signaler un calcul coûteux ou une fréquence d’exécution excessive. Un nombre élevé de lectures suggère que la requête parcourt plus de pages que prévu. Les écritures peuvent venir de modifications, de TempDB ou de spills. Les attentes décrivent les situations où les workers n’ont pas pu progresser. Les blocages décrivent des dépendances entre sessions, pas nécessairement une requête lente prise isolément.

Conservez les éléments de preuve avant de redémarrer des services ou de vider des caches. Capturez l’historique Query Store, les requêtes actives, les informations d’attente et les graphiques de supervision utiles. Un redémarrage peut améliorer le symptôme tout en détruisant l’état nécessaire pour l’expliquer.

Examiner les requêtes actives

Pendant un incident en cours, sys.dm_exec_requests montre le travail en train de s’exécuter. Joignez cette vue à sys.dm_exec_sessions pour obtenir le contexte client et utilisez sys.dm_exec_sql_text pour récupérer le texte soumis.

Requête SQL: Requêtes activesT-SQL
SELECT
    r.session_id,
    s.login_name,
    s.host_name,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.blocking_session_id,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time,
    SUBSTRING(
        txt.text,
        (r.statement_start_offset / 2) + 1,
        CASE
            WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), txt.text))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    ) AS statement_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE r.session_id <> @@SPID
  AND s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;
T-SQL30 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Notes

  • lignes 16–23statement_start_offset et statement_end_offset sont des décalages en octets dans un texte Unicode, d’où la division par 2 ; -1 signifie que l’instruction va jusqu’à la fin du lot. Le résultat est l’instruction en cours d’exécution, pas le lot entier.
  • ligne 27CROSS APPLY écarte les requêtes sans handle SQL. Utilisez OUTER APPLY pour toutes les conserver.

La lecture de ces DMV nécessite les autorisations appropriées sur l’état du serveur. Considérez le texte des requêtes comme une donnée opérationnelle potentiellement sensible. Restreignez son accès et évitez de copier des valeurs de paramètres dans des tickets ou conversations non sécurisés.

La durée seule ne suffit pas. Une requête peut durer longtemps parce qu’elle est bloquée tout en consommant peu de CPU. Une autre peut se terminer rapidement, mais s’exécuter des milliers de fois. Si possible, capturez plusieurs échantillons afin de distinguer une requête qui persiste d’un instantané ponctuel.

Regrouper la charge

Le regroupement permet de voir si la pression vient d’une identité applicative, d’une base, d’un hôte ou d’un état de requête. C’est particulièrement utile lorsque le grand nombre de sessions individuelles rend la liste des requêtes actives difficile à lire.

Requête SQL: Charge par connexion, base de données et hôteT-SQL
SELECT
    s.login_name,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    r.status,
    COUNT(*) AS active_requests,
    SUM(r.cpu_time) AS cpu_time_ms,
    SUM(r.logical_reads) AS logical_reads,
    SUM(r.writes) AS writes
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = r.session_id
WHERE s.is_user_process = 1
GROUP BY
    s.login_name,
    DB_NAME(r.database_id),
    s.host_name,
    r.status
ORDER BY logical_reads DESC;
T-SQL19 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Notes

  • ligne 16GROUP BY répète l’expression DB_NAME() parce que T-SQL n’accepte pas un alias du SELECT dans GROUP BY.

Ces totaux correspondent aux requêtes actives au moment précis de l’exécution, et non à un historique complet. Utilisez-les pour orienter l’investigation, puis passez à Query Store ou à la télémétrie de supervision pour examiner une période plus longue.

Un regroupement par hôte peut révéler un déploiement qui a créé une concurrence excessive. Un regroupement par login peut montrer qu’un compte d’intégration domine l’activité du serveur. Un regroupement par base évite qu’une charge importante mais saine détourne l’attention de l’application concernée.

Rechercher les blocages

blocking_session_id identifie la session qui empêche une requête d’obtenir une ressource nécessaire. La session bloquée subit l’attente, mais le bloqueur n’est pas forcément fautif. Il peut effectuer un travail légitime dans une transaction que l’application laisse ouverte plus longtemps que nécessaire.

Reconstituez la chaîne de blocage. Trouvez la tête de chaîne et examinez son instruction actuelle ou la plus récente, l’état de sa transaction, l’application et sa durée. Recherchez des sessions en veille avec une transaction ouverte, des modifications importantes, un comportement d’isolation incompatible ou des opérations DDL.

Ne faites pas de « tuer le bloqueur » votre réaction par défaut. Le rollback d’une transaction volumineuse peut prendre du temps et augmenter la pression. L’interruption d’une opération métier peut également laisser l’application dans un état inattendu. Décidez avec le responsable applicatif, comprenez les effets du rollback et capturez d’abord les éléments utiles. Le correctif durable peut consister à raccourcir les transactions, accéder aux objets dans un ordre cohérent, améliorer un index, changer de stratégie d’isolation ou corriger la gestion des erreurs dans l’application.

Lire le plan d’exécution

Un plan d’exécution explique comment SQL Server a choisi d’accéder aux données et de les combiner. Lisez-le en tenant compte des mesures d’exécution et de l’objectif de la requête.

  • Les seeks peuvent être efficaces, mais un seek répété des millions de fois peut rester coûteux.
  • Les scans ne sont pas automatiquement mauvais : parcourir une petite table, ou presque toute une grande table, peut être le bon choix.
  • Les key lookups sont utiles pour quelques lignes, mais peuvent devenir coûteux lorsqu’ils sont répétés pour un résultat volumineux.
  • Les choix de jointure — nested loops, hash ou merge — dépendent du nombre de lignes, de leur ordre et des index disponibles.
  • Les spills indiquent qu’un opérateur a eu besoin de plus d’espace de travail que son memory grant ne lui en accordait, souvent à cause d’estimations erronées ou d’une mémoire disponible insuffisante.
  • Le parallélisme peut réduire la durée tout en augmentant le CPU total et la pression liée à la concurrence.

Comparez les lignes estimées aux lignes réelles pour les opérateurs importants. De gros écarts peuvent entraîner un mauvais ordre de jointure, un mauvais type de jointure ou des memory grants inadaptés. Vérifiez également la quantité de données lue par rapport à celle renvoyée. Une requête qui renvoie dix lignes après en avoir lu des millions mérite attention, même si son résultat final est petit.

Les pourcentages de coût des opérateurs sont des estimations de l’optimiseur dans le cadre de ce plan. Ce ne sont pas des pourcentages de temps d’exécution mesurés, et ils ne remplacent pas un profiler. Commencez par la durée réelle, le CPU, les lectures, les attentes, les spills et le nombre de lignes.

Vérifier les statistiques

Les statistiques décrivent la distribution des données à l’optimiseur. Si elles sont obsolètes, échantillonnées de façon inadaptée à une distribution inhabituelle ou absentes pour une expression importante, les estimations de cardinalité peuvent être erronées même si des index adaptés existent.

Vérifiez la date de mise à jour des statistiques pertinentes, le nombre de modifications intervenues et la capacité de l’histogramme à représenter les valeurs utilisées par la requête lente. La maintenance automatique des statistiques est utile, mais elle ne garantit pas que chaque jeu de données fortement déséquilibré ou évoluant rapidement soit parfaitement représenté à tout moment.

Mettez les statistiques à jour de manière réfléchie et mesurez le résultat. Une mise à jour générale sur une grande base consomme des ressources et peut provoquer des changements de plans sans rapport avec l’incident. Si une mise à jour corrige la requête, documentez l’estimation qui a changé et pourquoi, afin que la stratégie de maintenance puisse traiter la cause racine.

Examiner les index comme un ensemble

Un index est utile s’il réduit les lectures importantes ou facilite le tri, les jointures et les contraintes à un coût d’écriture et de stockage acceptable. Un index couvrant peut inclure les colonnes non clés dont une requête a besoin et éviter des lookups répétés, mais des index plus larges coûtent davantage à maintenir et à conserver en cache.

Examinez les index existants avant d’en ajouter un autre. Recherchez les clés qui se recouvrent, les définitions en double, les index qui ne diffèrent que par quelques colonnes incluses et ceux que chaque écriture maintient alors qu’ils sont rarement utilisés. Consolider peut être préférable à accumuler, mais les statistiques d’utilisation sont réinitialisées et ne couvrent pas nécessairement tous les cycles métier : ne supprimez donc pas un index sur la base d’une seule période d’observation courte.

Les DMV d’index manquants sont des indices, pas des consignes. Leurs suggestions reposent sur des événements d’optimisation individuels. Elles ne tiennent pas compte de l’ensemble de vos index, de la charge d’écriture, du budget de stockage ni des priorités opérationnelles. Plusieurs suggestions peuvent parfois être regroupées en une seule conception ; d’autres peuvent être rejetées si elles optimisent une requête peu fréquente au prix d’écritures trop coûteuses.

Pour un index envisagé, consignez la requête cible, le plan et le nombre de lectures de référence, l’impact attendu sur les écritures et le plan de retour arrière. Mesurez de nouveau après le déploiement.

Examiner la sensibilité aux paramètres

SQL Server peut compiler un plan à partir d’un jeu de paramètres puis le réutiliser pour d’autres valeurs. C’est généralement bénéfique. Cela devient problématique lorsque différentes valeurs nécessitent des stratégies sensiblement différentes — par exemple, lorsqu’un client ne possède que quelques lignes tandis qu’un autre représente une grande partie de la table.

Les symptômes courants sont une latence intermittente, une requête qui ralentit après une recompilation ou un déploiement, et plusieurs plans présentant des consommations de ressources très différentes. Query Store est utile parce qu’il montre l’historique des plans et les distributions d’exécution, au lieu de ne montrer que le plan actuellement en cache.

Parmi les réponses possibles : réécrire la requête, améliorer les statistiques, séparer des formes de charge réellement différentes, utiliser un hint approprié, ne recompiler que dans les cas utiles ou recourir aux fonctions de gestion des plans prises en charge par la version de SQL Server. Aucune n’est une solution universelle. OPTION (RECOMPILE) échange du travail de compilation contre un plan adapté aux paramètres ; forcer un plan peut stabiliser un cas tout en en dégradant un autre. Commencez par comprendre la distribution des données et les habitudes d’exécution.

Utiliser Query Store pour l’historique

Query Store conserve dans la base l’historique des requêtes, des plans et des statistiques d’exécution. C’est souvent le meilleur point de départ pour une régression qui n’est plus active. Vous pouvez comparer la durée d’exécution, le CPU, les lectures, le nombre d’exécutions et les changements de plan entre plusieurs périodes.

Servez-vous-en pour répondre à des questions précises : la requête a-t-elle reçu un nouveau plan ? Le nombre d’exécutions a-t-il augmenté ? La durée moyenne a-t-elle changé alors que le plan est resté stable ? Un plan est-il systématiquement meilleur pour la charge concernée, ou les performances varient-elles selon les valeurs des paramètres ?

Forcer un plan peut constituer une mesure temporaire utile le temps de préparer un correctif durable, mais il faut en surveiller les effets. La distribution des données et le schéma changent. Un plan autrefois performant peut devenir inadapté : notez donc la raison du forçage et définissez quand il sera réévalué.

Traiter les modifications volumineuses par lots

Les opérations UPDATE, DELETE et INSERT volumineuses peuvent générer beaucoup d’activité dans le journal des transactions, conserver les verrous longtemps et provoquer des blocages. Le traitement par lots limite le travail de chaque transaction et permet de suivre l’avancement.

Requête SQL: Supprimer par lotsT-SQL
DECLARE @rows int = 1;

WHILE @rows > 0
BEGIN
    DELETE TOP (5000)
    FROM dbo.EventHistory
    WHERE EventDate < DATEADD(year, -2, SYSUTCDATETIME());

    SET @rows = @@ROWCOUNT;

    -- Commit each batch when this loop is not already inside a wider transaction.
    -- Optionally add controlled pacing based on measured production impact.
END;
T-SQL13 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Notes

  • ligne 5TOP (5000) borne chaque DELETE : chaque transaction, ses verrous et son usage du journal restent réduits.
  • ligne 9Lisez @@ROWCOUNT juste après le DELETE : la boucle s’arrête quand un lot ne supprime plus rien.

La taille des lots est un paramètre opérationnel, pas une constante magique. Mesurez la génération du journal, la durée des verrous, le débit et l’impact sur les travaux concurrents. Vérifiez que le prédicat est pris en charge afin que chaque lot ne reparcoure pas sans cesse la même grande plage. Concevez le traitement pour qu’il puisse reprendre et vérifiez si des lignes peuvent changer pendant son exécution.

Très grandes tables

Avec des centaines de millions de lignes, la conception physique et les procédures opérationnelles doivent aller de pair. N’indexez que les chemins d’accès dont le coût de maintenance est justifié. Adaptez la maintenance des statistiques au rythme des modifications. Archivez les données lorsque les exigences de rétention et de requête le permettent. Préférez le traitement incrémentiel aux scans ou réécritures répétés de l’historique.

Le partitionnement peut faciliter la gestion du cycle de vie et l’élimination des partitions lorsque la clé correspond à ces opérations. Ce n’est pas un bouton d’accélération universel. Les requêtes qui ne filtrent pas sur la dimension de partitionnement n’y gagnent parfois rien, et chaque partition ajoute des contraintes de gestion.

Testez les opérations par lots avec une distribution de données et une concurrence réalistes. Un plan qui fonctionne sur un petit sous-ensemble restauré peut se comporter autrement lorsque les statistiques, les besoins en mémoire et la profondeur des index reflètent la table complète. La feuille de route SQL Server à grande échelle approfondira le partitionnement, les modifications volumineuses, les statistiques et les index.

Checklist de performances

  1. Confirmer la période, le périmètre et l’application concernés.
  2. Identifier la charge active et préserver les éléments utiles.
  3. Examiner les blocages et reconstituer toute la chaîne.
  4. Repérer les requêtes coûteuses à partir du CPU, des lectures, des écritures, de la durée et de la fréquence.
  5. Examiner les plans d’exécution réels et les mesures d’exécution.
  6. Comparer les estimations aux lignes réelles et vérifier les statistiques.
  7. Évaluer les index existants et envisagés comme un ensemble.
  8. Comparer l’historique dans Query Store et détecter les régressions.
  9. Modifier une seule variable comprise et consigner la référence.
  10. Mesurer après la modification et conserver une possibilité de retour arrière.

Conclusion

Une bonne optimisation réduit l’incertitude avant de modifier le système. Elle identifie la charge, suit les faits à travers les attentes et l’utilisation des ressources, explique le plan d’exécution et mesure le résultat d’une modification maîtrisée.

Des corrections faites au hasard accélèrent parfois une requête, mais elles ne créent pas une pratique opérationnelle reproductible. Une investigation documentée, oui. Conservez assez de contexte pour que la prochaine personne puisse comprendre non seulement ce qui a changé, mais aussi pourquoi cette modification était la plus limitée qui convenait à la charge observée.

Étiquettes

  • SQL Server
  • Performance
  • Query Store
  • Indexation