GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

Sort Warning y Hash Warnings en SQL Server


Los Eventos de Traza Sort Warning y Hash Warnings, son un importante indicador de Problemas de Rendimiento en SQL Server, relacionados con la subestimación de memoria de consulta (Query Memory o Workspace Memory) en los Planes de Ejecución. Su ocurrencia, implica la ejecución de consultas SQL utilizando menos RAM de la recomendable, incurriendo en realizar la misma tarea en múltiples fases y escribiendo en TEMPDB (spilling to TEMPDB), en lugar de realizarlo en un único paso utilizando memoria RAM, y además, puede implicar problemas adicionales de Esperas (Waits) de consultas pendientes de adquirir memoria. Y todo esto, se complica más si tenemos muchos procesadores en SQL Server.

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.


[Fecha del Artículo (UTC): 10/01/2012]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Enero de 2017 (2)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas