Rendimiento

Ago

2024

¿A qué se debe el crecimiento excesivo de bases de datos?

El crecimiento descontrolado de las bases de datos es una preocupación común para los administradores de sistemas y bases de datos. El aumento natural de los datos puede ser una explicación pero, en ocasiones, este crecimiento puede estar influenciado por errores de modelado, configuraciones inadecuadas y prácticas de gestión poco eficaces. En este artículo, abordaremos algunos errores comunes que contribuyen a este problema y veremos soluciones prácticas para tratar de evitarlo.

Crecimiento normal de los datos

Antes de entrar en detalles sobre cómo controlar el crecimiento de nuestros datos tenemos que dejar clara una cosa. Tu base de datos va a crecer, es el comportamiento común. El crecimiento natural y orgánico de las bases de datos es un fenómeno esperado en cualquier sistema en funcionamiento. A medida que se almacenan más datos, ya sea por la expansión de la empresa, el aumento de las operaciones o la recopilación de información de clientes y transacciones, es normal que las bases de datos crezcan. Este crecimiento, aunque esperado, debe ser gestionado adecuadamente para evitar problemas de rendimiento y costes innecesarios.

Crecimiento por errores de modelado de bases de datos

Una de las áreas fundamentales donde se pueden originar problemas de crecimiento en bases de datos es en el diseño y modelado de la propia base de datos. Errores como la falta de normalización adecuada o la sobrecarga de índices pueden llevar a un uso ineficiente del espacio y un crecimiento innecesario de la base de datos.

Falta de normalización adecuada

Una de las causas más frecuentes del crecimiento innecesario de bases de datos que he podido contemplar a lo largo de los años es una falta de normalización adecuada. La normalización ayuda a minimizar la redundancia de datos y garantiza la integridad referencial. Sin embargo, cuando no se aplica correctamente, puede resultar en la duplicación de datos a lo largo de varias tablas, inflando el tamaño total de la base de datos.

Para ponerle solución, es recomendable revisar el diseño de las bases de datos para asegurar que estén normalizadas correctamente. Aunque en algunas situaciones específicas pueda ser necesario que desnormalicemos los datos para mejorar el rendimiento, sobre todo en entornos analíticos y de BI, esta decisión debemos tomarla con precaución y basándonos en un análisis sólido de las necesidades de la aplicación.

Sobrecarga de índices

Otro de los errores que más se cometen es el uso excesivo de índices. Es tan común ver bases de datos con índices innecesarios o duplicados como bases de datos sin índices. Este es otro problema que puede contribuir al crecimiento de las bases de datos ya que los índices nonclustered son objetos separados de la tabla que requieren su propio almacenamiento duplicando los datos en cada índice que aparezcan. Aunque los índices son fundamentales para acelerar las consultas, mantener demasiados puede llevar a un aumento innecesario en el tamaño de la base de datos, además de afectar el rendimiento durante las operaciones de inserción, actualización y eliminación.

La solución es sencilla, revisar los índices que no se utilizan y eliminarlos, pero a la vez requiere de conocimientos avanzados tanto de administración como del uso de la base de datos para no eliminar un índice que sí sea importante para el rendimiento. Otra buena práctica es usar índices filtrados cuando sea posible ya que pueden proporcionar beneficios de rendimiento específicos sin un aumento significativo en el espacio de almacenamiento.

Crecimiento por configuraciones inadecuadas en SQL Server

Además del diseño de la base de datos, la configuración del servidor SQL también puede influir significativamente en el tamaño y rendimiento de la base de datos. Configuraciones inadecuadas, como el modo de recuperación FULL sin una correcta gestión de los logs de transacciones, o una configuración incorrecta del tamaño del archivo, pueden provocar un crecimiento inesperado y descontrolado.

Modo de recuperación FULL y log de transacciones

El modo de recuperación FULL es esencial para una recuperación completa en caso de desastres, ya que registra todas las transacciones. Sin embargo, si no se gestionan adecuadamente, los logs de transacciones pueden crecer de forma incontrolada, ocupando mucho espacio en disco. Cuando configuras este modo de recuperación tienes que habilitar una buena política de backups con backup log frecuentes o vas a encontrarte con problemas de crecimiento del log descontrolado.

Como hemos dicho la solución es sencilla en este caso, basta con implementar una estrategia de backups regulares de los logs de transacciones. Esto no solo controla el tamaño del log, sino que también es crucial para la recuperación de datos. Es importante revisar la frecuencia de los backups y ajustar las políticas de recuperación según las necesidades de la organización. Si no es necesario este tipo de copias regulares deberías replantearte el modo de recuperación de tu base de datos.

Configuración inadecuada del tamaño del archivo

Es importante entender la diferencia entre el tamaño del fichero y el tamaño de los datos, ya que esta distinción puede proporcionar una visión más clara sobre cómo se está utilizando el espacio en la base de datos y qué medidas pueden ser necesarias para optimizar su rendimiento y almacenamiento. En SQL Server los archivos de base de datos se dimensionan más allá del tamaño real que tienen ocupado por los datos para que las futuras escrituras sean más rápidas. Este espacio libre en los ficheros es importante, por tanto, para el rendimiento pero hay que saber gestionarlo. Una configuración inicial incorrecta del tamaño de los archivos de datos y logs, junto con opciones de autogrowth mal configuradas, puede resultar en un crecimiento fragmentado y subóptimo de los archivos. Esto puede llevar a un uso ineficiente del espacio en disco y afectar negativamente el rendimiento.

Para evitar estos problemas debemos configurar adecuadamente el tamaño inicial de los archivos de datos y logs basándonos en las previsiones de crecimiento de la base de datos. Sin embargo, las bases de datos cambian con el tiempo y deberemos ajustar los incrementos de autogrowth para minimizar la fragmentación y asegurar un uso eficiente del espacio en disco.

Crecimiento por errores de aplicación

Los errores en el diseño y configuración no son las únicas fuentes de problemas. Las aplicaciones que interactúan con la base de datos pueden causar un crecimiento descontrolado, especialmente si no gestionan adecuadamente las transacciones o si insertan datos de manera ineficiente.

Errores de aplicación en la gestión de transacciones

Uno de los errores más comunes que puede llevar al crecimiento descontrolado de los logs de transacciones es el manejo inadecuado de las transacciones por parte de las aplicaciones. Esto incluye transacciones que permanecen abiertas por mucho tiempo o que no se cierran correctamente, lo cual puede causar un aumento innecesario en el tamaño del log.

Debemos revisar y optimizar el código de la aplicación para asegurarnos de que las transacciones se manejen de manera eficiente. Es crucial que las transacciones sean lo más cortas posibles y que se cierren correctamente para evitar el crecimiento innecesario del log. Prestaremos una atención especial a las transacciones de larga duración o aquellas que se producen con demasiada frecuencia.

Errores por insertar datos erróneamente

Otra causa de crecimiento desmedido es la inserción de datos redundantes debido a errores de lógica en la aplicación o a escrituras descontroladas en tablas de log de actividad de las aplicaciones o procesos. Esto puede suceder cuando las aplicaciones insertan datos duplicados debido a fallos en la validación o la falta de controles adecuados para evitar duplicados.

Para evitarlo, deberemos implementar mecanismos de validación en el nivel de la aplicación para prevenir la inserción de datos redundantes. Además, utilizar restricciones de unicidad y primary keys en la base de datos para garantizar la unicidad de los registros. Las tablas de auditoría o de log deberán limpiarse frecuentemente y revisar que la información registrada es útil. Por ejemplo, una buena práctica si un proceso tiene una lógica de reintentos es solo registrar una vez el error y poner una columna con un contador de repeticiones si el error es el mismo en cada reintento.

Evitar el crecimiento con mantenimiento

La gestión y mantenimiento regulares de nuestros datos es fundamental para controlar el tamaño de las bases de datos SQL Server. Esto incluye la limpieza de datos obsoletos y una monitorización constante del rendimiento y el uso del almacenamiento. Sin una gestión adecuada, es fácil que el crecimiento se descontrole, afectando el rendimiento y la eficiencia del sistema.

Limpieza de datos obsoletos

Las bases de datos tienden a acumular datos obsoletos o redundantes que ya no son útiles para la operación actual, contribuyendo al crecimiento desmedido. Esto es especialmente problemático en sistemas que carecen de políticas de limpieza o archivado de datos. Establecer procedimientos regulares de limpieza para eliminar registros obsoletos y/o implementar políticas de retención de datos y archivado que permitan gestionar los datos históricos de manera eficiente, liberará espacio en nuestras bases de datos más activas.

Monitorización y mantenimiento

Para terminar con este artículo, y aunque esto no es un error como tal no quería dejar de comentar que la falta de monitorización y mantenimiento adecuado puede permitir que problemas de crecimiento pasen desapercibidos. Esto incluye no solo el seguimiento del tamaño de los datos y logs, sino también la identificación de problemas de rendimiento que pueden indicar un uso ineficiente de recursos. Utilizar herramientas de monitorización propias o de terceros y establecer alertas ante crecimientos inusuales de bases de datos y logs puede evitarnos el problema de que el servidor termine quedándose sin espacio y deje de admitir nuevas transacciones.

Conclusión

El crecimiento excesivo de las bases de datos SQL Server a menudo se debe a una combinación de errores de modelado, configuraciones inadecuadas y prácticas de gestión insuficientes. Abordar estos problemas con soluciones prácticas, como la normalización adecuada, la gestión eficiente de índices, y la implementación de políticas de mantenimiento y backups, puede ayudar a controlar el tamaño de la base de datos y asegurar un rendimiento óptimo. Con un enfoque proactivo en estas áreas, seremos capaces de manejar el crecimiento de las bases de datos de manera más efectiva y evitar problemas futuros relacionados con el almacenamiento y el rendimiento.

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!

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Jul

2024

Bases de datos de sistema en SQL Server

Todos los que en algún momento hemos decidido profundizar en SQL Server nos hemos llegado a sentir abrumados por la cantidad de componentes internos y su complejidad de funcionamiento que tiene este motor de base de datos. A esto hay que sumar, que muchos de estos objetos de sistema carecen de documentación oficial en muchas ocasiones lo que los hace aún más opacos para el DBA novel. Entre estos componentes, las bases de datos del sistema juegan un papel fundamental. Estas bases de datos no solo son esenciales para el funcionamiento del servidor, sino que cuanto más conoces sus componentes más te facilitan la administración, la seguridad y la optimización del rendimiento de las bases de datos de usuario. En este artículo, vamos a hablar justo de eso, de las bases de datos del sistema en SQL Server, desglosando su propósito y funciones clave.

¿Qué son las Bases de Datos del Sistema en SQL Server?

Las bases de datos del sistema en SQL Server son un conjunto de bases de datos predefinidas que almacenan información interna sobre la configuración del servidor, los metadatos de las bases de datos de usuario y otros aspectos críticos de la operación del sistema. Estas bases de datos son fundamentales para el funcionamiento del servidor y la gestión de los datos que este contiene.

SQL Server necesita varias bases de datos del sistema, entre las que vamos a encontrar a las conocidas master, msdb, model, tempdb pero también alguna oculta como Resource Database y otras que solo aparecen si usamos características concretas como la SSISDB o la base de datos distribution. Cada una de estas bases de datos tiene una función específica que desempeñar y, en conjunto, permiten el correcto funcionamiento del servidor.

Base de datos master: El Corazón del Sistema

La base de datos master es la más crítica de todas. Almacena información crucial sobre el servidor SQL en sí mismo, incluyendo la configuración del servidor, los detalles de las bases de datos de usuario, y la información de inicio de sesión y permisos. En resumen, master actúa como el catálogo central de SQL Server.

Cualquier pérdida o corrupción de la base de datos master puede tener consecuencias graves, haciendo esencial contar con copias de seguridad regulares y verificadas de esta base de datos. Además, la restauración de master es un proceso delicado que debe realizarse con precaución para evitar daños adicionales al sistema.

msdb: La Base de Datos de sistema de los Jobs

La base de datos msdb es otra pieza clave en SQL Server. Esta base de datos almacena información sobre trabajos de SQL Server Agent, operaciones de respaldo y restauración, alertas, y operadores. También almacena los paquetes de los planes de mantenimiento nativos de SQL Server. En resumen, msdb es la base de datos encargada de la automatización y planificación de tareas dentro del servidor.

Para los DBAs es importantísima pues conocer sus objetos internos nos abre la puerta hacia la automatización, permitiéndonos programar y supervisar tareas que faciliten la administración diaria del sistema. Esto incluye desde respaldos automáticos hasta el envío de alertas cuando se detectan problemas. No debemos tampoco descuidar su mantenimiento pues es propensa a crecer en exceso al almacenar todo el historial de copias de seguridad, restauración, jobs, etc…

model: La Bases de Datos de sistema modelo

La base de datos model es el modelo de referencia que usa SQL Server para la creación de nuevas bases de datos. Cada vez que creamos una nueva base de datos, el motor de SQL se basa en la estructura y las configuraciones definidas en model. Esto incluye configuraciones de tamaño inicial, collation, y otros aspectos fundamentales.

Como DBAs tenemos la opción de modificar la configuración de la base de datos model para establecer configuraciones predeterminadas para nuevas bases de datos. Esto, que puede parecer trivial, es muy importante ya que puede ahorrarnos tiempo y asegurar la consistencia en entornos donde se crean bases de datos con frecuencia.

tempdb: Espacio Temporal para la Ejecución de Consultas

La base de datos tempdb es de uso temporal y es donde SQL Server maneja operaciones de procesamiento intermedio, como ordenaciones y operaciones de hash que no caben en memoria. Además sirve de almacenamiento de tablas temporales. Debido a la naturaleza volátil de sus datos, la base de datos tempdb se recrea cada vez que el servidor SQL Server se reinicia.

La gestión efectiva de tempdb es crucial para el rendimiento general del sistema. Esto incluye la configuración adecuada de su tamaño, el número de archivos de datos, y la ubicación física de los archivos para evitar cuellos de botella en el I/O. Una de nuestras responsabilidades como DBAs es asegurarnos de que nunca se llene, pues en ese momento nuestra instancia dejará de admitir transacciones nuevas. En este post hablamos más sobre este tema.

Resource Database: De sistema pero escondida

La base de datos de recursos es una base de datos oculta que contiene todas las definiciones de sistema para objetos incluidos en SQL Server. Aunque no es directamente accesible, Resource Database juega un rol crucial en las actualizaciones y en la recuperación del sistema, al permitir la actualización de objetos del sistema sin afectar las bases de datos de usuario. Al no ser accesible no nos tenemos que preocupar por ella, de nada serviría.Aunque esta base de datos tiene su página dedicada de Microsoft, tampoco existe mucha documentación al respecto.

Bases de datos de sistema especiales

Como hemos comentado en la introducción, además de las bases de datos de sistema que podemos encontrar en todas las instalaciones de SQL Server, existen otras que siendo bases de datos de sistema solo se crean si activamos la característica que las requiere. Estas son la SSISDB y la distribution.

SSISDB: Base de datos interna de SSIS

La base de datos SSISDB, también conocida como la base de datos del Catálogo de SSIS, es crucial para la gestión de paquetes de SQL Server Integration Services (SSIS). Esta base de datos almacena los paquetes, configuraciones y datos de ejecución para las tareas de ETL (Extract, Transform, Load). Nos permite un control centralizado de los procesos de integración de datos, lo cual es esencial para mantener la consistencia y fiabilidad en los entornos de datos empresariales.

Para los DBAs, SSISDB ofrece herramientas avanzadas de gestión y monitorización, incluyendo la capacidad de gestionar versiones de paquetes, programar ejecuciones y revisar logs de errores de SSIS. Además, SSISDB es fundamental para garantizar la seguridad y la auditoría de los procesos de integración de datos. Si quieres profundizar en la administración de la base de datos SSISDB te recomiendo este artículo que publiqué hace tiempo.

distribution: Administración de la Replicación

La base de datos distribution es esencial para la característica de replicación en SQL Server. Esta base de datos se utiliza para almacenar metadatos y datos de cola que se requieren durante la replicación transaccional y de mezcla. Se crea en el servidor distribuidor y actúa como un intermediario entre el publicador y los suscriptores, ayudando a garantizar que los cambios en los datos se distribuyan de manera eficiente y coherente.

Para los DBAs, conocer internatemente la base de datos distribution nos va a ayudar con la monitorización y resolución de problemas de replicación. Una administración adecuada de esta base de datos es fundamental para evitar cuellos de botella y asegurar la sincronización continua de los datos entre las distintas instancias.

Conclusión

En resumen, las bases de datos del sistema en SQL Server son esenciales para el funcionamiento y la administración efectiva del servidor. Cada una cumple un rol específico, desde la administración centralizada y la configuración del servidor en master, hasta el manejo de automatización y tareas en msdb, la gestión de espacio temporal en tempdb, y la supervisión de procesos de integración y replicación en SSISDB y distribution.

Para los DBAs, comprender y manejar adecuadamente estas bases de datos es crucial. No solo nos permite mantener la estabilidad y el rendimiento del servidor, sino que también asegura que estemos preparados para recuperarnos rápidamente en caso de fallos o desastres. Por si esto fuera poco, conocer sus objetos (tablas, vistas, procedimientos y funciones) nos van a permitir automatizar gran parte de nuestro trabajo.

Mantenerse al día con las mejores prácticas para la gestión de estas bases de datos, así como entender sus interacciones y dependencias, es una tarea continua y vital en nuestro rol como administradores de bases de datos. Con el conocimiento adecuado, podemos no solo mantener nuestros sistemas funcionando sin problemas, sino también optimizar el rendimiento y la seguridad en nuestros entornos de SQL Server. 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!

Jul

2024

SQL Server Profiler

Hoy os quiero hablar de SQL Server Profiler. Aunque su soporte está discontinuado y en un futuro será eliminado de las características de SQL Server en favor de otras herramientas como xEvents, sigue siendo una de las herramientas más poderosas y versátiles a nuestra disposición cuando estamos depurando un problema con consultas SQL.

Durante las próximas líneas, veremos en profundidad el uso del SQL Server Profiler, sus funcionalidades avanzadas, su impacto en los recursos del sistema y cómo puede ayudarnos a mantener nuestros sistemas SQL Server funcionando de manera eficiente.

¿Qué es el SQL Server Profiler?

El SQL Server Profiler es una herramienta nativa de SQL Server que nos permite crear trazas para capturar y analizar eventos que ocurren principalmente en instancias de SQL Server. Aunque su uso principal es para capturar eventos de SQL Server, también es posible conectarlo a una instancia de SQL Server Analysis Services y, por tanto, a un modelo de Power BI. Los eventos que vamos a poder capturar pueden ser consultas T-SQL, procedimientos almacenados, transacciones y mucho más, dependerá de a qué tipo de servidor estemos conectados. Como habrás imaginado ya, poder monitorizar y registrar estos eventos es crucial para ayudarnos en la resolución de problemas, la optimización del rendimiento y la auditoría de actividades en nuestras bases de datos.

Principales Características del SQL Server Profiler

SQL Server Profiler, como hemos visto, nos puede ser de gran utilidad para descubrir qué es lo que está pasando en nuestro SQL Server. Esto lo hace una herramienta ideal para cualquier DBA que persiga un problema de rendimiento o para un desarrollador que necesite depurar una aplicación. Para ello, podemos destacar estas características principales de la herramienta:

Monitorización en Tiempo Real y más: SQL Server Profiler nos permite observar la actividad sobre la base de datos a medida que ocurre, lo cual es esencial para identificar y solucionar problemas de rendimiento de manera inmediata. Pero no solo eso, también nos permite guardar esos archivos para analizarlos en detalle más adelante.
Filtrado de Eventos: Como el resultado de registrar toda la actividad puede ser un fichero tan grande que sea casi imposible de consumir, SQL Server Profiler implementa una serie de filtros específicos para centrarnos en eventos particulares, evitando así la sobrecarga de información y facilitando la identificación de problemas concretos.
Reproducción de Trazas: Como ya os comentaba antes, podemos guardar los archivos de trazas pero, no solo para su visualización. SQL Server Profiler nos da la capacidad de abrir esas trazas conectados en un entorno de prueba, por ejemplo, y reproducir paso a paso, todos los eventos de la traza. Esto es increíblemente útil para el análisis de problemas y depuración de código sin afectar el sistema de producción.

Configuración y Uso del SQL Server Profiler

Cuando queremos usar SQL Server Profiler primero debemos iniciar una nueva sesión de traza. Al hacerlo, se nos presentará una serie de opciones de configuración que nos permitirán personalizar la traza según nuestras necesidades. Veamos los pasos.

Pasos Básicos para Iniciar una Nueva Trazabilidad

Abrir SQL Server Profiler: Lo encontramos en el menú de herramientas de SQL Server Management Studio (SSMS) o como aplicación instalada en tu sistema operativo.
Crear Nueva Traza: Seleccionamos «File» > «New Trace» y nos conectamos a la instancia que queremos monitorizar. Recuerda que puede ser SQL Server (on premise o Azure), SSAS o un modelo de Power BI.
Seleccionar Plantilla: Podremos elegir una de las plantillas predefinidas, si es que se ajustan a nuestras necesidades, o configurar una nueva.
Configurar Eventos y Columnas: En esta pestaña podremos personalizar los eventos y columnas que queremos capturar y mostrar. Abajo a la derecha encontrarás unos checks para mostrar todos los eventos y columnas, recuerda activarlos para ver todas las opciones disponibles.
Aplicar Filtros: Podremos configurar filtros específicos para reducir la cantidad de datos capturados y enfocarnos en eventos relevantes. Es interesante por ejemplo el filtro por base de datos o por usuario si es que tenemos claro donde está más o menos localizado el problema.
Iniciar la Traza: Una vez configurada, iniciamos la traza y comenzamos a monitorizar los eventos en tiempo real.
Una vez con la traza iniciada la podremos pausar y reanudar a nuestro gusto. También detener definitivamente la captura de eventos. Una vez finalizado podremos guardar el fichero para analizarlo o reproducirlo más tarde.

Análisis de Datos Capturados

Una vez que hemos capturado una traza, el siguiente paso es analizar los datos para identificar posibles problemas de rendimiento o errores en la aplicación.

Uno de los usos más comunes del SQL Server Profiler es identificar consultas lentas que pueden estar afectando el rendimiento de la base de datos. Podemos buscar eventos específicos como «SQL» o «RPC» y revisar las métricas de tiempo de ejecución, CPU y lecturas/escrituras de disco para cada consulta.

Los bloqueos y deadlocks también pueden ser un gran problema que queramos analizar, sobre todo en sistemas con alta concurrencia (y sin un nivel de aislamiento Read Committed Snapshot). Utilizando eventos como «Lock» y «Lock Chain», podemos identificar las transacciones involucradas en deadlocks y tomar medidas para resolverlos, ya sea optimizando las consultas o ajustando la configuración de bloqueo.

Impacto de SQL Server Profiler en el rendimiento

Como puedes imaginar todo esto tiene una gran contrapartida y es su impacto en el rendimiento. El SQL Server Profiler puede generar una cantidad significativa de datos, especialmente en sistemas con alta actividad. Cada evento capturado se guarda en un archivo de traza, lo que puede resultar en un uso considerable del espacio en disco. Si no tenemos cuidado podemos llegar a llenar el disco.

Para evitar este problema de consumo excesivo de disco debemos tomar precauciones y aplicar filtros para capturar solo los eventos necesarios, de esta manera reduciremos la cantidad de datos registrados. También es importante configurar límites de tamaño para los archivos de traza y habilitar la opción de sobrescribir los archivos más antiguos. Por último debemos asegurarnos de que las trazas se almacenan en volúmenes con suficiente capacidad para evitar problemas de espacio en disco.

Otro de los problemas principales que podemos encontrar durante el proceso de captura y registro de eventos por parte del SQL Server Profiler es que puede afectar el rendimiento del servidor. El impacto varía según la cantidad de eventos capturados y la configuración de la traza. A mayor frecuencia de eventos capturados, mayor será el impacto en el rendimiento. Capturar una gran cantidad de eventos y columnas innecesarias aumenta la carga en el sistema, como es obvio.

Trata de acotar tus trazas en el tiempo y de capturar los eventos mínimos imprescindibles para evitar saturar la CPU y RAM de tu servidor, utilizar plantillas predefinidas optimizadas para tareas específicas puede ayudarte en este sentido. Es vital en este sentido programar la captura de trazas durante periodos de baja actividad del sistema o solo durante el tiempo que tengas localizado que se produce el error que tratas de depurar en lugar de mantenerlas activas por tiempo prolongado.

Casos de Uso Avanzados del SQL Server Profiler

Como has podido ver en lo que llevamos de artículo, SQL Server Profiler es una herramienta muy útil para depurar código o localizar problemas de rendimiento. Pero, no solo nos va a servir para eso, a continuación os presento otros escenarios en los que nos puede ser de utilidad.

Auditoría de Seguridad

Aunque no es lo ideal, SQL Server Profiler también puede ser utilizado para auditorías de seguridad. Podemos capturar eventos relacionados con el acceso a datos sensibles, cambios en la configuración del servidor y actividades de inicio de sesión. Esto nos permite mantener un registro detallado de las acciones realizadas en la base de datos, lo cual es esencial para cumplir con normativas de seguridad y auditoría. Sin embargo, sus especificaciones y consumo de recursos hacen que no sea la herramienta ideal. En este sentido podremos usar mejor los eventos extendidos o la auditoría nativa de SQL Server.

Optimización de Procedimientos Almacenados

La optimización de procedimientos almacenados es una tarea crítica. Muchas aplicaciones usan estos objetos para sus procesos en lugar de ejecutar código Ad Hoc. Utilizando el Profiler, podemos capturar la ejecución de procedimientos y analizar su rendimiento. Esto nos va a permitir identificar procedimientos que requieren optimización, ya sea mediante la revisión del código T-SQL, la reestructuración de índices y estadísticas o la modificación de la lógica de las consultas.

Diagnóstico de Problemas de Red

En entornos distribuidos, los problemas de red pueden afectar significativamente al rendimiento de SQL Server. El Profiler nos permite capturar eventos relacionados con conexiones y desconexiones, así como latencias en la comunicación. Con esta información, podemos trabajar junto con los administradores de red para resolver problemas de conectividad y mejorar el rendimiento general del sistema.

Uso del SQL Server Profiler para Monitorizar Power BI

Aunque es un proceso no documentado, podemos usar el Profiler para capturar las consultas DAX de Power BI. Yo esto lo he visto funcionando en una charla de Francisco Gutierrez en los Power BI Days de Bilbao y me dejó sorprendido. No esperaba que una herramienta discontinuada por Microsoft tuviera la capacidad de interactuar con Power BI.

Conclusión

El SQL Server Profiler es una herramienta muy potente para cualquier DBA que busque mantener el rendimiento de sus bases de datos SQL Server. Desde la identificación de consultas lentas hasta la auditoría de actividades y la resolución de problemas de red, el Profiler nos proporciona una visión detallada y en tiempo real del comportamiento de nuestro sistema. Su capacidad para personalizar la captura de eventos y analizar datos detallados lo convierte en una herramienta versátil y poderosa. Sin embargo, es importante ser conscientes del impacto que el Profiler puede tener en los recursos del sistema y aplicar estrategias adecuadas para minimizar su uso de disco y su impacto en el rendimiento. Además, siempre que podamos, deberemos usar los eventos extendidos y familiarizarnos con ellos ya que al SQL Server Profiler no le queda mucho entre nosotros.

Jul

2024

DATETRUNC: Una novedad de SQL Server 2022

Aunque ya ha pasado un tiempo desde la llegada de SQL Server 2022 no es raro encontrarse con gente que está empezando a adoptarlo o, con gente que aun llevando tiempo trabajando en esta versión no conoce todas sus novedades. Entre estas nuevas funcionalidades, una de las más destacadas es la función DATETRUNC. Esta nueva función integrada de fecha y hora simplifica enormemente el trabajo con fechas, permitiendo a los desarrolladores y administradores de bases de datos realizar tareas comunes de manera más eficiente y con menos código.

Hasta ahora, manipular fechas en SQL Server requería el uso de múltiples funciones y a menudo resultaba en código complicado y difícil de leer. Aunque si has visto nuestro video sobre el tema ya serás un experto. Con la llegada de DATETRUNC, estas tareas se simplifican significativamente. La función DATETRUNC nos permite truncar una fecha a una parte específica, como año, mes, día, hora, minuto, etc., eliminando la necesidad de combinaciones complejas de funciones de fecha.

¿Qué es DATETRUNC?

La función DATETRUNC recibe como parámetro una fecha que le pasemos y la trunca al nivel de precisión que hayamos especificado. Esto es especialmente útil cuando necesitamos agrupar datos por períodos específicos, como meses o trimestres, o simplemente cuando queremos estandarizar las fechas a un nivel común para comparaciones más precisas.

Sintaxis de DATETRUNC

La sintaxis de la función DATETRUNC es sencilla y directa, solo tendremos que pasar el nivel de precisión al que queremos truncar y un valor tipo fecha o fecha y hora:

DATETRUNC ( datepart, date )

datepart: Especifica la parte de la fecha a la que queremos truncar. Puede ser año, trimestre, mes, día, hora, minuto, segundo, etc.
date: La fecha que queremos truncar.

Ejemplos Prácticos de DATETRUNC

Por si han quedado dudas, y como a caminar se aprende caminando vamos con algunos ejemplos prácticos:

Truncar a Año

Imaginemos que tenemos una fecha y queremos truncarla al inicio del año:

SELECT DATETRUNC(year, '2023-07-15 14:30:00') AS TruncatedDate;

Este código devolverá: 2023-01-01 00:00:00.000. Hemos truncado la fecha al primer día del año 2023.

Truncar a Mes

Si queremos truncar una fecha al inicio del mes seguiremos la misma lógica:

SELECT DATETRUNC(month, '2023-07-15 14:30:00') AS TruncatedDate;

El resultado será: 2023-07-01 00:00:00.000. La fecha se trunca al primer día del mes de julio de 2023.

Truncar a Día

Para truncar una fecha al inicio del día:

SELECT DATETRUNC(day, '2023-07-15 14:30:00') AS TruncatedDate;

El resultado será: 2023-07-15 00:00:00.000. En este caso, la hora, los minutos y los segundos se eliminan, dejando solo la fecha.

Aplicaciones Prácticas en el Mundo Real

Todo esto es muy bonito pero, lo importante es cómo usarlo y cuando. Ahora te voy a contar algunos ejemplos útiles que se me ocurren para DATETRUNC. Seguro que a nuestros amigos de BI que siempre están jugando con dimensiones de fechas se le ocurren un montón más de ejemplos.

Agrupación de Datos

Una de las aplicaciones más comunes de DATETRUNC es en la agrupación de datos. Por ejemplo, podemos querer agrupar ventas por mes:

SELECT
    DATETRUNC(month, OrderDate) AS SaleMonth,
    SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY DATETRUNC(month, OrderDate);

Este ejemplo agrupa las ventas por mes, facilitando la generación de reportes mensuales.

Comparaciones de Fechas

Otra aplicación que nos va a ser muy útil es en las comparaciones de fechas. Si queremos comparar sólo la parte de la fecha sin considerar la hora, podemos utilizar DATETRUNC:

SELECT *
FROM Events
WHERE DATETRUNC(day, EventDate) = '2023-07-15';

Aquí, estamos comparando solo la parte de la fecha de EventDate con el 15 de julio de 2023, ignorando la hora.

Ventajas de Usar DATETRUNC

Como podrás imaginar, si le he dedicado un tiempo a este artículo es porque creo que DATETRUNC tiene alguna ventaja para ti. Como ya hemos comentado una de sus grandes bazas es la sencillez del código, que simplifica su escritura y comprensión, pero no es solo eso, también vamos a notar mejoras en el rendimiento.

Código Más Limpio y Legible

Antes de la introducción de DATETRUNC, truncar fechas requería el uso de funciones como DATEADD y DATEDIFF en combinaciones complejas. Con DATETRUNC, el código es mucho más limpio y fácil de entender.

-- Sin DATETRUNC
SELECT DATEADD(month, DATEDIFF(month, 0, '2023-07-15 14:30:00'), 0) AS TruncatedDate;
-- Con DATETRUNC
SELECT DATETRUNC(month, '2023-07-15 14:30:00') AS TruncatedDate;

¿Mejor Rendimiento?

La función DATETRUNC, en teoría, está optimizada para el rendimiento, reduciendo la sobrecarga de cómputo comparado con las versiones anteriores que implicaban múltiples ejecuciones de funciónes. Aunque como siempre, sigue siendo mejor no usar funciones.

En este ejemplo vemos como con la función DATETRUNC el coste de la consulta es un 1% menor pero no se acerca en absoluto a los resultados cuando no usamos funciones de fecha. Es curioso porque Microsoft asegura que ha optimizado el rendimiento pero el comportamiento sigue siendo el mismo.

El motor de SQL dice: “No tengo ni idea de cuál es la primera fecha que produciría YEAR(2017) y es imposible que pueda adivinarlo por mi mismo. Mejor leeré todas las fechas desde los inicios de los tiempos hasta ahora.” Esto es absurdo, lo sé, pero es así, y es una de las razones por las que siempre se dice que evitemos usar funciones en la cláusula WHERE. Como ves con DATETRUNC en SQL Server 2022 no es diferente.

Conclusión

SQL Server 2022 introdujo innovaciones y mejoras en nuestra capacidad para gestionar y manipular datos de manera eficiente. La función DATETRUNC es una adición bienvenida, simplificando la manipulación de fechas y mejorando la legibilidad y el rendimiento del código. Al empezar a usar esta nueva función, podremos escribir consultas más claras y un poco más eficientes, facilitando nuestro trabajo diario. Sin embargo no es oro todo lo que reluce y, como toda función, debemos evitar su uso en los filtros.

Jul

2024

DBA vs IA: Fight! La prueba de si la IA te va a quitar el trabajo

Los avances en inteligencia artificial son imparables, eso está claro. Además está claro que son la tendencia en el mercado, parece que términos como big data, cloud o blockchain son cosa del pasado y ahora lo moderno es tener IA hasta en el inodoro. Todas las empresas compiten por añadir IA a sus productos a la par que apuestan por integrar asistentes como ChatGPT o Copilot a sus procesos para facilitar la vida de los trabajadores. Y ojo que a mi me parece muy bien el avance de la IA pero no debemos olvidarnos de nuestra propia inteligencia natural. Por lo menos hasta que esto avance un poco más y vivamos en un mundo donde trabajen robots con IA y nosotros podamos dedicarnos a la holganza más absoluta. No me enrollo más, que lo que os vengo a contar hoy es muy interesante, una prueba más de cómo está ahora la IA en el ámbito de la administración de base de datos. Que cada uno saque sus conclusiones de si puede o no abandonarse a la vida contemplativa gracias a que una máquina complete sus tareas.

Definiendo la prueba

Os pongo en situación, me encontraba yo desempeñando mis labores diarias, por las que me pagan no estas tareas del blog que lo hago por amor al arte. En ese momento estaba empezando a tomar el control de una nueva instancia de SQL, en concreto una instancia administrada en Azure. Se trata de una instancia compartida por varios microservicios donde cada uno de ellos tiene su base de datos, en principio, totalmente independiente a las demás. Por lo que he podido ver en el resumen de consumo de recursos del portal web de Azure, el uso de CPU es elevado en esta instancia y, por desgracia, ahí se queda todo lo que puedes ver en esta ventana. Igual que cuando miras el activity monitor de un SQL en local la verdad.

Como es un servidor compartido y hay varias bases de datos debo priorizar por lo que lo lógico es empezar por la que mayor consumo hace de los recursos. Partimos de una gran ventaja, al ser Azure Query Store está habilitado por defecto en todas las bases de datos recolectando datos sin que yo haya tenido que decírselo pero, esta es una solución que muestra datos a nivel base de datos y no de instancia. No me sirve de primeras. Necesito otra solución y me parece una buena oportunidad de poner a prueba a la IA antes de tirar de mi navaja suiza de scripts. Como estamos hablando de Azure vamos a hacer que todo quede en casa y preguntar a Copilot.

Será Copilot un buen DBA, pongámoslo a prueba

Os dejo con lo que estáis todos esperando, la conversación íntegra con Copilot. Luego al final os daré mis conclusiones.

Round 1

Roberto:

Tengo una instancia administrada de sql en azure con una docena de bases de datos. Necesito saber cual de las bases de datos tiene un mayor consumo de recursos. ¿Cómo puedo hacerlo?

Copilot:

Para monitorear el consumo de recursos de las bases de datos en una instancia administrada de SQL en Azure, puedes utilizar el servicio de Azure SQL Analytics en Azure Monitor. Aquí te dejo los pasos generales para hacerlo:
Habilita la supervisión de diagnóstico para tu instancia administrada de SQL Server en Azure. Esto se hace en la sección de “Diagnóstico” en la configuración de tu instancia en el portal de Azure.

Configura la exportación de registros a un espacio de trabajo de Log Analytics. Durante la configuración del diagnóstico, puedes elegir enviar los datos a un espacio de trabajo de Log Analytics existente o crear uno nuevo.

Instala la solución de Azure SQL Analytics desde la galería de soluciones en tu espacio de trabajo de Log Analytics.

Una vez que la solución esté instalada y los datos comiencen a fluir, puedes ver los paneles de Azure SQL Analytics para obtener detalles sobre el consumo de recursos. Hay un panel específico llamado “SQL Managed Instance Resource Usage” que proporciona detalles sobre el uso de recursos a nivel de base de datos.

Recuerda que puede llevar algún tiempo hasta que los datos estén disponibles en los paneles después de habilitar la supervisión de diagnóstico.

Espero que esto te ayude a identificar la base de datos que consume más recursos. Si tienes más preguntas, no dudes en preguntar. 😊

Round 2

Roberto:

¿Hay manera de hacerlo desde las herramientas nativas de SQL en SSMS?

Copilot

Sí, puedes usar las herramientas nativas de SQL Server Management Studio (SSMS) para monitorear el consumo de recursos de las bases de datos en una instancia administrada de SQL en Azure. Aquí te dejo los pasos generales para hacerlo:

Conéctate a tu instancia administrada de SQL Server en Azure usando SSMS.

Abre una nueva consulta en la base de datos que deseas monitorear.

Ejecuta una consulta de DMV (Dynamic Management View) para obtener información sobre el consumo de recursos. Por ejemplo, puedes usar la vista sys.dm_db_resource_stats para obtener estadísticas de uso de recursos a nivel de base de datos. Aquí tienes un ejemplo de cómo podría ser esta consulta:

SELECT

    database_name,

    avg_cpu_percent,

    avg_data_io_percent,

    avg_log_write_percent

FROM

    sys.dm_db_resource_stats

ORDER BY

    avg_cpu_percent DESC;

Esta consulta te dará el porcentaje promedio de CPU, I/O de datos y escritura de log utilizados por cada base de datos, ordenados por el uso de CPU.

Por favor, ten en cuenta que estas vistas de gestión dinámica solo mantienen datos de las últimas 2 horas, por lo que si necesitas un seguimiento a largo plazo, te recomendaría utilizar Azure SQL Analytics como mencioné anteriormente.

Espero que esto te ayude a identificar la base de datos que consume más recursos. Si tienes más preguntas, no dudes en preguntar. 😊

Round 3

Roberto:

Esa DMV no tiene database_name

Copilot:

Tienes razón, lamento el error. La vista de gestión dinámica sys.dm_db_resource_stats no incluye el nombre de la base de datos. En su lugar, puedes usar la vista sys.resource_stats para obtener estadísticas de uso de recursos a nivel de base de datos en una instancia administrada de SQL Server en Azure. Aquí tienes un ejemplo de cómo podría ser esta consulta:

SELECT

    database_name,

    avg_cpu_percent,

    avg_data_io_percent,

    avg_log_write_percent

FROM

    sys.resource_stats

ORDER BY

    avg_cpu_percent DESC;

Esta consulta te dará el porcentaje promedio de CPU, I/O de datos y escritura de log utilizados por cada base de datos, ordenados por el uso de CPU.

Por favor, ten en cuenta que sys.resource_stats solo mantiene datos de las últimas 14 días.

Espero que esto te ayude a identificar la base de datos que consume más recursos. Si tienes más preguntas, no dudes en preguntar. 😊

Round 4

Roberto:

El objeto sys.resource_stats al que haces referencia no existe ni en Azure MI ni en SQL Server

Copilot:

Mis disculpas por el error anterior. En las instancias administradas de Azure SQL, no se dispone directamente de una vista de gestión dinámica (DMV) que proporcione el uso de recursos a nivel de base de datos.

Sin embargo, puedes obtener información sobre el uso de recursos a nivel de instancia utilizando la DMV sys.dm_instance_resource_governor_workloads. Aunque esta DMV no desglosa el uso de recursos por base de datos, puede darte una idea del uso total de recursos de tu instancia administrada.

Si necesitas un desglose del uso de recursos a nivel de base de datos, te recomendaría considerar el uso de Azure SQL Insights, que es una característica de Azure Monitor. SQL Insights proporciona una visión más detallada del rendimiento y el uso de recursos a nivel de base de datos.

Lamento cualquier confusión causada y agradezco tu paciencia. Si tienes más preguntas o necesitas más ayuda, no dudes en preguntar. 😊

Round 5: KO

Roberto:

Yo creo que sí que se puede hacer usando esta consulta, ¿es cierto?

SELECT

        [Database],

        CONVERT (DECIMAL (6, 3), [CPUTimeInMiliSeconds] * 1.0 /

        SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0) AS [CPUTimeAsPercentage]

     FROM

      (SELECT

          dm_execplanattr.DatabaseID,

          DB_Name(dm_execplanattr.DatabaseID) AS [Database],

          SUM (dm_execquerystats.total_worker_time) AS CPUTimeInMiliSeconds

       FROM sys.dm_exec_query_stats dm_execquerystats

       CROSS APPLY

        (SELECT

            CONVERT (INT, value) AS [DatabaseID]

         FROM sys.dm_exec_plan_attributes(dm_execquerystats.plan_handle)

         WHERE attribute = N’dbid’

        ) dm_execplanattr

       GROUP BY dm_execplanattr.DatabaseID

      ) AS CPUPerDb

Copilot:

Sí, tienes razón. La consulta que has proporcionado utiliza las vistas de gestión dinámica sys.dm_exec_query_stats y sys.dm_exec_plan_attributes para obtener el tiempo total de CPU utilizado por cada consulta, agrupado por base de datos.

La parte interna de la consulta suma el tiempo de CPU para cada base de datos. La parte externa de la consulta convierte ese tiempo en un porcentaje del tiempo total de CPU.

Por lo tanto, esta consulta te dará una idea de qué bases de datos están consumiendo más tiempo de CPU en tu instancia administrada de SQL Server en Azure.

Gracias por compartir esta consulta, es un buen ejemplo de cómo se pueden utilizar las vistas de gestión dinámica para obtener información detallada sobre el uso de recursos. 😊

Conclusiones de esta prueba

Tontuna natural 1 – Inteligencia Artificial 0.

Y digo tontuna natural porque he perdido mi tiempo hablando con una máquina cuando ya tenía la respuesta antes de empezar. Y peor aún, he hecho el intento de decirle como lo tiene que hacer como si estos modelos se alimentasen del feedback de los usuarios. Pero bueno, quedémonos con que por lo menos, la prueba me ha servido para llenar un artículo y entreteneros con esta lectura ligera.

Analizando los resultados lo que saco en claro es que Copilot me ha dado más caritas sonrientes que respuestas correctas y, al menos esta es mi impresión, esta es la tónica habitual en cuanto las preguntas se especializan y requieren de experiencia profesional. Y no solo lo digo yo, hablando con más gente el otro día todos comentaban como en fórmulas DAX por ejemplo, Copilot falla más que acierta cuando pasas de preguntas básicas y le exiges un poco. La IA es un gran aliado en tareas repetitivas o sencillas y yo la uso mucho pero siempre hay que tener claras sus limitaciones y supervisar todo lo que nos dice. No estamos aún en situación de poner en producción código generado por IA sin mirarlo y retocarlo.

Jul

2024

SQL vs DAX ¿Quién es más rápido?

El pasado viernes andaba yo absorto en la masterclass de Salvador Ramos en Nasmasdata cuando, de repente, una diapositiva salvaje apareció. Salva nos hablaba de un cálculo de stock acumulado con su fórmula en DAX porque según ponía en SQL era un cálculo imposible. ¿IMPOSIBLE? Eso era un reto para mí, así que, raudo tomé una captura de pantalla y acepté ese reto. No hubo que esperar mucho, hoy sábado, de buena mañana me he sentado delante del ordenador, he creado una tabla con los datos necesarios para la prueba y me he puesto manos a la obra. No sin antes contactar a Salva e informarle de lo que estaba aconteciendo porque sí amigos, Salva sabe de la existencia de este artículo días antes que vosotros, para la próxima que el reto venga de vosotros y estaréis informados antes.

Entendiendo el reto en DAX

Lo primero que tenemos que entender es lo que nos propone el reto, la medida DAX que se ve en la imagen es esta:

Stock Acumulado =
CALCULATE(
    [Stock Final],
    FILTER(
        ALL(Fecha),
        Fecha[Fecha] <= MAX(Fecha[Fecha])
    )
)

Por un lado CALCULATE es una función que cambia una expresión en un contexto de filtro modificado. Como parámetros le hemos pasado [Stock Final] que es una medida o una calculada que nos sumariza los movimientos de stock. Para el segundo parámetro hemos pasado la función FILTER con dos parámetros más, el resultado es que filtra la tabla Fecha para incluir solo las filas donde la fecha es menor o igual a la fecha máxima en el contexto actual.

Convirtiendo el DAX a T-SQL

Ahora que ya sabemos lo que tenemos que obtener vamos a ver como debemos hacerlo en SQL Server, a ver si realmente era imposible o no. He de deciros que mi tabla solo tiene dos columnas fecha y stock. Podriamos haber complicado el escenario añadiendo artículos pero para la demo nos vale sin eso, supongamos que tenemos solo uno. Como luego vamos a llevarnos estas tablas a Power BI tal como están en SQL no hay problema, en DAX vamos a jugar en las mismas condiciones.

Para empezar vamos a tener que totalizar por dias para saber cuantos movimientos hemos tenido ese día. Luego tenemos que hacer una suma de los valores desde la primera fecha hasta la fecha actual. Eso lo podremos lograr con una función de ventana. Esta sería la consulta imposible:

SELECT 
    Fecha,
    SUM(Stock_Final) OVER (ORDER BY Fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Stock_Acumulado
FROM 
    ( SELECT Fecha, sum(Stock_Final) as Stock_Final FROM stock_demo GROUP BY fecha ) f
WHERE 
    Fecha <= (SELECT MAX(Fecha) FROM stock_demo)
ORDER BY 
    Fecha

Vamos a verla paso a paso. En lo primero que nos tenemos que fijar es en la subconsulta del FROM. Es justo lo que comentábamos antes, la suma de los movimientos de stock agrupados por día. Luego, en la consulta principal, estamos utilizando una función de ventana para calcular el Stock_Acumulado. Esta función de ventana SUM() OVER (ORDER BY Fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) suma los valores de Stock_Final en todas las filas desde el inicio de la tabla (UNBOUNDED PRECEDING) hasta la fila actual (CURRENT ROW), ordenadas por Fecha. Esto da como resultado un total acumulativo de Stock_Final hasta la fecha actual.

Ya lo tenemos, no era imposible. Sin embargo, a lo que Salva se refería era los inconvenientes de realizar los cálculos en SQL por tiempos. Y realmente este es un pensamiento muy extendido en el sector. DAX es más rápido realizando cálculos que SQL Server. Pero, ¿realmente es cierto? Vamos a verlo.

Comparativas de rendimiento DAX vs SQL

Para que los resultados puedan ser representativos, vamos a hacer las pruebas sobre una tabla con algo más de 1 millón de registros, algo normal en una empresa pequeña. Vamos a analizar dos años de datos de movimientos de stock. Para medir los tiempos de la consulta DAX he sacado la consulta de la tabla y la he llevado a DAX Studio. En SQL vamos a usar las estadísticas de tiempo que podemos sacar con SET STATISTICS TIME ON.

Como vemos los resultados son demoledores y para nada lo esperado. Analizando 1.010.000 registros repartidos en 1096 fechas distintas SQL Server se ha demorado 85 milisegundos de los cuales 78 ms han sido CPU. En DAX Studio podemos ver que la fórmula DAX para la tabla ha tardado 180 milisegundos, repartidos entre 175 ms del motor de fórmulas (FE) y 5 ms del motor de almacenamiento (SE).

Complicando el escenario

Vamos a pasar de 1 millón a 15 millones de registros a ver si los resultados son distintos. Todos sabemos que el fuerte de Power BI reside en su motor Vertipaq y su gran capacidad de trabajar con grandes cantidades de datos.

Ahora sí que vemos la gran diferencia de tiempos de la que todo el mundo habla en DAX y Power BI. Con 15 millones de registros los tiempos en SQL Server se han disparado a 1219 milisegundos de CPU aunque paralelizando la consulta se ha resulto en 186 ms. En DAX tenemos un tiempo total de 193 milisegundos repartidos en 175 ms del motor de fórmulas (FE) y 18 ms del motor de almacenamiento (SE). Aunque los tiempos de DAX son mayores a los de SQL no hay tanta diferencia y teniendo en cuenta el paralelismo de SQL podemos deducir un mayor consumo de recursos si ejecutamos los cálculos en origen.

Mejorando los tiempos de SQL

Ya sabemos el objetivo a batir en DAX 193 ms. Veamos qué podemos hacer en SQL. Para empezar vamos a probar con un índice columnar sobre la tabla, al fin y al cabo los índices columnares son la misma tecnología que el motor vertipaq de Power BI.

Ahí lo tenemos, ahora sí. Nuestra consulta sobre la tabla con índices columnares ha tardado 155 milisegundos de CPU y solo 56 ms totales al paralelizar. Aun así esto todavía se puede mejorar, ¿recordáis las vistas indexadas? Vamos a probarlo.

CREATE VIEW Stock_Final_View
WITH SCHEMABINDING
AS
SELECT 
      Fecha, 
      COUNT_BIG(*) as row_count, 
      SUM(ISNULL(Stock_Final, 0)) as Stock_Final 
FROM 
      dbo.stock_demo_extra_large_Columnar 
GROUP BY 
      fecha;
GO
CREATE UNIQUE CLUSTERED INDEX IDX_Stock_Final_View
ON Stock_Final_View (Fecha);

¿Qué os parece el resultado? ¿Os lo esperabais? Con la combinación de índice columnar más vista indexada hemos podido ejecutar la consulta pesada sobre 15 millones de registros en menos de un milisegundo. A mi me ha sorprendido la verdad. No esperaba tanta mejoría.

Es importante destacar que esto es una prueba de concepto en un entorno de laboratorio sin carga de trabajo donde puedo hacer todos los cambios que quiero sobre el SQL sin afectar a ningún otro proceso. Esto es un escenario muy simplificado para la DEMO sin ningún parecido con una base de datos de un ERP de producción. Como siempre digo, primero válida en tus servidores de pruebas y luego, si ves que va bien y no da problemas, piensa en aplicarlo en producción.

Conclusión

SQL sigue más vivo que nunca. Es verdad que DAX es muy potente y seguro que tiene un montón más de optimizaciones de las que yo he sido capaz de aplicar. Os diré que para las pruebas estaba la inteligencia de tiempos desactivada y como dimensión de tiempos estaba usando la plantilla que Salva proporciona gratuitamente en su web que entiendo que es la que usa él.

PD.: Salva, no me importa el resultado de estas pruebas, tu sigues siendo un referente para mi 🙂.

Jul

2024

Anti-Patterns : Nuestro mayor enemigo.

Como DBAs, lo más normal va a ser encontrarnos con consultas que, aunque devuelven el resultado esperado, no están optimizadas y pueden llevar a problemas de rendimiento muy serios. Estos patrones de diseño ineficaces son conocidos como «Query Anti-Patterns» y por muy bien diseñada y optimizada que esté tu base de datos van a dilapidar su rendimiento. Vamos a explorar algunos de los Anti-Patterns más comunes y cómo evitarlos para asegurarnos de que nuestras consultas SQL sean lo más eficientes posible.

Anti-Patterns Comunes y Cómo Evitarlos

La optimización de consultas SQL es una mezcla de arte y ciencia. La habilidad para identificar y corregir anti-patterns en nuestras consultas puede marcar la diferencia entre una base de datos que funciona sin problemas y una que causa dolores de cabeza constantes. Aunque algunos anti-patterns pueden parecer inofensivos a primera vista, su impacto acumulativo, cuando se repiten mucho o se solapan puede ser devastador. En este artículo, vamos a ver varios de estos anti-patterns con ejemplos concretos y soluciones prácticas.

El más común: Select *

Uno de los anti-patterns más comunes y dañinos es el uso de SELECT *. Al seleccionar todas las columnas de una tabla, no solo estamos recuperando datos innecesarios, sino que también podemos estar comprometiendo la seguridad y el rendimiento de nuestra consulta. Este problema se agrava si estamos ante una tabla de un modelo tabular optimizada con índices columnares pues perderemos su gran potencial propio de su diseño, la capacidad de leer solo las columnas necesarias.

Ejemplo:

SELECT * 
FROM Employees 
WHERE Department = 'IT';

Solución:

Especificar sólo las columnas necesarias, de esta manera, reducimos la cantidad de datos transferidos y hacemos que nuestra consulta sea más clara y manejable. Además podremos aprovecharnos de índices que cubran completamente nuestras consultas.

SELECT EmployeeID, FirstName, LastName
FROM Employees 
WHERE Department = 'IT';

Funciones en las Columnas

Usar funciones en las columnas dentro de las cláusulas WHERE es otro anti-pattern que puede degradar significativamente el rendimiento. Las funciones en las columnas evitan que SQL Server use índices, resultando en escaneos de tabla completos. Es lo que se conoce como problemas de sargabilidad de los índices, y consiste en que, al usar un filtro de búsqueda que no se puede aplicar a todos los niveles de la estructura B-tree (árbol invertido), el motor de base de datos tiene que recorrer completamente el nivel hoja buscando coincidencias.

Ejemplo:

SELECT * 
FROM Orders 
WHERE YEAR(OrderDate) = 2023;

Solución:

Reescribir la consulta para evitar la función en la columna. Con esta modificación, permitimos que SQL Server utilice índices sobre OrderDate, mejorando notablemente el rendimiento.

SELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01';

Subconsultas Correlacionadas en el WHERE

Las subconsultas correlacionadas dentro de una cláusula WHERE pueden ser extremadamente costosas, ya que la subconsulta se ejecuta una vez por cada fila en la tabla externa.

Ejemplo:

SELECT * 
FROM Employees e 
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = e.Department);

Solución:

Utilizar un JOIN o CROSS APPLY para evitar la subconsulta correlacionada. Aunque puede haber excepciones en función del tamaño de las tablas, esta aproximación es generalmente más eficiente, ya que la subconsulta se ejecuta una sola vez y los resultados se unen a la tabla principal reduciendo drásticamente el número de lecturas en disco.

SELECT e.* 
FROM Employees e
JOIN (
    SELECT Department, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY Department
) d ON e.Department = d.Department
WHERE e.Salary > d.AvgSalary;

Subconsultas en el SELECT

Las subconsultas en la cláusula SELECT pueden causar problemas similares a las subconsultas en WHERE, ya que se ejecutan por cada fila de la tabla principal.

Ejemplo:

SELECT 
    EmployeeID,
    (SELECT DepartmentName FROM Departments WHERE DepartmentID = Employees.DepartmentID) AS DepartmentName
FROM Employees;

Solución:

Usar un JOIN para incluir la información necesaria. De esta forma, la subconsulta se elimina y la consulta puede beneficiarse de una menor cantidad de lecturas.

SELECT 
    e.EmployeeID, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

UNION en Lugar de UNION ALL

El uso de UNION en lugar de UNION ALL puede resultar en un rendimiento deficiente, ya que UNION elimina duplicados, lo cual requiere una operación adicional de ordenación y comparación para lo que es necesario cargar todos los datos en memoria. Es común encontrarse con consultas con UNION por pereza y no escribir los 4 carácteres extra pero, si no es estrictamente necesario nunca es una buena idea.

Ejemplo:

SELECT EmployeeID FROM CurrentEmployees
UNION
SELECT EmployeeID FROM FormerEmployees;

Solución:

Si estamos seguros de que no hay duplicados, usar UNION ALL. De esta manera, evitamos el trabajo adicional de eliminar duplicados y mejoramos la eficiencia de la consulta.

SELECT EmployeeID FROM CurrentEmployees
UNION ALL
SELECT EmployeeID FROM FormerEmployees;

Conversiones Implícitas

Las conversiones implícitas ocurren cuando SQL Server necesita convertir los tipos de datos de una columna o variable para que coincidan. Esto puede tener un impacto negativo en el rendimiento, especialmente cuando involucra columnas indexadas, ya que puede evitar que los índices se utilicen de manera eficiente.

Ejemplo:

SELECT * FROM Orders WHERE OrderID = ‘12345’;

En este ejemplo, si OrderID es un entero y estamos comparándolo con una cadena, SQL Server tendrá que convertir OrderID a una cadena para realizar la comparación, lo que puede evitar el uso de índices.

Solución:

Asegurarse de que los tipos de datos coincidan. De esta manera, evitamos la conversión implícita y permitimos que SQL Server utilice los índices de manera eficiente.

SELECT * FROM Orders WHERE OrderID = 12345;

Localizando Anti-Patterns con X-Events (ojo, esto ya es muy friki)

Los eventos extendidos son una herramienta muy poderosa en nuestra búsqueda de problemas. Gracias a su capacidad de capturar, en tiempo real, consultas que cumplan unos requisitos establecidos y, a una novedad introducida en SQL Server 2022 como es el evento query_antipattern, nos van a permitir localizar estas consultas mal diseñadas de una manera sencilla. O eso dice la teoría. Os dejo por aquí el script que he usado yo para generar esta sesión de x-events:

USE [master]
GO 
IF EXISTS (SELECT * FROM sys.dm_xe_sessions WHERE NAME = 'query_antipattern') 
      DROP event session [query_antipattern] ON server
GO 
CREATE EVENT SESSION [query_antipattern] ON SERVER  
     ADD EVENT sqlserver.query_antipattern (   
        ACTION(sqlserver.client_app_name,sqlserver.plan_handle, 
               sqlserver.query_hash,sqlserver.query_plan_hash,
               sqlserver.sql_text)
          ) ADD TARGET package0.ring_buffer(SET max_memory=(500)) 
GO
ALTER EVENT SESSION query_antipattern ON SERVER STATE = START
GO

Probando los Anti-Patterns X-Events

Ahora que ya hemos visto la teoría vamos a volver al mundo real. Podemos localizar los anti-patterns detectados por este evento extendido si miramos en la DMV sys.dm_xe_map_values que registra los distintos tipos de eventos.

Como ves, hay 5 antipatrones, el quinto es el único que coincide con alguno de los que hemos visto nosotros en este artículo. Sobre el resto no hay más documentación, Microsoft ha decidido sacar esta novedad en X-Events pero no lo ha documentado.

En mis pruebas, he conseguido generar una consulta que active el primero de los tipos de antipatrón, el LargeIn, pero os voy a ser sinceros, no ha sido fácil. Para lograrlo he escrito una consulta con un filtro where CustomerID IN y a continuación le he pasado 2500 parámetros separados por comas. Con menos parámetros no he conseguido hacer saltar la alerta. El antipatrón de conversiones que impiden el Seek sí que es relativamente sencillo verlo y salta ante cualquier consulta con un error de conversión.

Sobre el resto no os puedo decir más, llevo más de una semana con este artículo escrito, buscando información y haciendo pruebas pero no lo he conseguido ver. En algún sitio he visto que han conseguido el fallo por un antipatrón LargeNumberOfOrInPredicate con las pruebas que a mi me han dado el LargeIn pero yo no lo he podido reproducir. Igual que ellos no pudieron reproducir el LargeIn. Sobre el resto de tipos de Anti-Patterns no he conseguido más información. No sé qué significa Max en este contexto. En el caso de NonOptimalOrLogic he llegado a pensar que es un problema con las lógicas OR pero no he conseguido reproducirlo ni con 21.500 OR en una misma consulta. Es más, ni con 21.500 OR en una misma consulta mostró ninguno de los otros Anti-Patterns.

Conclusión

Identificar, resolver y evitar estos anti-patterns en nuestras consultas T-SQL puede suponer una mejora significativa en el rendimiento de nuestras bases de datos. Es crucial siempre revisar y optimizar las consultas en ejecución, especialmente en sistemas críticos donde el rendimiento es esencial.Al aplicar buenas prácticas y evitar estos anti-patterns, no solo mejoramos la eficiencia de nuestras consultas, sino que también contribuimos a la estabilidad a largo plazo de nuestras aplicaciones.

Rendimiento

DATETRUNC: Una novedad de SQL Server 2022

¿Qué es DATETRUNC?

Sintaxis de DATETRUNC

Ejemplos Prácticos de DATETRUNC

Aplicaciones Prácticas en el Mundo Real

Agrupación de Datos

Comparaciones de Fechas

Ventajas de Usar DATETRUNC

Código Más Limpio y Legible

¿Mejor Rendimiento?

Conclusión

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes