Cuando damos clases de SQL Server un aspecto al que los clientes – y a veces alumnos – quitan importancia a la parte de interioridades del motor de SQL Server. En mi puesto, en muchas ocasiones debo convencer a decisores sobre la formación a impartir a sus empleados; con mucha frecuencia tratan de evitar aspectos que se consideran internos del producto y que pueden verse como “de junta de trócola” y “para frikis”. En SolidQ siempre queremos que se imparta ese contenido, pero hoy día donde el argumento de más ponderación es el coste del servicio, muchos clientes intentan evitar contenidos formativos a los que no se les vea “retorno inmediato”. No quiero hacer de esta publicación una crítica o análisis de la formación especializada en España, pero debo indicar que cuando estaba pensando en la motivación de este post, la asociación entre interioridades de SQL Server, conocimiento que “quizás te sirva a medio plazo” y la educación especializada me vino directamente a la cabeza. También es posible que sea un problema mío de no saber convencer al cliente de la importancia del contenido presupuestado…
Esta publicación, la realizo con dos objetivos:
- Tenerla como referencia para que futuros clientes conozcan la razón de los aspectos muchas veces considerados como “junta de trócola” y el beneficio que les puede aportar (en esta publicación consultas un 25% más rápidas), y
- Explicar un cómo analizar un comportamiento anómalo de SQL Server utilizando diversos conocimientos internos de SQL Server.
Los conocimientos utilizados en esta publicación son los siguientes:
- Análisis de planes de ejecución
- Estadísticas de SQL Server
- Operador Hash Join
- Memoria utilizada para resolver consultas
- Speeling a tempdb
- Operador CASE WHEN
La Solución
Empezaré por el final para ahorrarte tiempo; si utilizas expresiones CASE en el predicado de tus consultas, ten cuidado con el orden de las comparaciones (WHEN x1 = v2 … THEN WHEN x2 = v2 THEN…) porque afecta a las estimaciones que hace el optimizador en cuanto a las filas que estima serán devueltas. Si para la primera comparación del CASE, no existe estadística de tipo EQ registrada, es muy probable que la estimación sea errónea. El tipo de consulta es el siguiente:
SELECT <columnas> FROM tabla1, tabla2, tabla3, ... WHERE CASE WHEN <col> = <x1> THEN <y1> WHEN <col> = <x2> THEN <y2> WHEN <col> = <x3> THEN <y3> END = <yn>
La ubicación de las comparaciones x1, x2, x3 puede provocar estimaciones diferentes a x2, x3, x1. En el pseudocódigo mostrado, si la tabla no tiene ninguna fila con valor <x1> es muy probable que las estadísticas sean erróneamente calculadas.
Este problema está reproducido en SQL Server 2012 RTM. Este problema no sucede en SQL Server 2014 CTP2. No lo reporto en Connect de Microsoft porque al estar resuelto en SQL Server 2014, la respuesta será que ya está resuelto en el producto. No lo considero como algo crítico como para que tenga que incluirse en SPs o CUs (si yo está ya incluido).
El Enunciado del Problema
Un cliente me comenta un problema que tiene con una consulta en SQL Server en la que a pesar de tener buenos índices y las estadísticas de los objetos bien actualizadas, no entiende por qué SQL Server genera tanta actividad en tempdb. He hablado con el cliente, y está de acuerdo con compartir el problema que tenía sin descubrir ningún aspecto de su diseño relacional, por lo que para poder reproducirlo he utilizado la base de datos del estándar TPCC. La consulta en cuestión es la siguiente:
SELECT count(distinct [ol_i_id]) FROM [tpcc].[dbo].[ORDER_LINE] where case when ol_w_id = 2 then 3 when ol_w_id = 1 then 2 end = 2
y el diseño lógico y físico de la tabla es el siguiente:
CREATE TABLE [dbo].[ORDER_LINE]( [ol_o_id] [int] NULL, [ol_d_id] [tinyint] NULL, [ol_w_id] [int] NULL, [ol_number] [tinyint] NULL, [ol_i_id] [int] NULL, [ol_delivery_d] [datetime] NULL, [ol_amount] [smallmoney] NULL, [ol_supply_w_id] [int] NULL, [ol_quantity] [smallint] NULL, [ol_dist_info] [char](24) NULL ) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [ORDER_LINE_I1] ON [dbo].[ORDER_LINE] ( [ol_w_id] ASC, [ol_d_id] ASC, [ol_o_id] ASC, [ol_number] ASC ) CREATE NONCLUSTERED INDEX [nci_order_line_ol_w_id] ON [dbo].[ORDER_LINE] ( [ol_w_id] ASC )
El conjunto de datos generado para la tabla corresponde a los datos correspondientes para un almacén (Datawarehouse old_w_id = 1).
La tabla tiene algo más de 14 millones de filas y este es el resultado de sp_spaceused:
La distribución del resto de las tablas es la siguiente:
Análisis del Plan de Ejecución
Para analizar el problema, como el cliente indica que ha detectado speeling en tempdb, creamos una traza de SQL Server Profiler en el entorno de pruebas, en la que incluimos los warnings que aparecen en la imagen:
En SQL Server Management Studio, habilitamos que nos muestre el plan de ejecución utilizado para la consulta teniendo el siguiente resultado:
Donde vemos que en el operador Hash Match, tenemos una muesca de warning que nos indica el problema mencionado.
Revisando el plan de ejecución en detalle, vemos una disparidad que puede ser la causas del problema (ver imagen más abajo):
– El operador Index Scan estima que va a devolver algo más de 235.000 filas, mientras que en realidad, devuelve algo más de 1.4 millones de filas.
– A su vez, el operador Hash Match, estima que va a devolver sobre 209.000 filas, mientras que en realidad devuelve 1 millón.
Este es el origen del problema; el operador Hash Match, antes de empezar a resolver la consulta – al generar el plan de ejecución – estima que va a necesitar memoria para dar cabida a a 235.000 filas, mientras que en realidad va a recibir casi 1.5 millones de filas. Recuerda que el operador Hash Match necesita utilizar memoria para realizar su procesamiento. Para ver cuanta memoria necesita la consulta, se puede utilizar la DMV sys.dm_exec_requests, pero la consulta necesita estar en ejecución para ver la memoria reservada. Por lo tanto, como estoy en un entorno de prueba, pongo la consulta en un bucle WHILE (1=1) para ver qué memoria se le asigna a esa conexión. La consulta utilizada es la siguiente, donde tendrás que ajustar el session_id para tu propio uso:
En este caso, SQL Server asigna algo más de 2MB para la consulta en cuestión.
Debemos investigar esa disparidad entre las filas estimadas y las realmente procesadas para una consulta tan simple en la que en principio falla para un operador tan sencillo como recorrer un índice non-clustered.
Si vemos las estadística del índice en cuestión, vemos lo siguiente:
El índice tiene “sampleadas” todas las filas del índice, y además, todas las filas del índice tienen el mismo valor (coincidencia entre la columna Rows, y la columna EQ_Row.
Por lo que claramente el optimizador no utiliza adecuadamente las estadísticas de ese índice.
El predicado de la consulta es el siguiente:
where case when ol_w_id = 2 then 3 when ol_w_id = 1 then 2 end = 2
Seguramente parezca tonta esta simplificación – en el caso del cliente tiene mayor complejidad – pero la consulta se podría reemplazar por lo siguiente:
SELECT count(distinct [ol_i_id])
FROM [tpcc].[dbo].[ORDER_LINE]
where ol_w_id = 1
Donde vemos que las estadística son mucho más acertadas que en el caso anterior. Podríamos decir que la expresión CASE está “armando el lío” y concretamente los “predicados” del predicado CASE.
Fijándome en el predicado, en las condiciones y en el histograma del índice,me doy cuenta que para el valor ol_w_id = 2, no existen ninguna fila en la tabla, por lo que decido probar a modificar una fila en la tabla para que tenga la columna ol_w_id igual a 2. Al ser un entorno de pruebas sin problema; lógicamente esto no debe hacerse en sistemas con datos reales.
Procedo a cambiar el valor, actualizo las estadísticas del índice y tengo lo siguiente:
Ejecuto la consulta original, y “voila”, desaparece el hash-warning y las estadísticas vuelven a ser acertadas:
Ahora comprobamos cuanta memoria tiene asignada la consulta y nos indica que tiene algo más de 11MB asignados; nótese que aunque es más exigente en cuanto a la cantidad de memoria que necesita, es más eficiente porque no necesita de tempdb:
Ahora la consulta no necesita de speeling a tempdb porque las estimaciones concuerdan con las filas recibidas y por lo tanto en la memoria asignada hay espacio suficiente para procesar el operador Hash Join.
Una vez diagnosticado el problema, comparamos la consulta original con otra que se ajuste a la “necesidad del fallo” de que no tenga filas en el histograma del índice, y tenemos los siguientes resultados:
Y en SQL Server Profiler tenemos el siguiente resumen:
la consulta primera (la arreglada) necesita de 15 segundos frente a los 21 segundos que necesita la segunda (la mala).
Estos números en el sistema del cliente eran mucho peores porque la diferencia entre la buena y la mala es que la mala abusa de tempdb y al ser un recurso compartido por todos los usuarios, las latencias de acceso a tempdb en el caso del cliente eran más altas.
En relación a la actividad en tempdb, estos son los datos resumidos de la DMV sys.dm_io_virtual_file_stats:
Puede verse que la consulta mala lee y escribe más de 140MB en la BBDD tempdb, mientras que la buena, apenas genera actividad en tempdb.
También se puede ver que la cantidad de datos leídos del archivo de datos de la BBDD tpcc es el mismo (el tamaño del índice nonclustered).
Conclusión
Quizás consideres esta publicación excesiva para un mal uso de la expresión CASE de la plantilla inicial, pero considera que el ejemplo mostrado es una simplificación del problema que tenía el cliente. En este caso, las estadísticas más estimadas han afectado a un único operador (el Hash Join mencionado), pero en casos de planes de ejecución con múltiples JOINs, es muy probable que si las estadísticas están erróneas al principio del plan, el problema se propague por todo el plan de ejecución.
Estos son el tipo de problemas en los que ayudamos a nuestros clientes: aportar conocimiento y experiencia a áreas en las que el cliente se siente desprotegido.
Finalmente, me gustaría transmitiros la importancia de conocer el motor de SQL Server y las herramientas (SSMS, Profiler, Perfmon, DMVs) que nos proporciona Microsoft para analizar en detalle que es lo que sucede en SQL Server. Tenemos un arsenal muy amplio y variado aunque también es probable que sean demasiados los métodos que disponemos para llegar a la misma conclusión. Tengo un amigo que dice que si hay un único camino no es posible perderse, pero si hay varias formas de llegar, es más que probable que acabes perdiéndote; quizás es momento de revisar la frase “todos los caminos llevan a Roma”…
