Performances constantes des requêtes dans le temps
Nous exécutons une charge d'application intensive (des milliers d'opérations/seconde) sur une base de données SQL Server avec pas mal de données. Certaines tables ont des milliards de lignes, plusieurs d'entre elles ont beaucoup d'insertions et de mises à jour.
Les performances de la base de données sont généralement assez bonnes, mais nous rencontrons périodiquement des problèmes de performances des requêtes ; des requêtes plutôt simples qui fonctionnaient très bien auparavant peuvent prendre 10 à 100 fois plus de temps tout d'un coup.
Cela semble être lié aux statistiques de table/index et à l'optimiseur de requête - la plupart du temps, une mise à jour des statistiques résoudra le problème, puis d'autres fois, une mise à jour des statistiques aggravera la situation (la réexécution de la mise à jour des statistiques résoudra alors généralement le problème finalement).
Ce qui semble se produire, c'est que l'optimiseur décide d'utiliser des index objectivement erronés pour certaines requêtes ; tout d'un coup, après avoir utilisé le bon pendant des jours et des semaines.
Ma question est : Pourquoi cela se produit-il et que pouvons-nous faire à ce sujet ?
Cette base de données fonctionne depuis des années avec essentiellement la même charge, à peu près les mêmes requêtes et le même nombre de mises à jour. Pour 99,995 % des requêtes, il ne devrait y avoir aucune raison de décider de stratégies d'index différentes au fil du temps, quelle que soit l'entrée (et, en effet, cela détruira complètement les performances des requêtes).
Comme indiqué ci-dessus, la mise à jour automatique des statistiques selon un calendrier génère souvent d'horribles problèmes - si l'échantillon de statistiques est faussé (ce qui semble se produire au moins 5 % des fois), nous nous retrouvons dans un monde de douleur.
Existe-t-il un moyen de dire à SQL Server (sur certaines tables) que l'histogramme et la densité des statistiques ne changeront pas avec le temps, veuillez donc continuer à utiliser le même plan de requête pour les requêtes impliquant cette table ? Si ce n'est pas le cas, comment pouvons-nous garantir un résultat prévisible de la mise à jour des statistiques dans le temps (en évitant le problème de statistiques biaisées décrit ci-dessus) ?
Pas de procédures stockées. Nous avons le contrôle sur le SQL, il peut donc potentiellement être modifié, mais c'est BEAUCOUP de code, il serait donc regrettable que nous devions modifier chaque requête (par exemple, ajouter une clause supplémentaire).
Une question de suivi : le reniflage de paramètres ne semble être pertinent que pour les procédures stockées, est-ce correct ?
Réponses
Je vous suggère de déterminer d'abord si ce sont les statistiques ou si son reniflement de paramètres vous fait du mal.
Indépendamment de ce qui précède, je vous suggère de lire l'article d'Erland sur le sujet.
Que faire à ce sujet est difficile à dire. Nous ne savons pas s'il s'agit de statistiques ou de reniflement.
Mais peut-être que l'ajout OPTIMIZE FORpeut être "la" solution. C'est moins cher que RECOMPILEpuisque vous n'avez pas à prendre le coup de production du plan à chaque exécution. Et cela vous donne de la prévisibilité. Ceci, bien sûr, suppose que vous n'avez pas le cas où les statistiques diffèrent tellement que la même entrée de paramètre donne des plans différents pour des raisons de statistiques.
Essayez d'identifier une requête. Voyez si vous avez un ou plusieurs plans pour la requête. Testez avec OPTIMIZE FORet/ou RECOMPILE. La seule option "globale" à l'échelle de la base de données dont vous disposez consiste à désactiver le reniflage des paramètres pour la base de données. Cela signifie que l'optimiseur optimise car il n'a aucune idée de la valeur. Tout cela et plus encore dans l'article d'Erland.
Le reniflage de paramètres ne s'applique pas uniquement aux procédures stockées. Cela s'applique également au SQL paramétré (généralement exécuté à l'aide de sp_executesql), qui est probablement beaucoup plus courant de nos jours que les procédures stockées.
Réponse générée à partir des commentaires
Vous pourriez obtenir les mauvais plans de requête en raison des mauvaises statistiques que vous avez obtenues après la mise à jour des statistiques. Mais vous pouvez également obtenir les mauvais plans de requête en raison de paramètres reniflant lorsque, après la mise à jour des statistiques, les premiers paramètres que votre requête a obtenus n'étaient pas comme d'habitude. Il est impossible de comprendre à partir de votre question lequel des problèmes est présenté. Essayez de recompiler le plan de requête lorsque la requête empire au lieu de mettre à jour les statistiques pour séparer les deux problèmes différents. – Denis Roubachkine
De nombreux facteurs peuvent entraîner une "reconstruction" du plan d'exécution. Cela expliquerait donc pourquoi cela fonctionne bien pendant un certain temps et commence soudainement à fonctionner lentement. Lorsque vous mettez à jour les statistiques, tous les plans d'exécution qui ont quelque chose à voir avec cet objet sont invalidés et cela entraînera la prochaine exécution à créer un nouveau plan. Selon les valeurs utilisées, cela peut résoudre ou non le problème (la plupart des valeurs le résolvent tandis que d'autres ne le font pas, ce qui explique pourquoi parfois cela fonctionne, parfois non).
Une autre façon de "corriger" le plan d'exécution serait d'utiliser Query Store (je pense que cela a commencé avec SQL Server 2016) et de "corriger" le plan à utiliser. Cela pourrait avoir quelques inconvénients si les données changent beaucoup (car SQL Server ne sera pas en mesure de produire un meilleur plan) mais cela pourrait résoudre ce genre de problème (j'ai une requête en cours d'exécution avec un plan d'exécution fixe depuis 2 ans maintenant et je n'ai pas 't eu le problème de reniflage de paramètres depuis). –Dominique Boucher