Entity Framework 4.2 exec sp_executesql no usa índices(sniffing de parámetros)

entity-framework sql-server-2008-r2

Question

Estoy encontrando algunos problemas importantes de rendimiento con consultas SQL simples generadas por Entity Framework (4.2) ejecutándose en SQL Server 2008 R2. En algunas situaciones (pero no en todas), EF usa la siguiente sintaxis:

exec sp_executesql ''DYNAMIC-SQL-QUERY-HERE'', @param1...

En otras situaciones, simplemente ejecuta el SQL sin procesar con los parámetros proporcionados incorporados en la consulta. El problema que encuentro es que las consultas ejecutadas con sp_executesql ignoran todos los índices en mis tablas de destino, lo que resulta en una consulta de rendimiento extremadamente pobre (confirmada al examinar el plan de ejecución en SSMS).

Después de un poco de investigación, parece que el problema podría ser causado por el ''olfateo de parámetros''. Si añado la sugerencia de consulta OPCIÓN (RECOMPRA) como sigue:

exec sp_executesql ''DYNAMIC-SQL-QUERY-HERE OPTION(RECOMPILE)'', @param1...

Se utilizan los índices en las tablas de destino y la consulta se ejecuta de forma extremadamente rápida. También intenté alternar en el indicador de rastreo utilizado para desactivar el rastreo de parámetros (4136) en la instancia de la base de datos ( http://support.microsoft.com/kb/980653 ), sin embargo, esto no pareció tener ningún efecto en absoluto.

Esto me deja con algunas preguntas:

1. ¿Hay alguna forma de agregar la sugerencia de consulta OPCIÓN (RECOMPRA) al SQL generado por Entity Framework?
2. ¿Hay alguna forma de evitar que Entity Framework use exec sp_executesql, y en su lugar simplemente ejecute el SQL sin procesar?
3. ¿Alguien más está teniendo este problema? ¿Alguna otra pista / sugerencia?

Información Adicional:

1. Reinicé la instancia de la base de datos a través de SSMS; sin embargo, intentaré reiniciar el servicio desde la consola de administración del servicio.
2. La parametrización se establece en SIMPLE (is_parameterization_forced: 0)
3. Optimizar para cargas de trabajo adhoc tiene la siguiente configuración
   • valor: 0
   • mínimo: 0
   • máximo: 1
   • value_in_use: 0
   • is_dynamic: 1
   • is_advanced: 1

También debo mencionar que si reinicio el servicio de SQL Server a través de la consola de administración de servicios DESPUÉS de habilitar la marca de traza 4136 con la secuencia de comandos siguiente, parece que realmente borra la marca de traza ... quizás debería hacerlo de otra manera ...

DBCC TRACEON(4136,-1)

Answer 1

En este punto, recomendaría:

Establezca la optimización para la carga de cargas de trabajo ad hoc en verdadero.

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 después de un tiempo esta configuración no parece haber ayudado, solo entonces probaría el soporte adicional del indicador de rastreo. Estos generalmente se reservan como último recurso. Establezca la marca de seguimiento utilizando la línea de comando a través del Administrador de configuración de SQL Server, en lugar de en una ventana de consulta y utilizando el indicador global. Ver http://msdn.microsoft.com/en-us/library/ms187329.aspx

Answer 2

tl; dr

update statistics

Tuvimos una consulta de delete con un parámetro (la clave principal) que tardó ~ 7 segundos en completarse cuando se llamó a través de EF y sp_executesql . Al ejecutar la consulta manualmente, con el parámetro incrustado en el primer argumento para sp_executesql la consulta se ejecutó rápidamente (~ 0.2 segundos). La option (recompile) agregar option (recompile) también funcionó. Por supuesto, esas dos soluciones no están disponibles para nosotros ya que estábamos usando EF.

Probablemente debido a restricciones de clave externa en cascada, el plan de ejecución para la consulta de larga ejecución fue, uhmm ..., enorme. Cuando miré el plan de ejecución en SSMS noté que las flechas entre los diferentes pasos en algunos casos eran más anchas que otras, lo que posiblemente indica que SQL Server tuvo problemas para tomar las decisiones correctas. Eso me llevó a pensar en estadísticas. Miré los pasos en el plan de ejecución para ver qué tabla estaba involucrada en los pasos sospechosos. Luego ejecuté update statistics Table para esa tabla. Luego volví a ejecutar la consulta incorrecta. Y volví a ejecutarlo de nuevo. Y otra vez solo para estar seguro. Funcionó. Nuestro perf fue vuelto a la normalidad. (Todavía algo peor que el rendimiento no sp_executesql , pero ¡oye!)

Resultó que esto era solo un problema en nuestro entorno de desarrollo. (Y fue un gran problema porque hizo que nuestras pruebas de integración durasen para siempre.) En nuestro entorno de producción, teníamos un trabajo en ejecución que actualizaba todas las estadísticas de forma regular.