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!
