Entity Framework 4.2 exec sp executesql n'utilise pas d'index (paramètre sniffing))
je rencontre quelques problèmes de performance majeurs avec des requêtes SQL simples générées par le framework Entity (4.2) tournant contre SQL Server 2008 R2. Dans certaines situations (mais pas toutes), EF utilise la syntaxe suivante:
exec sp_executesql 'DYNAMIC-SQL-QUERY-HERE', @param1...
dans d'autres situations est exécute simplement le SQL brut avec les paramètres fournis cuits dans la requête. Le problème que je rencontre est que les requêtes exécutées avec sp_executesql ignorent tous les index de mes tables cibles, ce qui entraîne un requête extrêmement médiocre (confirmée par l'examen du plan d'exécution dans le SSMS).
après un peu de recherche, il semble que le problème pourrait être causé par "l'reniflement des paramètres". Si j'ai ajouter l'OPTION(RECOMPILER) indicateur de requête comme ceci:
exec sp_executesql 'DYNAMIC-SQL-QUERY-HERE OPTION(RECOMPILE)', @param1...
les index des tables cibles sont utilisés et la requête s'exécute très rapidement. J'ai aussi essayé de modifier le drapeau de trace utilisé pour désactiver le paramètre sniffing (4136) sur l'instance de la base de données. (http://support.microsoft.com/kb/980653), mais cela ne semble avoir aucun effet.
Cela me laisse avec quelques questions:
- y a-t-il quoi qu'il en soit pour ajouter L'indication de requête(recompiler) à la requête SQL générée par Entity Framework?
- y a-t-il quoi qu'il en soit pour empêcher le Framework Entity d'utiliser exec sp_executesql, et de lancer simplement le SQL brut?
- est-ce que quelqu'un d'autre est confronté à ce problème? Toute autre astuces/conseils?
Information Supplémentaire:
- j'ai redémarré l'instance de la base de données via SSMS, cependant, je vais essayer de redémarrer le service depuis la console de gestion des services.
- Paramétrage est SIMPLE (is_parameterization_forced: 0)
- Optimiser les charges de travail ad hoc a les paramètres suivants
- valeur: 0
- minimum: 0
- maximum: 1
- value_in_use: 0
- is_dynamic: 1
- is_advanced: 1
je dois également mentionner que si je redémarre le service SQL Server via la console de gestion des services après avoir activé trace flag 4136 avec le script ci-dessous, semble effectivement effacer le traceur flag...peut-être que je devrais le faire d'une manière différente...
DBCC TRACEON(4136,-1)
2 réponses
À ce stade, je voudrais recommander:
paramétrez le paramètre optimiser pour les charges de travail ad hoc à true.
EXEC sp_configure 'show advanced', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'optimize for ad hoc', 1;
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
si après un certain temps ce réglage ne semble pas avoir aidé, alors seulement puis-je essayer le support supplémentaire du drapeau de trace. Ces sont généralement réservés en dernier recours. Définissez le drapeau de trace en utilisant la ligne de commande via le gestionnaire de Configuration du serveur SQL, par opposition à dans une fenêtre de requête et en utilisant le drapeau global. Voir http://msdn.microsoft.com/en-us/library/ms187329.aspx
tl;dr
update statistics
Nous avons eu un delete interrogation avec un paramètre (la clé primaire) qui a pris ~7 secondes à compléter quand appelé par L'intermédiaire de EF et sp_executesql. Lancer la requête manuellement, avec le paramètre intégré dans le premier argument à sp_executesql fait tourner la requête rapidement (~0.2 secondes). L'ajout d' option (recompile) a également fonctionné. Bien sûr, ces deux solutions de rechange ne sont pas disponibles pour nous puisque nous utilisions EF.
probablement dû à en cascade des contraintes de clé étrangère, le plan d'exécution pour la requête à long terme était, uhmm..., énorme. Quand j'ai regardé le plan d'exécution dans SSMS j'ai remarqué que les flèches entre les différentes étapes dans certains cas étaient plus larges que d'autres, indiquant probablement que SQL Server avait de la difficulté à prendre les bonnes décisions. Cela m'a amené à penser aux statistiques. J'ai regardé les étapes du plan d'exécution pour voir quelle table était impliquée dans les étapes suspectes. Ensuite, j'ai couru update statistics Table pour cette table. Puis J'Ai re-couru à la mauvaise requête. Et je re-couru de nouveau. Et encore, juste pour s'assurer. Il a travaillé. Notre perf était redevenu normal. (Encore un peu pire que non -sp_executesql performances, mais hey!)
Il s'est avéré que ce n'était qu'un problème dans notre environnement de développement. (Et c'était un gros problème parce qu'il a fait nos tests d'intégration prennent une éternité.) Dans notre environnement de production, nous avions un travail courant qui mettait à jour toutes les statistiques sur une base régulière.