MSSQL 2008 R1 - Pb plan d'execution Proceddure [Résolu]

[jeeps64]

Bonjour

J'ai une procédure avec plusieurs paramètres; ceux-ci sont utilisés dans la clause 'where' d'une requête en 'select'.

Quand je lance la procédure, elle a un plan tout pourri et dure longtemps
Lorsque je lance la requête 'select' directement en initialisant les variables, la requête tourne rapidement et prend un plan correct

En lançant des statistiques sur une des tables de la requête, cela a résolu le problème. Cette table contient une colonne date utilisée dans un des 'search argument' de la requête avec la date du jour passé en paramètre de la proc.

Donc avant que je lance les stats, l'histogramme de cette colonne date n'avait pas de ligne en date du jour.

J'ai l'impression que l'optimiseur se comporte différemment quand on lance un procédure et le select mais en mode ligne de commande


J'espère que c'est assez clair. Si je dois développer certains points, n'hésitez pas à me le dire

Jeeps64

[SQLpro]

Et oui... Généralement l'utilisation d'une requête complexe générée en dynamique n'est pas génial, sauf à utiliser certains trucs...
1) sp_executeSQL si possible et non EXEC ('...')
2) préparer le maximum de données dans des variables locales
3) spécifier les préfixes de schéma (dbo si vous n'en avez aucun autre) à tous les objets (tables, vues, procédures...)
4) créer les index adéquats (et pas seulement les statistiques) sur les colonnes les plus fréquemment recherchés (à lire sur le sujet : http://sqlpro.developpez.com/cours/quoi-indexer/)

Et si tout cela ne suffit pas, lire l'excellent article de Erland Sommarskog
http://blog.developpez.com/sqlpro/p9...s-de-requetes/

A +

[dbaffaleuf]

Envoyé par jeeps64

J'ai l'impression que l'optimiseur se comporte différemment quand on lance un procédure et le select mais en mode ligne de commande
Jeeps64

La plan de la proc est toujours réutilisé sauf cas d'invalidation de plan, et la mise à jour des stats en constitue un. Exécuter la requête en ad hoc permet certes d'obtenir un plan tout neuf, mais force une recompilation systématique dans la plupart des cas.

[jeeps64]

Bonjour

La procédure prend le bon plan si je redéclare les variables à l'intérieur de la procédure
Une 2ème possibilité est de recalculer les statisiques

Je vais voir à implémenter un job de stats après alimentation de la table posant problème.

Merci de vos conseils

Jeeps64

[mikedavem]

La procédure prend le bon plan si je redéclare les variables à l'intérieur de la procédure

Vous êtes sur un problème typique du parameter sniffing.
En affectant vos variables en interne à la procédure, vous forcez l'optimiseur à ne pas se baser sur les statistiques liés à l'exécution d'une valeur mais bien celles liés aux objets sous jacents à la procédure.

Une 2ème possibilité est de recalculer les statisiques

Oui mais attention ... car vous allez forcement entrainer des recompilations de votre procédure. A voir donc si le jeu en vaut la chandelle. Regardez si votre première solution vous donne un résultat satisfaisant dans le temps.

++

[SQLpro]

Envoyé par jeeps64

Je vais voir à implémenter un job de stats après alimentation de la table posant problème.

Mieux vaut dans ce cas désactiver les index avant insertion et les reconstruire après. Cela recalcule les stats.

A +

[jeeps64]

Bonjour

Merci de vos divers retour
Nous avons mis en place les stats après insertion (en fin de batch de nuit).
Il ne s'agit pas de tables volumineuses et donc l'update stats est rapide

A la première exécution, il recompile le plan ( mais le bon cette fois ci)

Merci encore

Jeeps64