Discussion :

[Ne0zenith]

Bonjour,

Je m'arrache les cheveux depuis quelques jours sur une requête dont les performances sont passées de bonnes (temps d'exécution de 10s) à très mauvaises (19mn) du jour au lendemain. Cette requête est contenue dans une vue et exécutée deux fois par jour.

Pour le contexte :
1 serveur SQL Server 2012, contenant deux bases Base1 et Base2.
La requête concernée est exécutée depuis Base2, sur 24 tables et vues contenues uniquement dans Base1. Nous n'avons un accès qu'en lecture sur Base1, d'où la création de cette vue sur Base2 plutôt que Base1.

Lorsque la requête est lancée depuis Base1 (Requête1), le temps d'exécution est tout à fait correct (autour de 10s). Lorsque la même requête est lancée depuis Base2 (Requête2) le temps d'exécution passe à 19mn.
Les plans d'exécution sont différents, lorsque j'applique le plan de la requête1 à la requête2, les temps de réponse sont tout à fait satisfaisant, mais je ne peux pas me contenter de cette solution car nous faisons fréquemment évoluer cette vue, donc le plan serait à revoir à chaque fois).

Pour les différences que je constate sur les plans, et qui je suppose pourraient expliquer les écarts constatés :
- le plan d'exécution de la Requête2 applique des prédicats sur des analyses d'index pour lesquelles il n'y a pas de prédicat sur le plan de la requête1,
- Sur ces mêmes analyses d'index, le nombre d'exécutions et le nombre de lignes lues explose littéralement : respectivement 1 contre 3052 et 163K contre 497M pour mon exemple le plus flagrant, entre Requête1 et Requête2. Les nombres estimés sont pourtant peu ou prou identiques,
- L'analyse du plan m'indique une estimation de cardinalité à 70 sur Base1, et 120 sur Base2.

Pourquoi les plans d'exécution sont-ils différents, alors qu'il s'agit de la même requête passée sur les mêmes tables et vues (donc des statistiques, clés, index,... identiques) ?

Merci d'avance de votre aide,
Etienne

[SQLpro]

Cela dépend du contexte d'exécution de chacune des requêtes. Une requête dans une vue et une requête ad hoc, même si le code est identique n'est pas executé de la même façon en fonction des paramètres de contexte qui différent entre les requêtes ad hoc et les requêtes intégrées dans un autre objet...

A +

[Ne0zenith]

Bonjour,

Et merci du retour.
A l'origine, ma requête est contenue dans une vue, mais pour faire mes tests j'ai lancé la même requête adhoc sur ces deux bases de données différentes.
Il s'agit donc bien de deux requêtes adhoc avec une syntaxe identique, pointant sur des objets identiques, mais lancées depuis deux bases de données différentes (bien que sur un seul et même serveur).

J'imagine que soit les paramètres de contexte sont différents sur les deux bases (et dans ce cas, comment les trouver ?), soit le fait d'aller chercher pour la Requête2 les données sur la Base1 l'empêche d'évaluer correctement le plan d'exécution à mettre en oeuvre ?

Etienne

[SQLpro]

le paramétrage des bases visible dans sys.databases... Il y a de nombreux paramètres en jeu, ne serai-ce que le niveau de compatibilité, mais aussi les paramètres ANSI, ARIABORTH....

A +

[Ne0zenith]

Merci,

Les paramètres dans sys.database ne sont pas identiques, mais n'ont pas évolué lors de la perte de performance.
La Base1 a un niveau de compatibilité de 110 et une collation "Latin1_General_CI_AS", tandis que la base2 a un niveau de compatibilité à 120 et une collation "SQL_Latin1_General_CP1_CI_AS". Tous les autres paramètres sont identiques, y compris ANSI et ARIABORTH (tous deux à 0).

En cherchant, j'ai vu qu'on pouvait utiliser OPTION (QUERYTRACEON 9481) pour revenir à une estimation de cardinalité à 70 sur Base2 seulement... En relançant la requête aujourd'hui (sans le OPTION (QUERYTRACEON 9481)), ses performances sont équivalentes à ce que j'avais il y a 3 semaines (10s), soit avant la grosse dégradation de perfs.
Je ne sais pas si je dois pleurer ou me réjouir... Plutôt pleurer j'imagine, car ça risque de se reproduire mais je n'ai plus moyen de réaliser des tests pour améliorer les performances

EDIT : J'avais une requête quasi identique qui a le même souci de perf, mais qui ne s'est pas réglé seul aujourd'hui.
J'ai ajouté à la fin de la requête OPTION (QUERYTRACEON 9481), ça règle le problème de perfs.

Donc deux nouvelles questions :
- Puis-je appliquer cette option dans une vue ou une procédure stockée ? Dans une vue à priori ce n'est pas possible...
- Y a-t-il possibilité de régler ce problème de performances qui semble donc bien lié au niveau de compatibilité différent entre mes deux bases uniquement pour mes 2 vues concernées (utilisées dans des procédures stockées) ? J'ai peur des effets des bord que pourrait engendrer ce changement sur ma base2...

Etienne

[SQLpro]

La première des choses est de vous fixer sur une version de rétrocompatibilité (si possible la dernière) et surtout de recalculer toutes les statistiques de toutes les tables en mode FULLSCAN.

En effet, le modèle de calcul des stats de l'optimiseur est adhérent à la compatibilité de la version de la base et en particulier avec le changement de l'estimateur de cardinalité de l'optimiseur survenu avec la version 2012.

Avec mon collègue Arian papillon, nous avions fait une session sur ce sujet lors des journées SQL Server :

A +

[Ne0zenith]

Merci de votre retour - désolé pour le délai de réponse, j'avais encore d'autres problèmes sur le feu et l'application du OPTION (QUERYTRACEON 9481) avait temporairement réglé le souci.

Ce n'est plus le cas aujourd'hui, ça tombe à nouveau en erreur depuis ce matin.
Je vais étudier tout ça, merci.

[Ne0zenith]

Bonjour,

Je viens donner des nouvelles :
J'ai bien suivi toute la vidéo et je comprends bien le principe des stats et de l'estimateur de cardinalité, cependant ça n'explique pas complètement que la même requête fonctionne sur la base1 mais pas la base2.

Voici donc où j'en suis :
- J'ai ajouté une procédure permettant de passer les statistiques juste avant le lancement de ma requête (les stats sont habituellement régulièrement calculées),
- J'ai ajouté à la procédure lançant la requête depuis la base2 le OPTION (QUERYTRACEON 9481) qui me permet de forcer le moteur à tourner avec l'estimateur de cardinalité SQL Server 2012 (comme sur ma base1)
Cela semble fonctionner depuis lundi, pour une requête mais pas pour la seconde.

Je ne peux pas changer le niveau de compatibilité de ma base1 car il s'agit d'un progiciel sur lequel je n'ai pas vraiment la main, et pour lequel je ne maîtrise pas les impacts que pourraient apporter ce changement.
Je pourrais changer celui de ma base2, mais il me faudrait alors m'assurer que cela n'engendre pas de régression sur le reste, d'où ma préférence pour cibler la requête grâce au OPTION (QUERYTRACEON 9481).

Les différents serveurs hébergeant mes différentes BDD devraient migrer prochainement vers un seul et même serveur, je tenterai alors d'uniformiser les niveaux de compatibilité des différentes bases (3 ou 4 différents !),
J'en profiterai également pour regrouper certaines BDD qui n'ont pas lieu d'être scindées, il faut juste que cela tienne jusqu'à la migration !

Merci pour votre aide,
Etienne

[SQLpro]

Si vous vous mettez en version 2022, le query store est activé automatiquement et vous pouvez forcer automatiquement le meilleur plan des requêtes...

https://learn.microsoft.com/en-us/sq...l-server-ver16

A +

[Ne0zenith]

Merci, mais ce sera du SQL Server 2019 et malheureusement je n'ai pas mon mot à dire là-dessus.
Je verrai s'il sera possible d'harmoniser les paramètres des deux bases qui resteront, et notamment le niveau de compatibilité pour le pousser sur du SQL Server 2019 (150) pour toutes deux.

Etienne