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

Hash Warning, Sort Warning y las Estadísticas de SQL Server


Hasta el momento, hemos visto cómo pueden producirse los eventos de traza Hash Warning / Sort Warning como resultado de la reutilización de un Plan de Ejecución con Operadores Costosos entre invocaciones con diferentes valores de los parámetros de entrada, incurriendo en una subestimación de Memoria de Consulta. El presente artículo muestra un caso diferente, en particular, la ocurrencia de eventos Hash Warning y Sort Warning como resultado de unas estadísticas incorrectas o poco actualizadas, para lo cual hemos falseado las estadísticas con UPDATE STATISTICS WITH ROWCOUNT o PAGECOUNT.

Continuando con nuestra serie de artículos sobre Problemas de Rendimiento en SQL Server relacionados con los eventos de traza Hash Warning y Sort Warning, tras entender cómo funcionan las concesiones de memoria de consulta (Memory Grants) en SQL Server y la diferencia entre el acceso a TEMPDB por desbordamiento (spilling to TEMPDB) y las Tablas de  Trabajo (Work Tables), y después de ver un ejemplo de Sort Warning al reutilizar el Plan de Ejecución y otro ejemplo de Hash Warning al reutilizar el Plan de ejecución, en esta ocasión vamos a ver un tercer ejemplo, también en esta misma línea.

Al igual que vimos en artículos anteriores, en los cuales forzábamos la ocurrencia de los eventos Hash Warning y Sort Warning al reutilizar un Plan de Ejecución generado para unos valores de parámetros de entrada distintos (que afectaban a un número de filas inferior), el problema de fondo es el mismo: se utiliza un Plan de Ejecución con Operadores Costosos (Expensive Operators) que estimó una cantidad de Memoria de Consulta en función de un número de filas estimadas, y en tiempo de ejecución se descubre que el número de filas afectadas y las necesidades de memoria reales son mucho mayores (subestimación), incurriendo en un desbordamiento sobre TEMPDB (spilling to TEMPDB) para poder ejecutar la consulta en cuestión.

Sin embargo, el ejemplo que se describe en el presente artículo, muestra una estimación incorrecta al generar el Plan de Ejecución debido a unas estadísticas no actualizadas. Sé lo que estas pensando. ¿Cómo simulamos de forma sencilla, en nuestro entorno de laboratorio, un escenario con estadísticas no actualizadas o incorrectas? Buena pregunta. Por suerte, Eladio Rincón (uno de los que saben) en su Post Sort Warning in SQL Server: Complicaciones derivadas del Sort Operator y el Equipo de Optimización de SQL Server en su Post UPDATE STATISTICS undocumented options, lo pensaron antes que nosotros. Así da gusto ;-)

Falsear Estadísticas con UPDATE STATISTICS WITH ROWCOUNT y/o UPDATE STATISTICS WITH PAGECOUNT

Hasta donde yo sé, el Optimizador de Consultas de SQL Server utiliza, entre otras cosas, las estadísticas de campos e índices, para la generación de los Planes de Ejecución. De hecho, en otros motores de base de datos, es habitual promover las estadísticas del entorno de producción, al resto de entornos del ciclo de vida, es decir, a Desarrollo, Calidad, Pre-Producción, y cualquier otro entorno que pudiera existir. De este modo, consiguen obtener en estos entornos, unos Planes de Ejecución congruentes a los que se obtendrán en producción, aunque evidentemente, dado que los datos no son los mismos, el resultado de la ejecución podrá no ser representativo en muchos casos.

Volviendo al tema que nos ocupa, en nuestro caso vamos a falsear las estadísticas para simular un servidor con estadísticas poco actualizadas, y evidenciar como por culpa de dichas estadísticas erróneas, se pueden generar eventos Hash Warning y Sort Warning.

Para falsear las estadísticas de SQL Server vamos a utilizar el comando no documentado UPDATE STATISTICS WITH ROWCOUNT, para de este modo, decirle a SQL Server que el número de filas de la tabla deseada es el que nosotros nos inventemos. También podríamos utilizar el comando no documentado UPDATE STATISTICS WITH PAGECOUNT, aunque en nuestro caso de ejemplo utilizaremos el ROWCOUNT.

Por otro lado, tenemos un Plan de Mantenimiento que realiza una Reindexación  completa de la base de datos de Adventure Works. Como sabemos, la Reindexación también actualiza las estadísticas (de hecho, esta es una de las diferencias entre reindexar o defragmentar índices). Por lo tanto, después de jugar con el UPDATE STATISTICS WITH ROWCOUNT, si lo deseamos, podemos ejecutar este Job del Plan de Mantenimiento para volver a dejar las estadísticas en buen estado, a través de dicha Reindexación.

Preparando el entorno de Laboratorio

Partimos de una instancia SQL Server 2008 R2, con la base de datos de Adventure Works instalada. Para nuestro ejemplo, hemos creado un índice sobre la tabla que deseamos consultar, que eliminaremos al finalizar nuestras pruebas. Seguidamente hemos creado un procedimiento almacenado (dbo.SalesOrderSelect), el cual devuelve un conjunto de filas ordenado realizando un ORDER BY, y filtrando por un rango de fechas (BETWEEN) que se especifican como parámetros de entrada de dicho Procedimiento Almacenado. Como siempre, al final del artículo podéis descargar el Script SQL con todo el código de ejemplo.

Para continuar, deberemos abrir el SQL Profiler, y ejecutar una traza para capturar las ocurrencias de los siguientes Eventos:

  • Errors and Warnings - Hash Warning
  • Errors and Warnings - Sort Warnings
  • Stored Procedures – SP:CacheHit
  • Stored Procedures – SP:CacheInsert

Reducimos TEMPDB al menor tamaño posible. En nuestro caso de ejemplo, el MDF de TEMPDB ocupa tan sólo 1,5MB.

Reducimos TEMPDB al menor tamaño posible. En nuestro caso de ejemplo, el MDF de TEMPDB ocupa tan sólo 1,5MB.

Además, vamos a consultar la DMV sys.dm_io_virtual_file_stats, para identificar las lecturas y escrituras realizadas sobre TEMPDB, y de este modo, poder tener otra medida que nos indique si se ha incurrido en un acceso a TEMPDB o no. En la siguiente pantalla capturada se muestra la consulta realizada y la salida obtenida.

Además, vamos a consultar la DMV sys.dm_io_virtual_file_stats, para identificar las lecturas y escrituras realizadas sobre TEMPDB, y de este modo, poder tener otra medida que nos indique si se ha incurrido en un acceso a TEMPDB o no. En la siguiente pantalla capturada se muestra la consulta realizada y la salida obtenida.

Ejecución de las Pruebas para reproducir el evento Sort  Warning y falseo de las Estadísticas

Vamos a ejecutar nuestro Procedimiento Almacenado de ejemplo en un escenario con las estadísticas correctas (donde sufrimos reindexaciones frecuentes de nuestra base de datos, lo cual, mantiene actualizadas las estadísticas), utilizando la opción Include Actual Execution Plan habilitada, tal y como se muestra en el siguiente ejemplo. Como podemos observar, el valor de Memory Grant es 3552.

Vamos a ejecutar nuestro Procedimiento Almacenado de ejemplo en un escenario con las estadísticas correctas (donde sufrimos reindexaciones frecuentes de nuestra base de datos, lo cual, mantiene actualizadas las estadísticas), utilizando la opción Include Actual Execution Plan habilitada, tal y como se muestra en el siguiente ejemplo. Como podemos observar, el valor de Memory Grant es 3552.

Si nos fijamos en la operación SORT, el número de filas estimadas y el número de filas actuales (afectadas) es prácticamente coincidente (24504 vs 24494), por lo que podemos entender que la concesión de memoria para esta consulta (Memory Grant) es apropiada.

Si nos fijamos en la operación SORT, el número de filas estimadas y el número de filas actuales (afectadas) es prácticamente coincidente (24504 vs 24494), por lo que podemos entender que la concesión de memoria para esta consulta (Memory Grant) es apropiada

Si revisamos la salida de mensajes teniendo las estadísticas de IO activadas (SET STATISTICS IO ON), podemos observar que NO se ha creado ninguna Tabla de Trabajo (Work Table) sobre TEMPDB.

Si revisamos la salida de mensajes teniendo las estadísticas de IO activadas (SET STATISTICS IO ON), podemos observar que NO se ha creado ninguna Tabla de Trabajo (Work Table) sobre TEMPDB.

Si volvemos a consultar la DMV sys.dm_io_virtual_file_stats para identificar las lecturas y escrituras realizadas sobre TEMPDB, podremos comprobar que el número de lecturas y escrituras es el mismo que antes de empezar las pruebas, luego no se ha accedido a TEMPDB.

Si volvemos a consultar la DMV sys.dm_io_virtual_file_stats para identificar las lecturas y escrituras realizadas sobre TEMPDB, podremos comprobar que el número de lecturas y escrituras es el mismo que antes de empezar las pruebas, luego no se ha accedido a TEMPDB.

Si revisamos la Traza de SQL Profiler, podremos observar que en esta primera invocación se generó el Plan de Ejecución y se cacheo en memoria (CacheInsert). Además, no se ha producido ningún evento Hash Warning ni Sort Warning.

Si revisamos la Traza de SQL Profiler, podremos observar que en esta primera invocación se generó el Plan de Ejecución y se cacheo en memoria (CacheInsert). Además, no se ha producido ningún evento Hash Warning ni Sort Warning.

Hasta aquí todo normal, al menos, para un entorno que mantiene sus estadísticas correctamente actualizadas.

Ahora llega el contraejemplo. Lo primero, vamos a falsear las estadísticas de SQL Server utilizando el comando no documentado UPDATE STATISTICS WITH ROWCOUNT, para seguidamente forzar la generación de un nuevo Plan de Ejecución para nuestro procedimiento almacenado de ejemplo a través del sp_recompile. Esto se muestra en la siguiente pantalla capturada.

Ahora llega el contraejemplo. Lo primero, vamos a falsear las estadísticas de SQL Server utilizando el comando no documentado UPDATE STATISTICS WITH ROWCOUNT, para seguidamente forzar la generación de un nuevo Plan de Ejecución para nuestro procedimiento almacenado de ejemplo a través del sp_recompile. Esto se muestra en la siguiente pantalla capturada.

Volvemos a ejecutar nuestro procedimiento almacenado de ejemplo, incluso con exactamente los mismos valores para los parámetros de entrada. Sin embargo, en esta ocasión, al generarse el nuevo Plan de Ejecución, el Optimizador de Consultas de SQL Server está teniendo en cuenta las actuales (y erróneas) estadísticas. Y esto se nota. Para esta segunda invocación en situación de estadísticas no actualizadas, el valor de Memory Grant es 1024 en lugar de 3052.

Volvemos a ejecutar nuestro procedimiento almacenado de ejemplo, incluso con exactamente los mismos valores para los parámetros de entrada. Sin embargo, en esta ocasión, al generarse el nuevo Plan de Ejecución, el Optimizador de Consultas de SQL Server está teniendo en cuenta las actuales (y erróneas) estadísticas. Y esto se nota. Para esta segunda invocación en situación de estadísticas no actualizadas, el valor de Memory Grant es 1024 en lugar de 3052.

Si nos fijamos en la operación SORT, el número de filas estimadas y el número de filas actuales (afectadas) es completamente diferente (24504 vs 100), lo que justifica que la concesión de memoria (Memory Grant)  para esta invocación NO sea la apropiada.

Si nos fijamos en la operación SORT, el número de filas estimadas y el número de filas actuales (afectadas) es completamente diferente (24504 vs 100), lo que justifica que la concesión de memoria (Memory Grant)  para esta invocación NO sea la apropiada.

Si revisamos la salida de mensajes, de nuevo no se ha utilizado ninguna Tabla de Trabajo (Work Table).

Si revisamos la salida de mensajes, de nuevo no se ha utilizado ninguna Tabla de Trabajo (Work Table).

Aunque sin embargo, si volvemos a consultar la DMV sys.dm_io_virtual_file_stats para identificar las lecturas y escrituras realizadas sobre TEMPDB, podremos comprobar que el número de lecturas y escrituras sobre TEMPDB ha aumentado, pero como hemos visto, esto no ha sido por la creación de Tablas de Trabajo (Work Tables). ¿Por qué ha sido?

Aunque sin embargo, si volvemos a consultar la DMV sys.dm_io_virtual_file_stats para identificar las lecturas y escrituras realizadas sobre TEMPDB, podremos comprobar que el número de lecturas y escrituras sobre TEMPDB ha aumentado, pero como hemos visto, esto no ha sido por la creación de Tablas de Trabajo (Work Tables). ¿Por qué ha sido?

Al revisar nuestra Traza de SQL Profiler, podemos ver que se ha producido un evento Sort Warning. Cáspita !!! De nuevo, hemos sufrido un desbordamiento sobre TEMPDB (spilling to TEMPDB) por una subestimación de memoria de consulta, aunque en esta ocasión, no ha sido por la reutilización del Plan de Ejecución, sino por la existencia de estadísticas mal actualizadas.

Al revisar nuestra Traza de SQL Profiler, podemos ver que se ha producido un evento Sort Warning. Cáspita. De nuevo, hemos sufrido un desbordamiento sobre TEMPDB (spilling to TEMPDB) por una subestimación de memoria de consulta, aunque en esta ocasión, no ha sido por la reutilización del Plan de Ejecución, sino por la existencia de estadísticas mal actualizadas.

Era de temer. El tamaño del fichero de datos de TEMPDB ahora es mayor que al principio del artículo.

Era de temer. El tamaño del fichero de datos de TEMPDB ahora es mayor que al principio del artículo.

Conclusiones y Script SQL de ejemplo

A través del presente artículo hemos pretendido mostrar como la existencia de estadísticas poco actualizadas y la necesidad de generar Planes de Ejecución con Operadores Costosos (Expensive Operators) que requieran de concesiones de Memoria de Consulta (Memory Grants), puede facilitar la creación de eventos de Hash Warning y Sort Warning, debido a que el Optimizador de Consultas de SQL Server genera el Plan de Ejecución utilizando las (incorrectas) estadísticas de que dispone. En este escenario, se crea un Plan de Ejecución que asume un número de filas (estimadas), y al ejecutarse la consulta descubre que el número de filas reales es muy diferente, lo cual, puede provocar problemas de subestimación de memoria (y en consecuencia, eventos Hash Warning y Sort Warning) o de sobrestimación de memoria (despilfarrando memoria). En esta ocasión, no estamos sufriendo una reutilización del Plan de Ejecución, pero igualmente, incurrimos en los mismos problemas de estimación de memoria que vimos en los anteriores artículos de esta serie.

Como en casos anteriores, no podía faltar el Script SQL con el código de ejemplo.

Descargar código SQL de ejemplo de Sort Warnings y falseo de estadísticas (SQLServer_Hash_Warning_Sort_Warnings_Estadisticas.zip)

Poco más por hoy. Como siempre, confío que la lectura resulte de interés.

 


[Fecha del Artículo (UTC): 03/02/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

Marzo de 2017 (3)
Enero de 2017 (4)
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