Teoría BBDD

Como vimos en el pasado artículo, cuando gestionamos bases de datos en SQL Server, uno de los aspectos más importantes es asegurarnos de que las estadísticas están actualizadas. Las estadísticas son fundamentales para que el optimizador de consultas del motor de SQL Server pueda tomar decisiones eficientes sobre cómo ejecutar las consultas. SQL Server nos proporciona varias formas de actualizar estas estadísticas, y dos de las más comunes son el comando UPDATE STATISTICS y el procedimiento almacenado sp_updatestats. En este artículo, vamos a analizar a fondo las diferencias entre ambos, cómo y cuándo utilizarlos, y qué impacto tienen en el rendimiento general de nuestras bases de datos.

Importancia de las estadísticas en SQL Server

Las estadísticas en SQL Server son colecciones de datos que describen la distribución de valores en una o más columnas de una tabla o índice. Estas estadísticas ayudan al optimizador de consultas a estimar la cantidad de filas que devolverá una consulta, lo que a su vez le permite seleccionar los planes de ejecución más eficientes.

Cuando las estadísticas están desactualizadas, el optimizador puede tomar decisiones incorrectas sobre los planes de ejecución, lo que provoca un rendimiento deficiente en las consultas. De ahí la importancia de mantenerlas actualizadas, especialmente en bases de datos con un alto volumen de inserciones, actualizaciones o eliminaciones de datos.

¿Qué es UPDATE STATISTICS?

El comando UPDATE STATISTICS es una instrucción explícita que actualiza las estadísticas para una tabla o índice en particular. Nos ofrece un control muy granular sobre qué estadísticas actualizar y cómo hacerlo. Podemos especificar un conjunto de opciones que nos permiten, por ejemplo, actualizar las estadísticas basadas en un muestreo de datos o una recolección completa de todos los datos.

Sintaxis básica de UPDATE STATISTICS

Para actualizar una estadística en concreto podemos usar el comando UPDATE STATISTICS con la siguiente sintaxis:

UPDATE STATISTICS [NombreTabla] [NombreEstadística]

Sin embargo, podemos necesitar actualizar todas las estadísticas de una tabla llamada en concreto, en ese caso escribiremos:

UPDATE STATISTICS [NombreTabla] ;

Y si queremos actualizar una estadística específica de un índice, podríamos usar el nombre del indice en vez de el nombre de la estadística:

UPDATE STATISTICS [NombreTabla]  [NombreIndice] ;

El comando UPDATE STATISTICS también nos permite utilizar varias opciones avanzadas como FULLSCAN, SAMPLE, y RESAMPLE, que determinan el método que SQL Server utiliza para actualizar las estadísticas.

• FULLSCAN fuerza a SQL Server a leer todas las filas de la tabla para actualizar las estadísticas.
• SAMPLE nos permite definir un porcentaje o número de filas de la tabla que se usarán para actualizar las estadísticas.
• RESAMPLE reutiliza las configuraciones de muestreo anteriores para actualizar las estadísticas.

Casos de uso de UPDATE STATISTICS

El uso de UPDATE STATISTICS es apropiado cuando necesitamos un control fino sobre el proceso de actualización de estadísticas. Por ejemplo en bases de datos críticas. En entornos de producción donde el rendimiento es crucial, y necesitamos asegurarnos de que las estadísticas de ciertas tablas grandes o muy consultadas se actualizan con precisión. Otro caso de uso es con tablas con datos muy volátiles. Si tenemos tablas que cambian frecuentemente, como las que contienen datos transaccionales, las estadísticas pueden quedar obsoletas rápidamente. En estos casos, podemos forzar la actualización periódica de las estadísticas con UPDATE STATISTICS.

Limitaciones de UPDATE STATISTICS

La principal desventaja de UPDATE STATISTICS es que requiere que seleccionemos manualmente las tablas o índices que deben ser actualizados. Esto puede ser laborioso en bases de datos con muchas tablas y estadísticas. Además, si no seleccionamos las estadísticas adecuadas, podríamos pasar por alto aquellas que se han desactualizado, lo que afectaría el rendimiento.

¿Qué es sp_updatestats?

sp_updatestats es un procedimiento almacenado proporcionado por SQL Server que actualiza todas las estadísticas en la base de datos actual que hayan sido marcadas como obsoletas. Este procedimiento es mucho más conveniente cuando queremos actualizar las estadísticas de toda la base de datos de forma masiva y automática, sin tener que preocuparnos por cada tabla o índice en particular.

Cómo funciona sp_updatestats

Cuando ejecutamos sp_updatestats, SQL Server examina todas las tablas y determina qué estadísticas deben actualizarse en función de la propiedad modification_counter (contador de modificaciones). Solo las estadísticas que hayan sufrido cambios significativos (según los algoritmos internos de SQL Server) serán actualizadas, lo que optimiza el uso de los recursos del servidor. Para ejecutarlo simplemente usamos:

EXEC sp_updatestats;

Al hacerlo, SQL Server actualiza automáticamente las estadísticas necesarias sin necesidad de especificar tablas, índices o configuraciones adicionales.

Casos de uso de sp_updatestats

El uso de sp_updatestats es más apropiado cuando necesitamos realizar una actualización general de las estadísticas en toda la base de datos. Algunos ejemplos de uso incluyen el mantenimiento periódico o las bases de datos con poca actividad.

En bases de datos grandes, donde no queremos revisar manualmente todas las tablas o índices, podemos usar sp_updatestats como parte de nuestras tareas programadas de mantenimiento para asegurarnos de que las estadísticas estén razonablemente actualizadas. Por el contrario, si nuestras bases de datos no experimentan muchos cambios de datos, sp_updatestats puede sernos suficiente para mantener las estadísticas actualizadas sin un impacto significativo en el rendimiento.

Limitaciones de sp_updatestats

Aunque sp_updatestats es conveniente, no nos ofrece el mismo nivel de control que UPDATE STATISTICS. Al ser un procedimiento almacenado, SQL Server decide qué estadísticas actualizar basándose en su propio criterio, lo que puede no ser siempre ideal en situaciones donde necesitamos precisión y control. Además, puede no actualizar todas las estadísticas que en realidad lo necesitan si los cambios en los datos no alcanzan los umbrales establecidos por SQL Server.

Diferencias clave entre UPDATE STATISTICS y sp_updatestats

Como acabamos de ver, los métodos de actualización manual de estadísticas tienen sus diferencias, lo que hace que cada uno sea indicado para un caso concreto. 

Mientras que UPDATE STATISTICS nos permite un control muy específico sobre qué estadísticas actualizar, sp_updatestats es una solución más generalizada.

Por otro lado, sp_updatestats es menos intensivo en términos de recursos, ya que solo actualiza las estadísticas que SQL Server considera desactualizadas, mientras que UPDATE STATISTICS puede ser más intensivo, especialmente si usamos opciones como FULLSCAN.

En cuanto a la sencillez, sp_updatestats es mucho más sencillo de utilizar en escenarios donde no necesitamos un control tan granular sobre las estadísticas. Sin embargo, si necesitamos actualizar solo ciertas tablas o índices críticos, UPDATE STATISTICS es la mejor opción.

Por último, sp_updatestats, gracias a sus características, es más adecuado para procesos automáticos de mantenimiento, mientras que UPDATE STATISTICS puede necesitar más intervención manual, dependiendo del caso de uso.

Cuándo utilizar cada uno

La elección entre UPDATE STATISTICS y sp_updatestats depende de las necesidades específicas de nuestro entorno de base de datos. Si estamos administrando una base de datos crítica con muchas consultas y necesitamos un rendimiento óptimo en tablas específicas, UPDATE STATISTICS con opciones avanzadas como FULLSCAN es la mejor opción. Esto garantiza que las estadísticas se actualicen con precisión y se basen en datos reales y completos pero tendrá un alto coste en recursos.

Por otro lado, si buscamos un enfoque menos manual y necesitamos actualizar las estadísticas de toda la base de datos sin dedicar demasiado tiempo a configurar el proceso, sp_updatestats es una opción rápida y eficaz, especialmente cuando se utiliza en combinación con tareas programadas de mantenimiento.

Conclusión

Mantener las estadísticas actualizadas en SQL Server es una parte fundamental del mantenimiento de la base de datos para asegurar el rendimiento óptimo de las consultas. Mientras que UPDATE STATISTICS nos da un control detallado sobre qué estadísticas actualizar y cómo hacerlo, sp_updatestats ofrece una solución más automatizada y general para mantener la base de datos en buen estado.

La clave está en conocer cuándo utilizar cada enfoque. Si gestionamos bases de datos con altos volúmenes de datos o con requisitos específicos de rendimiento, optar por UPDATE STATISTICS puede ser lo más adecuado. Sin embargo, en escenarios de mantenimiento general, sp_updatestats nos proporciona una forma conveniente y eficaz de mantener las estadísticas actualizadas sin esfuerzo manual adicional. Como siempre, es fundamental realizar pruebas y monitorear el impacto de estas operaciones en el rendimiento de nuestras consultas y en el uso de los recursos del sistema.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Las estadísticas en SQL Server son fundamentales para que el optimizador de consultas pueda generar planes de ejecución eficientes. Sin ellas, el rendimiento de las consultas se vería gravemente afectado, ya que el motor de base de datos se basa en estas estadísticas para estimar cuántas filas serán procesadas, seleccionar los índices correctos, y en general, tomar decisiones optimizadas. En este artículo, veremos los diferentes tipos de actualización de estadísticas en SQL Server: las actualizaciones automáticas, las automáticas asimétricas y las manuales.

¿Qué son las estadísticas en SQL Server?

Antes de entrar en los tipos de actualización de estadísticas, es importante tener claro qué son las estadísticas en SQL Server. Estas, son colecciones de metadatos que describen la distribución de los valores en una o más columnas de una tabla o índice. SQL Server crea y mantiene estas estadísticas para ayudar al optimizador de consultas a evaluar de manera precisa el costo de ejecutar una consulta.

Una estadística consta de un histograma, que es un resumen de los valores en la columna, junto con un conjunto de densidades que indican cuán distribuidos están esos valores. Esto le permite a SQL Server hacer predicciones precisas sobre las operaciones de búsqueda, escaneo y uniones entre tablas.

Actualización automática de estadísticas en SQL Server

SQL Server incluye una opción que permite que las estadísticas se actualicen automáticamente cuando el sistema detecta que han quedado desfasadas. Esta es la opción, configurable a nivel de base de datos, y se conoce como AUTO_UPDATE_STATISTICS y está activada por defecto.

¿Cuándo se actualizan automáticamente?

Las estadísticas se actualizan automáticamente cuando SQL Server detecta que ha habido un cambio significativo en la distribución de los datos en una tabla o índice. De manera general, SQL Server utiliza un umbral basado en el número de filas modificadas:

1. Si una tabla tiene menos de 500 filas, las estadísticas se actualizan después de que 500 modificaciones ocurran en la tabla.
2. Si una tabla tiene más de 500 filas, se actualizan cuando el número de filas modificadas supera el 20% de la tabla más 500 filas.

En versiones anteriores a SQL Server 2016, este umbral era distinto y podía ser bastante ineficiente, especialmente en tablas grandes, ya que requería una cantidad considerable de cambios en los datos para activar la actualización automática. Esto significaba que las estadísticas podían quedarse desactualizadas por un largo tiempo si los cambios en los datos eran graduales.

Beneficios de la actualización automática

El mayor beneficio de las estadísticas automáticas es que reduce la necesidad de intervención manual. El sistema se encarga de mantener las estadísticas al día, lo que es útil en entornos donde no se dispone de un DBA que monitorice constantemente las operaciones. Sin embargo, en ciertas situaciones, especialmente cuando el volúmen de datos empieza a crecer, este mecanismo puede no ser suficiente para garantizar el rendimiento óptimo, lo que nos obliga a añadir otros mecanismos de actualización adicionales.

Actualización automática asimétrica de estadísticas

SQL Server introdujo en versiones recientes una característica conocida como Asynchronous Statistics Update o AUTO_UPDATE_STATISTICS_ASYNC, que se traduce en actualizaciones automáticas de estadísticas en segundo plano. Esta característica permite que las estadísticas se actualicen de manera asíncrona, es decir, sin detener la ejecución de la consulta que disparó la necesidad de actualización.

¿Cómo funcionan las actualizaciones asimétricas?

Cuando el modo AUTO_UPDATE_STATISTICS_ASYNC está habilitado, SQL Server no espera a que las estadísticas se actualicen antes de ejecutar la consulta. En lugar de detenerse y esperar a que las estadísticas se regeneren, la consulta se ejecuta utilizando el plan basado en las estadísticas antiguas. Luego, en segundo plano, SQL Server actualiza las estadísticas para futuras consultas.

Este enfoque tiene varias ventajas, ya que evita que las consultas sufran retardos debido a la actualización de estadísticas en tablas grandes, lo que puede ser un proceso costoso y demorado. Sin embargo, estaremos ejecutando las consultas con un plan subóptimo que podría afectar gravemente al rendimiento.

Cuándo utilizar las actualizaciones asimétricas

Las actualizaciones asimétricas son especialmente útiles en escenarios donde se prioriza la latencia de las consultas sobre la exactitud inmediata de las estadísticas. Por ejemplo, en sistemas OLTP de alta concurrencia donde la velocidad de respuesta es crítica, habilitar las actualizaciones asimétricas puede mejorar la experiencia del usuario.

No obstante, hay que tener cuidado, ya que la primera consulta que se ejecute con estadísticas obsoletas podría tener un plan de ejecución subóptimo, lo que afectaría el rendimiento de esa consulta específica. Sin embargo, consultas posteriores, que se ejecuten una vez que esa actualización de estadística haya concluido, deberían beneficiarse de las estadísticas actualizadas.

Actualización manual de estadísticas

Por último, tenemos la opción de actualizar las estadísticas de manera manual. Esto se hace cuando el DBA o el equipo de desarrollo tenemos un control más preciso sobre cuándo y cómo deben actualizarse las estadísticas en el sistema. Las actualizaciones manuales se realizan mediante el comando UPDATE STATISTICS, o utilizando los procedimientos almacenados del sistema como sp_updatestats.

¿Cuándo realizar actualizaciones manuales?

Es recomendable hacer actualizaciones manuales en los siguientes escenarios:

• Carga masiva de datos: Cuando se insertan, actualizan o eliminan grandes cantidades de datos en una tabla, es probable que las estadísticas se queden desactualizadas antes de que el umbral de actualización automática se alcance. En este caso, actualizar las estadísticas manualmente puede mejorar drásticamente el rendimiento de las consultas subsiguientes.
• Procesos de ETL: En operaciones de ETL donde se transforman y mueven grandes volúmenes de datos, realizar una actualización manual de estadísticas al final del proceso puede ayudar a asegurar que las consultas que utilicen esos datos optimicen su ejecución.
• Consultas de bajo rendimiento: Si observamos un descenso significativo en el rendimiento de ciertas consultas, puede ser útil actualizar manualmente las estadísticas para asegurarnos de que el optimizador de consultas tiene información precisa y actualizada sobre la distribución de los datos.

Estrategias de actualización manual

Existen diferentes estrategias para realizar actualizaciones manuales. Una opción es usar el comando UPDATE STATISTICS directamente sobre tablas específicas o sobre todas las tablas de una base de datos. Otra opción más eficiente es utilizar sp_updatestats, que actualiza las estadísticas de todas las tablas que hayan sufrido modificaciones, evitando el coste innecesario de actualizar estadísticas que no requieren actualización.

Además, SQL Server permite configurar el grado de muestreo en las estadísticas mediante el parámetro WITH FULLSCAN, que obliga a SQL Server a escanear todas las filas en lugar de hacer un muestreo. Esto es especialmente útil cuando queremos asegurar la mayor precisión posible, aunque puede ser más costoso en términos de tiempo.

Actualización de estadísticas como parte de un plan de mantenimiento

Aunque las actualizaciones automáticas funcionan bien en la mayoría de los casos sencillos, en cuanto la base de datos y los requerimientos crecen, puede ser recomendable que incluyamos la actualización de estadísticas como parte de nuestros planes de mantenimiento. Esta práctica asegura que las estadísticas estén siempre actualizadas de forma programada, evitando que se queden obsoletas y afecten el rendimiento del sistema.

Implementación de un plan de mantenimiento

El uso de planes de mantenimiento en SQL Server es una estrategia común para tareas recurrentes como la reconstrucción de índices, la limpieza de la base de datos y la actualización de estadísticas. Sin embargo, los planes de mantenimiento estándar de SQL Server a menudo carecen de la flexibilidad y optimización que muchos entornos requieren. Es aquí donde entran los scripts de mantenimiento de Ola Hallengren, ampliamente reconocidos en la comunidad SQL Server por su robustez y capacidad de personalización.

Ola Hallengren ofrece un conjunto de scripts que permiten configurar tareas de mantenimiento, incluida la actualización de estadísticas, de manera muy eficiente. Estos scripts ofrecen un control avanzado sobre cómo y cuándo se deben actualizar las estadísticas, permitiendo especificar opciones como:

• Tablas específicas: Se pueden actualizar solo las estadísticas de las tablas que lo necesiten, en lugar de hacer un barrido completo de todas las tablas.
• Opciones de muestreo: El script permite configurar el tipo de muestreo para las estadísticas, lo que ofrece un mayor control sobre el rendimiento y precisión de las actualizaciones.
• Actualización condicional: El script de Ola Hallengren también permite que las estadísticas se actualicen solo cuando las tablas hayan alcanzado un cierto umbral de modificaciones, lo que evita actualizaciones innecesarias.

Si estás buscando una solución integral y flexible para el mantenimiento de estadísticas, puedes consultar los scripts de Ola Hallengren en su página oficial.

Configuración del script de Ola Hallengren para estadísticas

El script de mantenimiento de Ola Hallengren nos permite programar la actualización de estadísticas con una sintaxis sencilla y múltiples opciones. Un ejemplo básico de uso para actualizar estadísticas sería:

EXECUTE dbo.IndexOptimize
@Databases = 'MiBaseDeDatos',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

En este ejemplo, se actualizan todas las estadísticas de la base de datos especificada, pero solo aquellas que hayan sido modificadas. Esta opción es excelente para evitar el coste innecesario de actualizar estadísticas que no lo requieren.

Consideraciones y mejores prácticas

El uso adecuado de las actualizaciones automáticas, asimétricas, manuales y mediante planes de mantenimiento depende del contexto y la carga de trabajo del sistema. Algunas recomendaciones incluyen:

• Mantener activadas las actualizaciones automáticas: En la mayoría de los casos, es recomendable dejar activada la opción AUTO_UPDATE_STATISTICS para que SQL Server mantenga las estadísticas actualizadas sin intervención manual. Esto cubre dignamente la mayoría de los escenarios diarios sin añadir carga administrativa adicional.
• Utilizar actualizaciones asimétricas en entornos de alta concurrencia: Las actualizaciones asíncronas pueden mejorar el rendimiento en algunos sistemas OLTP donde la velocidad de respuesta es crucial, aunque deben usarse con precaución para evitar planes de ejecución subóptimos. Antes de activar esta opción te recomiendo probarla y ver si realmente es beneficiosa para ti..
• Incluir las actualizaciones en planes de mantenimiento: Utilizar herramientas avanzadas como los scripts de Ola Hallengren para actualizar estadísticas de forma regular y controlada es una práctica recomendada en entornos críticos o con grandes volúmenes de datos. Esto nos asegura que las estadísticas estén siempre actualizadas, reduciendo el riesgo de que las consultas sufran debido a información desactualizada.

Conclusión

Las estadísticas en SQL Server son fundamentales para el rendimiento de las consultas, y mantenerlas actualizadas es clave para que el optimizador de consultas genere planes de ejecución eficientes. Ya sea que optemos por actualizaciones automáticas, asimétricas, manuales o basadas en un plan de mantenimiento, tener una estrategia clara es esencial para asegurar un rendimiento óptimo en la base de datos.

Si deseas más detalles sobre cómo implementar planes de mantenimiento efectivos o utilizar los scripts de Ola Hallengren para optimizar tus estadísticas, consulta nuestro artículo sobre planes de mantenimiento en SQL Server para obtener una visión completa de cómo automatizar y mejorar estas tareas rutinarias.

 Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!