En esta serie de artículos vamos a volver a hablar de Problemas de Rendimiento en SQL Server, en esta ocasión, relacionados con el cacheo y reutilización de los Planes de Ejecución en SQL Server (bueno, no siempre, pues puede haber otros motivos como unas estadísticas incorrectas o poco actualizadas), los Operadores Costosos (Expensive Operators) de los Planes de Ejecución (ej: SORT, SORT DISTINCT, HASH JOIN, HASH AGGREGATE) y sus consideraciones de memoria, los Memory Grant (y su relación con el paralelismo, es decir, las opciones DOP), la sobrestimación y subestimación de las necesidades reales de memoria RAM (Query Memory o Workspace Memory), el desbordamiento sobre TEMPDB (spilling to TEMPDB) para poder ejecutar una consulta utilizando menos memoria RAM de la necesaria, algunas esperas (waits), y los Eventos de Traza Sort Warning y Hash Warnings (disponibles desde SQL Server 2005). En anteriores artículos ya hemos tocado problemas algo parecidos (en cierto modo…), como es el caso de Parameter Sniffing. Ahora vamos a dar un pasito más, tocando un poco el Tunning de consultas y procedimientos almacenados en SQL Server.
Los Eventos de Traza Sort Warning y Hash Warnings
Los Eventos de Traza Sort Warning y Hash Warnings se producen cuando la ejecución de una Consulta SQL o de un Procedimiento Almacenado se realiza utilizando menos RAM de la que recomendable. Aunque en un principio, podríamos pensar que este problema se podría deber a que nuestro servidor no dispone de suficiente memoria RAM, en muchos casos el problema es que se está utilizando un Plan de Ejecución no apropiado (por reutilizar un Plan ya existente en caché o por utilizar una estadísticas incorrectas o poco actualizadas), como veremos a lo largo de esta serie de artículos.
En consecuencia, la ocurrencia de los Eventos de Traza Sort Warnings y Hash Warnings en la ejecución de una consulta SQL, implica que dicha consulta necesitará utilizar TEMPDB (spilling to TEMPDB) y realizar su trabajo en múltiples pasos, presentando un rendimiento muy pobre.
- Hash Warning. Indica que una operación Hash no puede realizarse completamente en memoria, debido a que los datos de entrada son mayores que la cantidad de memoria RAM disponible asignada a la consulta SQL. En este caso, la operación Hash debe realizarse en múltiples pasos y escribiendo en TEMPDB (spilling to TEMPDB), degenerando en lo que se denomina Grace Hash Join o Recursive Hash Join. Esta situación suele estar acompañada de una percepción de pérdida de rendimiento. Para más información, puede consultarse los BOL o MSDN: Hash Warning Event Class y Understanding Hash Joins.
- Sort Warnings. Indica que una operación SORT no puede realizarse completamente en memoria, por lo cual, dicha operación debe realizarse utilizando una menor cantidad de memoria RAM, realizando la ordenación en varias fases, y escribiendo en TEMPDB (spill to TEMPDB). Esta situación suele estar acompañada de una percepción de pérdida de rendimiento. Para más información, puede consultarse los BOL o MSDN: Sort Warnings Event Class.
En ambos casos, el problema es el mismo: la utilización de TEMPDB por desbordamiento (spilling to TEMPDB), y la realización de la tarea en cuestión en múltiples pasos, para de este modo poder apañarse con la insuficiente cantidad de memoria RAM asignada para ejecutar la consulta SQL.
Téngase en cuenta, que los Eventos de Traza Sort Warnings y Hash Warnings sólo se producen cuando se ejecuta una consulta SQL utilizando menos memoria RAM de la necesaria, es decir, en caso de subestimación. Por lo tanto, si una consulta SQL se ejecuta utilizando mucha más RAM de la necesaria (sobrestimación), no se mostrarán los Eventos de Traza Sort Warnings y Hash Warnings, ahora:
- En esta situación, también estamos ante un problema, ya que estamos desperdiciando memoria RAM, que se podría estar aprovechando por otras consultas SQL. Es decir, esto puede provocar que otras consultas SQL se queden en Espera (Wait) pendientes de que se disponga de suficiente memoria RAM (Query Memory) para poder empezar a ser ejecutadas.
- Por otro lado, si observamos Eventos de Traza Sort Warnings y Hash Warnings, es probable que tengamos algún problema de estimación de RAM, por lo tanto, aunque estos eventos se muestren sólo cuando se produce subestimación, es probable que también tengamos sobrestimación y no lo sepamos. Ojo.
Aquí quería llegar. El problema de una incorrecta estimación de memoria RAM (Query Memory), no sólo se limita a una pérdida de rendimiento por la utilización de TEMPDB y por la realización de tareas costosas (ej: ordenaciones) en varias fases. Además, podemos llegar a tener un importante problema de Esperas (Waits) de las consultas para obtener la memoria RAM (Query Memory) que necesitan, lo cual, en entornos de concurrencia puede degenerar en un efecto bola de nieve que nos deje el sistema aparentemente inservible.
Sorprendente. Pero hay más detalles curiosos relacionados con todo este tema. Por ejemplo, resulta que las necesidades de memoria RAM de una consulta (Memory Grant) se ven afectadas por las opciones de paralelismo de la consulta (DOP: Degree Of Parallelism). Lo que quiero decir es que para ejecutar la misma consulta SQL sobre el mismo Hardware, la cantidad de memoria (Memory Grant) será mayor cuanto mayor sea el número de CPUs utilizado por la consulta durante su ejecución. Un detalle importante, ya que esto puede provocarnos aún más problemas de Esperas (Waits) de consultas para conseguir memoria. De hecho, ya hace un tiempo que hablamos sobre Configurar MDOP y sus consecuencias de Rendimiento en SQL Server.
Por lo tanto, como hemos visto hasta ahora, si observamos muchos Eventos de Traza Sort Warnings y Hash Warnings, muy probablemente tenemos un problema. Y muy probablemente, un problema serio.
Hasta ahora, hemos estado hablando de pérdida de rendimiento relacionada con el acceso a TEMPDB. Es importante tener clara la diferencia del acceso a TEMPDB por desbordamiento (spilling to TEMPDB) y por la utilización de Tablas de Trabajo (Work Tables), ya que son cosas distintas que no tienen nada que ver. El acceso a TEMPDB por desbordamiento (spilling to TEMPDB) es el que se produce cuando ocurren los eventos de traza Hash Warning y Sort Warning, mientras que las Tablas de Trabajo (Work Tables) son tablas internas que utiliza SQL Server para poder ejecutar consultas que incluyen ciertos Operadores en su Plan de Ejecución, como por ejemplo el Hash Join, y que podremos observar si activamos las estadísticas de IO (SET STATISTICS IO ON).
Pero ¿en qué situaciones nos podemos encontrar los eventos Hash Warning y/o Sort Warning? En esta serie de artículos, vamos a hablar de los siguientes casos:
- Reutilización de Planes de Ejecución con Operadores Costosos (Expensive Operators) y diferentes valores para los parámetros de entrada. En una primera ejecución de un procedimiento almacenado (que utiliza unos valores particulares para los parámetros de entrada) se crea el Plan de Ejecución y se almacena en Caché. En segundas ejecuciones se reutiliza el Plan de Ejecución en Caché, pero al utilizar diferentes valores para los parámetros de entrada, las necesidades de memoria de consulta del Plan de Ejecución reutilizado difieren de las necesidades reales (por afectar a un diferente número de filas), incurriendo en subestimación de memoria de consulta (y los correspondientes eventos Hash Warnings o Sort Warnings) o en sobrestimación de consulta.
- Estadísticas incorrectas o mal actualizadas y Planes de Ejecución con Operadores Costosos (Expensive Operators). Al generarse un Plan de Ejecución con Operadores Costosos (que requieren de Memoria de Consulta), se utilizan las estadísticas para estimar el número de filas afectadas, y de aquí, estimar la cantidad de memoria de consulta (Query Memory o Workspace Memory) necesaria. Al ejecutarse la consulta utilizando el Plan de Ejecución generado, se descubre que el número de filas es incorrecto, habiéndose producido una subestimación de memoria de consulta (y los correspondientes eventos Hash Warning o Sort Warning) o una sobrestimación de memoria de consulta.
Hasta aquí llega el presente artículo. En el próximos artículo nos centraremos en la relación existente entre la concesión de memoria de consulta (Memory Grant) y los eventos Hash Warnings y Sort Warnings, incluyendo el comportamiento en entornos con múltiples procesadores (DOP: Degree Of Parallelism), para posteriormente mostrar un ejemplo práctico de Sort Warnings por reutilización del Plan de Ejecución, un ejemplo práctico de Hash Warnings por reutilización del Plan de Ejecución y un ejemplo práctico de Sort Warnings por estadísticas incorrectas o poco actualizadas.
Poco más por hoy. Como siempre, confío que la lectura resulte de interés.