SQL Server

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.

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!

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.

Jul

2024

Modos de Recuperación: Guía Completa

Uno de los aspectos más cruciales de las bases de datos SQL Server y, a menudo, complejos para los que se están iniciando son los modos de recuperación. En este artículo, voy a tratar de profundizar en los detalles de los modos de recuperación de SQL Server, detallando sus características, usos y mejores prácticas para optimizar el rendimiento y la seguridad de nuestras bases de datos. Espero que tanto si estáis empezando a trabajar con bases de datos como si sois ya experimentados podáis aprender algo nuevo.

¿Qué son los Modos de Recuperación en SQL Server?

Empecemos por el principio, en SQL Server, los modos de recuperación determinan cómo se gestiona el log de transacciones y cómo se pueden restaurar las bases de datos tras un fallo. Comprender estos modos es esencial para garantizar la integridad de los datos y minimizar el tiempo de inactividad en caso de incidentes. En este sentido, vamos a poder trabajar con tres modos distintos, simple, full (completo) o bulk-logged (registro masivo).

Tipos de Modos de Recuperación

Como acabamos de ver existen tres modos principales de recuperación en SQL Server: Simple, Full y Bulk-Logged. Cada uno de estos modos tiene sus propias características y escenarios de uso.

Modo de Recuperación Simple

El modo de recuperación simple es el más básico de los tres. En este modo, el espacio utilizado por el registro de transacciones se reutiliza automáticamente después de cada punto de control (checkpoint), lo que significa que no se requiere una gestión detallada del log.

Esto tiene una serie de ventajas como la simplicidad de no requerir una administración exhaustiva del log de transacciones o un menor tamaño del log al reutilizar el espacio. Siempre se mantiene el tamaño del log relativamente pequeño mientras no hagamos transacciones descomunales. Sin embargo, también tiene sus contras. Como desventajas nos vamos a encontrar con una menor capacidad de copias de seguridad ya que solo vamos a poder hacer copias completas o diferenciales. Esto, por supuesto, se traduce en una menor capacidad de recuperación a la hora de necesitar una restauración. En estos casos es probable que suframos una mayor pérdida de datos. En caso de fallo, los datos desde el último respaldo hasta el momento del fallo se perderán, esto es así siempre pero, en el caso de otros modos de recuperación con copias de logs, este tiempo suele ser menor.

Como habrás podido comprobar este modo es ideal para bases de datos que no necesitan un alto grado de recuperación de datos, como bases de datos de desarrollo o pruebas o entornos de producción sin gran cantidad de cambios o sin mucha criticidad.

Modo de Recuperación Full

El modo de recuperación completo, el modo por defecto de todas las nuevas bases de datos de SQL Server y Azure, ofrece el mayor nivel de protección para los datos. En este modo, cada transacción se registra completamente, y se puede realizar una restauración a cualquier punto en el tiempo. Para lograr esto el log de transacciones almacena las transacciones sin borrarlas incluso cuando han terminado y solo se borran cuando ya han sido salvadas en una copia de seguridad de log.

Como ventajas a este comportamiento podemos encontrar una capacidad de copias y por tanto de recuperación más granular. Una base de datos en modo de recuperación Full nos va a permitir la restauración de la base de datos a un punto concreto en el tiempo, minimizando la pérdida de datos. Por supuesto esta mayor capacidad de copias tiene otra ventaja añadida y es la flexibilidad a la hora de diseñar nuestra solución de backups. Al soportar backups completos, diferenciales y de log de transacciones vamos a poder adaptar nuestro plan de copias a nuestros procesos de manera que no interfieran en el rendimiento.

No todo es tan bonito, claro, a cambio tendremos una gestión de los log más compleja que requerirá más cuidado y vigilancia por nuestra parte para evitar que crezca demasiado.

Esto puede ser más complicado de administrar debido a la necesidad de realizar backups de log regulares.

Como ves, este modo es el indicado para bases de datos de producción críticas donde la pérdida de datos debe ser mínima y disponen de un administrador de base de datos que diseña y supervisa el plan de copias de seguridad para evitar incidencias.

Modo de Recuperación por Bulk-Logged

El modo de recuperación de registro masivo, es una opción intermedia entre el Modo Simple y el Modo Completo. Está diseñado para optimizar las operaciones masivas de carga de datos. Este modo minimiza el uso del espacio del log de transacciones cuando se ejecutan operaciones masivas como BULK INSERT, SELECT INTO o CREATE INDEX. Funciona de manera similar al Modo de Recuperación Completo, con la excepción de que los registros de transacciones se registran mínimamente durante las operaciones masivas. Esta forma de registro mínimo ayuda a mantener el log más pequeño al no registrar tanta información.

Gracias a estas optimizaciones, mejora la eficiencia en las operaciones masivas como la carga de datos con BULK INSERT que serán más rápidas y generarán menos registros en el log. Todo esto lo consigue sin perder la flexibilidad ya que ofrece algunas capacidades de recuperación punto en el tiempo, similares al modo full. Es importante remarcar ese algunas capacidades ya que cuando estamos haciendo una operación de carga masiva de datos no vamos a tener registros en el log de transacciones de esa operación lo que va a afectar a la posibilidad de recuperar en ese punto en el tiempo en concreto. Además, aún requiere una gestión adecuada del log de transacciones igual que el modo full.

A tener en cuenta

A pesar de que las recuperaciones punto en el tiempo pueden realizarse en algunas situaciones, si la base de datos se daña mientras se realiza una operación de carga masiva, solo se puede recuperar hasta el último backup del log de transacciones creado antes de la operación masiva. Si no se realizan operaciones de carga masiva mientras se utiliza este modo, entonces se puede realizar una restauración punto en el tiempo de manera similar al modo de recuperación completo. Para que podamos minimizar la pérdida de datos al realizar operaciones de carga masiva, es recomendable realizar un backup del log de transacciones justo antes de la operación masiva y otro inmediatamente después de que la operación haya finalizado. De esta manera, se puede realizar una recuperación punto en el tiempo utilizando los backups del log de transacciones tomados antes y después de la operación de carga masiva.

Elección del Modo de Recuperación Adecuado

La elección del modo de recuperación adecuado depende de varios factores, incluyendo los requisitos de recuperación de datos, la frecuencia de cambios en los datos y la capacidad de gestionar los backups del log, como ya hemos comentado anteriormente.

Requisitos de Recuperación: Si necesitamos la capacidad de recuperar la base de datos hasta un punto específico en el tiempo, el modo full es imprescindible. Si la pérdida de algunos datos es aceptable, el modo simple puede ser suficiente y nos va a simplificar mucho la tarea.
Frecuencia de Cambios: Las bases de datos con cambios frecuentes pueden beneficiarse del modo completo, mientras que aquellas con menos cambios pueden utilizar el modo simple o el Modo en Bulk-Logged.
Capacidad de Gestión: La capacidad de gestionar backups regulares y el tamaño del log de transacciones también influye en la elección. El modo completo requiere una mayor gestión, mientras que el modo simple es más fácil de manejar.

Mejores Prácticas para la Gestión de Modos de Recuperación

Como con todos los temas importantes, existen una serie de buenas prácticas que no debemos descuidar. En el caso de los modos de recuperación estas recomendaciones pasan por:

Realizar Backups Regulares: Independientemente del modo de recuperación, es crucial realizar backups completos de manera regular. Estos se podrán complementar con otros backups como diferenciales o log en función de las necesidades y del modo de recuperación elegido.
Monitorizar el Tamaño del Log: Especialmente en el modo full y Bulk-Logged, es importante monitorear y gestionar el tamaño del log de transacciones para evitar que crezca descontroladamente.
Probar Restauraciones: Realizar pruebas de restauración periódicas para asegurar que los backups son funcionales y los datos se pueden recuperar según lo planeado. Esto, que muchas veces se pasa por alto pues un backup no testeado puede dejarnos tirados cuando más lo necesitemos.
Documentar Procedimientos: Mantener una documentación detallada de los procedimientos de backup y restauración para asegurar una respuesta rápida y efectiva en caso de fallo es clave, como ya vimos en el post sobre la recuperación ante desastres.

¿Qué modo estoy usando? ¿Cómo lo cambio?

Existen varios métodos para averiguar el modo de recuperación actual de una base de datos y poder cambiarlo. El primero y más sencillo es a través de la interfaz gráfica del SSMS. Para ello haremos clic derecho sobre una base de datos y abriremos sus propiedades. Ya en las propiedades nos dirigiremos a las opciones para encontrar el modo de recuperación actual y poder cambiarlo si lo deseamos.

Otra forma de verlo es consultando la vista de sistema sys.databases donde vamos a encontrar una columna llamada recovery_mode_desc con esta información.

SELECT
   name,
   recovery_model_desc
FROM sys.Databases

Si deseamos modificar el modo de recuperación por código T-SQL usaremos estas sintaxis para el modo simple, full o bulk-logged respectivamente

USE master
GO
ALTER DATABASE NombreBaseDatos SET RECOVERY SIMPLE
GO

USE master 
GO
ALTER DATABASE NombreBaseDatos SET RECOVERY FULL
GO

USE master
GO
ALTER DATABASE NombreBaseDatos SET RECOVERY BULK_LOGGED
GO

Conclusión

Los modos de recuperación en SQL Server son un aspecto clave en la estrategia de administración de bases de datos. Elegir el modo adecuado y gestionarlo de manera efectiva pueden marcar la diferencia entre una recuperación exitosa y una pérdida de datos importante. Comprender las ventajas y desventajas de cada modo y aplicar las mejores prácticas nos asegurará la integridad y disponibilidad de nuestros datos, garantizando así un rendimiento óptimo y una respuesta eficiente ante cualquier incidente.

Jul

2024

NULL, un dato especial que debemos saber tratar

Cuando hablamos de datos, uno de los conceptos más interesantes y a menudo malinterpretados es el tratamiento del valor NULL. Nosotros, como DBAs, debemos comprender en profundidad cómo manejar estos valores especiales para asegurar la integridad y eficiencia de nuestras bases de datos. En este artículo, quiero profundizar en cómo SQL Server y las bases de datos SQL de Azure tratan los valores nulos, sus implicaciones y las mejores prácticas para su gestión. Por último compararemos este comportamiento con otros sistemas de gestión de bases de datos (SGBD) como PostgreSQL, MySQL y Oracle.

¿Qué significa NULL?

En el estándar SQL, NULL representa un valor desconocido o no aplicable. A diferencia de otros lenguajes de programación o de fórmulas, NULL no es igual a cero ni a una cadena vacía, en resumen, es simplemente la ausencia de un valor. Esta distinción es crucial, debemos interiorizarla y no olvidarla jamás, hazle un hueco en tu cerebro junto al recuerdo del gol de Iniesta en el minuto 116 de la final del Mundial de Sudáfrica 2010. Y no digo esto porque si, este comportamiento afecta a la forma en que las consultas se construyen y ejecutan. En entornos como SQL Server y Azure SQL, manejar correctamente los valores nulos puede marcar la diferencia entre una base de datos coherente y una llena de inconsistencias.

Tratamiento del valor NULL en SQL Server y Azure SQL

Lo primero que debemos recordar es que NULL no representa ningún valor y como tal no se va a comportar como un valor típico. En SQL Server, cualquier comparación lógica con NULL (por ejemplo, =, <, >, etc.) dará como resultado otro NULL. Esto se debe a que, al ser un valor desconocido, no se puede determinar con certeza si es igual o diferente a otro valor. Tenemos que pensar en que a la lógica de verdadero o falso (sí o no) se le va a sumar una tercera posibilidad, desconocido. Desconocido no es ni verdadero ni falso, ni sí ni no. Por esta razón, debemos utilizar el operador IS NULL o IS NOT NULL para verificar su existencia.

Efectos de NULL en las consultas

Cuando escribimos consultas, el manejo de los nulos es crucial. Vamos a verlo con un ejemplo, supongamos que tenemos una tabla Empleados y queremos encontrar a los que no tienen un jefe asignado. Podriamos pensar que esta consulta va a funcionar:

SELECT * FROM Empleados WHERE JefeID = NULL;

Sin embargo, esta consulta no devolverá ningún resultado, porque JefeID = NULL siempre es falso. La consulta correcta es:

SELECT * FROM Empleados WHERE JefeID IS NULL;

Funciones de manejo de NULL

Para poder salvar estas limitaciones SQL Server y Azure SQL ofrecen varias funciones para tratar los valores nulos de manera efectiva. Algunas de las más útiles incluyen:

ISNULL(): Esta función reemplaza NULL con un valor especificado. Por ejemplo, para mostrar «N/A» en lugar de NULL en un campo de teléfono, usaríamos:

SELECT Nombre, ISNULL(Telefono, 'N/A') AS Telefono FROM Empleados;

COALESCE(): Hemos visto que ISNULL() es una función que acepta dos valores y devuelve el primero que no sea NULL. Coalesce es igual, pero acepta múltiples argumentos y devuelve el primero que no sea NULL. Esto es especialmente útil cuando trabajamos con varias columnas que podrían contener valores nulos:

SELECT Nombre, COALESCE(Telefono1, Telefono2, Telefono3, 'N/A') AS Telefono FROM Empleados;

Tratamiento de NULL en funciones agregadas

Las funciones agregadas como SUM(), AVG(), MAX(), y MIN() directamente ignoran los valores nulos en sus cálculos. Esto es importante para evitar resultados inesperados. Por ejemplo, si queremos calcular el salario promedio de nuestros empleados, SQL Server excluirá automáticamente los valores nulos de la columna y podremos hacer esta consulta simple sin miedo a errores o resultados inesperados:

SELECT AVG(Salario) FROM Empleados;

Tratamiento de NULL en Dynamic Data Masking (DDM)

DDM nos permite enmascarar la información sensible de nuestra base de datos y, en ocasiones, la propia ausencia de información es sensible en sí. Sin embargo, y esto es algo mejorable a mi parecer, DDM no nos permite ocultar valores NULL sensibles en nuestras tablas. Es decir, DDM solo enmascara datos conocidos, mostrando un valor NULL real a los usuarios sin privilegios igual que a los que sí tienen permiso de desenmascarar.

Implicaciones de NULL en índices y claves

Al definir índices y claves, debemos tener en cuenta cómo los valores nulos afectan a estas estructuras. Por ejemplo, en SQL Server, un índice único permite múltiples valores nulos, lo cual puede ser útil pero también peligroso si no se entiende y maneja correctamente. Veamos ahora

Claves Primarias

Las claves primarias (PK) no admiten valores nulos. Esto es lógico, una clave primaria debe identificar de manera única cada fila en una tabla, y un valor nulo, al ser desconocido, no puede cumplir con esta restricción de unicidad. Por lo tanto, al diseñar nuestras tablas, debemos asegurarnos de que las columnas definidas como clave primaria siempre contengan valores no nulos. Realmente, SQL Server hará esto por nosotros y solo nos dejará definir como campos clave de una PK aquellas columnas definidas como NOT NULL, es decir, que no admiten valores nulos.

CREATE TABLE Empleados ( 
EmpleadoID INT NOT NULL PRIMARY KEY,
Nombre NVARCHAR(50),
JefeID INT NULL
);

En el ejemplo anterior, EmpleadoID no puede contener valores nulos, garantizando así la unicidad y la identificabilidad de cada registro. Gracias a esa restricción podemos definir la columna como clave primaria.

Claves Foráneas

Las claves foráneas (FK), por el contrario, sí pueden admitir valores nulos. Un valor nulo en una clave foránea indica que no hay una relación con la tabla referenciada. Esto puede ser útil para representar situaciones donde una relación no es obligatoria. Por ejemplo, si un empleado no tiene un jefe designado, el campo JefeID puede ser NULL.

ALTER TABLE Empleados ADD CONSTRAINT FK_Empleados_Jefe FOREIGN KEY (JefeID) REFERENCES Empleados(EmpleadoID);

En este caso, JefeID puede ser nulo, lo que indica que un empleado no tiene jefe.

Mejores prácticas para manejar NULL

Para mantener la integridad y eficiencia de nuestras bases de datos, debemos seguir algunas mejores prácticas en el uso y control de nulos. Siempre que sea posible, definiremos valores por defecto en nuestras columnas para evitar NULL. Por ejemplo, en lugar de permitir NULL en una columna de estado, podríamos definir «Desconocido» como valor por defecto. Los valores por defecto son aquellos que se van a poner en nuestra base de datos cuando insertamos un registro y no especificamos manualmente otra cosa. Por ejemplo:

ALTER TABLE Empleados ADD CONSTRAINT DF_Estado DEFAULT 'Desconocido' FOR Estado;

No solo los nulos pueden ser un problema, también es posible que un dato técnicamente válido no tenga sentido de negocio. Para estos casos podemos utilizar restricciones CHECK y NOT NULL para asegurarnos de que nuestras columnas contengan siempre valores válidos. Por ejemplo, la siguiente restricción se asegura de que el salario de un empleado siempre sea un número mayor que 0. Como hemos visto antes, esta restricción también invalidará los valores NULL de forma intrínseca.

ALTER TABLE Empleados ADD CONSTRAINT CK_Salario CHECK (Salario > 0);

Por último, debemos documentar claramente cuándo y por qué permitimos nulos en nuestras tablas y asegurarnos de que todos los desarrolladores sigan las mismas buenas prácticas a la hora de crear tablas.

Comparación con otros SGBD

Como este tema es importante vamos a ver el comportamiento de NULL cuando trabajamos con otros de los principales motores de base de datos presentes en el mercado.

PostgreSQL maneja los valores nulos de manera similar a SQL Server. Al igual que en SQL Server, las comparaciones directas con NULL siempre devuelven NULL, y se utilizan IS NULL y IS NOT NULL para verificar la existencia de valores NULL. PostgreSQL también soporta funciones como COALESCE() y NULLIF() para manejar NULL. Una diferencia notable es que PostgreSQL permite definir restricciones más avanzadas usando expresiones booleanas, lo que puede ser útil para controles más complejos sobre los valores NULL.
MySQL, el manejo de nulos es también bastante similar. Las comparaciones directas con NULL resultan en NULL, y se usan IS NULL y IS NOT NULL para las verificaciones. MySQL proporciona las funciones IFNULL() y COALESCE() para manejar valores NULL. Un aspecto distintivo de MySQL es que, en ciertos modos de SQL, puede comportarse de manera más laxa con los valores nulos en índices únicos, permitiendo múltiples entradas NULL, lo cual es similar a SQL Server.
Oracle maneja los nulos de manera muy coherente con el estándar SQL. Las comparaciones con NULL devuelven NULL, y se utilizan IS NULL y IS NOT NULL. Oracle también ofrece funciones como NVL() (que es equivalente a ISNULL() en SQL Server) y COALESCE(). Una característica única de Oracle es su uso de NULLS FIRST y NULLS LAST en las cláusulas ORDER BY, permitiendo un control más granular sobre el orden de los valores NULL en los resultados de las consultas.

Comparación con DAX

Ya hemos hablado de DAX (Data Analysis Expressions) en el blog. DAX es un lenguaje de fórmulas utilizado en Power BI, Analysis Services y Power Pivot para realizar cálculos y consultas de datos. DAX tiene su propia forma de manejar valores nulos, conocida como BLANK. En DAX, el equivalente de NULL es BLANK. El manejo de valores BLANK en DAX difiere de los NULL en SQL en varios aspectos clave

Comparaciones

En DAX, una comparación directa con BLANK no devuelve BLANK. En lugar de eso, BLANK es tratado como 0 (cero) en comparaciones numéricas y como una cadena vacía en comparaciones de texto. Por ejemplo:

IF([Valor] = BLANK(), "Es nulo", "No es nulo")

Funciones de manejo de BLANK

DAX proporciona varias funciones para manejar valores BLANK, incluyendo BLANK(), ISBLANK(), y COALESCE(). La función COALESCE() en DAX es similar a su homóloga en SQL, devolviendo el primer valor que no sea BLANK.

COALESCE([Columna1], [Columna2], 0)

Impacto en cálculos agregados

Las funciones agregadas en DAX, como SUM(), AVERAGE(), MAX(), y MIN(), manejan los valores BLANK de manera similar a SQL, ignorándolos en los cálculos. Sin embargo, es importante saber que en DAX, un BLANK en una medida puede tener implicaciones diferentes dependiendo del contexto del cálculo. Supongamos que tenemos una tabla de ventas en Power BI y queremos calcular el total de ventas, tratando los valores nulos en la columna Cantidad como ceros:

TotalVentas := SUMX(Ventas, COALESCE(Ventas[Cantidad], 0))

En este ejemplo, la función SUMX recorre cada fila en la tabla Ventas y utiliza COALESCE para tratar los valores BLANK en la columna Cantidad como 0.

Diferencias clave DAX vs SQL

Como hemos visto el tratamiento de los valores nulos en DAX difiere del de SQL. Podríamos resumir las diferencias así:

Manejo en comparaciones: Mientras que en SQL, NULL no se puede comparar directamente y cualquier comparación devuelve NULL, en DAX el valor BLANK se trata como 0 o una cadena vacía, dependiendo del contexto.
Funciones específicas: DAX tiene funciones específicas para trabajar con BLANK, y estas funciones son esenciales para manejar datos en contextos de análisis y cálculos complejos.
Contexto de cálculo: En DAX, el contexto de cálculo puede afectar cómo se manejan los valores BLANK, especialmente en modelos de datos complejos donde se utilizan múltiples tablas y relaciones.

Conclusión

El manejo de valores nulos es una característica fundamental cuando trabajamos con datos, ya sea en SQL Server, Azure SQL, otros SGBD como PostgreSQL, MySQL y Oracle o con herramientas de BI como Power BI. Aunque las diferencias en el comportamiento son sutiles, cada SGBD ofrece herramientas y funciones específicas para tratar con valores NULL de manera eficiente.

Comprender estas diferencias y mejores prácticas es imprescindible para diseñar y gestionar nuestras bases de datos de manera más efectiva, asegurando la integridad y eficiencia de nuestros sistemas.

Jul

2024

Servidores SQL administrándose solos con políticas

Amigo DBA, vete de vacaciones tranquilo. Crea políticas (directivas) para que tus servidores SQL Server se administren por si solos. En este video te muestro las políticas que yo tengo en mis servidores y como crear las tuyas personalizadas.

Gracias a las políticas de SQL Server vamos a poder exigir el cumplimiento de las directivas que nosotros definamos para que todo siga funcionando como debería en nuestra ausencia. Podremos elegir entre evaluar cada una de las políticas a petición o bajo demanda y su comportamiento, por ejemplo, si van a dejar un log o a prevenir el cambio directamente.

Una vez creadas tus políticas podrás exportarlas e importarlas en el resto de tus servidores para no tener que repetir el trabajo. Esto último te lo muestro en el artículo de la semana pasada que puedes encontrar aquí. No esperes más y toma el control de tus servidores. Usa estas políticas para mantener la coherencia en tus esquemas, asegurar el cumplimiento de normativas internas o automatizar tus tareas.

Espero que te haya gustado el video, si es así por favor, deja tu me gusta y suscríbete al canal que nos ayuda mucho. Si quieres ver más videos como este puedes encontrarlos todos aquí. 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 al que te puede unir. En este grupo estamos creando una comunidad de usuarios y administradores de SQL Server donde cualquiera pueda preguntar sus dudas y compartir sus casos prácticos para que todos seamos mejores profesionales. ¡Hasta la próxima!

Jun

2024

Políticas de SQL. Creando servidores Autoadministrados

Una de las mejores características de un buen profesional es que siempre busca formas de hacer su trabajo más eficiente y efectivo. Los administradores de base de datos no estamos exentos de esta norma y, una de las herramientas que tenemos a nuestra disposición para lograr esto es el uso de políticas en SQL Server. Si no habías oído hablar antes de esto no te preocupes, es una opción de SQL Server a menudo subestimada pero al final de este artículo vas a poder ver su gran potencial. Durante las próximas líneas explicaremos cómo las políticas pueden facilitarnos la vida a la hora de gestionar nuestras bases de datos, garantizando tanto el cumplimiento de normas como la eficiencia y el buen rendimiento.

Imaginemos un escenario donde gestionamos múltiples instancias de SQL Server, cada una con sus propias configuraciones, niveles de seguridad y requisitos de rendimiento. No es dificil verdad, es nuestro pan de cada día. Sabrás entonces que mantener la coherencia y el cumplimiento normativo en un entorno así puede ser una tarea exigente. Pues bien, aquí es donde las políticas de administración de SQL Server entran en juego y nos permiten definir, aplicar y automatizar reglas y directrices de una manera estructurada y eficiente.

¿Qué son las Políticas en SQL Server?

Las políticas en SQL Server son un componente del Policy-Based Management (PBM), introducido por primera vez en SQL Server 2008. Esta funcionalidad nos permite definir reglas y condiciones para nuestros servidores y bases de datos, asegurando que cumplan con ciertos estándares de configuración y rendimiento. En términos simples, una política es un conjunto de reglas que se aplican a los objetos de SQL Server para garantizar que se comporten de una manera específica. Estas reglas pueden abarcar desde la configuración del servidor hasta el diseño de la base de datos, y pueden ayudarnos a mantener un alto nivel de calidad y consistencia en nuestras bases de datos.

Por ejemplo, podríamos crear una política que fuerce a que todas las tablas tengan una PK (clave primaria). Esta política se aplicaría a todas las bases de datos en el servidor, y cualquier usuario intente crear una tabla sin PK recibiría un bonito error.

Otra política común es obligar a que todas las bases de datos tengan copias de seguridad regulares. Esta política la podremos configurar para verificar que se haya realizado un backup en las últimas 24 horas, y si no es así, que genere una alerta (que nos envía un correo) para que nosotros como administradores de la base de datos tomemos medidas.

Las políticas también nos pueden ser útiles para mantener la seguridad de nuestras bases de datos. Podríamos tener una política que fuerce a que todas las conexiones al servidor se realicen a través de una conexión segura, o que todas las contraseñas cumplan con ciertos requisitos de complejidad

Componentes Clave de una Política

Antes de ver cómo tenemos que hacer para crear una política personalizada tenemos que tener claros una serie de conceptos que vamos a necesitar. En concreto vamos a estar trabajando con condiciones, facetas y modos de evaluación además de las propias políticas. Veamos que son cada uno de ellos.

Condición (Condition): Una condición es un conjunto de expresiones que definen un estado deseado o un comportamiento que queremos comprobar. Por ejemplo, podríamos tener una condición que verifique si la recuperación de base de datos está configurada como «FULL».
Facetas (Facets): Las facetas son conjuntos predefinidos de propiedades de los objetos de SQL Server. Por ejemplo, hay facetas para bases de datos, servidores, procedimientos almacenados, entre otros. Cada faceta contiene varias propiedades que podemos utilizar en nuestras condiciones.
Política (Policy): Una política combina una condición con una faceta y define cómo y cuándo se debe evaluar esta combinación. Las políticas pueden ser evaluadas bajo demanda, de forma programada o en respuesta a eventos específicos.
Modo de Evaluación (Evaluation Mode): Este define cuándo y cómo se evaluará una política. Existen varios modos, incluyendo «On Demand», «On Schedule», «On Change – Prevent» y «On Change – Log Only».

Creando y Administrando Políticas

Para implementar políticas en SQL Server, utilizamos la característica de Administración Basada en Políticas (PBM, por sus siglas en inglés). Como ya hemos visto, PBM nos permite definir políticas, verificar su cumplimiento y aplicarlas automáticamente. Lo primero que deberemos hacer es definir una condición. Una vez que hayamos definido una condición, ya podremos crear una política que utilice esa condición. Por último definiremos su modo de evaluación.

Paso 1: Definir una Condición

El primer paso para crear una política es definir una condición. Supongamos que queremos asegurarnos de que todas nuestras bases de datos estén en modo de recuperación FULL. Para ello, primero definimos una condición:

Abrimos SQL Server Management Studio (SSMS) y navegamos hasta «Management» -> «Policy Management». Hacemos clic derecho en «Conditions» y seleccionamos «New Condition». Le damos un nombre a nuestra condición, por ejemplo, «Database Recovery Full». En «Facet», seleccionamos «Database». En «Expression», añadimos una nueva condición: @RecoveryModel = ‘FULL’.

Paso 2: Crear la Política

Con la condición definida, procedemos a crear la política que la usará:

Hacemos clic derecho en «Policies» y seleccionamos «New Policy». Le damos un nombre, como «Ensure Full Recovery Mode». Asignamos la condición «Database Recovery Full» que creamos anteriormente. En «Against Targets», especificamos los objetos a los que se aplicará esta política, en este caso, todas las bases de datos. Elegimos el modo de evaluación. Para este ejemplo, seleccionamos «On Change – Log Only» para registrar cualquier incumplimiento sin impedir cambios.

Paso 3: Evaluar y Aplicar la Política

Una vez creada la política, podemos evaluarla inmediatamente:

Hacemos clic derecho en la política y seleccionamos «Evaluate». SQL Server nos mostrará todas las bases de datos que no cumplen con la política. Podemos tomar acciones correctivas directamente desde el cuadro de diálogo de evaluación si es necesario.

Paso Extra: Exporta tus políticas

Cuando ya tengas todas tus políticas creadas en uno de tus servidores, no es necesario que las recrees en el resto, simplemente podemos exportarlas y volverlas a importar en tantos servidores como queramos. Para ello solo tendremos que dar clic derecho sobre nuestra política y dar a exportar. Para importar haremos clic derecho sobre la carpeta políticas en nuestro SSMS y le daremos a importar política.

Beneficios del Uso de Políticas

Como venimos comentando, las políticas nos permiten estandarizar configuraciones y prácticas en todas nuestras instancias de SQL Server. De esta manera vamos a lograr asegurar el cumplimiento de normas corporativas y regulatorias. Esto es especialmente importante en entornos que deben cumplir estándares y leyes como GDPR, HIPAA o SOX.

Además, al definir políticas que se evalúan automáticamente, podemos reducir nuestra carga de trabajo y minimizar el riesgo de errores humanos. Por ejemplo, podemos programar evaluaciones periódicas para asegurar que todas nuestras configuraciones de seguridad y rendimiento se mantengan conforme a las políticas definidas.

Pero esto no es todo, las políticas también nos ayudan a identificar y corregir problemas potenciales antes de que se conviertan en problemas mayores. Al tener visibilidad continua de cómo se comportan nuestros servidores y bases de datos en relación con nuestras políticas, podemos ser proactivos en la gestión y el mantenimiento.

Conclusión

El uso de políticas para administrar SQL Server es una práctica que nos permite mantener el control y la coherencia en nuestros entornos de bases de datos. Al definir y aplicar políticas claras, podemos asegurar el cumplimiento normativo, automatizar tareas administrativas y mantener un alto nivel de rendimiento y seguridad. En resumen, las políticas no solo simplifican la administración de SQL Server, sino que también nos permiten ser más eficientes en nuestro trabajo como DBAs. Así que, no esperes más. Pruébalo y considera aprovechar el poder de las políticas para facilitarte la vida.

SQL Server

Servidores SQL administrándose solos con políticas

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes