indices

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.

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).

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!

Abr

2025

Orden de los registros en SQL Server cuando no usamos ORDER BY

Cuando trabajamos con SQL Server, ya seamos DBAs, analistas o desarrolladores, a menudo nos topamos con una situación que puede parecer trivial, pero que esconde una trampa para quienes no conocen el funcionamiento interno del motor de SQL Server: ¿cuál es el orden de los resultados cuando no se especifica explícitamente un ORDER BY? El otro día me lo preguntó un compañero y quería también compartirlo con vosotros. La respuesta, aunque sencilla, tiene implicaciones importantes tanto para la lógica de negocio como para el rendimiento y la consistencia de nuestras aplicaciones.

El mito del orden “natural” en SQL Server

Todos hemos oído alguna vez frases como “esta tabla siempre me devuelve los datos en orden de inserción” o “los resultados salen ordenados por la clave primaria aunque no lo indique”. Y aunque en muchas ocasiones estas afirmaciones parecen cumplirse, lo cierto es que confiar en un orden implícito es una práctica peligrosa que puede romperse en cualquier momento. SQL Server, por diseño, no garantiza ningún tipo de orden si no se especifica expresamente una cláusula ORDER BY.

El optimizador y la aleatoriedad controlada

Esto pasa porque el motor de SQL Server tiene como objetivo principal devolver el resultado correcto de la consulta en el menor tiempo posible. Esto implica que, cuando no hay una indicación explícita de orden, el optimizador tiene plena libertad para usar el plan de ejecución que considere más eficiente, incluso si eso implica devolver los datos en un orden distinto cada vez que se ejecuta la misma consulta.

La forma en que SQL Server accede a los datos —ya sea mediante un table scan, index scan, index seek, lookup o incluso los hash match— influye directamente en el orden en el que los registros son devueltos. Y como estos planes pueden variar en función de las estadísticas, la carga del sistema o incluso la edición de SQL Server, el orden final de los resultados es impredecible.

El rol de los índices: ¿orden oculto?

Es cierto que muchas veces el orden “parece” coincidir con el de un índice, en especial cuando se usa un index scan. Por ejemplo, si tenemos una tabla con una clave primaria basada en un índice clustered, es habitual que un SELECT * FROM Tabla sin ORDER BY devuelva los datos según ese índice clustered. Pero esto no es una garantía, ni una promesa del motor.

Un cambio en el plan de ejecución, una actualización estadística, o una simple alteración en el número de registros puede hacer que SQL Server decida usar otro índice, o incluso hacer un table scan, y romper ese orden «natural».

Casos prácticos: cuándo cambia el orden y por qué

Supongamos una tabla de pedidos con una clave primaria sobre OrderID. Si hacemos:

SELECT OrderID, CustomerID, OrderDate FROM Sales.Orders;

En la mayoría de las ejecuciones obtendremos los datos ordenados por OrderID. Pero si añadimos un WHERE, un JOIN, un TOP, un GROUP BY, o incluso una clausula INCLUDE en un índice, el plan de ejecución puede variar, y con ello el orden.

En una prueba con AdventureWorks, podréis observar cómo una simple adición de una condición WHERE puede provocar un cambio de plan de ejecución de un Clustered Index Scan a un NonClustered Index Seek, seguido de un Key Lookup, y con ello se alterará el orden de los resultados.

El problema de confiar en el azar

Imaginemos que una aplicación espera que los resultados vengan ordenados por fecha. La visualización puede estar funcionando correctamente durante meses, pero tras una reorganización de índices o una actualización del motor, el plan de ejecución cambia. De pronto, los datos aparecen en orden aparentemente aleatorio. El fallo no está en SQL Server, sino en haber confiado en algo que nunca fue una garantía.

A nivel de lógica de negocio, esta suposición puede tener consecuencias nefastas, especialmente en procesos de paginación, importaciones, exportaciones de datos o cálculos acumulativos.

El caso especial de las funciones TOP sin ORDER BY

Un caso particularmente peligroso es el uso de TOP(n) sin un ORDER BY. Por ejemplo:

SELECT TOP 1 FirstName, LastName FROM Person.Person;

¿El primer registro? ¿Según qué criterio? Puede que hoy obtengamos un empleado llamado «Tolomeo», mañana «Herminia» y pasado «Anacleto». El motor devolverá el primero que encuentre según el plan actual, que puede cambiar sin previo aviso. Este es uno de los errores más comunes en desarrollo, y conviene tenerlo siempre presente.

Orden en columnstore, tablas temporales y paralelismo

Cuando trabajamos con índices columnstore, la aleatoriedad del orden aún se amplifica más. Este tipo de almacenamiento columnar está optimizado para escaneos masivos, y el orden de los registros no es algo relevante desde el punto de vista del motor. Además, el uso de paralelismo, buffers intermedios y reordenamientos internos hacen que cada ejecución pueda devolver los datos en un orden distinto.

En consultas complejas con operaciones UNION, CTE, tablas temporales o incluso operadores spool intermedios, la combinación de resultados puede alterar el orden. Una operación Merge Join, por ejemplo, puede forzar un orden intermedio, pero si se sustituye por un Hash Match debido a un cambio en las estadísticas, ese orden desaparece.

¿Y en Azure SQL?

En el caso de bases de datos en Azure, ya sea en modo SQL Database o en SQL Managed Instance, el comportamiento es exactamente el mismo. La arquitectura del servicio no cambia esta característica. De hecho, dado que el motor puede escalar dinámicamente o balancear cargas, es aún más crítico no confiar en ningún tipo de orden implícito.

Conclusión

Como práctica profesional, debemos asumir que ninguna consulta en SQL Server garantiza un orden si no usamos expresamente la cláusula ORDER BY. Esta regla es especialmente importante cuando diseñamos procedimientos almacenados, informes o integraciones que dependen del orden de los datos.

Aunque el comportamiento “parezca” consistente, no debemos basarnos en lo que ocurre hoy, sino en lo que el motor puede decidir hacer mañana. En nuestras pruebas y validaciones, es conveniente forzar el uso de ORDER BY siempre que sea necesario incluso cuando el conjunto de datos es pequeño, para asegurar que nuestras aplicaciones sean consistentes, mantenibles y previsibles.

Pero cuidado, como suelo comentar en mis formaciones, el ORDER BY es una operación especialmente costosa, no debemos abusar de ella cuando no sea necesario tener ordenados los datos. Sin embargo, esto no quiere decir que sea solo un adorno estético para los datos, es una parte fundamental de la lógica de las consultas. Sin él, estamos a merced del optimizador, y eso nunca es buena idea.

¿Te gustaría que prepare un vídeo para youtube con pruebas para demostrar visualmente estos comportamientos? Pídemelo y lo montamos.

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.

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.

Mar

2025

¿Reconstruir índices? Quizá ya no tiene tanto sentido como pensábamos

Durante años, una de las tareas de mantenimiento más comunes en nuestros servidores SQL Server ha sido la reconstrucción de índices. La idea de eliminar la fragmentación, mejorar el rendimiento de las consultas y, en ocasiones, recuperar espacio en disco ha estado firmemente arraigada en nuestras rutinas. Sin embargo, la evolución de SQL Server con la introducción de características como Accelerated Database Recovery (ADR) y Read Committed Snapshot Isolation (RCSI) nos obliga a replantearnos si esta práctica sigue teniendo el mismo sentido que antes. En este artículo, basándonos en un experimento que hice recientemente, veremos cómo estas nuevas funcionalidades impactan en la necesidad de reconstruir índices y por qué, en muchos casos, puede que estemos invirtiendo tiempo y recursos de forma innecesaria.

¿Reconstruir índices con ADR? Un nuevo paradigma en la recuperación

Para entender por qué la reconstrucción de índices podría ser menos relevante con ADR, primero debemos recordar cómo funciona esta característica. Sin ADR, cuando modificamos una fila, SQL Server guarda los valores antiguos en el registro de transacciones y actualiza la fila directamente. Si la transacción se revierte, el motor debe recuperar los valores antiguos del registro y aplicarlos de nuevo a la fila. Cuantas más filas se hayan modificado, más tiempo tardará la reversión.

Con ADR, esta operativa cambia radicalmente. En lugar de sobrescribir la fila original, SQL Server escribe una nueva versión de la fila dentro de la misma tabla, manteniendo la versión antigua intacta. Esta estrategia permite que las reversiones de transacciones sean casi instantáneas, ya que no es necesario leer y aplicar información del registro de transacciones.

Como seguramente ya habréis imaginado, almacenar múltiples versiones de una misma fila en la tabla tiene un impacto directo en el consumo de espacio. Para demostrarlo, hace unos días realicé una prueba creando dos bases de datos idénticas, una con ADR habilitado (Test_ADR) y otra sin él (Test), y cargué ambas con un millón de filas en tablas con la misma estructura. Inicialmente, como era de esperar, la tabla con ADR activado (Products_ADR) ocupó más espacio que la tabla normal (Products). Esto se debe a que, de forma similar a RCSI, ADR necesita añadir una marca de tiempo a cada fila para rastrear sus versiones.

¿Reconstruir índices con ADR y RCSI? Un experimento revelador

La primera sorpresa llegó al reconstruir los índices en ambas tablas. Tras la reconstrucción, el tamaño de la tabla Products_ADR, que inicialmente era mayor, se redujo drásticamente hasta igualar el tamaño de la tabla Products. Esto nos plantea una pregunta intrigante: si ADR ya estaba activo al cargar los datos, ¿por qué la reconstrucción de índices liberó tanto espacio? Se podría pensar que las marcas de tiempo de versionado deberían haberse insertado con los datos iniciales, sin causar una fragmentación excesiva.

Repetí este experimento varias veces, incluso en bases de datos con ADR y RCSI activados simultáneamente, y los resultados fueron consistentes. Después de la carga inicial de datos, las tablas con ADR y/o RCSI tendían a ser más grandes. Sin embargo, tras una reconstrucción de índices, todos los tamaños se normalizaban.

La verdadera diferencia se hizo evidente al simular actividad de escritura. Al actualizar un 10% de las filas en todas las tablas, observamos que en la base de datos “normal”, el tamaño de los objetos se mantenía relativamente estable, con un ligero aumento en el índice no clúster de la columna actualizada. Esto es comprensible, ya que las filas modificadas podrían necesitar moverse a nuevas páginas para mantener el orden del índice. No obstante, en las bases de datos con ADR y/o RCSI habilitados, el tamaño de los objetos explotó, llegando casi a duplicarse tras la primera actualización. Al realizar más rondas de actualizaciones, la tendencia se mantuvo: mientras que la base de datos sin ADR crecía de forma gradual, las bases de datos con ADR y RCSI experimentaban un crecimiento mucho más rápido.

¿Por qué crecen las bases de datos con el versionado de filas?

El crecimiento del tamaño de las bases de datos al habilitar funcionalidades como ADR (Accelerated Database Recovery) y RCSI (Read Committed Snapshot Isolation) se debe al mecanismo de versionado de filas, que permite lecturas consistentes sin bloqueos. Sin embargo, aunque la ubicación del almacén de versiones sea la TempDB como con RCSI existe un overhead por fila que explica este aumento de tamaño.

Cuando ADR está habilitado

La Recuperación Acelerada de Bases de Datos utiliza un almacén de versiones persistente (PVS) que se encuentra dentro de la propia base de datos de usuario. Esto significa que las versiones anteriores de las filas modificadas se almacenan en el mismo archivo de datos (.mdf) de la base de datos. Como resultado directo, el tamaño de la base de datos en disco aumenta para albergar estas versiones.

Adicionalmente, cada fila de la tabla contendrá 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 el principal causante del aumento del tamaño de la base de datos.

Cuando RCSI está habilitado (sin ADR)

Si la base de datos tiene habilitado el aislamiento por instantánea de lectura confirmada (RCSI) pero no la Recuperación Acelerada de Bases de Datos (ADR), el almacén de versiones se crea y se mantiene en la base de datos del sistema TempDB. Esto significa que las versiones de las filas modificadas en la base de datos de usuario se almacenan temporalmente en TempDB. Por lo tanto, podriamos pensar que la base de datos de usuario en sí misma debería no experimentar un aumento tan drástico debido al almacenamiento de las versiones, aunque TempDB sí crecerá para acomodar estas versiones.

Sin embargo, al igual que con ADR, cada fila de la tabla en la base de datos de usuario seguirá teniendo el puntero de 14 bytes que apunta al almacén de versiones, aunque en este caso, el almacén esté ubicado en TempDB. Este overhead por fila en la base de datos de usuario hace que el crecimiento que veamos en la tabla sea igual que en las que están en una base de datos con ADR.

Otras funcionalidades afectadas por el versionado de filas

Además de ADR y RCSI que, como acabamos de ver, usan un almacén de versiones, hay más funcionalidades de SQL que lo necesitan. En concreto, las más comunes son las bases de datos secundarias legibles en configuraciones Always On que emplean un almacén de versiones para ofrecer lecturas consistentes en la réplica secundaria.

Otra característica son las vistas indexadas que utilizan el versionado de filas para mantener la consistencia y los Triggers AFTER UPDATE que pueden depender del versionado de filas para acceder a los estados anteriores de las filas modificadas.

En resumen, el crecimiento de las bases de datos con el versionado de filas se debe tanto al almacenamiento de las versiones anteriores de las filas en sí (dentro de la base de datos con ADR, o en TempDB con RCSI) como al overhead de un puntero de 14 bytes añadido a cada fila en la base de datos de usuario para referenciar este almacén de versiones. Es crucial tener en cuenta estas implicaciones de almacenamiento al planificar la implementación de estas funcionalidades.

¿Reconstruir índices para ahorrar espacio? Una ilusión temporal

Ante este crecimiento acelerado de las tablas con ADR y RCSI, la reacción natural sería pensar en la reconstrucción de índices como una solución para recuperar el espacio «perdido». Y, efectivamente, al reconstruir los índices en estas tablas infladas, su tamaño volvía a los valores iniciales, dando la sensación de haber «ahorrado» espacio en disco.

Sin embargo, esta ganancia de espacio es puramente ilusoria y temporal. En cuanto la carga de trabajo habitual se reanudaba y se volvían a realizar actualizaciones, el tamaño de las tablas con ADR y RCSI volvía a inflarse rápidamente. Nos encontrábamos en un ciclo sin fin de crecimiento y reconstrucción, sin abordar la causa fundamental del aumento de tamaño.

La clave para entender esta dinámica reside en la forma en que ADR y RCSI gestionan el versionado de filas. Al mantener las versiones antiguas de las filas modificadas, es inevitable que el espacio ocupado por la tabla crezca con la actividad de escritura. 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 evita que se generen nuevas versiones con futuras modificaciones. Por lo tanto, si nuestro objetivo es «ahorrar» espacio mediante la reconstrucción de índices en un entorno con ADR o RCSI, debemos entender que este ahorro será efímero. El espacio «ahorrado» volverá a ser necesario a medida que se generen nuevas versiones de las filas.

¿Reconstruir índices como en 2005? Los tiempos cambian

Esta observación nos lleva a una reflexión importante sobre nuestras prácticas de mantenimiento. Si seguimos reconstruyendo índices como si estuviéramos en 2005, pensando que estamos logrando una mejora significativa en términos de espacio en disco y rendimiento, es hora de detenernos y reconsiderar nuestra estrategia. Las mejores prácticas evolucionan con los nuevos avances de la tecnología.

La evolución de las mejores prácticas nos indica que la obsesión por la utilización del espacio en disco a menudo nos lleva a tratar los síntomas, como la hinchazón de las tablas, en lugar de la causa subyacente, que en entornos con ADR y RCSI es el versionado de filas necesario para su funcionamiento. Reconstruir índices regularmente en estos entornos puede ser una solución ilusoria para el espacio , ya que el espacio ganado se volverá a utilizar rápidamente a medida que la carga de trabajo genere nuevas versiones de las filas.

Incluso podría ser contraproducente a largo plazo si se realiza sin una justificación real de mejora del rendimiento, especialmente considerando la menor penalización por fragmentación en unidades de estado sólido (SSD), que ofrecen tiempos de acceso aleatorio mucho más rápidos que los discos duros tradicionales (HDD). Además en entornos con almacenamiento virtualizado, la contigüidad física de los datos es aún menos común y tiene menos relevancia la fragmentación de los índices.

Casos donde la reconstrucción sí tiene sentido

Existen casos específicos donde la reconstrucción sí tiene sentido, pero son menos comunes. Por ejemplo, cuando se insertan inicialmente filas con muchos valores nulos que posteriormente se actualizan y ya no se modifican. En estos casos una reconstrucción podría compactar las páginas y liberar espacio que ya no es necesario. Sin embargo, en la mayoría de los escenarios con ADR o RCSI habilitados, si nuestro principal objetivo al reconstruir índices es ganar espacio en disco, las ganancias serán en gran medida temporales e insignificantes. Debemos enfocarnos en el problema real que estamos tratando de resolver: ¿es el espacio en disco o el rendimiento de las consultas? En muchos casos, ADR y RCSI están diseñados para mejorar la concurrencia y la disponibilidad, lo que podría reducir la necesidad de reconstrucciones de índices frecuentes con fines de rendimiento, especialmente en combinación con un hardware de almacenamiento adecuado.

Conclusión

Los experimentos que he realizado nos muestran claramente que la reconstrucción de índices en bases de datos con ADR y/o RCSI activados tiene un impacto diferente al que estábamos acostumbrados. Si bien inicialmente puede parecer que recuperamos espacio en disco, este ahorro es fugaz, ya que la propia naturaleza del versionado de filas hará que las tablas vuelvan a crecer con la actividad de escritura.

Es fundamental que nosotros, como profesionales de bases de datos, comprendamos a fondo cómo funcionan estas nuevas características y cómo impactan en nuestras tareas de mantenimiento. En lugar de seguir ciegamente las prácticas del pasado, debemos analizar el problema real que intentamos resolver. Si el aumento de tamaño de nuestras tablas es una consecuencia directa del versionado de filas necesario para ADR y RCSI, quizás la solución no sea reconstruir índices constantemente, sino dimensionar adecuadamente nuestro almacenamiento y enfocar nuestros esfuerzos en otras áreas de optimización.

En definitiva, la llegada de ADR y RCSI nos invita a replantearnos nuestras rutinas de mantenimiento de índices. Entender el mecanismo subyacente del versionado de filas es crucial para tomar decisiones informadas y evitar invertir tiempo y recursos en acciones que nos ofrecen solo una sensación temporal de mejora. La evolución de SQL Server nos exige una evolución en nuestra forma de gestionarlo.

Feb

2025

Tipos de datos de texto en SQL Server

El manejo de datos de texto en SQL Server es una tarea fundamental que influye en la eficiencia del almacenamiento y en el rendimiento de las consultas. Los textos son prácticamente el tipo de datos más común pero no por ello menos importante. Elegir el tipo de dato correcto no solo optimiza el uso del espacio y mejora la velocidad de acceso, sino que también evita problemas de conversión y compatibilidad. Para ponérselo difícil, SQL Server proporciona varios tipos de datos para almacenar texto, cada uno con características específicas que los hacen adecuados para distintos escenarios.

Tipos de datos de texto en SQL Server

SQL Server ofrece distintos tipos de datos para almacenar texto. Clasificándolos en grupos podríamos hablar de los tipos de longitud fija y los de longitud variable. Además según sus características podríamos también hablar de los tipos grandes y unos que no se deberían usar porque ya están deprecados y solo se mantienen por compatibilidad.

Tipos de longitud fija y variable

Estos tipos de datos son los más comunes en bases de datos relacionales, ya que permiten definir el tamaño del almacenamiento y optimizar el uso del espacio.

CHAR y VARCHAR

Los tipos CHAR y VARCHAR almacenan texto en formato de un solo byte por carácter, utilizando una codificación específica como Latin1_General.

El tipo CHAR siempre ocupa el número de bytes definidos en su declaración, sin importar la cantidad real de caracteres almacenados. Si se define una columna como CHAR(50), cada fila ocupará exactamente 50 bytes, rellenando con espacios si el contenido es menor. Este comportamiento es eficiente en datos de longitud fija, como códigos de país o identificadores.

Por otro lado, VARCHAR permite almacenar texto de longitud variable, reservando sólo los bytes necesarios para cada valor más un pequeño encabezado que indica la longitud. Si una columna está definida como VARCHAR(50) pero el valor almacenado solo tiene 10 caracteres, SQL Server solo reservará esos 10 bytes más los metadatos, reduciendo el desperdicio de espacio.

NCHAR y NVARCHAR

A diferencia de CHAR y VARCHAR, los tipos NCHAR y NVARCHAR utilizan Unicode, lo que les permite almacenar caracteres de múltiples alfabetos en el mismo sistema. Para poder hacer esto, cada carácter en estos tipos ocupa dos bytes en lugar de uno.

NCHAR es un tipo de longitud fija, similar a CHAR, pero con soporte Unicode. NVARCHAR, en cambio, almacena sólo los caracteres necesarios y se recomienda para datos de texto multilingües de longitud variable. Si la base de datos necesita almacenar nombres internacionales, direcciones o cualquier otro dato que pueda incluir caracteres especiales, NVARCHAR es la opción más adecuada.

Tipos de datos de texto de gran tamaño

Para almacenar grandes volúmenes de texto, SQL Server proporciona variantes especializadas de VARCHAR y NVARCHAR, así como los tipos TEXT y NTEXT, que están en desuso.

VARCHAR(MAX) y NVARCHAR(MAX)

SQL Server introdujo VARCHAR(MAX) y NVARCHAR(MAX) para permitir el almacenamiento de texto de longitud variable sin las restricciones de VARCHAR(n). Estos tipos pueden contener hasta 2 GB de datos, lo que los hace ideales para almacenar grandes documentos, descripciones extensas o datos textuales sin una longitud definida.

El motor de SQL Server optimiza el almacenamiento de estos tipos dependiendo de su tamaño. Si el contenido es menor a 8 KB, se almacena directamente en la página de datos de la tabla, al igual que VARCHAR(n). Sin embargo, si el valor excede ese límite, SQL Server lo almacena en páginas separadas (páginas LOB) y guarda una referencia en la tabla principal, lo que puede afectar el rendimiento de ciertas consultas.

TEXT y NTEXT (deprecados)

En versiones anteriores de SQL Server (pero muy antiguas), los tipos TEXT y NTEXT se utilizaban para almacenar grandes volúmenes de texto. Sin embargo, estos tipos fueron declarados obsoletos a partir de SQL Server 2005 y ya no se recomiendan para nuevas implementaciones.

El problema principal con TEXT y NTEXT es que SQL Server los trata como objetos separados de la tabla principal, lo que hace que las consultas sobre estos datos sean menos eficientes. Además, su manipulación es más compleja, ya que muchas funciones de texto estándar como LEN() o SUBSTRING() no funcionan directamente sobre estos tipos sin una conversión previa.

Dado que VARCHAR(MAX) y NVARCHAR(MAX) ofrecen una alternativa más flexible y optimizada, se recomienda migrar cualquier columna TEXT o NTEXT a estos nuevos tipos.

Impacto del almacenamiento y rendimiento según el tipo de datos

El almacenamiento eficiente de los datos de texto es clave para el rendimiento de SQL Server. Todos los datos en SQL Server se almacenan en páginas de 8 KB, lo que significa que la elección del tipo de dato afecta la cantidad de información que cabe en cada página y, por lo tanto, la velocidad de acceso y manipulación de los datos.

Los tipos de longitud fija, como CHAR y NCHAR, pueden desperdiciar espacio si los valores almacenados son más cortos de lo esperado. Sin embargo, su acceso es más rápido en ciertos escenarios, ya que SQL Server sabe exactamente dónde empieza cada registro sin necesidad de calcular su tamaño. Esto es útil en columnas con valores homogéneos en longitud.

Los tipos de longitud variable, como VARCHAR y NVARCHAR, optimizan el uso del espacio, pero pueden introducir fragmentación en el almacenamiento, especialmente si los valores cambian de tamaño con frecuencia. En bases de datos de alto rendimiento, esto puede afectar la eficiencia de las consultas.

Por último, el uso de VARCHAR(MAX) y NVARCHAR(MAX) debe ser solo para casos puntuales e imprescindibles, ya que, cuando los valores almacenados superan los 8 KB, SQL Server los mueve a páginas de datos separadas, lo que puede ralentizar las consultas. Si es posible, es preferible definir una longitud específica en VARCHAR(n) o NVARCHAR(n) para mantener el rendimiento. Además, estos tipos de datos de gran tamaño no se pueden indexar con índices “normales” y si necesitamos filtrar por ellos tendremos que recurrir a los Full-Text Index

Conversiones de tipos de datos

Una de las mejores y a la vez peores cosas que ponen a nuestra disposición las bases de datos es la conversión implícita entre tipos de datos de texto. Mejores porque nos simplifica mucho la vida pero cuidado porque es un arma de doble filo. Todo lo que ganas en simplicidad lo pierdes en rendimiento. Cuando SQL Server compara valores con diferentes tipos, realiza conversiones automáticas que pueden afectar el rendimiento.

Si una columna está definida como VARCHAR y se compara con un valor NVARCHAR, SQL Server convierte automáticamente la columna completa a Unicode antes de ejecutar la consulta. Este proceso puede forzar un escaneo completo de la tabla en lugar de permitir el uso de índices optimizados, ralentizando significativamente las búsquedas.

Para evitar este problema, es fundamental asegurarse de que las comparaciones se realicen con tipos de datos compatibles. Si una columna es VARCHAR, los valores en las consultas deben ser también VARCHAR, y si es NVARCHAR, deben llevar el prefijo N para indicar que son Unicode.

También es importante evitar concatenaciones que provoquen conversiones innecesarias. Si una operación de concatenación involucra un valor NVARCHAR, todo el resultado se convierte a Unicode, lo que puede aumentar el uso de memoria y afectar el rendimiento.

Conclusión

SQL Server ofrece varias opciones para almacenar datos de texto, cada una con ventajas y desventajas según el escenario de uso. CHAR y NCHAR son adecuados para datos de longitud fija, mientras que VARCHAR y NVARCHAR son más eficientes para texto variable. Para grandes volúmenes de datos, VARCHAR(MAX) y NVARCHAR(MAX) proporcionan flexibilidad sin las limitaciones de los tipos TEXT y NTEXT, que ya están obsoletos.

La correcta elección del tipo de dato de texto tiene un impacto directo en el almacenamiento, la velocidad de consulta y la eficiencia del sistema. Comprender las diferencias entre estos tipos y sus implicaciones en el rendimiento es clave para diseñar bases de datos eficientes y escalables.

Feb

2025

Niveles de compatibilidad de las bases de datos

Hoy me he encontrado con un caso que es más habitual del que me gustaría y quería compartirlo con vosotros y ver qué podemos aprender de él. Estaba revisando el servidor de un cliente que nunca ha tenido un DBA, era un SQL Server 2022 Standard que había montado su equipo de IT. El caso es que, aun siendo un SQL Server 2022, todas las bases de datos estaban en nivel de compatibilidad 100, es decir en el modo de funcionamiento propio de SQL Server 2008. Al preguntar, me han contado que desde su primer SQL Server 2008 su informático siempre había ido aplicando las actualizaciones y subidas de versiones cuando era necesario pero claro, no sabían que había que cambiar nada en las bases de datos.

Este caso que os cuento no es excepcional, y muchos administradores de bases de datos han pasado por situaciones similares. Tener bases de datos en un SQL Server moderno pero con un nivel de compatibilidad antiguo es más común de lo que parece. Pero, ¿qué implica esto realmente? ¿Por qué es importante gestionar correctamente los niveles de compatibilidad? Vamos a profundizar en este tema y en las consideraciones clave antes de realizar cualquier cambio.

¿Qué es el Nivel de Compatibilidad en SQL Server?

El nivel de compatibilidad de una base de datos en SQL Server define qué conjunto de características y comportamientos del motor de base de datos están habilitados para esa base de datos en particular. Microsoft SQL Server introduce constantemente nuevas funcionalidades y mejoras en el motor de consulta con cada versión. Sin embargo, para evitar problemas de compatibilidad con aplicaciones antiguas, permite mantener una base de datos funcionando con un conjunto de reglas de versiones anteriores.

Por ejemplo, una base de datos con nivel de compatibilidad 100 (SQL Server 2008) ejecutada en un SQL Server 2022 seguirá comportándose en muchos aspectos como si estuviera en SQL Server 2008. Esto puede parecer una buena idea para garantizar que las consultas y procedimientos almacenados antiguos sigan funcionando sin problemas, pero también significa renunciar a muchas mejoras de rendimiento y nuevas funcionalidades del motor de base de datos.

¿Por qué es importante el Nivel de Compatibilidad?

El nivel de compatibilidad afecta aspectos fundamentales del comportamiento de SQL Server, incluyendo el optimizador de consultas, nuevas funciones y sintaxis de T-SQL y mejoras en la seguridad y el rendimiento general.

Como sabéis, SQL Server introduce mejoras constantes en el optimizador de consultas en cada nueva versión del producto. Mantener un nivel de compatibilidad antiguo significa que las consultas podrían no beneficiarse de los nuevos algoritmos y estrategias de ejecución de consultas. Además, algunas funciones y tipos de datos más modernos pueden no estar disponibles en niveles de compatibilidad antiguos.

En cuanto a seguridad, Microsoft mejora continuamente la seguridad en SQL Server, y algunas de estas mejoras solo están disponibles en niveles de compatibilidad más recientes. Lo mismo pasa con el rendimiento, características como Batch Mode on Rowstore, Adaptive Query Processing, Memory Grant Feedback, entre muchas otras, solo están habilitadas en niveles de compatibilidad más recientes.

El cambio clave con el Nivel de Compatibilidad 120 (SQL Server 2014)

Uno de los cambios más significativos en la historia de los niveles de compatibilidad de SQL Server ocurrió con SQL Server 2014 (Nivel 120). Microsoft rediseñó por completo el comportamiento del optimizador de consultas con la introducción del nuevo estimador de cardinalidad.

¿Qué es el estimador de cardinalidad y por qué es importante?

El estimador de cardinalidad es el componente del optimizador de consultas que predice cuántas filas se van a procesar en cada paso de una consulta. Estas predicciones, basadas entre otras cosas en las estadísticas, influyen directamente en la selección de planes de ejecución eficientes.

Con SQL Server 2014, Microsoft cambió la forma en que se estiman las filas, lo que en muchos casos mejoró el rendimiento, pero en otros, los menos, generó degradaciones inesperadas. Por eso, al cambiar el nivel de compatibilidad de 110 (SQL Server 2012) a 120 (SQL Server 2014), algunos planes de ejecución cambiaron drásticamente. Si tu base de datos aún se encuentra en un nivel de compatibilidad antiguo (110 o inferior), al actualizar a 120 o superior, es crucial revisar los planes de ejecución antes de aplicar el cambio en producción.

Impacto del Nivel de Compatibilidad 120 en los planes de mantenimiento

Otro cambio relevante con SQL Server 2014 y su nivel de compatibilidad 120 fue cómo se comportaban los planes de mantenimiento. Antes de SQL Server 2014, muchas bases de datos dependían de planes de mantenimiento que ejecutaban reconstrucción y reorganización de índices, estadísticas de actualización y otras tareas rutinarias de mantenimiento. Sin embargo, al actualizar a SQL Server 2014 con nivel de compatibilidad 120, muchos de estos procesos cambiaron significativamente debido a un nuevo comportamiento en la actualización de estadísticas

En versiones anteriores, las estadísticas se actualizaban con una heurística más básica basada en el número de cambios en los índices. A partir de SQL Server 2014, Microsoft introdujo un algoritmo mejorado que ajusta automáticamente la frecuencia de actualización de estadísticas en función de la variabilidad de los datos. Esto significa que algunos planes de mantenimiento antiguos pueden volverse ineficientes, ya que las estadísticas pueden actualizarse con menor frecuencia de lo esperado.

Cambios en la fragmentación de índices

También, antes de SQL Server 2014, los DBA solíamos programar reconstrucción y reorganización de índices en intervalos fijos. Sin embargo, con el nuevo estimador de cardinalidad, algunas consultas que antes se beneficiaban de la reconstrucción de índices ya no requieren mantenimiento tan frecuente, mientras que otras pueden necesitar ajustes más específicos.

Nuevo comportamiento en las esperas de consultas y la concesión de memoria

SQL Server 2014 introdujo Memory Grant Feedback, que ajusta dinámicamente la memoria asignada a las consultas. Este cambio afectó la manera en que los planes de mantenimiento deben ejecutarse en bases de datos de gran tamaño, ya que ahora SQL Server aprende con el tiempo y puede mejorar la asignación de memoria en ejecuciones repetitivas. Sin embargo, si por una actualización de estadísticas o mantenimiento de índices el plan se recompila todos esos cálculos ya no valen.

¿Cómo adaptar los planes de mantenimiento?

Si decides actualizar el nivel de compatibilidad a 120 o superior, es recomendable revisar y adaptar los planes de mantenimiento de la base de datos para evitar ineficiencias. Lo primero que tienes que hacer es revisar la estrategia de actualización de estadísticas.

En lugar de una actualización forzada en cada ciclo, es mejor (generalmente) dejar que SQL Server maneje esto dinámicamente. Por supuesto, siempre evalúa el impacto real en las consultas antes de aplicar actualizaciones manuales con UPDATE STATISTICS.

En cuanto a la reconstrucción de índices podemos utilizar la DMV sys.dm_db_index_physical_stats para analizar si realmente es necesario reconstruir índices. Y, como hemos dicho siempre que hablamos de mantenimiento de índices, ciertas tablas pueden beneficiarse más de una reorganización en lugar de una reconstrucción completa. Tendremos que valorar el nivel de mantenimiento en función de la fragmentación del índice.

En cualquier caso, Query Store puede ayudarnos a detectar cambios drásticos en planes de ejecución antes y después de cambiar el nivel de compatibilidad. Podemos incluso usar la opción de force plan si una consulta se ve afectada negativamente tras el cambio.

Consideraciones antes de cambiar el Nivel de Compatibilidad

A modo resumen de lo que llevamos hasta aquí, antes de modificar el nivel de compatibilidad de una base de datos, debemos hacer una serie de verificaciones y pruebas para asegurarnos de que el cambio no afectará negativamente a la operativa.

Analizar dependencias y compatibilidad con las aplicaciones
Revisar consultas y procedimientos almacenados
Ejecutar pruebas de rendimiento en un entorno de pruebas
Usar Query Store para comparar planes de ejecución
Ajustar planes de mantenimiento según el nuevo comportamiento de SQL Server

Cómo cambiar el Nivel de Compatibilidad

Para cambiar el nivel de compatibilidad de una base de datos en SQL Server, podemos hacerlo desde las propiedades de la base de datos en SSMS en la pestaña “Opciones”. También lo puedes hacer utilizando la siguiente instrucción T-SQL:

ALTER DATABASE NombreBaseDatos SET COMPATIBILITY_LEVEL = 150;

En este ejemplo 150 es para SQL Server 2019, los niveles de compatibilidad son los siguientes:

También podemos verificar el nivel de compatibilidad actual para todas las bases de datos con esta consulta:

SELECT name, compatibility_level FROM sys.databases

Conclusión

El nivel de compatibilidad en SQL Server no es solo una configuración más; afecta directamente al rendimiento, comportamiento del optimizador de consultas y eficiencia de los planes de mantenimiento. Con el cambio introducido en SQL Server 2014, muchas bases de datos experimentaron transformaciones en sus planes de ejecución y rutinas de mantenimiento. Antes de hacer cualquier modificación, es clave realizar pruebas exhaustivas y ajustar estrategias de optimización. Si vamos a actualizar, hagámoslo bien y con un plan claro para minimizar riesgos y aprovechar al máximo las mejoras de SQL Server.

indices

Crecimiento del Persistent Version Store (PVS) en SQL Server

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

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

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

Estrategias efectivas para controlar el tamaño del PVS

Monitorizando el Persistent Version Store

Conclusión

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes