Les ralentissements SQL Server ne se résument presque jamais à un manque de CPU. Ils naissent de l’interaction entre plans d’exécution, cardinalités, mémoire, I/O, tempdb, concurrence, maintenance et pratiques d’exploitation.
À retenir
- La baseline doit être construite autour des services métier.
- Query Store est une mémoire de production, pas un bouton magique.
- Statistiques, index et tempdb doivent être gouvernés comme un portefeuille.
- La résilience se démontre par la restauration et les exercices.
1. Partir du service, pas de l’instance
Une DSI doit d’abord distinguer les parcours métier, leurs volumes, leurs horaires, leurs SLO et leurs dépendances. Une moyenne d’instance peut paraître correcte alors qu’un parcours critique se dégrade à chaque clôture. La baseline doit donc combiner temps de réponse applicatifs, débits, taux d’erreur et métriques du moteur.
Le premier livrable utile est une carte reliant applications, bases, instances, jobs, interfaces et propriétaires. Sans cette vue, l’équipe optimise souvent ce qui est visible plutôt que ce qui crée le plus de risque.
2. Faire de Query Store une mémoire exploitable
Query Store conserve l’historique des requêtes, des plans et des statistiques d’exécution. Il permet d’identifier une régression, une requête fortement consommatrice ou une variabilité de plans. Mais sa valeur dépend de sa configuration : mode de capture, taille, rétention, nettoyage et contrôle de son état.
Une gouvernance sérieuse distingue l’observation, l’analyse et l’action. Forcer un plan peut stabiliser rapidement un service, mais ce choix doit être suivi : un plan adapté hier peut devenir pénalisant après une évolution de volumétrie ou de distribution.
3. Classer les attentes avant d’agir
Les wait statistics orientent le diagnostic vers CPU, stockage, verrous, réseau, journal, parallélisme ou synchronisation. Elles ne donnent pas à elles seules la cause, mais évitent les optimisations au hasard.
Le bon raisonnement croise attentes, périodes, requêtes, plans, métriques système et symptômes métier. Une attente élevée peut être normale pour un workload ; c’est sa relation avec la dégradation du service qui la rend actionnable.
4. Traiter les régressions de plans et la sensibilité aux paramètres
Des temps de réponse très variables peuvent provenir d’un plan choisi pour une valeur de paramètre puis réutilisé dans un contexte différent. Le diagnostic doit examiner les distributions, la compilation, les estimations de cardinalité et les changements de niveau de compatibilité.
Les fonctionnalités de traitement intelligent des requêtes peuvent réduire certains risques avec un effort limité, mais elles dépendent de la version et du niveau de compatibilité. Leur activation doit être testée sur un workload représentatif, avec capacité de retour arrière.
5. Gouverner les statistiques plutôt que les mettre à jour aveuglément
Les statistiques alimentent les estimations de l’optimiseur. Des statistiques obsolètes ou insuffisantes peuvent conduire à de mauvais algorithmes de jointure, à des allocations mémoire inadaptées ou à des plans instables.
À l’inverse, les mettre à jour trop fréquemment augmente le coût de collecte et les recompilations. La stratégie doit tenir compte de la volatilité, de la taille, des colonnes utilisées, des fenêtres de maintenance et des incidents réellement observés.
6. Optimiser le portefeuille d’index, pas chaque requête isolément
Un index manquant peut accélérer une lecture, mais il augmente aussi le coût des écritures, de la maintenance, du stockage et des sauvegardes. La sur-indexation peut être aussi coûteuse que l’absence d’index.
La DSI doit arbitrer à l’échelle du portefeuille : fréquence, criticité, gains attendus, coûts d’écriture, redondance, densité de pages et valeur des index filtrés ou columnstore selon le workload.
7. Considérer tempdb comme une ressource de service
tempdb supporte tris, hash, versions de lignes, objets temporaires et de nombreux traitements internes. Une requête incontrôlée peut affecter plusieurs applications partageant l’instance.
Il faut suivre l’espace, les allocations, les spills, la contention et les consommateurs. SQL Server 2025 introduit une gouvernance de l’espace tempdb via Resource Governor ; cette fonction peut améliorer la fiabilité si les groupes de workloads et les limites sont définis avec prudence.
8. Revoir mémoire, parallélisme et configuration dans leur contexte
Les valeurs de mémoire maximale, MAXDOP, cost threshold for parallelism, croissance des fichiers et nombre de fichiers tempdb ne doivent pas être appliquées comme une recette universelle. Elles doivent refléter l’hôte, la virtualisation, la concurrence, les bases et le profil des requêtes.
Chaque changement doit être corrélé à des indicateurs et documenté. Une amélioration locale peut déplacer le goulot vers le journal, le stockage ou un autre workload.
9. Réduire blocages et deadlocks par la conception
Le blocage est normal dans un moteur transactionnel ; il devient un problème lorsqu’il dépasse le niveau de service. Il faut identifier les chaînes de bloqueurs, les transactions longues, l’ordre d’accès aux objets, les niveaux d’isolation et les traitements qui conservent des verrous inutilement.
Le remède peut être applicatif, SQL, index, découpage de transactions ou isolation. Augmenter un timeout ne résout pas la cause et peut amplifier la saturation.
10. Remplacer la maintenance automatique par une maintenance fondée sur le besoin
Reconstruire tous les index ou mettre à jour toutes les statistiques selon un seuil fixe consomme des ressources, allonge les fenêtres et génère du journal. La stratégie doit tenir compte de la densité de pages, de l’usage, de la taille et du bénéfice attendu.
Les sauvegardes doivent être surveillées comme un flux critique : durée, débit, compression, rétention, chiffrement, vérification et capacité de restauration. SQL Server 2025 ajoute notamment l’algorithme ZSTD pour la compression des sauvegardes, avec des compromis CPU et temps à mesurer.
11. Mesurer la résilience au lieu de déclarer “Always On”
Un groupe de disponibilité améliore la disponibilité, mais ne protège pas contre toutes les erreurs logiques, suppressions, corruptions ou compromissions. Il faut surveiller les files d’envoi et de redo, la santé des réplicas, les bascules, la chaîne de sauvegarde et le comportement des clients.
Les RTO et RPO doivent être démontrés par des exercices. Une topologie complexe non testée peut augmenter le temps de décision lors d’un incident.
12. Installer une gouvernance de performance
La performance durable nécessite un rituel : incidents, régressions, top consommateurs, changements, capacité, risques et actions. Le tableau de bord doit relier métriques techniques et services métier.
Un bon backlog classe les actions par impact, risque, effort et réversibilité. Cela évite que l’équipe passe des semaines sur une optimisation élégante mais marginale.
Sources et références
Les choix d’architecture, de sécurité et de licence doivent être validés au regard de la version, de l’édition, du contrat et des exigences propres à l’organisation.