Depuis SQL Server 2016, Microsoft propose dans toutes les éditions un outil d’analyse de performance qui peut sembler un petit peu obscure à premier abord.
Il ne remplace pas réellement le Data Collection, qui existe toujours, mais propose une approche moins intrusive que celui-ci. Il n’y a pas de jobs SSIS, il n’y a pas besoin d’une base de données dédiée et la consommation de ressource est moins élevée.
Le but de cet article n’est pas de documenter l’intégralité de son fonctionnement, mais de faire un focus sur la simplicité à forcer un plan d’exécution.
Pour rappel, on active le Query Store par base de données :
On fera attention au “Capture Mode” : avant SQL Server 2019, le mode par défaut est “all”. Cela veut dire que toutes les requêtes seront cataloguées, y compris si elles ne sont pas exécutées fréquemment et ont un temps de compilation/exécution ridicule. On peut vite arriver à des volumes capturés très elevés. Et le code pour exploiter les rapports utilise des UDF, se retrouve donc mono-threadé, et le temps d’affichage des rapports peut devenir abominablement long.
Le mode par défaut “all” a donc été remplacé par le “auto” depuis 2019. Celui-ci se focalise sur les requêtes exécutées fréquemment et avec des temps de compilation/exécution longs. Si vous ne savez pas où vous mettez les pieds : mettez le “auto” !
Maintenant, pour identifier les régressions, il y a plusieurs approches. Traditionnellement, on approche les problèmes de performances par le “Top Resource Consuming Queries”. Il est aussi possible de passer par le rapport “Regressed Queries” mais d’expérience il se révèle malheureusement moins efficace.
Dans l’exemple ci-dessus, on voit donc une requête ayant consommé beaucoup de temps CPU. Il y a 4 plans d’exécutions identifiés avec principalement 2 types. On a un type “rapide” et un type “lent”. On a beau regardé le détail du plan d’exécution “lent” le subtree cost n’est pas radicalement différent. Nous avons à faire un problème d’estimation de cardinalité dans ce plan, à l’intérieur d’un traitement batch.
Dans l’idéal, je préférerais résoudre le problème de cardinalité proprement, mais les délais de correction du code contrarient les besoins de l’équipe métier. Il reste donc une solution : fixer le plan d’exécution manuellement.
Pour cela, on choisi le plan d’exécution parmi les 4 qui nous intéresse, et on clique sur “force plan”
On voit donc que la requête dispose d’un identifiant, et son plan également. Cela nous permettra de la retrouver à l’avenir. On peut également cliquer sur le bouton “track query” pour la retrouver plus facilement à l’avenir dans le rapport “tracked queries”.
On voit également un tick sur le plan d’exécution dans le rapport des “top resource consuming queries” ainsi que la possibilité de cliquer sur “unforce plan”
Et voilà ! Nous avons forcé le plan d’exécution d’une requête problématique.
Le but désormais est plutôt de corriger la source de l’erreur de cardinalité car maintenir la connaissance de cette opération en phase d’exploitation n’est pas chose aisée. Il vaut mieux avoir un code et un modèle de données propre plutôt que de jouer sur les forçages de plan régulièrement.
Plus d’information sur le Query Store et ses bonnes pratiques :
Continuez votre lecture sur le blog :
- Identifier une requête consommatrice via le palmarès SQL Server AllDB (David Baffaleuf) [SQL Server]
- Les nouveautés de SQL Server 2022 (Vincent Delabre) [SQL Server]
- Formation Optimisation de requêtes (David Baffaleuf) [SQL Server]
- Retrouver la requête à l’origine d’une erreur 8623 “The query processor ran out of internal resources and could not produce a query plan” (David Baffaleuf) [SQL Server]
- Vidéo Query Store des JSS 2015 disponible (Benjamin VESAN) [SQL Server]