Rendimiento

09
May
2025

¿Qué podemos aplicar de la metodología Six Sigma a SQL Server?

Aunque Six Sigma suena a fábricas, líneas de producción y tipos con casco revisando procesos con una tablet que no entienden del todo, sus principios encajan sorprendentemente bien en el día a día de quienes administramos bases de datos. ¿Queremos estabilidad, rendimiento y menos sustos en producción? Esto también va con nosotros.

No hace falta creerse un cinturón negro (literalmente, Six Sigma va por cinturones como el karate) para entender lo que propone. Six Sigma se centra en reducir la variabilidad, eliminar errores y optimizar procesos. ¿Te suena familiar? Claro. En el fondo, cuando hacemos tuning, revisamos procesos de mantenimiento o auditamos un servidor que se arrastra desde hace meses, estamos buscando lo mismo: eficiencia y control. La diferencia es que, con Six Sigma, lo hacemos con un marco claro, basado en datos y no en corazonadas.

DMAIC: el ciclo Six Sigma que también entiende un DBA

Six Sigma gira en torno a un ciclo de mejora continua llamado DMAIC. No, no es una broma con las siglas de alguna empresa de hosting dudosa. Hablamos de cinco fases perfectamente aplicables a SQL Server: Definir, Medir, Analizar, Mejorar y Controlar.

Fases del ciclo DMAIC de Six Sigma

Definir (Define) es identificar claramente qué proceso o componente queremos mejorar. Nada de “el servidor va lento” como diagnóstico. ¿Son los backups que tardan siglos? ¿Consultas que convierten CPUs en tostadoras? ¿El sistema de auditoría que escribe más que Kafka? Si no sabemos qué, no sabremos cómo.

Medir (Measure) exige recopilar datos reales. No lo que creemos, no lo que el desarrollador «recuerda que antes iba más rápido», sino métricas objetivas. Aquí entran herramientas como Query Store, Extended Events o, si nos ponemos clásicos, una buena captura de sys.dm_exec_requests. Medimos tiempos, recursos consumidos, ratios de deadlocks, tiempos de espera. Medimos lo que importa.

Analizar (Analyze) nos lleva a buscar la causa raíz, no el síntoma. ¿Es un índice mal diseñado? ¿Una estadística obsoleta? ¿Un plan de ejecución que cambió por un parámetro traicionero? No vale con saber que una query tarda 40 segundos; hay que saber por qué.

Mejorar (Improve) significa actuar. No nos quedamos con el diagnóstico para contarlo en la próxima daily. Aquí afinamos consultas, revisamos planes de ejecución, creamos (o eliminamos) índices, y, si hace falta, activamos OPTIMIZE FOR o le damos una vuelta al esquema. Mejora implica intervención con sentido, no aplicar la última sugerencia de Stack Overflow como quien lanza dados.

Controlar (Control) es cerrar el círculo. Asegurar que el problema no reaparezca en tres semanas cuando nadie mire. Esto implica monitorización continua, alertas, revisiones periódicas, e incluso políticas formales que eviten el caos reincidente. El control no es micromanagement, es prevención profesional.

Todo esto puede parecer obvio para quien ya lleva años en esto, pero seamos honestos: ¿cuántas veces resolvemos un problema y nos olvidamos de controlarlo?

Ciclo DMAIC en versión DBA

Visualiza este ciclo aplicado a nuestro mundo:

Sí, es cíclico. Y no, no se termina nunca. Como los correos de usuarios pidiendo acceso de sysadmin.

Ejemplos prácticos: aplicando Six Sigma SQL Server

Vamos a bajar a tierra cada fase con escenarios reales. Porque sí, queda muy bien hablar de metodologías, pero lo importante es aplicarlas sin parecer un consultor que no ha tocado un Management Studio en años.

Fases de análisis

Definir: Imaginemos que tenemos backups nocturnos que cada vez tardan más. O una aplicación web que de repente responde como si estuviera en un 3G con cobertura dudosa. O usuarios que se quejan de bloqueos cada lunes a las 10. Aquí no hablamos de «mejorar el rendimiento», sino de «disminuir el tiempo de backup de 90 a 45 minutos», «reducir el tiempo de respuesta de la API a menos de 300 ms», o «eliminar los bloqueos en el proceso X». Concreción, no filosofía.

Medir: Nada de adivinar. Nos vamos a Query Store a ver los planes de ejecución históricos, capturamos métricas con Extended Events, analizamos wait stats, miramos IO con sys.dm_io_virtual_file_stats, y sacamos ratios de bloqueos por segundo. Si no lo podemos medir, no lo podemos mejorar. Así de simple.

Y ya que estamos, digámoslo claro: el 90% de los “problemas de rendimiento” no son problemas de rendimiento. Son problemas de diseño sin medir. 

## Analizar: Ya con los datos en mano, toca ver qué está pasando de verdad. ¿El backup tarda por una fragmentación absurda en los logs? ¿La consulta lenta está usando un plan de ejecución malo? ¿El índice no se usa porque las estadísticas llevan semanas sin actualizarse? Esta fase separa a los DBA de verdad del resto. El análisis técnico y profundo no se improvisa.

Fases de resolución

Mejorar: Aquí toca mancharse las manos. Creamos índices, ajustamos queries, reescribimos procedimientos o configuramos Resource Governor para evitar que un proceso devore todo. Aplicamos cambios, sí, pero con criterio y midiendo impacto.

Controlar: Una vez resuelto, dejamos trazas para volver atrás si algo falla, implementamos alertas y documentamos el cambio. Porque el control no es una fase de “paz mental”. Es garantizar que el mismo marrón no vuelva como un bug de Windows Update.

Six Sigma no es solo humo de colores

Six Sigma no es una receta mágica, pero su enfoque metódico y basado en datos encaja como anillo al dedo en entornos SQL Server donde lo que no se mide, se convierte en intuición. Y las intuiciones son como los SELECT *: muy populares, muy cómodas… y muy peligrosas.

Además, adoptar esta filosofía nos permite algo clave: dejar de apagar fuegos y empezar a diseñar para que no haya incendios. El DBA reactivo sobrevive. El DBA proactivo duerme tranquilo (o al menos, algo más).

Y si alguna vez tienes que enfrentarte a una auditoría o a justificar por qué se ha invertido en monitorización o en refactorizar consultas, hablar de Six Sigma y DMAIC te da un marco claro y defendible. Además, queda muy bien en el CV. Aunque no sepas karate.

Conclusión

Aplicar Six Sigma en el entorno de SQL Server no es ponerse un casco ni hablar en jerga industrial. Es adoptar una mentalidad que exige claridad, datos, análisis y control continuo. Es lo que ya deberíamos estar haciendo, pero con estructura.

¿Queremos servidores que no den sustos, procesos que rindan como deben y decisiones que se basen en hechos? Pues dejemos de improvisar y empecemos a aplicar lo que funciona. DMAIC no es magia. Pero en manos de un buen DBA, casi lo parece.

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
05
May
2025

Introduccion a Service Broker

Hay funcionalidades de SQL Server que llevan años en la sombra, casi como reliquias de una época en la que creíamos que todo tenía que resolverse con triggers, cursores o, peor aún, con consultas agresivas cada 30 segundos. Entre esas joyas ignoradas, olvidadas o simplemente mal comprendidas, destaca Service Broker. No es nuevo, no es trendy y desde luego no tiene marketing detrás, pero cuando se entiende y se usa bien, hace cosas que ni Kafka ni RabbitMQ pueden soñar… al menos no sin invitar a medio equipo de infraestructura.

¿Qué es Service Broker y por qué sigue vivo?

Service Broker es el subsistema de mensajería nativo de SQL Server. Está ahí desde la versión 2005, y a diferencia de muchas promesas de aquella época, sigue funcionando sin necesidad de parches ni microservicios externos. La idea es sencilla: permitir que dos o más bases de datos se comuniquen de forma asíncrona, fiable, y con garantías de entrega, utilizando colas, mensajes y contratos definidos dentro del propio motor.

¿Parece demasiado bonito? Lo es, si lo usas bien. Pero claro, como todo en SQL Server, si lo activas sin entenderlo, puede acabar como aquel CLR que alguien dejó en producción y que ahora nadie se atreve a tocar.

Mensajes, contratos y colas: bienvenidos al lenguaje de Service Broker

La arquitectura de Service Broker se apoya en tres conceptos fundamentales: mensajes, contratos y colas.

Los mensajes son lo que uno espera: trozos de datos enviados de un punto a otro. Pueden llevar XML, texto, JSON, o lo que se te ocurra empaquetar mientras quepa en varbinary(max). Pero cuidado: si lo que mandas es basura, lo que recibes es basura asincrónica.

Los contratos definen qué tipos de mensajes se pueden intercambiar y quién puede enviarlos. Un contrato no es un capricho burocrático, es la única barrera entre un sistema robusto y un caos de mensajes huérfanos flotando en una cola sin dueño.

Las colas, por su parte, son objetos de base de datos donde se almacenan los mensajes. Pueden estar enlazadas a una tabla de activación, y de ahí lanzar procedimientos almacenados automáticamente cuando llega un mensaje. Y aquí es donde empiezan las posibilidades interesantes: procesamiento asíncrono, desacoplamiento lógico, incluso orquestación de tareas complejas sin necesidad de middleware externo.

Activación interna: el punto en el que Service Broker se vuelve interesante

Service Broker permite asociar una cola a un procedimiento almacenado. Cuando llegan mensajes nuevos, SQL Server puede lanzar automáticamente una instancia de ese procedimiento. Esto permite procesar eventos en tiempo real (bueno, en SQL-real) sin que el cliente tenga que esperar.

¿Significa esto que podemos hacer procesamiento paralelo dentro de SQL Server sin tocar SSIS, sin montar microservicios ni invocar APIs REST desde T-SQL? Exactamente. Con matices, claro. Esto no es Node.js, ni pretende serlo. Pero si lo que necesitas es procesar tareas, disparar workflows o mover datos entre bases, sin acoplar todo a una única transacción gigantesca, entonces estás en el lugar adecuado.

Un ejemplo real: procesar tareas sin freír el servidor

Supongamos que tenemos un sistema que necesita registrar operaciones de auditoría cada vez que un usuario modifica un registro crítico. Antes de que alguien proponga otro trigger que escriba directamente en la tabla de auditoría (y ralentice la operación principal), pensemos con más cabeza: ¿por qué no delegar esa escritura a un sistema asincrónico?

Con Service Broker, podemos:

  • Crear un mensaje con la información de auditoría.
  • Enviarlo a una cola específica.
  • Tener un procedimiento almacenado escuchando esa cola.
  • Procesar el mensaje y escribir la auditoría sin interferir con la transacción principal.

Esto no sólo mejora el rendimiento, sino que evita que un fallo en la auditoría reviente una operación de negocio. Sí, lo sabemos: no siempre se puede hacer, pero cuando se puede, es una diferencia de nivel.

Seguridad, rutas y conversación: lo que hay que saber de service Broker

Service Broker funciona bajo un modelo de «conversaciones» entre servicios. Sí, como las conversaciones de WhatsApp, pero con contratos y sin stickers. Estas conversaciones son bidireccionales y tienen estado, lo que permite enviar múltiples mensajes en ambos sentidos de forma controlada.

Para que todo esto funcione entre diferentes bases de datos o instancias, necesitamos definir servicios, rutas, y a veces, incluso certificados si vamos a cruzar servidores. Aquí es donde muchos tiran la toalla, porque no es trivial y la documentación oficial tiene ese encanto críptico tan característico de Microsoft cuando parece que no quiere que usemos algo.

Pero si superamos esta curva de aprendizaje pronunciada, podremos tener mensajería distribuida transaccional entre instancias SQL Server sin necesidad de colas externas, sin middleware y sin lag de 10 segundos.

¿Por qué no se usa más?

Primero, porque no está de moda. Service Broker no tiene un logo molón, ni una conferencia anual, ni camisetas con chistes de desarrolladores de colas. Pero eso no lo hace menos útil. Segundo, porque requiere entender T-SQL más allá del SELECT con JOIN, y eso ya elimina a cierto porcentaje de desarrolladores.

Y tercero, porque no se ha enseñado bien. Muchos lo han visto como «algo para entornos muy específicos», sin darse cuenta de que puede resolver problemas cotidianos: colas de trabajo, asincronía controlada, desacoplamiento entre componentes de una aplicación.

Cuándo usar Service Broker y cuándo no

No todo es un clavo, y Service Broker no es un martillo universal. Si necesitas alta escalabilidad, integración con sistemas heterogéneos o necesitas exponer eventos a sistemas no-SQL, probablemente debas mirar soluciones como Azure Service Bus, RabbitMQ o Kafka.

Pero si tu universo es SQL Server, si el cuello de botella está en las transacciones, y si quieres que los procesos se comuniquen entre sí dentro del mismo motor, entonces estás dejando pasar una oportunidad de oro si ignoras Service Broker.

Conclusión

Service Broker no es una bala de plata, ni pretende serlo. Pero es una de esas funcionalidades que, bien comprendidas, permiten diseñar soluciones limpias, escalables y robustas sin necesidad de inventarse una arquitectura de microservicios para cada cosa.

Y lo mejor es que ya está en tu SQL Server. Solo tienes que activarlo, entenderlo, y no cometer los errores de quienes pensaron que podían usarlo sin leer el manual. Porque sí, hay que cerrar las conversaciones, y no, no es opcional. Como dejar un BEGIN CONVERSATION sin END CONVERSATION: es como dejar una transacción abierta esperando que alguien más la cierre. O que venga un unicornio.

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
14
Abr
2025

Tablas temporales vs. variables de tabla en SQL Server: diferencias que sí importan

Las tablas temporales y las variables de tabla en SQL Server son como los destornilladores y las llaves inglesas de nuestra caja de herramientas: parecidas en concepto, pero con propósitos, comportamientos y peculiaridades bien distintos. Ambos mecanismos nos permiten trabajar con conjuntos de datos intermedios sin necesidad de recurrir a tablas permanentes, pero quien haya intentado usarlos indistintamente en entornos reales sabe que las diferencias importan. Y mucho.

Vamos a destripar ambas opciones con calma, ver cómo se comportan, qué ventajas ofrecen y en qué situaciones conviene (o no) usarlas. Porque sí, aunque parezcan intercambiables, tratarlas como tal suele acabar en consultas lentas, planes de ejecución misteriosos y DBA rezando en voz baja.

Tablas temporales: las inquilinas del tempdb

Las tablas temporales (#TablasTemporales) son lo más parecido a una tabla normal que podemos crear en tiempo de ejecución sin dejar huella permanente. Se crean en la base de datos tempdb, y existen mientras dure la sesión (o el scope) que las creó. Podemos definir índices, claves primarias, restricciones, estadísticas… Vamos, que son tablas de verdad, aunque vivan en alquiler. Su sintaxis es familiar y directa:

CREATE TABLE #ClientesTemp (
    Id INT PRIMARY KEY,
    Nombre NVARCHAR(100),
    FechaAlta DATETIME
);

O incluso más común aún:

SELECT *
INTO #VentasTemp
FROM Ventas
WHERE Fecha >= '2025-01-01';

El plan de ejecución que las acompaña suele ser robusto, especialmente si trabajamos con cantidades de datos considerables. SQL Server genera estadísticas automáticas sobre las columnas, lo cual permite un plan más ajustado al volumen real. Esto, que parece un detalle técnico sin importancia, marca la diferencia entre una consulta que vuela y otra que arrastra los pies como yo un lunes por la mañana antes del primer café.

¿Inconvenientes? Algunos. El uso de tempdb implica competencia con otros procesos que también están abusando del mismo recurso. Además, su ciclo de vida depende del contexto: si las creamos en un procedimiento almacenado y lo llamamos varias veces, conviene borrarlas explícitamente con DROP TABLE o usar IF OBJECT_ID(…) IS NOT NULL.

Y por supuesto, cuidado con el scope: una tabla temporal creada dentro de un procedimiento no es accesible desde fuera. Pero eso no debería sorprendernos. Tampoco esperamos que un DECLARE de una variable sobreviva al final del procedimiento.

Variables de tabla: pequeñas, rápidas… y caprichosas

Las variables de tabla (@VariablesDeTabla) se introdujeron como una forma rápida y elegante de manejar pequeños conjuntos de datos sin el overhead de una tabla temporal completa. Son ideales para almacenar unas cuantas filas, iterar lógicamente sobre ellas o devolver resultados simples.

La sintaxis es limpia:

DECLARE @Productos TABLE (
    Id INT,
    Nombre NVARCHAR(50)
);

Y su ciclo de vida es exactamente el del bloque donde se declaran. No hay que preocuparse por borrarlas ni por interferencias externas. Hasta aquí todo bien.

Ahora viene el problema: SQL Server no genera estadísticas sobre variables de tabla. Nunca. Ni en 2008 ni en 2022. Esto significa que el optimizador trabaja a ciegas. Literalmente: asume que una variable de tabla tiene una única fila. Da igual si tiene 1 o 10.000. El plan de ejecución será el de una tabla de una fila. Y eso, amigos, rara vez termina bien.

¿Hay excepciones? Desde SQL Server 2019, con OPTION (RECOMPILE), el optimizador puede estimar el número real de filas en algunos casos. Pero es una tirita en una fractura abierta. A veces ayuda, otras no. Y seguir usándolas a ciegas es una receta para la frustración.

¿Entonces son inútiles? No, ni mucho menos. Funcionan de maravilla cuando el número de filas es pequeño (menos de 100 suele ser seguro) y cuando las operaciones son simples. Pero si metemos un JOIN, un GROUP BY o empezamos a empujar lógica compleja… mejor sacar la artillería de verdad: tabla temporal.

Tablas temorales vs Variables de tabla: lo que no te dice la documentación

Hablemos claro. Las diferencias no están solo en la sintaxis o el ámbito. Lo importante es cómo se comportan bajo carga, cómo afectan al plan de ejecución y qué tipo de mantenimiento requieren. Para compararlas vamos a ver uno a uno los aspectos más interesantes.

Estadísticas

Las tablas temporales sí generan estadísticas; las variables de tabla no. Esto significa que las temporales permiten planes de ejecución más óptimos en escenarios con muchos datos. Las variables, no.

Soporte de índices

Ambas opciones permiten claves primarias y restricciones únicas. Desde SQL Server 2014 es posible definir índices secundarios en variables de tabla, pero solo dentro de la declaración y con sintaxis limitada. En tablas temporales podemos crear cualquier tipo de índice, incluidos los columnstore, sin restricciones adicionales.

Transacciones

Las variables de tabla no se ven afectadas por ROLLBACK. Si algo falla, su contenido sigue ahí, lo cual puede ser bueno… o un bug encubierto. Las tablas temporales, en cambio, participan en las transacciones como cualquier otra tabla.

Almacenamiento y persistencia

Ambas opciones viven en tempdb, aunque las variables lo hagan de forma menos visible. Pero a nivel físico, no hay magia: no están «en memoria», como algunos aún creen. Eso sí, las temporales suelen dejar más rastro en el sistema de archivos si no se gestionan bien.

Variables-tabla-2

Además, como ya hemos comentado las tablas temporales duran lo que dura la sesión, es decir, mientras no las borremos o cerremos esa sesión seguirán ahí. Las variables tipo tabla, por el contrario duran lo que dura la ejecución del lote (batch).

Variables-tabla-1

Si veis en la imagen, la segunda consulta no encuentra nada. Esto pasa porque después del “GO” ya se considera otro lote.

Paralelismo

Las tablas temporales pueden beneficiarse del paralelismo en las consultas; las variables de tabla, salvo casos contados y versiones muy recientes de SQL Server, no.

Lectura y escritura

En escenarios de alto volumen, las operaciones sobre variables de tabla pueden ser considerablemente más lentas que sobre tablas temporales. Aunque el coste del DECLARE parezca nulo, el impacto acumulado en los planes de ejecución mal optimizados se paga caro.

Casos de uso, ¿Cuándo elegir tablas temporales o variables de tabla?

Una variable de tabla nos viene de perlas cuando queremos devolver una pequeña tabla desde una función, cuando estamos en mitad de un script complejo que necesita guardar una docena de valores intermedios, o cuando buscamos claridad sin sacrificar rendimiento (porque sabemos que los datos son pocos y controlados). Por norma general, no deberíamos usarlas para más de 100 registros.

Una tabla temporal brilla en todo lo demás: cargas intermedias, transformaciones complejas, conjuntos de datos que van a vivir varias etapas, o cuando necesitamos analizar y refinar el rendimiento de una consulta. Incluso para esas subconsultas que usamos varias veces en una misma consulta y pueden llegar a ser pesadas.

También conviene recordar que hay un tercer actor en esta historia: las tablas temporales globales (##TablaGlobal) y las tablas de memoria (MEMORY_OPTIMIZED). Pero eso ya es otro capítulo. O varios.

Conclusión

Elegir entre una tabla temporal y una variable de tabla no debería depender del estado de ánimo, sino del uso que le vamos a dar. Si los datos son escasos, la lógica es sencilla y no necesitamos estadísticas ni índices complejos, la variable funciona. Pero si hay que unirse a otras tablas, mover volumen o exprimir rendimiento, la tabla temporal es la opción profesional.

No olvidemos que el optimizador de SQL Server toma decisiones basadas en lo que sabe. Y con una variable de tabla, lo que sabe es poco. Si le damos una tabla temporal bien definida, con índices y estadísticas, puede hacer su trabajo. Si le damos una caja cerrada con un «ya te apañarás», no esperemos milagros.

Así que, la próxima vez que tengamos que elegir entre DECLARE @Tabla y CREATE TABLE #Temp, pensemos dos veces. Porque sí, ambas pueden almacenar datos. Pero sólo una de ellas está preparada para aguantar una jornada completa sin pedir un café doble a mitad de camino. Y no, no es la variable.

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, 1 comentario
11
Abr
2025

Procedimientos almacenados para auditoría en SQL Server; Cuando el «sálvese quien pueda» no es estrategia

Llevamos años insistiendo en la importancia de tener auditoría en los sistemas. Y no, no hablamos de perseguir al becario por haber hecho un DELETE sin WHERE. Hablamos de una estrategia seria, sostenible y bien diseñada para dejar constancia de qué ocurre dentro de nuestras bases de datos. Y aquí es donde los procedimientos almacenados (sí, esos viejos conocidos de mil batallas) pueden jugar un papel muy digno si sabemos usarlos con cabeza.

Porque seamos honestos: muchos sistemas de auditoría empiezan con buenas intenciones y terminan convertidos en un Frankenstein lleno de triggers, jobs ocultos y tablas que nadie se atreve a truncar. Pero no tiene por qué ser así. Vamos a hablar de cómo diseñar procedimientos almacenados que nos permitan registrar operaciones relevantes, mantener la trazabilidad sin comprometer el rendimiento, y, sobre todo, que no nos obliguen a pedir perdón cada vez que ejecutamos un SELECT contra la tabla de auditoría.

¿Por qué procedimientos almacenados y no triggers o Extended Events?

Antes de lanzarnos a picar código como si no hubiera mañana, pongamos un poco de contexto. Sí, podríamos usar triggers para capturar operaciones DML. Sí, Extended Events son una maravilla para ciertas trazas. Pero aquí venimos a hablar de soluciones controladas, personalizables y que se integren de forma limpia con nuestras operaciones diarias.

Los procedimientos almacenados nos permiten centralizar la lógica de inserción, modificación y eliminación, y al mismo tiempo registrar en una tabla de auditoría todo lo que nos interesa: quién, cuándo, qué y desde dónde. Si el acceso a los datos está canalizado a través de SPs, tenemos el terreno perfecto para auditar sin traumas. ¿Que no todo pasa por SPs? Entonces el problema no es la auditoría, es el diseño general de la aplicación. Y eso es otra guerra.

Diseño de la tabla de auditoría: no empecemos la casa por el tejado

Antes de ponernos a escribir procedimientos, necesitamos tener clara nuestra tabla de auditoría. No, no vale con un INSERTED tirado en una tabla con columnas tipo Campo1, Campo2, y una nota que diga “aquí va lo que sea”. Una buena tabla de auditoría tiene estructura, contexto y propósito. Una estructura mínima (y funcional) podría ser algo así:

CREATE TABLE dbo.Auditoria
(
    IdAuditoria INT IDENTITY(1,1) PRIMARY KEY,
    TablaAfectada SYSNAME NOT NULL,
    Operacion NVARCHAR(10) NOT NULL,
    Usuario NVARCHAR(128) NOT NULL,
    Fecha DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    HostName NVARCHAR(128) NULL,
    Aplicacion NVARCHAR(128) NULL,
    ClaveRegistro NVARCHAR(512) NULL,
    Datos NVARCHAR(MAX) NULL
);

Y aquí hay que hacer una pausa. Porque sí, el campo Datos contiene JSON, pero no es de tipo JSON. ¿Por qué? Porque SQL Server, hasta la versión 2022, no soporta el tipo JSON nativo en entornos on-premises. En Azure SQL Database ya existe, y todo apunta a que SQL Server 2025 lo incorporará. Pero mientras tanto, usamos NVARCHAR(MAX).

Y sí, uso JSON. No, no me he pasado al NoSQL. Pero si queremos registrar los datos afectados sin necesidad de 50 columnas por cada tabla, el JSON nos da flexibilidad. 

No porque nos guste —aunque a muchos sí—, sino porque es lo que hay. Y además, no nos engañemos, el JSON nos da lo que necesitamos: almacenar estructuras flexibles y trabajar con ellas fácilmente gracias a funciones como JSON_VALUE, OPENJSON o ISJSON(). En resumen: guardamos JSON como texto, pero con cabeza.

Procedimientos almacenados con lógica de auditoría: así sí

Ahora que tenemos clara la tabla, vamos a por la lógica. El patrón es simple: cada procedimiento almacenado que realice operaciones sobre los datos deberá incluir una llamada al procedimiento de auditoría, justo después de la operación. Imaginemos un SP de actualización de empleados. Este es un ejemplo limpio y funcional, luego vosotros lo complicáis lo que queráis.

CREATE PROCEDURE dbo.ActualizarEmpleado
    @IdEmpleado INT,
    @Nombre NVARCHAR(100),
    @DepartamentoId INT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Empleados
    SET Nombre = @Nombre,
        DepartamentoId = @DepartamentoId
    WHERE IdEmpleado = @IdEmpleado;

    IF @@ROWCOUNT = 0 RETURN;

    DECLARE @Datos NVARCHAR(MAX);
    DECLARE @Clave NVARCHAR(512);

    SELECT @Datos = (
        SELECT @Nombre AS Nombre, @DepartamentoId AS DepartamentoId
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );

    SET @Clave = CAST(@IdEmpleado AS NVARCHAR);

    EXEC dbo.InsertarAuditoria 
        'Empleados',
        'UPDATE',
        @Clave,
        @Datos;
END

Y el procedimiento InsertarAuditoria, que sigue siendo igual de simple:

CREATE PROCEDURE dbo.InsertarAuditoria
    @Tabla SYSNAME,
    @Operacion NVARCHAR(10),
    @Clave NVARCHAR(512),
    @Datos NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Auditoria (TablaAfectada, Operacion, Usuario, HostName, Aplicacion, ClaveRegistro, Datos)
    SELECT 
        @Tabla,
        @Operacion,
        ORIGINAL_LOGIN(),
        HOST_NAME(),
        APP_NAME(),
        @Clave,
        @Datos;
END

Nada de magia negra, solo lógica clara, controlada y auditable en sí misma.

Cuándo montar auditoría (y cuándo no): porque registrar cada pestañeo no es auditar

No todo merece una entrada en la auditoría. Si registramos cada acceso, cada SELECT o cada ejecución de SP, acabaremos con una tabla de 2 TB que nadie se atreve a consultar y que ralentiza cada INSERT. Hay que auditar cambios de estado relevantes: inserciones, modificaciones, eliminaciones y, dependiendo del negocio, consultas que tengan implicaciones legales o de privacidad. Auditar todo “por si acaso” es tan útil como llevar paraguas en el desierto. Y casi igual de cómodo.

Versionado de datos vs auditoría: primos lejanos

Una confusión habitual es pensar que la auditoría y el versionado son lo mismo. Spoiler: no lo son. Auditar es dejar constancia de qué ocurrió, quién lo hizo y cómo. Versionar es guardar el estado completo anterior de un registro, por si hay que volver atrás o comparar. Podemos combinar ambos enfoques, pero no deberíamos mezclarlos como si fueran equivalentes.

Para el versionado tenemos técnicas distintas como el libro de contabilidad, o incluso estructuras propias si el estándar se nos queda corto. Si lo que queremos es saber quién cambió qué, la auditoría nos basta. Si queremos saber qué valores tenía antes, entonces toca sacar la artillería del versionado.

Auditoría y rendimiento: ese delicado equilibrio

Registrar operaciones añade carga. No importa lo minimalista que sea el SP de auditoría: es una operación más por cada modificación. Si nuestra base de datos gestiona 10.000 cambios por minuto, quizás tengamos que plantear estrategias adicionales, como colas (Service Broker, por ejemplo) o particionado de la tabla de auditoría.

Y no, no vale con poner la tabla en otra base de datos para “que no moleste”. Separar el almacenamiento está bien, pero si la transacción espera a que el log de la otra base escriba… seguimos teniendo cuello de botella.

Diseñar una auditoría eficiente es como diseñar un buen índice: no basta con saber que existe, hay que entender cómo afecta a todo lo demás.

Conclusión

Los procedimientos almacenados siguen siendo una herramienta poderosa para implementar auditorías en SQL Server, especialmente cuando tenemos control sobre cómo accede la aplicación a los datos. Nos permiten registrar cambios con contexto, mantener el rendimiento bajo control y evitar la opacidad de soluciones como triggers masivos o rastreos indiscriminados.

Y sí, usamos JSON. Aunque hoy lo almacenamos como NVARCHAR(MAX), es una decisión consciente, no un apaño. Cuando SQL Server soporte el tipo JSON nativamente, y lo hará porque ya lo hace en Azure, estaremos listos para aprovecharlo sin rehacer nada.

Así que no, no necesitas un monstruo de triggers ni una solución de terceros para saber qué está pasando en tu sistema. A veces, basta con un poco de disciplina, procedimientos bien pensados… y la firme convicción de que auditar no es opcional. Es supervivencia.

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
21
Mar
2025
Funciones de Ventana

Funciones de Ventana

Cuando analizamos datos con SQL Server, a menudo nos encontramos con la necesidad de realizar cálculos complejos que involucren conjuntos de filas relacionados entre sí. Tradicionalmente, recurríamos a subconsultas o a la ya conocida cláusula «GROUP BY». Sin embargo, existe un conjunto de herramientas mucho más potente y elegante para abordar estas situaciones: las funciones de ventana. A lo largo de este artículo, exploraremos en profundidad qué son, cómo funcionan y cómo podemos sacarles el máximo partido en nuestras consultas en SQL Server.

¿Qué son realmente las Funciones de Ventana en SQL Server? 

Las funciones de ventana son un tipo especial de funciones que nos permiten realizar cálculos sobre un conjunto de filas que están relacionadas con la fila actual que estamos procesando. No es nada nuevo, las vimos por primera vez en 1998 en Oracle8i y fueron introducidas como parte del estándar SQL en su versión 3 en el año 2003. En SQL Server las tenemos desde la versión 2005 y posteriormente llegaron a otros sistemas de bases de datos como PostgreSQL (2009), MariaDB (2016) y MySQL (2018).

La clave de su potencia reside en que, a diferencia de las funciones agregadas tradicionales que colapsan múltiples filas en una única fila de salida (como sucede con «GROUP BY»), las funciones de ventana operan dentro de una «ventana» o «marco de ventana» definido por nosotros, devolviendo un valor para cada fila individual.

Imaginemos una tabla de ventas donde queremos calcular el total de ventas acumulado por vendedor a lo largo del tiempo. Sin una función de ventana, tendríamos que recurrir a subconsultas complejas o a cursores, lo que puede resultar ineficiente y difícil de mantener. Con una función de ventana, podemos definir una ventana que incluya todas las ventas del vendedor hasta la fecha actual, calculando el acumulado para cada venta sin perder la información de cada transacción individual.

La sintaxis fundamental para utilizar una función de ventana involucra la cláusula «OVER()». Esta cláusula es la que define la «ventana» sobre la cual la función operará. Dentro de «OVER()», podemos especificar cómo se particionarán los datos y cómo se ordenarán dentro de cada partición.

Sintaxis de las Funciones de Ventana en SQL Server

La estructura básica para emplear una función de ventana es la siguiente:

SELECT
    columna1,
    columna2,
    funcion_ventana (argumento) OVER (
        [PARTITION BY lista_de_columnas]
        [ORDER BY lista_de_columnas [ASC | DESC]]
        [ROWS o RANGE especificación_de_marco]
    ) AS alias_de_columna
FROM
    tabla;

Analicemos cada uno de los componentes esenciales:

  • funcion_ventana: Aquí especificamos la función de ventana que queremos aplicar. Puede ser una función de agregación (como «SUM()», «AVG()», «MIN()», «MAX()», «COUNT()»), una función de ranking («ROW_NUMBER()», «RANK()», «DENSE_RANK()», «NTILE()»), o una función de valor («LAG()», «LEAD()», «FIRST_VALUE()», «LAST_VALUE()»). El argumento dependerá de la función específica.
  • OVER(): Esta cláusula es obligatoria para indicar que estamos utilizando una función de ventana. Es dentro de sus paréntesis donde definimos el contexto de la ventana.
  • PARTITION BY lista_de_columnas (Opcional): La cláusula «PARTITION BY» divide el conjunto de resultados en particiones basadas en los valores de las columnas especificadas. La función de ventana se aplicará de forma independiente a cada una de estas particiones. Si omitimos «PARTITION BY», la función se aplicará a toda la tabla como una única partición.
  • ORDER BY lista_de_columnas [ASC | DESC] (Opcional): Dentro de cada partición (o en toda la tabla si no hay «PARTITION BY»), la cláusula «ORDER BY» define el orden lógico de las filas. Este orden es crucial para muchas funciones de ventana, especialmente las de ranking y las que trabajan con valores de filas precedentes o siguientes. Si se omite, el orden de las filas dentro de la partición será arbitrario.
  • ROWS o RANGE especificación_de_marco (Opcional): Esta cláusula nos permite definir aún más el marco de la ventana dentro de cada partición. Podemos especificar un conjunto de filas contiguas que se incluirán en el cálculo de la función para la fila actual. Las opciones más comunes incluyen:
    •  «ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW»: Incluye todas las filas desde el inicio de la partición hasta la fila actual.
    • «ROWS BETWEEN n PRECEDING AND CURRENT ROW»: Incluye las «n» filas anteriores a la fila actual y la fila actual.
    • “ROWS BETWEEN CURRENT ROW AND n FOLLOWING»: Incluye la fila actual y las «n» filas siguientes.
    • “ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING»: Incluye todas las filas de la partición.
    • «RANGE» funciona de manera similar a «ROWS», pero en lugar de un número fijo de filas, define el marco basándose en los valores de las columnas especificadas en «ORDER BY».

Tipos de Funciones de Ventana en SQL Server

Las funciones de ventana se pueden clasificar en varios tipos, cada uno diseñado para abordar necesidades específicas de análisis:

Funciones de Agregación

Podemos utilizar las funciones de agregación que ya conocemos (SUM(),AVG(), MIN(), MAX() o COUNT()) como funciones de ventana al incluirlas con la cláusula «OVER()». La diferencia fundamental con su uso tradicional con «GROUP BY» es que, al emplearlas como funciones de ventana, no perdemos la granularidad de las filas individuales.

Por ejemplo, para obtener el total de ventas por ciudad y a la vez visualizar el importe de cada pedido individual junto con el total de su ciudad, podríamos escribir algo como:

SELECT
    order_id,
    order_date,
    customer_name,
    city,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY city) AS total_ventas_ciudad
FROM
    Orders;

Aquí, «SUM(order_amount) OVER (PARTITION BY city)» calcula la suma de «order_amount» para todas las filas que comparten el mismo valor en la columna «city», y este total se muestra en cada fila correspondiente a esa ciudad.

Funciones de Ranking 

Las funciones de ranking nos permiten asignar una posición o rango a cada fila dentro de una partición según un criterio de ordenación específico. SQL Server nos ofrece las siguientes funciones de ranking:

  • ROW_NUMBER(): Asigna un número secuencial único a cada fila dentro de una partición, comenzando en 1. Si hay filas con los mismos valores en la columna de ordenación, se les asignarán números diferentes según el orden en que se procesen.
  • RANK(): Asigna un rango a cada fila dentro de una partición basado en el orden de las columnas especificadas en «ORDER BY». Si dos o más filas tienen el mismo valor, recibirán el mismo rango, y el siguiente rango se saltará. Por ejemplo: 1, 2, 2, 4…
  • DENSE_RANK(): Similar a «RANK()», asigna rangos basados en el orden, pero no se salta ningún rango en caso de empate. Por ejemplo: 1, 2, 2, 3, 4…
  • NTILE(n): Divide las filas dentro de una partición en «n» grupos (aproximadamente) iguales y asigna un número de grupo (desde 1 hasta «n») a cada fila. Es útil para identificar percentiles, cuartiles, etc..

Funciones de Valor 

Las funciones de valor nos permiten acceder a valores de otras filas dentro de la misma partición (o en toda la tabla) sin necesidad de realizar joins o subconsultas. Las más utilizadas son:

  • LAG(columna, n, valor_predeterminado): Accede al valor de la «columna» en la fila que está «n» filas antes de la fila actual dentro de la partición (ordenada por «ORDER BY»). Si no existe una fila anterior en la distancia especificada, devuelve el «valor_predeterminado» (si se proporciona, sino devuelve «NULL»).
  • LEAD(columna, n, valor_predeterminado): Accede al valor de la «columna» en la fila que está «n» filas después de la fila actual dentro de la partición (ordenada por «ORDER BY»). Similar a «LAG()», permite especificar un «valor_predeterminado» si no existe una fila posterior.
  • FIRST_VALUE(columna): Devuelve el valor de la «columna» de la primera fila dentro de la partición (ordenada por «ORDER BY»).
  • LAST_VALUE(columna): Devuelve el valor de la «columna» de la última fila dentro de la partición (ordenada por «ORDER BY»). Es importante tener en cuenta que, por defecto, el marco de la ventana para «LAST_VALUE()» va desde el inicio de la partición hasta la fila actual, por lo que a menudo se utiliza con una especificación de marco como «ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING» para obtener el verdadero último valor de la partición.

Cláusulas avanzadas en Funciones de Ventana

Como mencionaba anteriormente, las cláusulas «ROWS» y «RANGE» nos permiten refinar la definición del marco de la ventana. «ROWS» define el marco en términos de un número fijo de filas precedentes o siguientes a la fila actual. «RANGE», por otro lado, define el marco basado en los valores de la columna de ordenación.

Por ejemplo, para calcular una media móvil de ventas de los últimos tres meses (incluyendo el mes actual), podríamos utilizar:

SELECT
    mes,
    ventas,
    AVG(ventas) OVER (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media_movil_3_meses
FROM
    VentasMensuales;

Aquí, «ROWS BETWEEN 2 PRECEDING AND CURRENT ROW» define una ventana que incluye la fila actual y las dos filas anteriores según el orden de la columna «mes».

El poder de «PARTITION BY» y «ORDER BY» juntos en Funciones de Ventana 

La combinación de «PARTITION BY» y «ORDER BY» dentro de la cláusula «OVER()» es donde realmente brilla el potencial de las funciones de ventana. «PARTITION BY» nos permite dividir los datos en grupos lógicos, mientras que «ORDER BY» establece un orden significativo dentro de cada uno de estos grupos.

Consideremos el ejemplo de calcular el ranking de productos más vendidos dentro de cada categoría:

SELECT
    categoria,
    producto,
    ventas,
    RANK() OVER (PARTITION BY categoria ORDER BY ventas DESC) AS ranking_ventas
FROM
    VentasProductos;

En este caso, los productos se particionan por «categoria», y dentro de cada categoría, se ordenan por «ventas» de forma descendente. La función «RANK()» asignará un ranking a cada producto dentro de su respectiva categoría.

Usando la Cláusula «WINDOW» para simplificar consultas complejas en SQL Server

A partir de SQL Server 2022 (con un nivel de compatibilidad de base de datos 160 o superior), se introduce la cláusula «WINDOW». Esta cláusula nos permite definir especificaciones de ventana con nombre que pueden ser referenciadas por múltiples funciones de ventana dentro de una misma consulta. Esto mejora significativamente la legibilidad y el mantenimiento de consultas complejas que utilizan las mismas definiciones de ventana varias veces. La sintaxis básica de la cláusula «WINDOW» es:

WINDOW nombre_ventana AS (
    [PARTITION BY lista_de_columnas]
    [ORDER BY lista_de_columnas [ASC | DESC]]
    [ROWS o RANGE especificación_de_marco]
);

Una vez definida la ventana con nombre, podemos referenciarla en la cláusula «OVER()» de nuestras funciones de ventana:

SELECT
    ...,
    SUM(ventas) OVER nombre_ventana AS total_ventas,
    AVG(precio) OVER nombre_ventana AS precio_medio
FROM
    TablaVentas
WINDOW nombre_ventana AS (PARTITION BY categoria ORDER BY fecha_venta);

Esto resulta especialmente útil cuando tenemos varias funciones de ventana que comparten la misma lógica de partición y ordenación.

Conclusión

Las funciones de ventana representan una herramienta fundamental en el arsenal de cualquier experto en SQL Server. Nos brindan la capacidad de realizar análisis sofisticados sobre conjuntos de datos relacionados sin sacrificar la información a nivel de fila, abriendo un abanico de posibilidades para calcular totales acumulados, medias móviles, rankings dinámicos, y comparar valores entre filas.

Dominar la sintaxis de la cláusula «OVER()», comprender los diferentes tipos de funciones de ventana (agregación, ranking, valor), y saber cómo utilizar las cláusulas «PARTITION BY», «ORDER BY», «ROWS», y «RANGE» nos permitirá escribir consultas más eficientes, legibles y potentes. La introducción de la cláusula «WINDOW» en versiones recientes de SQL Server simplifica aún más la gestión de consultas complejas con múltiples definiciones de ventana.

Os animamos a explorar y practicar con estas funciones en vuestros proyectos. El potencial analítico que desbloquean las funciones de ventana en SQL Server es enorme y, sin duda, os permitirá llevar vuestras habilidades de análisis de datos al siguiente nivel.

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
19
Mar
2025

Tarea de Limpieza Fantasma en SQL Server

En nuestro día a día como profesionales de SQL Server, nos enfrentamos a una multitud de procesos internos que trabajan silenciosamente para mantener nuestras bases de datos saludables y con un rendimiento óptimo. Uno de estos procesos, a menudo menos visible pero de vital importancia, es la tarea de limpieza fantasma. En este artículo, vamos a sumergirnos en las profundidades de este mecanismo, explorando su funcionamiento interno, su necesidad y las consideraciones clave para su gestión.

¿Qué son los Registros Fantasma y por qué aparecen en SQL Server?

Cuando eliminamos registros de una tabla que tiene un índice (ya sea clúster o no clúster), SQL Server no los borra físicamente de inmediato de las páginas de datos. En su lugar, el registro se marca internamente como «para ser eliminado», un estado que conocemos como registro fantasma «ghosted». Imaginemos una biblioteca donde, en lugar de retirar un libro inmediatamente de la estantería, simplemente le ponemos una etiqueta que dice «para retirar». El libro sigue allí, ocupando espacio, pero marcado para una acción posterior.

Esta técnica de «eliminación lógica» ofrece varias ventajas significativas en términos de rendimiento durante las operaciones de borrado. Primero, la operación de eliminación en sí misma se vuelve mucho más rápida, ya que solo implica cambiar un bit en la cabecera del registro en lugar de una manipulación física de los datos. Segundo, las operaciones de reversión (rollback) también se optimizan, ya que simplemente necesitamos desmarcar los registros como «para eliminar» en lugar de tener que reinsertar los datos borrados.

Además de la optimización del rendimiento en las operaciones DML, los registros fantasma son esenciales para otras funcionalidades importantes de SQL Server. Son un componente clave para el bloqueo a nivel de fila, permitiendo que diferentes transacciones operen concurrentemente en distintas filas de la misma página sin interferir entre sí. También son fundamentales para el aislamiento de instantánea, donde necesitamos mantener versiones anteriores de las filas para garantizar una vista consistente de los datos para las transacciones en curso.

El Proceso de Limpieza Fantasma

Una vez que una eliminación se ha confirmado (commit), entra la tarea de limpieza fantasma. Este es un proceso en segundo plano, monohilo, que se encarga de eliminar físicamente los registros que han sido marcados como fantasmas. Podemos pensar en este proceso como el personal de la biblioteca que, periódicamente, revisa los libros con la etiqueta «para retirar» y los saca físicamente de las estanterías, liberando espacio.

La tarea de limpieza fantasma se ejecuta automáticamente en intervalos regulares: cada 5 segundos para SQL Server 2012 y versiones posteriores, y cada 10 segundos para SQL Server 2008 y 2008 R2. En cada activación, el proceso verifica si alguna base de datos ha sido marcada como que contiene registros fantasma. Si encuentra alguna, escanea las páginas PFS de esa base de datos en busca de páginas que contengan registros fantasma. Al encontrarlas, procede a eliminar físicamente los registros marcados, con un límite de 10 páginas procesadas por cada ejecución. Esta limitación asegura que el proceso de limpieza no consuma excesivos recursos del sistema en un solo ciclo.

Es importante destacar que la tarea de limpieza fantasma opera a nivel de base de datos. Cuando una página contiene registros fantasma, la base de datos a la que pertenece se marca internamente. El proceso de limpieza solo escaneará aquellas bases de datos que estén marcadas de esta manera. Una vez que todos los registros fantasma de una base de datos han sido eliminados, la base de datos se marca como «sin registros fantasma», y el proceso la omitirá en sus siguientes ejecuciones. Además, si la tarea de limpieza no puede obtener un bloqueo compartido en una base de datos (por ejemplo, si otra operación está utilizando la base de datos de forma exclusiva), la omitirá y volverá a intentarlo en su próxima ejecución.

¿Por qué son importantes los Registros Fantasma?

Como mencionaba anteriormente, la existencia de los registros fantasma no es arbitraria; responde a necesidades fundamentales del motor de base de datos. La optimización del rendimiento de las operaciones de eliminación es una de las principales razones. Al realizar una eliminación lógica inicial, SQL Server minimiza la sobrecarga inmediata, permitiendo que las transacciones se completen más rápidamente. Esto es crucial en sistemas con una alta tasa de operaciones DML.

La necesidad de bloqueo a nivel de fila también juega un papel crucial. Los registros fantasma permiten que el motor de base de datos mantenga la coherencia y el aislamiento entre transacciones concurrentes que podrían estar interactuando con la misma página de datos.

Finalmente, en entornos donde el aislamiento de instantánea está habilitado, los registros fantasma son esenciales para mantener las versiones anteriores de las filas. Esto garantiza que las transacciones que se iniciaron antes de la operación de eliminación puedan seguir viendo una imagen consistente de los datos en el momento en que se iniciaron.

Monitorizando la actividad de Limpieza Fantasma y la presencia de Registros Fantasma

Aunque la tarea de limpieza fantasma opera en segundo plano, es posible monitorizar su actividad y la presencia de registros fantasma en nuestras bases de datos. Podemos utilizar la DMV `sys.dm_exec_requests` para identificar si el proceso de limpieza fantasma está en ejecución, buscando peticiones con un comando similar a ‘%ghost%’.

Para determinar cuántos registros fantasma existen en una base de datos específica, podemos utilizar la DMV `sys.dm_db_index_physical_stats`. La siguiente consulta nos proporciona un resumen del número total de registros fantasma por base de datos:

SELECT sum(ghost_record_count) total_ghost_records, db_name(database_id)
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED')
group by database_id
order by total_ghost_records desc;

Esta información puede ser valiosa para identificar bases de datos con una acumulación significativa de registros fantasma, lo que podría indicar una alta actividad de eliminación o posibles problemas con el proceso de limpieza.

Deshabilitar la Limpieza Fantasma: ¿Cuándo y cuáles son las consecuencias?

En sistemas con una carga de trabajo muy alta de eliminaciones, se puede deshabilitar la tarea de limpieza fantasma utilizando el trace flag 661. La justificación detrás de esto es que, en algunos escenarios extremos, el proceso de limpieza podría no ser capaz de mantenerse al día con el ritmo de las eliminaciones, lo que podría llevar a problemas de rendimiento al mantener páginas en el buffer pool y generar E/S.

Sin embargo, es crucial entender las graves implicaciones de deshabilitar la limpieza fantasma. Al hacerlo, los registros marcados para eliminación permanecerán en las páginas indefinidamente, impidiendo que SQL Server reutilice ese espacio. Esto puede llevar a un crecimiento innecesario del tamaño de la base de datos («bloated database files») y a problemas de rendimiento. La falta de reutilización de espacio obliga a SQL Server a agregar datos a páginas nuevas, lo que puede aumentar la frecuencia de divisiones de página («page splits»). Las divisiones de página son operaciones costosas que pueden afectar negativamente el rendimiento de las consultas y la creación de planes de ejecución.

Compensar la no limpieza automática

Si decidimos deshabilitar la tarea de limpieza fantasma (una acción generalmente no recomendada y que debe probarse exhaustivamente en un entorno controlado antes de implementarse en producción), necesitaremos tomar acciones alternativas para eliminar los registros fantasma y reclamar el espacio. Algunas opciones son:

  • Reconstrucción de índices: Esta operación mueve los datos alrededor de las páginas, eliminando los registros fantasma en el proceso.
  • Ejecución manual de `sp_clean_db_free_space`: Este procedimiento almacenado limpia los registros fantasma de todas las páginas de datos de una base de datos.
  • Ejecución manual de `sp_clean_db_file_free_space`: Similar al anterior, pero permite limpiar los registros fantasma de un archivo de datos específico.

Es fundamental recordar que deshabilitar la tarea de limpieza fantasma sin implementar una estrategia de limpieza alternativa conducirá inevitablemente a problemas de rendimiento y un crecimiento descontrolado de la base de datos.

Registros Fantasma en Heaps

Es interesante notar que, durante el procesamiento normal, los registros fantasma no ocurren en las tablas HEAP. La razón principal es que estas tablas no tienen una estructura de índice que requiera el mantenimiento de eliminaciones lógicas para la coherencia de la estructura. Cuando se elimina un registro de una tabla HEAP, se elimina físicamente de la página.

Sin embargo, existe una excepción importante a esta regla: cuando el aislamiento de instantánea está habilitado, las eliminaciones de un HEAP sí generan registros fantasma como parte del proceso general de control de versiones. Esto puede tener efectos secundarios interesantes. Por ejemplo, un registro con control de versiones tiene una sobrecarga adicional de 14 bytes. Si un registro de un HEAP se convierte repentinamente en un registro con control de versiones debido a una eliminación bajo aislamiento de instantánea, su tamaño puede aumentar en 14 bytes, lo que podría hacer que ya no quepa en la página actual. Esto, en casos donde la página está llena, podría llevar a que el registro se mueva, resultando en un par de registros de reenvío/reenviado, solo por haber sido eliminado.

La Limpieza Fantasma y su impacto en el Log de transacciones

Las operaciones relacionadas con los registros fantasma también se reflejan en el registro de transacciones. Cuando se elimina un registro de una página de índice, la operación se registra con un contexto de «ghosting» (LCX_MARK_AS_GHOST). Además, la modificación de la página PFS para indicar la presencia de registros fantasma también se registra.

Cuando la tarea de limpieza fantasma se activa y elimina físicamente los registros fantasma, esta acción también se registra en el log de transacciones como una operación de LOP_EXPUNGE_ROWS. Podemos observar esta actividad en el log de transacciones utilizando la función fn_dblog().

Consideraciones de rendimiento

En sistemas con un volumen extremadamente alto de operaciones de eliminación, es posible que la tarea de limpieza fantasma no pueda mantenerse al día con el ritmo de generación de registros fantasma. Dado que es un proceso monohilo, en servidores con muchos núcleos y una alta concurrencia de eliminaciones, un solo hilo dedicado a la limpieza podría convertirse en un cuello de botella.

Cuando la tarea de limpieza fantasma se retrasa significativamente, puede generar problemas de rendimiento al mantener páginas con registros eliminados en el buffer pool, consumiendo memoria que podría ser utilizada por datos activos. También puede generar operaciones de E/S al acceder a estas páginas para realizar la limpieza. En algunos casos, esto puede llevar a un aumento en el uso de CPU y a una degradación general del rendimiento del sistema.

Alternativas en escenarios de alta carga

Ante escenarios donde la tarea de limpieza fantasma por defecto no es suficiente, existen algunas alternativas a considerar (siempre con precaución y una comprensión clara de sus implicaciones):

  • Deshabilitar temporalmente la limpieza fantasma y realizar mantenimiento de índices de forma más agresiva: Como decía antes, esto debe hacerse con extremo cuidado y requiere una estrategia clara para reclamar el espacio.
  • Utilizar DBCC FORCEGHOSTCLEANUP: Este comando no documentado fuerza la limpieza de todos los registros fantasma de una base de datos. Aunque puede ser útil en situaciones de emergencia para reclamar espacio rápidamente, su naturaleza no documentada implica que su comportamiento y posibles efectos secundarios no están garantizados y podrían cambiar en futuras versiones de SQL Server. Su uso debe ser evaluado y probado cuidadosamente en entornos no productivos. Yo personalmente no lo recomiendo.
  • Ajustar el diseño de la base de datos y los patrones de acceso: En algunos casos, repensar la forma en que se realizan las eliminaciones o la estructura de los índices podría ayudar a mitigar la acumulación de registros fantasma. Por ejemplo, en tablas con altas tasas de eliminación e inserción en las mismas áreas del índice, podría ser beneficioso considerar estrategias de particionamiento.
  • Forzar la activación de la limpieza fantasma: Se dice, aunque no es una solución oficial, que realizar un escaneo completo de un índice (por ejemplo, con un SELECT COUNT(*)) puede forzar a que las páginas con registros fantasma se añadan a la cola de la tarea de limpieza para su posterior procesamiento.

Conclusión

La tarea de limpieza fantasma es un componente esencial del motor de base de datos de SQL Server que trabaja discretamente para asegurar que el espacio ocupado por los registros eliminados se recupere y que el rendimiento general no se vea afectado negativamente. Aunque en situaciones excepcionales se puede considerar su deshabilitación, las implicaciones de hacerlo son considerables y hay que probarlo muy bien.

Entender cómo funcionan los registros fantasma y el proceso de limpieza nos permite diagnosticar mejor posibles problemas de rendimiento relacionados con una alta actividad de eliminación y tomar decisiones más informadas sobre la gestión de nuestras bases de datos. Como administradores de SQL Server, debemos ser conscientes de este proceso silencioso pero crucial, asegurando que nuestras bases de datos no se conviertan en un cementerio de «fantasmas» olvidados.

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
17
Mar
2025

Crecimiento del Persistent Version Store (PVS) en SQL Server

En el día a día de la administración de bases de datos SQL Server, nos encontramos con diversas funcionalidades que buscan optimizar el rendimiento y la disponibilidad de nuestros sistemas. Una de ellas, la Recuperación Acelerada de Bases de Datos (ADR), introdujo un concepto fundamental para la gestión de transacciones y la recuperación ante fallos: el Persistent Version Store (PVS). Aunque el ADR nos brinda notables ventajas en cuanto a la rapidez de las reversiones de transacciones y la disponibilidad de la base de datos, su componente central, el PVS, puede convertirse en una fuente de preocupación si no comprendemos su funcionamiento y cómo controlar su tamaño. En este artículo, profundizaremos en qué consiste el PVS, cómo impacta en el espacio de almacenamiento de nuestras bases de datos y, lo más importante, qué estrategias podemos implementar para mantenerlo bajo control. 

¿Qué es el Persistent Version Store (PVS) de SQL Server y cómo funciona?

El Persistent Version Store (PVS) es un mecanismo introducido con la característica de Accelerated Database Recovery (ADR) en SQL Server. Su principal función es almacenar las versiones antiguas de las filas que han sido modificadas por transacciones aún activas o recientemente completadas. Esta estrategia difiere significativamente del comportamiento tradicional de SQL Server sin ADR, donde los valores antiguos se guardan en el registro de transacciones. Con ADR, al modificar una fila, SQL Server escribe una nueva versión de dicha fila en la misma tabla, manteniendo la versión anterior intacta en el PVS. Esta arquitectura permite que las reversiones de transacciones sean prácticamente instantáneas, ya que el motor no necesita recuperar y aplicar información desde el registro de transacciones.

Es crucial entender que el PVS reside dentro de la propia base de datos de usuario, específicamente en los mismos archivos de datos (.mdf). Esta ubicación contrasta con el almacén de versiones utilizado por el Read Committed Snapshot Isolation (RCSI) cuando ADR no está habilitado, el cual se crea y mantiene en la base de datos del sistema TempDB. Aunque ambos mecanismos se basan en el versionado de filas para ofrecer lecturas consistentes sin bloqueos, la persistencia del PVS dentro de la base de datos de usuario tiene implicaciones directas en su tamaño y gestión.

¿Cómo influye el PVS en el tamaño de nuestra base de datos?

La implementación del PVS tiene un impacto directo en el consumo de espacio de nuestras bases de datos. Al almacenar múltiples versiones de las filas modificadas, es inevitable que el tamaño de la base de datos en disco aumente para albergar estas versiones. De hecho, al habilitar ADR, incluso antes de realizar modificaciones, se observa un aumento inicial en el tamaño de la tabla en comparación con una tabla idéntica sin ADR. Esto se debe a que ADR necesita añadir una marca de tiempo a cada fila para rastrear sus versiones.

Además del espacio ocupado por las versiones de las filas en sí, cada fila de la tabla con ADR habilitado contiene un puntero de 14 bytes que apunta a la ubicación de su versión en el PVS, incluso si la fila no ha sido modificada recientemente. Este overhead por fila es un factor significativo que contribuye al aumento del tamaño de la base de datos. Es importante señalar que este mismo puntero existe incluso cuando solo RCSI está habilitado (sin ADR), aunque en ese caso apunte al almacén de versiones ubicado en TempDB. Por lo tanto, el crecimiento en la tabla de usuario debido a este overhead es similar tanto con ADR como con RCSI. Hablamos de esto aquí.

Los experimentos han demostrado que, tras la carga inicial de datos en tablas con ADR y/o RCSI activados, estas tienden a ser más grandes que las tablas sin estas funcionalidades. Este crecimiento se acelera considerablemente al realizar actividad de escritura, llegando incluso a duplicarse el tamaño de los objetos tras la primera actualización en bases de datos con ADR y/o RCSI habilitados. Esta tendencia se mantiene con rondas sucesivas de actualizaciones, donde las bases de datos con versionado de filas experimentan un crecimiento mucho más rápido que aquellas sin él.

El PVS y el mito de la reconstrucción de índices para ahorrar espacio

Ante este crecimiento acelerado de las tablas con ADR y RCSI, una reacción común podría ser recurrir a la reconstrucción de índices como una solución para recuperar el espacio aparentemente «perdido». Efectivamente, al reconstruir los índices en estas tablas, se observa una reducción drástica en su tamaño, igualando incluso el tamaño de tablas sin ADR. Esto podría generar la ilusión de haber «ahorrado» espacio en disco.

Sin embargo, esta ganancia de espacio es puramente ilusoria y temporal. Tan pronto como la carga de trabajo habitual se reanuda y se realizan nuevas actualizaciones, el tamaño de las tablas con ADR y RCSI vuelve a inflarse rápidamente. Nos encontramos, por lo tanto, en un ciclo continuo de crecimiento y reconstrucción sin abordar la causa fundamental del aumento de tamaño: el versionado de filas necesario para el funcionamiento de ADR y RCSI.

La reconstrucción de índices simplemente reorganiza los datos y elimina las versiones antiguas que ya no son necesarias en el momento de la reconstrucción, pero no impide la generación de nuevas versiones con futuras modificaciones. Por lo tanto, si nuestro principal objetivo al reconstruir índices en un entorno con ADR o RCSI es ganar espacio en disco, debemos comprender que este ahorro será efímero. El espacio «ahorrado» volverá a ser necesario a medida que se generen nuevas versiones de las filas. En lugar de centrarnos en la reconstrucción como una panacea para el espacio, debemos enfocarnos en dimensionar adecuadamente nuestro almacenamiento y comprender las implicaciones del versionado de filas en el crecimiento de nuestras bases de datos.

Estrategias efectivas para controlar el tamaño del PVS

Dado que la reconstrucción de índices no es una solución sostenible para controlar el tamaño del PVS, ¿qué alternativas tenemos a nuestra disposición? La clave reside en comprender la naturaleza del PVS y cómo interactúa con la actividad de nuestra base de datos.

En primer lugar, es fundamental realizar un dimensionamiento adecuado del almacenamiento. Si habilitamos ADR, debemos ser conscientes del potencial crecimiento adicional que experimentará nuestra base de datos debido al almacenamiento de las versiones de filas y al overhead por fila. Ignorar este aspecto puede llevarnos rápidamente a situaciones de falta de espacio en disco.

En segundo lugar, la monitorización activa del tamaño del PVS es esencial. SQL Server nos proporciona herramientas para observar el comportamiento del PVS y detectar patrones de crecimiento inusuales. Mediante el uso de Dynamic Management Views (DMVs), como sys.dm_tran_persistent_version_store_stats, podemos obtener información valiosa sobre el tamaño actual del PVS, el porcentaje que representa del tamaño total de la base de datos, el número de transacciones abortadas y la antigüedad de las transacciones activas.

Además de la monitorización del tamaño, es importante analizar la actividad de la base de datos. Cargas de trabajo con una alta intensidad de escritura generarán más versiones de filas y, por lo tanto, un mayor crecimiento del PVS. Asimismo, las transacciones de larga duración pueden impedir la limpieza de las versiones antiguas, contribuyendo a un aumento sostenido del tamaño del PVS. Identificar y optimizar estas transacciones puede tener un impacto significativo en la gestión del espacio del PVS.

Monitorizando el Persistent Version Store

Terminar u optimizar las transacciones de larga duración se convierte en una práctica crucial. Si una transacción permanece abierta durante un tiempo prolongado, las versiones de las filas modificadas tanto por esa transacción como por todas las transacciones siguientes no podrán ser limpiadas del PVS, lo que provocará su crecimiento descontrolado.

Como mencionaba anteriormente, las DMVs son nuestras principales aliadas para supervisar el PVS. La DMV sys.dm_tran_persistent_version_store_stats nos ofrece una visión detallada del estado del almacén de versiones persistente. Algunas de las columnas más relevantes que podemos consultar son:

  • persistent_version_store_size_kb: Indica el tamaño actual del PVS en kilobytes.
  • oldest_active_transaction_id: El ID de la transacción activa más antigua.
  • oldest_transaction_begin_time: La hora de inicio de la transacción activa más antigua.

Podemos utilizar la siguiente consulta (de la documentación oficial) para obtener una visión general del PVS en nuestras bases de datos habilitadas para ADR:

SELECT pvss.database_id,
       DB_NAME(pvss.database_id) AS database_name,
       pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
       100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_percent_of_database_size,
       df.total_db_size_kb / 1024. / 1024 AS total_db_size_gb,
       pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
       pvss.current_aborted_transaction_count,
       pvss.aborted_version_cleaner_start_time,
       pvss.aborted_version_cleaner_end_time,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       dt.database_transaction_begin_time AS oldest_transaction_begin_time,
       asdt.session_id AS active_transaction_session_id,
       asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
       pvss.pvs_off_row_page_skipped_low_water_mark,
       pvss.pvs_off_row_page_skipped_min_useful_xts,
       pvss.pvs_off_row_page_skipped_oldest_aborted_xdesid
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (
            SELECT SUM(size * 8.) AS total_db_size_kb
            FROM sys.master_files AS mf
            WHERE mf.database_id = pvss.database_id
                  AND
                  mf.state = 0
                  AND
                  mf.type = 0
            ) AS df
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

Esta consulta nos proporciona información crucial para entender el consumo de espacio del PVS y la antigüedad de las transacciones activas, lo que nos puede ayudar a identificar posibles cuellos de botella o transacciones problemáticas que estén impidiendo la limpieza del almacén de versiones.

También es útil esta otra consulta (misma fuente) para obtener las consultas que pueden ser un potencial problema. Está parametrizada por defecto para localizar las transacciones de más de 15 minutos de duración o 1 Gb de log de transacciones, lo que ya empieza a ser preocupante y puede tener un impacto en el tamaño del PVS.

DECLARE @LongTxThreshold int = 900;  /* The number of seconds to use as a duration threshold for long-running transactions */
DECLARE @LongTransactionLogBytes bigint = 1073741824; /* The number of bytes to use as the generated log threshold for long-running transactions */

SELECT  dbtr.database_id,
        DB_NAME(dbtr.database_id) AS database_name,
        st.session_id,
        st.transaction_id,
        atr.name,
        sess.login_time,
        dbtr.database_transaction_log_bytes_used,
        CASE WHEN GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time) THEN 'DurationThresholdExceeded'
             WHEN dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes THEN 'LogThresholdExceeded'
             ELSE 'Unknown'
        END
        AS reason
FROM sys.dm_tran_active_transactions AS tr
INNER JOIN sys.dm_tran_session_transactions AS st
ON tr.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS sess
ON st.session_id = sess.session_id
INNER JOIN sys.dm_tran_database_transactions AS dbtr
ON tr.transaction_id = dbtr.transaction_id
INNER JOIN sys.dm_tran_active_transactions AS atr
ON atr.transaction_id = st.transaction_id
WHERE GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
      OR dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

Conclusión

La introducción del Persistent Version Store (PVS) con la Recuperación Acelerada de Bases de Datos (ADR) representa un avance significativo en la forma en que SQL Server gestiona las transacciones y la recuperación. Sin embargo, como hemos explorado, su funcionamiento basado en el versionado de filas tiene implicaciones directas en el tamaño de nuestras bases de datos. La idea de que la reconstrucción de índices sea una solución efectiva para controlar este tamaño ha demostrado ser una ilusión temporal.

En lugar de recurrir a prácticas de mantenimiento obsoletas, debemos adoptar un enfoque más informado y proactivo. Esto implica dimensionar adecuadamente nuestro almacenamiento, monitorizar activamente el tamaño del PVS mediante las DMVs proporcionadas por SQL Server y, fundamentalmente, comprender y optimizar la actividad de nuestras bases de datos, prestando especial atención a las transacciones de larga duración.

En definitiva, la gestión exitosa del PVS requiere que evolucionemos nuestras rutinas de mantenimiento y nos centremos en comprender el mecanismo subyacente del versionado de filas. Solo así podremos tomar decisiones informadas y evitar invertir tiempo y recursos en acciones que nos ofrecen solo una sensación temporal de mejora, asegurando un rendimiento óptimo y una gestión eficiente del espacio en nuestras bases de datos SQL Server.

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

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios