Requête lente sur SQL Server : 8 causes à examiner.

D'abord : calcul, lecture ou attente ?

Avant toute correction, on mesure : durée, temps processeur, nombre de lectures logiques (SET STATISTICS IO, TIME ON), plan d'exécution réel, et type d'attente de la requête. Query Store, disponible depuis SQL Server 2016, conserve l'historique des requêtes et des plans, et permet de comparer un plan d'hier à celui d'aujourd'hui.

Les huit causes les plus courantes

• 1. Plan d'exécution inadapté : régression après une mise à jour, un changement de version ou de niveau de compatibilité. Query Store permet de forcer provisoirement l'ancien plan.
• 2. Statistiques obsolètes : le moteur se trompe sur le nombre de lignes. On compare les lignes estimées et les lignes réelles dans le plan.
• 3. Index manquants ou inadaptés : lectures complètes de table. Les index suggérés ne sont pas à créer sans réflexion, car chaque index ralentit les écritures.
• 4. Parameter sniffing : le plan a été compilé pour des valeurs de paramètres atypiques et convient mal aux autres. Solutions : recompilation ciblée, plan forcé, réécriture.
• 5. Blocages et verrous : la requête n'est pas lente, elle attend. On identifie la session bloquante, les transactions trop longues et le niveau d'isolation.
• 6. Code T-SQL peu favorable à l'optimiseur : fonctions appliquées aux colonnes filtrées, conversions implicites de types, curseurs, SELECT *, jointures sur colonnes calculées.
• 7. Ressources insuffisantes : mémoire trop juste, tempdb saturée, stockage lent, processeur monopolisé par d'autres requêtes.
• 8. Volumétrie : une requête correcte pour dix mille lignes ne l'est plus pour cent millions. Archivage et partitionnement peuvent s'imposer.

Une méthode en cinq étapes

• Identifier les requêtes les plus coûteuses, avec Query Store ou les vues de gestion dynamique.
• Capturer le plan d'exécution réel et les statistiques d'E/S.
• Comparer lignes estimées et lignes réelles pour repérer l'erreur d'estimation.
• Corriger une seule chose à la fois : index, statistiques ou réécriture.
• Mesurer de nouveau, dans les mêmes conditions.

Les mauvais réflexes

• Ajouter de la mémoire ou des processeurs avant d'avoir regardé le code.
• Vider le cache de plans sur la production sans mesurer l'effet.
• Créer tous les index suggérés par le moteur.
• Reconstruire tous les index chaque nuit sans analyse.
• Ajouter NOLOCK partout : cela masque les blocages au prix de lectures incohérentes.