sp_executesql et plan d'exécution

Bonjour,

J'ai dû faire face à un problème de performance très pénible aujourd'hui. Nous avons une appli .Net qui s'est mise à partir complètement en sucette à cause d'une requête.

Mon analyse c'est que les plans d'exécution ont changé et que la-dite requête, très mal écrite, a mal supporté le changement ;)

J'ai donc proposé une optimisation qui fonctionnait très bien... sauf dans l'appli. Finalement, on a mis le doigt sur le problème : cette requête est exécutée via sp_executesql avec des paramètres. Je pense que la chaîne de caractères envoyées ne change pas, du fait des paramètres, et le plan d'exécution n'est donc pas recalculé. En changeant juste le type des paramètres (nvarchar(32) -> nvarchar(10)) ça a suffit à forcer le calcul d'un nouveau plan et tout va bien maintenant.

Ma question est donc la suivante :
Considérant une requête du genre :

Code:

---

exec N' SELECT name FROM matable WHERE id = @ids ',N'@ids nvarchar(14)',@ids=N'2ZYB'

---

Comment être sûr, qu'elle aura le même comportement que :

Code:

---

SELECT name FROM matable WHERE id = N'2ZYB'

---

A défaut, y a-t-il une meilleure manière de procéder alors que j'ai toujours vécu dans l'idée que les variables c'est bon, mangez-en et tout :D

Merci ;)

Hello,

L'utilisation de sp_executesql permet de réutiliser le plan d'exécution comme tu l'as dit. Cependant il y a des avantages à cela mais aussi des inconvénients dans certains cas. En effet, l'optimiseur ne connaît pas à l'avance les valeurs des variables locales et va les "sniffer" pour produire le plan d'exécution d'où ton comportement. Si tu es dans ce cas et que cela te pose problème et que tu as la maîtrise du code tu peux toujours essayer d'utiliser OPTIMIZE FOR UNKNOWN. Dans ce cas l'optimiseur de requêtes va non plus utiliser les statistiques liées à la valeur utilisée lors de la première compilation mais va utiliser une hypothèse d'uniformité de valeurs dans la table en utilisation le vecteur de densité globale pour la colonne id X nombre de lignes totale dans la table. Ceci reste à tester évidemment car dans certains cas cela va régler ton problème mais pas forcément à tous les coups ;)

++

Bonjour,

Merci de ta réponse, j'avais déjà vu ce paramètre au détour de mes recherches mais je n'ai pas réussi à comprendre où cela était positionné ? Tout ce que j'ai lu laissait à penser que c'est une option que l'on met dans une procédure stockée appelé par sp_executesql. Or, nous n'exécutons pas de procédure mais des requêtes SQL.

Merci de ton aide

Tu peux utiliser cette option directement dans une requête SQL.

Par exemple :

Code:

---

1 2 3 4

SELECT * FROM Person.Address WHERE City = @city_name AND PostalCode = @postal_code OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );

---

++

Merci infiniment. Sur l’entrefaite, j'ai trouvé une doc précisant tout ça : http://blogs.msdn.com/b/sqlprogramma...8-feature.aspx

:merci:

Hello,

Je me permets d'intervenir malgré que la discussion soit taguée résolue car un point à attirer mon attention.

Dans le premier message il est dit :

Citation:

---

J'ai donc proposé une optimisation qui fonctionnait très bien... sauf dans l'appli.

---

Cela peut être du au fait que, par défaut, SSMS et .NET (j'imagine que vous travailler en .NET) n'utilisent pas les mêmes valeurs par défaut pour certains paramètres. Or, vu que ces paramètres sont des caches_key, sql server va générer un plan de requête différent (du plan de requête utilisé dans SSMS lors de vos tests) pour la requête exécutée depuis l'application. Si ces paramètres n'ont pas été modifié, vous pourriez être dans cette problématique.

Plus d'info dans cet article (que je suis en train de traduire ^^).

Bonjour,

Petite précision supplémentaire, vous disposez également de l'option OPTION(RECOMPILE).
Comme on peut s'en douter, elle force la recompilation de la requete à chaque éxécution, et est donc à utiliser avec parcimonie, mais peut cependant être utile dans certains cas, notamment quand la requete est relativement simple (comme c'est le cas ici, avec donc un cout de compilation assez faible), et qu'une répartition hétérogène des données peut nécessiter de plans différents.

Effectivement Kropernic les options de sessions peuvent avoir une influence sur la génération des plans d'exécution, c'est aussi une voie à exploiter :)

++

Citation:

---

Envoyé par mikedavem

Effectivement Kropernic les options de sessions peuvent avoir une influence sur la génération des plans d'exécution, c'est aussi une voie à exploiter :)

++

---

Yeah ! J'ai pensé à un truc qu'un mvp avait oublié :yaisse2::yaisse2::yaisse2:

Citation:

---

Envoyé par Kropernic

Or, vu que ces paramètres sont des caches_key, sql server va générer un plan de requête différent (du plan de requête utilisé dans SSMS lors de vos tests) pour la requête exécutée depuis l'application. Si ces paramètres n'ont pas été modifié, vous pourriez être dans cette problématique.

---

C'est bien comme ça que je l'ai interprété et sur le coup, ça a été réglé en changeant le type des paramètres mais évidemment, c'est juste pour dépanner dans l'urgence ;)

Même si le problème est résolue, la discussion peut continuer à être alimentée par les forces vives du forum :bravo:

Citation:

---

Envoyé par orafrance

C'est bien comme ça que je l'ai interprété et sur le coup, ça a été réglé en changeant le type des paramètres mais évidemment, c'est juste pour dépanner dans l'urgence ;)

Même si le problème est résolue, la discussion peut continuer à être alimentée par les forces vives du forum :bravo:

---

"Dans l'urgence", ça dépend !

Pourquoi ces paramètres de sessions devraient-ils être différents ? Y a-t-il un intérêt ? J'imagine que oui dans certains cas spécifique. Mais est-ce le vôtre ?

Personnellement, quand j'ai découvert cela, je me suis empressé d'uniformisé tout ça. Au moins comme ça, je n'aurai plus de surprise.

Si une requête est lente, elle sera lente partout et je pourrai me concentrer sur la résolution du problème.

Citation:

---

Envoyé par Kropernic

Pourquoi ces paramètres de sessions devraient-ils être différents ? Y a-t-il un intérêt ? J'imagine que oui dans certains cas spécifique. Mais est-ce le vôtre ?

---

Oui, ça avait aussi du sens parce que le type des paramètres ne correspondait pas du tout au type des colonnes avec lesquels ils sont utilisés ;)

Le calcul d'un nouveau plan c'était le gateau sur la cerise :D

Citation:

---

Envoyé par orafrance

Oui, ça avait aussi du sens parce que le type des paramètres ne correspondait pas du tout au type des colonnes avec lesquels ils sont utilisés ;)

Le calcul d'un nouveau plan c'était le gateau sur la cerise :D

---

Euh... Je parle des paramètres de sessions, pas des paramètres de votre requête ^^.

Donc les trucs comme par exemple

Code:

---

SET ARITHABORT ON;

---

ou

Code:

---

SET ARITHABORT OFF;

---

ha non, j'ai pas du tout touché aux paramètres de sessions ;)

Citation:

---

Envoyé par Kropernic

Yeah ! J'ai pensé à un truc qu'un mvp avait oublié :yaisse2::yaisse2::yaisse2:

---

Ca mériterait presque que tu payes l'apéro pour fêter ça ;-) ... ben oui ne pas perdre le nord .. important :)

++

Citation:

---

Envoyé par mikedavem

Ca mériterait presque que tu payes l'apéro pour fêter ça ;-) ... ben oui ne pas perdre le nord .. important :)

++

---

(Vendredi, c'est demain pourtant...) Si un jour on se rencontre, ce sera avec plaisir ^^. P-e un jour à un SQL Days si j'arrive à y venir.