Teoría BBDD

22
May
2024

Estimación de Cardinalidad en SQL Server

Como DBAs siempre nos encontramos en una constante búsqueda de optimización y mejora del rendimiento de nuestras bases de datos. Esto, antes o después nos lleva a encontrarnos con  un concepto fundamental pero que puede resultar complicado al principio: la estimación de cardinalidad. Este concepto, aunque pueda parecer magia interna de SQL Server, es esencial para entender cómo el motor de base de datos de SQL Server decide el mejor plan de ejecución para ejecutar nuestras consultas. Es decir, es la clave para elegir el camino más óptimo para resolver lo que le hemos pedido.

Estimación de Cardinalidad

Como hemos adelantado ya en la introducción ,la estimación de cardinalidad es el proceso mediante el cual SQL Server trata de predecir el número de filas a las que va a afectar una consulta. No sólo calcula el número total de filas afectadas sino que lo hace con una granularidad total, calculando cuántas filas pasarán por todos y cada uno de los distintos componentes de los planes de ejecución posibles para resolver la consulta. Este número, también conocido como cardinalidad, es crucial para el optimizador de consultas, ya que sin esta información el motor de base de datos no podría elegir el plan de ejecución más eficiente. SQL Server utiliza estadísticas para realizar estas estimaciones de cardinalidad. 

Estadísticas en SQL Server: La base de la cardinalidad

Como acabamos de ver, el optimizador de consultas utiliza las estadísticas para estimar la cardinalidad. Por ejemplo, si estamos buscando registros de en una tabla donde la columna ‘edad’ es mayor que 30, SQL Server utilizará las estadísticas para estimar cuántos registros cumplen con este criterio. Pero, ¿cómo funciona exactamente?

Las estadísticas en SQL Server son objetos que almacenan información sobre la distribución de los valores en una o más columnas de una tabla o vista indexada. Cada objeto de estadísticas está compuesto por un histograma que describe la distribución de los valores, y un vector de densidad que contiene información sobre la correlación de los valores en las columnas.

SQL Server crea y actualiza automáticamente las estadísticas para las columnas indexadas en nuestras tablas y vistas. También podemos crear estadísticas para columnas no indexadas utilizando el comando CREATE STATISTICS, o podemos actualizar las estadísticas existentes utilizando el comando UPDATE STATISTICS.

Es importante tener en cuenta que las estadísticas pueden volverse obsoletas a medida que los datos en nuestras tablas cambian. Cuando esto sucede, las estimaciones de cardinalidad basadas en estas estadísticas pueden ser inexactas.  Esto puede llevar a SQL Server a elegir un plan de ejecución subóptimo, lo que puede resultar en un rendimiento deficiente de la consulta. Otro de los problemas comunes, aunque el plan de ejecución sea el correcto es una asignación de recursos no óptima para la resolución de las consultas lo que puede llevarnos a una profunda degradación de rendimiento.

Planes de Ejecución: El resultado de la estimación de cardinalidad

Un plan de ejecución es, en resumen, una serie de pasos que SQL Server sigue para ejecutar una consulta. Cada paso en el plan de ejecución tiene su propio componente que representa una operación atómica, como un escaneo de tabla, un join, o una operación de ordenación.

El optimizador de consultas de SQL Server utiliza las estadísticas para estimar la cardinalidad y elige el plan de ejecución que tiene el menor costo estimado. El costo de un plan de ejecución se mide en términos de la cantidad de recursos que se espera que consuma, como la CPU, la E/S de disco, y la memoria RAM.

Podemos ver el plan de ejecución de una consulta utilizando la opción SET SHOWPLAN_ALL ON. Esto nos proporcionará una representación gráfica del plan de ejecución, junto con información detallada sobre cada operación en el plan.

No vamos a profundizar mucho más en este sentido pues ya le hemos dedicado a este tema este artículo completo en este blog.

Conclusión

La estimación de cardinalidad es un aspecto esencial en la optimización de consultas en SQL Server. Aunque puede parecer un concepto complejo, entender cómo funciona puede ayudarnos a mejorar significativamente el rendimiento de nuestras bases de datos.

Es importante recordar que las estadísticas, que son la base de la estimación de cardinalidad, deben mantenerse actualizadas para garantizar estimaciones precisas. Como siempre, la clave está en conocer nuestras bases de datos, entender cómo se utilizan y aplicar este conocimiento para optimizar su rendimiento.

En resumen, la estimación de cardinalidad es una herramienta poderosa en nuestras manos. Con un buen entendimiento de cómo funciona, podemos hacer que nuestras bases de datos trabajen de manera más eficiente y efectiva. ¡Sigamos aprendiendo y mejorando juntos!

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!

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 0 comentarios
20
May
2024

Agenda semanal SoyDBA

Iniciamos una nueva semana con una agenda apasionante, esta semana va a ser muy especial para mí y todo es gracias a vosotros que día a día me apoyáis leyendo y compartiendo mi contenido. Creo que, gracias a vuestro constante apoyo y entusiasmo por aprender cosas nuevas, se está formando una gran comunidad de profesionales alrededor de este blog. Personalmente os estoy profundamente agradecido ya que este apoyo repercute directamente en mi crecimiento personal y desarrollo profesional. ¡GRACIAS POR ACOMPAÑARME EN ESTE MARAVILLOSO PROYECTO!

Ahora, sin más palabrería cursi, me gustaría compartir con vosotros la emocionante agenda de actividades que se nos presenta esta semana esperando que podáis vosotros también participar de ella

Noches de DBAs: primer hito en mi agenda semanal

Como primera actividad de esta semana estaré participando como ponente en el evento Noches de DBAs. Este evento es organizado por Alberto de Rossi para la comunidad de Power BI User Group de Lima en Perú. Es una gran oportunidad para aprender, compartir conocimientos y experiencias con otros profesionales del campo. Nos vamos a enfocar en el lado de la fuente de datos cuando usamos la plataforma de datos Microsoft. Conocer sobre la operación de las fuentes y cómo optimizarlas también es importante para mantener adecuadamente una solución de inteligencia de negocios con Power BI. En esa noche podréis asistir a dos presentaciones a cargo de experimentados DBA. Trataremos los siguientes temas:

  • Niveles de aislamiento en SQL Server y gestión de la concurrencia de los procesos, a cargo de Roberto Carrancio. 
  • Cómo capturar y optimizar los querys ejecutados desde Power BI, a cargo de Alberto De Rossi

Ponentes:

Alberto De Rossi

Alberto es un profesional con más de 20 años de experiencia en tecnologías de la información, dedicado a la consultoría de proyectos relacionados con el diseño, implementación y administración de soluciones de datos e inteligencia de negocios, así como a la capacitación en Azure, Power BI y SQL Server. Cuenta en su haber con el reconocimiento MVP de Microsoft desde hace ya 6 años. Os dejo por aquí su perfil de MVP.

Roberto Carrancio

Roberto, el mismo que escribe estas líneas (y el resto del blog). Como ya sabéis soy DBA de SQL server con más de 10 años de experiencia en el sector. Durante este tiempo he tenido oportunidad de lidiar con proyectos en compañías de todos los tamaños y sectores, desde pymes hasta grandes multinacionales. 

Agenda

Este evento tendrá lugar el Miércoles 22 de Mayo a las 18:30 hora de Perú (GMT-5), lo que en España es el Jueves 23 de Mayo a las 01:30. El evento será online, retransmitido en directo y la asistencia es gratuita, simplemente tenéis que apuntaros aquí para recibir el enlace con la invitación. Una vez concluidas las sesiones, quedarán disponibles abiertamente para su consulta en el canal de Youtube de Power BI User Group Lima. Os dejaré los enlaces en mis redes y posiblemente también en el blog.

Power Platform Madrid 2024 para cerrar la agenda semanal

Después de mi participación en el evento de Lima, asistiré presencialmente al evento Power Platform Madrid 2024 el sábado 25 por la mañana. En esta ocasión, estaré asistiendo como oyente, buscando aprender de otros expertos de la comunidad y mantenerme al día con las últimas tendencias y desarrollos en el sector. Os dejo la descripción del evento en el que podréis encontrar talleres prácticos el viernes 24 y más de 40 ponencias el sábado 25:

Bienvenido a la sesión presencial de Power Platform de Madrid 2024, el evento para profesionales y entusiastas de la tecnología, centrado en la potente herramienta que es Microsoft Power Platform.

Este evento representa una oportunidad única para aquellos que buscan conectar con otros miembros de la comunidad, compartir desafíos y soluciones, y expandir su red de contactos profesionales en un ambiente de colaboración y descubrimiento.

Ya sea que te estés iniciando en estas tecnologías o busques afianzar y expandir tu maestría, este evento está diseñado para inspirar y elevar tus capacidades.

El viernes 24 se celebrarán talleres prácticos dirigidos por grandes profesionales, y el sábado 25 sesiones divulgativas con todo un elenco de ponentes. Puedes consultar todos los detalles en la agenda del evento ¡No te lo pierdas!

Consulta aquí la agenda de talleres y sesiones

Compra aquí tu entrada.

Espero que esta semana llena de nuevas experiencias y aprendizajes para mi os resulte interesante también a vosotros. Me encantaría veros por ahí. Y, por supuesto, estaré aquí para compartir con vosotros todas las novedades y conocimientos adquiridos durante estos eventos. Para terminar, no os preocupéis, el blog va a seguir su programación habitual con artículos y video blogs. 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!

Publicado por Roberto Carrancio en Otros, 0 comentarios
15
May
2024

¿Cómo no hacer un DWH? (Parte 1 de 2)

Hoy vamos a viajar al pasado para recuperar un artículo de Ralph Kimball escrito en 2001 pero que aún, 23 años después, sigue estando muy presente. El artículo recoge los 12 errores más comunes (y más perjudiciales) que se cometen a la hora de diseñar un DataWareHouse (DWH). Si ya tienes experiencia en este tipo de entornos vas a notar que son errores que a menudo podemos cometer pero que necesariamente tenemos que corregir al poco tiempo. En estos entornos no valen las cosas a medias, para un buen rendimiento es necesario hacer las cosas bien desde el principio o la bola crecerá tanto que será ingestionable.

¿Qué es un DWH?

Empecemos por el principio, un almacén de datos, datawarehouse o DWH es un sistema diseñado para almacenar y, posteriormente, analizar datos. No es un sistema cualquiera y tiene sus particularidades ya que en él, vamos a integrar todos los datos de los distintos orígenes de nuestra organización, incluso de orígenes externos. Por ejemplo, en nuestro DWH tendremos los datos de nuestra aplicación de ventas, contabilidad, tienda web y, en ocasiones, incluso la base de datos de artículos de nuestros proveedores.

Banner-Telegram

Los DWH se caracterizan por contener grandes cantidades de información histórica permitiendo a los analistas de datos realizar análisis temporales y crear predicciones de tendencias a futuro. Como podrás imaginar, para que todo esto funcione, tiene que estar todo muy organizado, estructurado y optimizado para el acceso y análisis rápido de la información. Gracias a procesos ETL (extracción, transformación y carga por sus siglas en inglés) vamos a cargar las tablas de dimensiones y de hechos del modelo de estrella de nuestro DWH,

Errores comunes cuando creamos un DWH

Ahora si, después de esta pequeña introducción vamos al lío, estos son los doce errores más comunes definidos por Kimball.

Error 12: Incluir campos de texto en tablas de hechos para filtrar u ordenar

En un modelo dimensional, las tablas de hecho y de dimensiones se diferencian por contener indicadores numéricos y atributos respectivamente. A veces, la clasificación de un dato puede ser ambigua, como la hora de una venta o el nombre del transportista que nos entrega un pedido. En caso de duda, os recomiendo evitar textos largos en las tablas de hecho para optimizar el espacio y el rendimiento, y considerar como dimensión cualquier dato compartido entre varias tablas de hecho. Al fin y al cabo el almacenamiento es barato en precio pero costoso a la hora de leerlo, las tablas de hechos pueden llegar a tener millones de registros y debemos evitar poner en ellas cualquier texto que pueda ser común a varias tablas o registros. Eso será siempre una dimensión.

Error 11: Escatimar en la información de nuestras dimensiones para ahorrar espacio.

La información es poder y aunque en este momento podamos pensar que no necesitamos ciertos datos y que podemos prescindir de ellos siempre es importante guardarlos por si en un futuro los necesitamos. Esa es la idea detrás de las primeras capas bronce de los data lakes pero, llevado al caso que nos ocupa, en nuestro DWH es una buena idea almacenar esa información si se refiere a dimensiones. El tamaño de las tablas de dimensiones con cientos o pocos miles de registros será despreciable al lado de las grandes tablas de hechos con millones de registros por lo que no debemos preocuparnos por ese aspecto. Otra cosa será que saquemos los datos en la capa de presentación si no se nos solicita pero tenerlo siempre será una buena idea.

Error 10: Dividir las jerarquías y en varias dimensiones

Las dimensiones se agrupan en jerarquías que tienen una relación de uno a muchos. En un país existen muchas provincias y en cada una de esas provincias muchas poblaciones. Cada población a su vez tiene muchos clientes. Podemos pensar en que es una buena idea tener las tablas normalizadas como nos enseñaron en la universidad y crear una tabla por cada una de esas dimensiones.

Eso es lo idea para un modelo transaccional pero cuando hablamos de un DWH donde las lecturas priman sobre las escrituras y el almacenamiento, en la última capa de nuestro DWH el modelo debe ser puramente de estrella y no de copo de nieve por lo que en una sola tabla para esa jerarquía. Es importante aquí destacar de esta última frase la referencia a esa última capa que consumen los reportes ya que nuestro DWH tendrá otras capas inferiores donde sí tendremos normalizados los datos.

Error 9: No enfrentar las dimensiones lentamente cambiantes

Ya explicamos en nuestro artículo sobre el modelo de estrella lo que es una dimensión de variación lenta o lentamente cambiante. Es importante que anticipemos estos cambios desde el inicio del diseño de nuestro modelo o en un futuro tendremos problemas. Por ejemplo, no os imagináis el caos que se me montó una vez a mi cuando un NIE de un cliente extranjero cambió a DNI al obtener la nacionalidad y yo no había pensado que ese dato era cambiante. 

Error 8: No crear foreign keys específicas en tu DWH

En ocasiones podemos pensar que un valor intrínseco de nuestras dimensiones puede actuar como clave y cometemos el error de no crear una clave dedicada para esos registros. Sin embargo, a la larga eso puede llegar a ser un problema y para evitarlo es aconsejable crear nuestros propios identificadores numéricos para los registros de nuestras dimensiones y enlazar con ellos las tablas de hechos. Pensad en el ejemplo del error anterior, el NIE o DNI puede parecer un identificador personal válido pero cuando no lo es puede ser un verdadero quebradero de cabeza. 

Error 7: Añadir dimensiones a la tabla de hechos del DWH

Esto puede parecer básico, pero si ya os habéis enfrentado a varios modelos de DWH sabréis que es más común de lo que parece. En un modelo de estrella todas las dimensiones deben estar en sus tablas de dimensiones y las tablas de hechos solo deben tener relación con esas dimensiones o jerarquía. Caer en este error nos llevará a un mayor tamaño de nuestra tabla de hechos lo que repercutirá directamente en el consumo de E/S de nuestras consultas y empeorará el rendimiento.  

Conclusión

Hemos visto la mitad de los errores más comunes descritos por Kimball, como el artículo si no iba a quedar muy grande vamos a dejar los 6 siguientes errores (y los más graves) para una segunda parte. Espero que os esté gustando, que os sea de utilidad y por supuesto que a partir de ahora no caigáis en estos errores. Si al leer esto has pensado en algo que tienes mal en tu DWH solucionalo antes de que sea tarde, vas a ver que, a la larga, la ganancia es sustancial. 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!

Publicado por Roberto Carrancio en Rendimiento, 1 comentario
10
May
2024

JOIN: La guía definitiva

¿Sabes qué son y cuándo usar cada tipo de JOIN? Esta es la base de cualquier consulta SQL que empieza a volverse compleja. Seguro que a ninguno os sorprende que os diga que, hoy en día, el conocimiento de SQL es una de las habilidades más demandadas en el mercado laboral. Cualquiera que trabaje con datos, a casi cualquier nivel, va a necesitar en algún momento consultar información y en este aspecto el lenguaje SQL es clave. No solo lo usan todas las bases de datos relacionales, sino que hasta las bases de datos NoSQL o descentralizadas han terminado admitiendo este lenguaje de consultas. O, al menos, un  lenguaje “SQL Like” para la consulta de sus datos no estructurados o semiestructurados.

Por todo esto, se hace muy importante para los profesionales de datos tener la capacidad de escribir consultas complejas con cláusulas JOIN que nos permitan unir la información de varias tablas. 

Joins en SQL

Los joins en SQL son la base de cualquier consulta compleja. Como su propio nombre indica, nos permiten combinar filas de dos o más tablas basándonos en una columna relacionada entre ellas. Suele ser una Foreing Key pero realmente no es necesario que exista esa restricción entre las tablas para hacer la consulta con un join. 

Banner-Telegram

Tipos de Join

Como hemos podido ver, los joins son una de las cláusulas más importantes que podemos usar en nuestra consulta de SQL. Además existen varios tipos de joins y cada uno de ellos tiene una función y un uso específico.

INNER JOIN

El INNER JOIN es la operación más básica. Algunos SGBD como SQL Server o Azure SQL admiten la sintaxis JOIN a secas sin especificar el INNER. Con este join seleccionaremos registros que tienen valores coincidentes en ambas tablas. Es el más común de los joins y se utiliza con frecuencia para combinar filas de dos o más tablas.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers 
 ON Orders.CustomerID = Customers.CustomerID;

LEFT (OUTER) JOIN

El LEFT JOIN nos devuelve todos los registros de la tabla izquierda y los registros coincidentes de la tabla derecha. Si no hay una coincidencia, el resultado es NULL en el lado derecho.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
LEFT JOIN Customers
 ON Orders.CustomerID = Customers.CustomerID;

RIGHT (OUTER) JOIN

El RIGHT JOIN nos va a devolver todos los registros de la tabla derecha y los registros coincidentes de la tabla izquierda. Si no hay una coincidencia, el resultado es NULL en el lado izquierdo.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers 
 ON Orders.CustomerID = Customers.CustomerID;

FULL (OUTER) JOIN

El FULL JOIN devuelve todos los registros cuando hay una coincidencia en cualquiera de las tablas izquierda o derecha. Si no hay una coincidencia, el resultado es NULL en ambos lados.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
FULL JOIN Customers 
 ON Orders.CustomerID = Customers.CustomerID;

Usos avanzados de JOINS

Además de los usos básicos de JOIN que ya hemos visto, podemos combinarlos con el uso del filtro IS NULL para obtener otro tipo de datos. Por ejemplo combinando LEFT JOIN 

con un filtro ISNULL cuando la clave de la tabla derecha es nulo podremos sacar solo los registros de una tabla que no tienen relaciones con otra. Igual pero al revés podemos hacerlo con RIGHT JOIN. Combinando un FULL JOIN con un filtro que nos garantice que las claves por las que enlazamos son nulas podremos sacar los registros de ambas tablas que no tienen relación con la otra.

Se que todo esto os puede parecer un poco lioso así contado pero seguro que con esta infografía lo vais a ver más claro. Ya sabéis, una imagen vale más que mil palabras (600 a estas alturas del artículo 🤣 ).

join

Escrituras con JOINS

Esto no sería la guía definitiva de los joins si no mencionamos también la sintaxis de las operaciones de escritura. Obviamente no tiene sentido unir varias tablas en una consulta de inserción ya que los insert son en una sola tabla pero sí que podremos querer actualizar o borrar datos de una tabla cruzando con otras para asignar un valor presente en otra tabla o para alguna validación extra. 

Update con JOIN

Es un escenario bastante común, en ocasiones queremos actualizar un valor de una tabla tal como lo tenemos en otra. Para eso podemos hacer un update con join tal como vemos aquí.

UPDATE O SET CustomerID = OB.CustomerID
FROM Orders O
INNER JOIN Orders_Backup OB
 ON O.OrderID = OB.OrderID

Como veis en este caso, tenemos una tabla de backup y queremos restaurar el valor de CustomerID desde el backup. Este es uno de los usos más comunes de este tipo de sintaxis.

Delete con JOIN

Existen varios escenarios en los que vamos a necesitar un join en una clausula delete. El más sencillo será borrar los registros de una tabla que tengan coincidencia en otra tabla. Sin embargo también podemos, por ejemplo, querer borrar solo los registros que no existen en la segunda tabla. Vamos a ver estos y otro ejemplo extra

Ejemplo sencillo de borrado de registros de una tabla origen que tengan coincidencia en una tabla filtro. En este caso vamos a borrar todos los pedidos de clientes con una nacionalidad en concreto:

DELETE origen
FROM Orders origen
INNER JOIN Customers  filtro
 ON origen.CustomerID = filtro.CustomerID
WHERE filtro.nacionalidad = ‘Wakanda’

Otro ejemplo muy común como ya hemos visto es el de borrar datos de una tabla que no tengan relaciones con otras. Por ejemplo, queremos borrar todas las líneas de una factura que no tengan cabecera.

DELETE lineas
FROM LineasFactura lineas
LEFT JOIN CabecerasFactura facturas
 ON lineas.factID = facturas.factID
WHERE  facturas.factID IS NULL

Por último, para cerrar ya esta guía vamos a ver un ejemplo especial que no todos los sistemas de bases de datos admiten que es el uso de join para borrados en cascada. Esta sintaxis, no la vais a poder usar en SQL Server o Azure SQL pero si, por ejemplo, en MySQL.

DELETE Orders, Customers
FROM Orders
INNER JOIN Customers
 ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.nacionalidad = ‘Wakanda’

Conclusión

Los joins son una herramienta esencial en SQL que nos permite combinar datos de diferentes tablas de manera eficiente. Aunque puede parecer complicado al principio, con práctica y experiencia, se convertirán en una parte integral de tus consultas SQL. No en vano son imprescindibles para todo DBA o trabajador de datos. Recuerda, la mejor manera de aprender es practicando, usa tus datos de demo para hacer pruebas e interiorizar los conceptos. 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!

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
09
Abr
2024

Claves Foráneas (FK): Trucos, Ventajas e Inconvenientes

En las bases de datos, las claves foráneas (FK) son una herramienta esencial para mantener la integridad referencial entre las tablas. Sin embargo, su uso puede tener tanto ventajas como desventajas, especialmente en términos de rendimiento y consumo de CPU. Tanto es así que por ejemplo MySQL por defecto renuncia a la integridad referencial en pro de la velocidad. Otros SGBD relacionales como SQL Server, PostgreSQL u Oracle sí que tratan las FK como una restricción. Comprender este comportamiento es clave para entender el comportamiento de nuestras bases de datos. Pero, veámoslo con detenimiento.

¿Qué son y cómo funcionan las FK?

Como hemos adelantado en la introducción, las claves foráneas son una forma de garantizar la integridad referencial en nuestras bases de datos. Cuando definimos una FK, estamos creando una relación entre dos tablas, donde una tabla tiene una columna o un conjunto de columnas que hacen referencia a una clave primaria en otra tabla. De esta manera no podremos crear en nuestra tabla destino registros con un valor que no exista previamente en la tabla de referencia.

Pensad en un modelo normalizado donde tenemos una tabla de cabeceras de facturas y en otra tabla las líneas de esas facturas. Una FK entre las tablas nos permitirá no poder tener en la tabla de líneas ningún registro que pertenezca a una factura inexistente. Esto tiene más implicaciones, como por ejemplo que en la tabla de facturas no podamos editar o borrar un dato si tiene líneas asociadas que, de otra manera, quedarían huérfanas. 

Actualización y Borrado en Cascada

Las claves foráneas en SQL Server y otros sistemas permiten configurar acciones de actualización y borrado en cascada. Esto significa que cuando se actualiza o se elimina un registro en la tabla principal, SQL Server automáticamente actualizará o eliminará los registros correspondientes en las tablas relacionadas.

La principal ventaja de la actualización y borrado en cascada es que simplifica la gestión de las bases de datos. No necesitamos escribir código adicional para manejar estas operaciones, ya que SQL Server se encarga de ello por nosotros. Esto puede ahorrar tiempo y reducir la posibilidad de errores.Sin embargo, la actualización y borrado en cascada también tienen sus inconvenientes. Si no se utilizan correctamente, pueden llevar a la eliminación inadvertida de datos. Por lo tanto, es importante utilizar estas características con cuidado y entender completamente sus implicaciones antes de implementarlas.

En resumen, la actualización y borrado en cascada son herramientas poderosas que pueden simplificar la gestión de nuestras bases de datos. Sin embargo, como con cualquier herramienta, deben utilizarse con cuidado y con un entendimiento completo de sus ventajas e inconvenientes.

Ventajas de las FK

Las FK proporcionan varias ventajas. La más importante, como ya hemos visto, es la garantía de la integridad referencial. Esto significa que no podemos tener datos huérfanos en nuestra base de datos. Además, las FK pueden ayudar a mejorar la legibilidad y la organización de nuestra base de datos. Además tienen otra gran ventaja que a menudo pasamos por alto y no es otra que facilitarnos la comprensión del modelo de datos.

Mantenimiento de la Integridad referencial

Las FK garantizan que los datos en las tablas relacionadas siempre sean consistentes. Esto es crucial para evitar anomalías de datos y garantizar la precisión de los resultados de las consultas.

Comprensión del modelo de datos

Las FK simplifican el proceso de entender el modelo de datos, sobre todo cuando nos enfrentamos a él por primera vez y no hay documentación. Al proporcionar conexiones claras entre las tablas es sencillo entender esas relaciones para cualquiera que lo sepa leer.

Inconvenientes de las FK

A pesar de sus ventajas, las FK, como todo, también tienen sus inconvenientes. Como hemos mencionado, pueden afectar al rendimiento y al consumo de CPU y disco. Además, pueden complicar las operaciones de inserción, actualización y eliminación, ya que requieren comprobaciones adicionales.

Rendimiento

El uso de FK puede tener un impacto significativo en el rendimiento y el consumo de CPU y el disco duro de nuestra base de datos. Cuando se inserta, actualiza o elimina un registro en una tabla que tiene una FK, SQL Server debe realizar comprobaciones adicionales en todas las tablas referenciadas para mantener la integridad referencial. Esto puede aumentar el tiempo de ejecución de estas operaciones y, por lo tanto, el consumo de CPU y disco. Personalmente he llegado a operaciones tan simples como un DELETE por la PK de una tabla que, debido a las FK, obliga a SQL Server a leer más de 50 tablas. Tenemos que tener cuidado con esto sobre todo a medida que el modelo de datos crece y las relaciones entre las tablas se complican.

Además, las verificaciones de integridad referencial requieren un procesamiento adicional, lo que puede aumentar el consumo de CPU además del disco duro. En sistemas con una gran cantidad de transacciones, esto puede ser un problema. Recuerda mantener siempre una buena monitorización sobre el rendimiento de tu modelo de datos.

Complejidad de las operaciones de escritura

No solo nos vamos a encontrar con degradaciones en el rendimiento de SQL Server sino que, para nosotros como DBAs y para los administradores de los datos también se va a añadir una capa de complejidad a la hora de trabajar con los datos. Son escenarios muy comunes para un DBA el de restaurar solo los datos de una tabla de la copia de seguridad o el de copiar datos entre distintos entornos. En estos casos, por ejemplo, tendremos que tener muy claro el orden de las inserciones y deberemos hacerlo de manera secuencial para evitar errores. O eso o deshabilitamos temporalmente las FK pero en este caso deberemos validarlas otra vez al activarlas y en tablas grandes eso es inviable. 

Indexar para evitar los inconvenientes de las FK

Como hemos podido ver, el hecho de tener FKs en nuestra base de datos va a añadir una lógica adicional a las transacciones de escritura lo que va a provocar un mayor consumo de recursos y por tanto un peor rendimiento de nuestro SQL Server. En este sentido, el indexado de los campos de las claves foráneas es una práctica esencial que puede mejorar significativamente el rendimiento de nuestras bases de datos. Crear índices para esos campos referenciados por una clave foránea permitirá, que a la hora de actualizar o borrar un registro en la tabla principal, SQL Server pueda usarlos para verificar rápidamente la integridad referencial sin tener que leer completamente la tabla.

Conclusión

Las claves foráneas son una herramienta poderosa en SQL Server pero también es un arma de doble filo. Aunque pueden tener un impacto en el rendimiento y el consumo de CPU, los beneficios que aportan en términos de integridad referencial suelen superar estos costes. Como siempre, la clave está en entender cómo funcionan y usarlas de manera inteligente. Recuerda, cada base de datos es un mundo y lo que funciona en uno puede no funcionar en otro. Por lo tanto, siempre es recomendable probar y monitorizar el rendimiento antes de implementar cualquier cambio a gran escala.

Como siempre, te animo a experimentar con estas técnicas y a explorar todas las posibilidades que ofrecen. 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 LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 1 comentario
26
Mar
2024

VLF: Entendiendo el Log de SQL Server nivel pro

En nuestra constante búsqueda de optimización y eficiencia en el manejo de bases de datos, nos encontramos con una pieza clave en el rompecabezas de SQL Server: los Virtual Log Files (VLF). Estos componentes, aunque pequeños y a menudo pasados por alto, son fundamentales para el rendimiento y la gestión de nuestras bases de datos. Hoy, nos sumergimos en el mundo de los VLF para entender su función y cómo una gestión adecuada puede marcar la diferencia en nuestro trabajo diario como DBAs. Referenciando nuestro artículo anterior ¿Cómo funcionan los ficheros de Log de SQL Server?, podemos recordar que el log de transacciones juega un papel vital en la recuperación de datos y, los VLFs, como parte fundamental de los logs, tienen la misma importancia.

¿Qué son los VLF y por qué son importantes?

Los VLF son segmentos virtuales en los que se divide el archivo de log de transacciones de una base de datos en SQL Server. Es decir, SQL Server no trabaja con el fichero de log completo sino con pequeñas particiones del mismo. Cada vez que se crea o se expande un log, SQL Server lo divide en varios VLFs. La cantidad y el tamaño de estos VLF pueden tener un impacto significativo en el rendimiento de la base de datos, especialmente durante operaciones de recuperación y respaldo. En casos extremos, si tenemos el log muy particionado puede afectarnos también al rendimiento de nuestras transacciones. 

Gestión Efectiva de VLF

Para mantener un sistema saludable, es crucial gestionar adecuadamente los VLF. Un número excesivo de VLF pequeños puede causar lentitud en el proceso de recuperación de la base de datos, mientras que muy pocos VLF grandes pueden dificultar el proceso de respaldo. Por ello, encontrar el equilibrio correcto es esencial. Los VLF son los bloques de construcción de este log, y su gestión eficiente permite una recuperación más rápida y una mejor disponibilidad de la base de datos.

Una estrategia efectiva para la gestión de VLF implica monitorear su cantidad y tamaño regularmente. SQL Server proporciona herramientas que nos permiten visualizar y ajustar estos parámetros, como el deprecado comando DBCC LOGINFO o su nueva versión, la función sys.dm_db_log_info. Además, prácticas como el truncamiento regular del log y la expansión controlada del archivo de log ayudan a mantener un número óptimo de VLF.

Creación de VLFs

Hemos visto que el motor de SQL Server crea automáticamente los VLFs durante el crecimiento de los ficheros de logs, sin embargo, esto no significa que nosotros no podamos influir sobre ello. Simplemente tendremos que conocer qué reglas sigue SQL para crear VLFs para adaptarnos a ello. Antes de nada tenemos que especificar que las reglas de creación de VLFs han cambiado con SQL 2022 por lo que tanto para esta versión como para Azure tendremos que tenerlo en cuenta. Veamos cómo se crean los VLFs:

  • Cuando el crecimiento del archivo de log es menor a 64 Mb SQL creará 4 VLFs en versiones antiguas y solo 1 VLF en SQL 2022 y en Azure.
  • Si el crecimiento del archivo de log es igual o mayor a 64 Mb pero menor a 1 Gb se crearán 8 VLFs.
  • Si el crecimiento del archivo de log es igual o mayor a 1Gb se crearán 16 VLFs.

Cuantos VLFs tienen mis logs

Ya sabemos cómo se crean los VLFs y por qué podemos llegar a tener muchos en nuestros mecheros de logs pero, no os he dicho aún cómo comprobar en vuestras bases de datos cuantos VLFs teneis. Para ello usaremos el siguiente script. Y ahora la pregunta clave, ¿Cuántos VLF debería tener? La respuesta es depende, no hay una respuesta universal a esta pregunta. Personalmente creo que sobre 50 es una buena cifra pero dependerá en gran medida del tamaño del fichero de log. 

Hora de hacer números y corregir los VLF

Supongamos que nuestra base de datos tiene un log de 20Gb y hemos visto que por alguna mala práctica del pasado tenemos muchos VLF. Lo primero que tendremos que hacer es un shrink para reducir el log lo máximo posible. Una vez con el log en su tamaño mínimo vamos a volver a dimensionarlo a sus 20Gb originales pero siguiendo las buenas prácticas de VLF. Vamos a crear un número de VLF cercano a 50. Para ello no podemos dimensionar directamente en 20Gb porque solo nos crearía 16 VLF y serían muy grandes para ser administrados por SQL de manera óptima. Qué pasa si aumentamos 8 Gb iniciales, luego otros 8 y terminamos con un crecimiento de 4 Gb. Esto serían 3 crecimientos del fichero superiores a 1 Gb por lo que se nos crearían 16 VLF en cada “salto”, 16*3=48. Parece bastante razonable. Ahora, si el fichero puede necesitar crecer en un futuro, podría ser interesante hacer 3 crecimientos de 8 Gb lo que nos daría 24 Gb de log repartidos igualmente en 48 VLF (unos ficheros de 512 Mb cada uno, algo asumible).

Conclusión

La gestión de VLF no es solo una tarea de mantenimiento; es una oportunidad para maximizar el rendimiento de nuestras bases de datos. Al comprender la importancia de estos elementos y aplicar prácticas de gestión efectivas, podemos asegurar un sistema robusto y ágil. Los VLF son, sin duda, pequeños gigantes en el mundo de SQL Server, y su correcta administración está reservada a unos pocos DBAs expertos con gran pericia técnica, como vosotros ahora.

Espero que este artículo te haya sido útil. 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 LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 0 comentarios
25
Mar
2024

¿Cómo funcionan los ficheros de Log de SQL Server?

En el artículo de hoy vamos a profundizar en los ficheros de log. Estos ficheros representan una de las herramientas más potentes para garantizar la integridad y la recuperación de datos. Estos archivos, a menudo subestimados, son fundamentales para el funcionamiento de cualquier sistema que dependa de la gestión de datos transaccionales como es SQL Server. 

¿Qué son los ficheros de Log?

Los ficheros de log, también conocidos como archivos de transacciones, registran todas las operaciones que modifican los datos o la estructura de la base de datos. Cada transacción en SQL Server comienza con una entrada en el log, asegurando que cualquier cambio pueda ser rastreado y, si es necesario, revertido o reproducido. Físicamente, los podemos encontrar como ficheros con extensión LDF. 

La importancia de los Logs en la recuperación de datos

Como ya vimos en el artículo sobre los CHECKPOINTS, una de las funciones más críticas de los ficheros de log es su papel en la recuperación de datos. En caso de un fallo del sistema, los logs son esenciales para restaurar la base de datos a un punto consistente en el tiempo, minimizando la pérdida de datos y manteniendo la integridad de los datos. Esto, en última instancia, nos garantiza la continuidad de nuestro negocio.

El impacto del nivel de recuperación en el comportamiento de la base de datos

El nivel de recuperación de una base de datos es un factor determinante en la forma en que SQL Server maneja los ficheros de log y, por ende, afecta directamente el comportamiento de la base de datos. Al seleccionar un modelo de recuperación, estamos definiendo cómo y cuándo se registran las transacciones en los ficheros de log, lo que tiene implicaciones significativas en la disponibilidad, la integridad y la recuperabilidad de los datos.

Modelo de recuperación simple

Con el modelo de recuperación simple, SQL Server minimiza el mantenimiento de los ficheros de log al truncar automáticamente los registros de transacciones que ya no son necesarios para la recuperación. Truncar el fichero significa vaciarlo, sin embargo, como ya hemos comentado en más ocasiones, el tamaño del fichero no se reduce, se queda pre dimensionado para cuando otras transacciones lo necesiten. Este modelo de recuperación de las bases de datos reduce el espacio de almacenamiento requerido y simplifica la gestión, pero limita la capacidad de recuperar la base de datos a puntos específicos en el tiempo, ofreciendo solo la posibilidad de restaurar hasta el último respaldo completo o diferencial.

Modelo de recuperación completa

A diferencia del modo simple, el modelo de recuperación completa mantiene un registro detallado de todas las transacciones, lo que permite una recuperación punto a punto. Esto significa que podemos restaurar la base de datos a cualquier momento específico, siempre y cuando dispongamos de los respaldos de log necesarios. Para entendernos, las transacciones no se borran del fichero de log nada más concluyen (commit o rollback) sino que se mantienen en el fichero hasta que se hace una copia de seguridad. Este nivel de detalle, por tanto, requiere una gestión más activa de los ficheros de log para evitar un crecimiento excesivo y problemas de rendimiento.

Modelo de recuperación bulk-logged

El modelo de recuperación bulk-logged es una variante del modelo completo que ofrece una solución intermedia. Permite operaciones masivas de datos, como importaciones o indexaciones, sin registrar cada detalle en el log mientras mantiene todo el detalle de las transacciones normales. Esto nos permite reducir el tamaño del fichero durante estas operaciones. Aunque ofrece ciertas ventajas en términos de rendimiento, también conlleva un riesgo mayor en la recuperación de datos, ya que no se pueden recuperar transacciones individuales realizadas durante las operaciones masivas.

Gestión y mantenimiento de los ficheros de Log

Una gestión efectiva de los ficheros de log implica monitorear su tamaño y realizar un mantenimiento regular, como el truncamiento de log, que libera espacio para nuevas transacciones. Ignorar estas prácticas puede llevar a un crecimiento descontrolado del log y afectar negativamente el rendimiento del sistema. 

Como hemos visto, el log de transacciones almacena las transacciones en curso en el modelo de recuperación simple o, en el modo completo las en curso y las finalizadas de las que no dispongamos backup. Podemos entender entonces que, puede llenarse completamente por varias razones, que van desde un gran número de transacciones hasta consultas mal optimizadas o configuraciones inadecuadas. Uno de los factores más comunes es la retención de registros de transacciones debido a transacciones largas o no completadas. Además, si el modelo de recuperación de la base de datos está configurado como completo o bulk-logged y no se realizan copias de seguridad del log de forma regular, el log seguirá creciendo, ya que SQL Server espera que estos registros estén disponibles para una posible recuperación punto a punto.

¿Por qué no se vacía mi log de transacciones?

SQL Server proporciona una columna muy útil en la vista de catálogo sys.databases llamada log_reuse_wait_desc, que indica la razón por la cual el espacio del log de transacciones no se puede reutilizar. Algunos de los tipos de log_reuse_wait más comunes son:

  1. ACTIVE_TRANSACTION: Indica que hay una transacción activa que impide el truncamiento del log.
  2. LOG_BACKUP: Señala que se necesita una copia de seguridad del log para permitir la reutilización del espacio.
  3. CHECKPOINT: Sugiere que no se ha alcanzado un punto de control que permita el truncamiento del log.
  4. ACTIVE_BACKUP_OR_RESTORE: Implica que una operación de copia de seguridad o restauración está en curso, lo que impide el truncamiento.
  5. REPLICATION: Indica que la replicación no ha terminado de procesar los registros que necesitan ser distribuidos. Este tipo de espera se usa también para Log Shipping.
  6. DATABASE_MIRRORING: Señala que el espejo de base de datos está activo y que los registros aún no se han enviado a la réplica secundaria.
  7. AVAILABILITY_REPLICA: Indica que una réplica de disponibilidad de AlwaysOn está aplicando registros del log a una base de datos secundaria.

Cada uno de estos estados requiere una acción específica para liberar espacio en el log de transacciones. Por ejemplo, si el log_reuse_wait_desc muestra LOG_BACKUP, se debe realizar una copia de seguridad del log para permitir que el espacio sea reutilizado. Si muestra ACTIVE_TRANSACTION, se debe investigar y resolver la transacción larga o bloqueada.

Conclusión

Los ficheros de log son el core de la seguridad y la integridad de las bases de datos en SQL Server. Su correcta gestión y comprensión son esenciales para cualquier DBA que busque asegurar la máxima eficiencia y confiabilidad en sus sistemas. Al dominar los ficheros de log, nos equipamos con una poderosa herramienta para enfrentar casi cualquier desafío en el mundo de las bases de datos.

Entender por qué se llena el log de transacciones y cómo gestionar los diferentes tipos de log_reuse_wait es esencial para cualquier administrador de bases de datos SQL Server. Una gestión adecuada del log no solo previene problemas de rendimiento, sino que también asegura la disponibilidad y la recuperabilidad de la base de datos. Con este conocimiento, los DBAs podemos tomar medidas proactivas para mantener el log de transacciones en un tamaño manejable y garantizar el funcionamiento óptimo de nuestros sistemas de bases de datos.

Espero que este artículo te haya sido útil. 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 LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en SQL Server, 2 comentarios