Hace poco me he enfrentado a un problema de rendimiento en consultas con SQLServer que me ha llevado mi tiempo resolverlo: todo iba fenómeno en el entorno de desarrollo pero a los 10 minutos de estar en producción SQLServer se colapsaba y comenzaba el terror del cliente, los desarrolladores y los muchachos de IT. Culpas cruzadas «Mis consultas son chorros de oro, debes de tener mal configurado SQLServer » dicen los desarrolladores «Debéis tener las consultas mal que me estáis tirando la BD seguido» replica IT, «Estoy perdiendo dinero, arregladlo ya» exige el cliente y otras leches agrias.
Los únicos síntomas visibles es que TempDb (donde SQLServer guarda resultados intermedios de consultas) está echando humo… por tanto; bloqueos de I/O en los accesos a disco… horror en el supermercado, terror en el ultramarinos: el infierno de Dante.
Partiendo de la etiología descrita, la cura siempre estuvo ahí, bien clara y bien escondida. En la literatura clásica sobre TempDb no se menciona lo que provoca este problema concreto y menos en las Pattern & Practices para desarrolladores. Hubo que investigar bastante para relacionarlo con un problema conocido de SQLServer y las grandes consultas parametrizadas -por ejemplo, la consulta que devuelve filtrados los productos por múltiples criterios en una tienda online- que apenas se menciona en foros de desarrolladores peros sí en los foros para DBAs de SQLServer.
En este post os presentaré un escenario bien conocido por todos y un problema que acecha en silencio y solo aparece cuando la base de datos crece y muy difícil de detectar en vuestro entorno local de desarrollo; os explicaré de forma simple por qué ocurre y, ante todo, una solución relativamente sencilla.
El escenario
Supongamos una consulta TSQL lanzada directamente (SQL Management Studio, SqlConnection, etc.) y también con LINQ to Entities desde una aplicación ASP.NET a través de SQLClient. Dicha consulta en cuestión tiene un montón de parámetros y es una de las piedras angulares de la aplicación del cliente: filtra por distintos criterios y orden elegidos por el usuario y pagina a la par que ordena los resultados para mostrarlos en una tabla; el pan de cada día de muchos de nosotros.
Se consulta una tabla central con más de 2 millones de tuplas y varias tablas satélite con relación 1:N que complementan la normalización de los datos.
Concretamente y siguiendo un patrón conocido por todos, se emplean dos consultas, una obtiene el Count filtrado de los resultados y otra los resultados filtrados y ordenados en sí.
El problema
Dicha consulta en entornos de desarrollo se comportaba rápidamente y en 1-2 segundos se obtenían los datos filtrados para mostrar en el grid. Pero en el entorno de producción y de manera no reproducible algunas de estas consultas y para determinados filtros tardaban desde un minuto hasta 10 minutos.
Se utilizó la herramienta SqlProfiler de SQLServer, se buscaron bloqueos, abrazos mortales, falta de índices, reconstrucción de los mismos, distintos modos de aislamiento de transacciones… sin ningún resultado que resolviese el problema… solo sirvió para constatar que ocurría y los clientes VIP (de pago) de la app se quejaban y amenazaban con irse a la web de la competencia.
Así que cogí la consulta TSQL de marras, la de mil y un filtros parametrizados y me la llevé al SQL Managament Studio para comenzar a revisar los planes de ejecución (opción «Incluir plan de ejecución actual (Ctrl+M)»).
Los planes eran, en principio, óptimos, pero a medida que empezaba a variar los valores de los parámetros de los filtros comencé a observar que varias operaciones del plan de ejecución necesitaban TempDb para guardar resultados parciales. De hecho, esto es lo que causaba los embotellamientos y retrasos de I/O a la hora de resolver la consulta.
Para aquellos que no sepan qué es TempDb simplemente indicaros que es el oscuro lugar donde las malas estimaciones de SQLServer con un plan de ejecución guardan los inesperados resultados intermedios. Si vuestras consultas usan TempDB, el tiempo de resolución crece exponencialmente con el tamaño de vuestra base de datos.
Curiosamente, si probaba la consulta con los parámetros que causaban ese uso de TempDB horas más tarde, no se usaba TempDb y se ejecutaba inmediatamente, además el plan de ejecución de la consulta ya no era el mismo y era óptimo para esos nuevos filtros.
Investigando el problema di con su explicación y solución. Pese a que es relativamente bien conocido apenas hay literatura en MSDN al respecto que relacione el síntoma con el problema. Personalmente nunca lo vi mencionado en los posts clásicos de buenas prácticas a la hora de acceder/consumir SQLServer, lo cual me parece sorprendente porque, como veremos a continuación, es un problema bastante común en las aplicaciones o servicios de hoy en día y de difícil detección y diagnóstico porque ocurre normalmente en el entorno de producción dónde hay muchas más tuplas y usuarios concurrentes que en los entornos de desarrollo en los que trabajamos.
El diagnóstico.
¿Os suena el Parameter sniffing? no importa si no sabéis qué es; es fácil de entender y un poco más tedioso de resolver.
Fijaos que a la hora de tirar consultas desde .Net nos han insistido, bombardeado , con ejemplos, en los que se nos animaba a parametrizar nuestras queries exhaustivamente “así se reutiliza el plan de ejecución y nos protege de ataques de SQL injection y otras bondades” y desde luego que tienen mucha razón, pero con un pero, un gran e interesante pero: SQLServer no regenera el plan de ejecución óptimo en una consulta parametrizada cuando se reutiliza y debéis de saber que dependiendo de los parámetros de filtrado, incluso los valores que toman, afectan al plan de ejecución óptimo.
De forma más clara: cuando le enviamos por primera vez una consulta parametrizada a SQLServer éste genera un plan de ejecución óptimo para los valores de los parámetros pasados en esa primera query.
El problema está en que en consultas donde el número de parámetros de filtrado (normalmente en el WHERE o en una cláusula JOIN) es relativamente grande y, sobre todo, las consultas son siempre sobre un subconjunto de parámetros… el plan inicial y cacheado generado por SQLServer no es óptimo, y no solo no es óptimo, sino que puede ser terriblemente ineficiente.
Para ilustrarlo veamos estas dos consultas similares: Una está en TSQL y otra en Entity Framework (EF) ambas son bastante equivalentes en su resolución. Hemos obviado el paginado para simplificarla; quien quiera saber cómo se hace con TSQL directamente puede consultar: http://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch. En EF supongo que todo el mundo conoce Take y Skip.
Consulta TSQL parametrizada:
[sourcecode language=»sql»]
SELECT p.id, p.productName, p.price, o.startDate, …
FROM Products p INNER JOIN Offers o …
WHERE
(@nameSearchPattern IS NULL OR (@nameSearchPattern IS NOT NULL AND p.productName LIKE @nameSearchPattern))
AND (@maxPrice=-1 OR (@maxPrice>-1 AND @maxPrice=<p.Price ))
AND (@minPrice=-1 OR (@minPrice>-1 AND @minPrice<=p.Price ))
AND (@maxDate IS NULL OR (@maxDate IS NOT NULL AND @maxDate=<o.startDate ))
AND (@minDate IS NULL OR (@minDate IS NOT NULL AND @minDate>=o.startDate ))
…
AND p.Available=1
…
[/sourcecode]
En su versión resumida en LINQ To Entities (EF):
[sourcecode language=»csharp»]
var query = from p in ctx.Products
where (name!=null || (p.productName.Contains(name))
(maxPrice==-1 ||(maxPrice=<p.Price)
…
[/sourcecode]
Una consulta única para resolverlo todo; la típica query para una tabla con múltiples opciones de filtrado; siguiendo los consejos usamos una única consulta para que prevalezca su plan de ejecución y junto con los parámetros correctamente definidos debería volar en cada ejecución.
Pues nada más lejos de la realidad… con 5M de tuplas probablemente esta consulta sea el mayor cuello de botella de vuestra app.
El primer antipatrón es el uso de expresiones en la cláusula where como “(@maxPrice=-1 OR (@maxPrice>0 AND @maxPrice=<p.Price ))”. SQLServer va a analizar y EVALUAR toda la expresión, aunque @maxPrice sea -1 … en otras palabras: Al contrario que C#, SQLServer no evalúa expresiones en cortocircuito.
Aunque parezca increíble es mucho más óptimo construir la consulta ad-hoc e incluir el “AND @maxPrice<=p.price” en el caso de que @maxPrice sea mayor que 0.
Por desgracia esto también ocurre en LINQ to Entities, así que es mejor hacer la comparación fuera y ampliar el where de la query.
El segundo antipatrón tiene que ver con cómo SQL Server crea los planes de ejecución para una consulta parametrizada: calcula el plan de ejecución en función del valor de los primeros parámetros de la primera ejecución de la consulta; en sucesivos usos de la misma consulta parametrizada no comprueba si el plan de ejecución sigue siendo óptimo; y por ende no lo recalcula.
Más claro todavía: la primera consulta del día la hace un señor de Murcia que quiere filtrar por nombre los productos… y se obtiene un plan de ejecución que usa una serie de índices y una serie de técnicas de ejecución para el nombre del producto. La segunda, tercera y sucesivas consultas son de clientes de Santiago de Compostela y Bilbao que buscan por rango de precios o por fechas en busca de ofertas: el plan de ejecución óptimo debería de usar probablemente índices para el precio y para las fechas de las ofertas pero el plan que se usa es el primero que se calculó: El que busca por nombre de producto. ¿Os imagináis el caos?
¿Cómo puede ser tan ineficiente calculando los planes? la respuesta es una larga discusión imposible de abordar sin un par de gin-tonics y por motivos obvios simplificaré la respuesta: no ocurre en todos los casos, pero prácticamente sí en todas aquellas que afectan a grandes conjuntos de tuplas, que se usan recurrentemente y tienen una cláusula WHERE relativamente grande, que mezcla expresiones (filtros) con tipos de datos como varchar, números y fechas.
Por ejemplo, si en la primera consulta enviada se filtra únicamente por fecha, y en la segunda solo por categorías o un flag cualquiera… el plan de ejecución óptimo es totalmente distinto. Incluso, y aquí viene nuestro mayor descubrimiento, con los valores pasados a los parámetros el plan de ejecución óptimo puede variar.
En la mayoría de ejemplos que encontramos el filtrado es solo por uno o dos parámetros y sobre un conjunto pequeño de tuplas (un conjunto pequeño sería menos de 100.000 por ejemplo). La mayor parte de la BD está en memoria y aunque no genere planes óptimos no es necesario usar TempDB por lo tanto el problema se enmascara en el entorno de desarrollo; si casi nadie revisa la consulta TSQL generada por EF mucho menos planes de ejecución de dichas consultas.
Pero en un entorno real, de millones de tuplas (no es tan infrecuente) simplemente el cambio en un parámetro de un LIKE de ‘algo%’ a ‘%algo’ puede suponer estrategias de ejecución óptimas totalmente diferentes.
En resumen: SQLServer no siempre genera planes óptimos para nuestras consultas parametrizadas y esto sucede desde SQLServer 2005 hasta SQLServer 2014.
Si alguien está interesado en saber más sobre el problema del “Parameter sniffing” puede consultar:
Breve introducción https://blogs.technet.microsoft.com/mdegre/2011/11/06/what-is-parameter-sniffing/
Un ejemplo completo y con planes de ejecución generados, si bien no hagáis mucho caso de la solución (fundamentada en procedimientos almacenados) https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
¿Cómo podemos resolver esto?
La solución
Lo primero es evitar expresiones complejas en el WHERE, recordemos que no se evalúan en cortocircuito y es mejor construir la consulta por partes. Sí, sí, concatenar en el WHERE los ANDs que hagan falta exclusivamente y en Linq to Entities igual.
Lo segundo y no menos importante es indicarle a SQL Server de que genere de nuevo el plan de ejecución cada vez que varían los parámetros de la consulta.
Sabemos que la solución ideal sería que fuese capaz automáticamente de regenerar el plan de ejecución cacheado si este dista mucho de ser óptimo para los nuevos parámetros. No debe tener una solución sencilla y la realidad es que SQLServer no lo hace.
Para generar de nuevo el plan de ejecución, basta añadir al final de la consulta TSQL:
1 2 3 |
<strong>OPTION (RECOMPILE) </strong> |
En nuestro escenario los resultados fueron inmediatos y milagrosos: se acabó el uso de TempDB y por tanto los retrasos de 10 minutos.
Evidentemente recalcular el plan de ejecución tiene un coste en tiempo, pero suele ser tan nimio (del orden de milisegundos), que compensa con creces el problema que resuelve.
No os olvidéis que el buen funcionamiento de “OPTION (RECOMPILE)” depende de que las estadísticas de la base de datos estén actualizadas… así que cada noche o tras una carga, actualización o borrado masivo es conveniente hacer un «UPDATE STATISTICS» en TSQL para que el optimizador de planes de ejecución tenga información fresca y obtenga el mejor plan de ejecución.
Hasta este punto hemos visto cómo resolver el problema en una consulta TSQL pero… ¿y si estoy usando Entity Framework?
En ese caso podríamos tener una discusión sobre si una consulta tan crítica en vuestra app o servicio debería ser resuelta por EF o usar el patrón de diseño Fast Lane Reader. Sobre el uso de EF y el patrón repositorio hay bastante discusión al respecto, por ejemplo: http://rob.conery.io/2014/03/04/repositories-and-unitofwork-are-not-a-good-idea/
Pero en general, si no estáis habituados a trabajar con TSQL probablemente Linq to Entities genere consultas mucho mejores que las vuestras.
Volviendo a cómo resolver el problema en EF, podéis implementar un CommandInterceptor si estáis usando EF6.
[sourcecode language=»csharp»]
public class OptionRecompileHintDbCommandInterceptor : IDbCommandInterceptor
{
…
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader>; interceptionContext)
{
addQueryHint(command);
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
addQueryHint(command);
}
private static void addQueryHint(IDbCommand command)
{
if (command.CommandType != CommandType.Text ||
!(command is SqlCommand))
return;
if (command.CommandText.StartsWith(«select», StringComparison.OrdinalIgnoreCase) !command.CommandText.Contains(«option(recompile)»))
{
command.CommandText = command.CommandText + » option(recompile)»;
}
}
}
[/sourcecode]
En el “application_start” o en el «global.asax» registráis el interceptor y listo:
[sourcecode language=»csharp»]DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());[/sourcecode]
Bueno, ya sabéis algo más de cómo se comporta SQL Server con consultas que creíamos perfectas y a prueba de bomba. En mi opinión lo peor de este asunto es que solo aparece en entornos de producción o staging, cuando múltiples usuarios ejecutan la misma gran consulta parametrizada y el tamaño de la BD es tan grande que es imposible cachearla en memoria. Así que es difícil de descubrir, si no se conoce previamente, hasta que el entorno del cliente revienta.
Para finalizar me gustaría recomendaros este post del 2008, revisado año tras año, de Erlang Sommarskog, un MVP de SQLServer que fue de los primeros en detectar este problema y sugerir varias soluciones. Algunas no son muy recomendables (procedimientos almacenados con dynamic sql) pero los principios del “parameter sniffing” siguen siendo perfectamente válidos. http://www.sommarskog.se/dyn-search-2008.html
Y tras todo lo dicho ¿Algún viejo proyecto que queráis repasar?
Deja un comentario