Puntos de comprobación de base de datos (SQL Server)

Artículo
10/13/2015

En este tema se proporciona información general sobre SQL Server puntos de control de base de datos. Un punto de control crea un punto conocido correcto desde el que el Motor de base de datos de SQL Server puede empezar a aplicar los cambios incluidos en el registro durante la recuperación después de un cierre o un bloqueo insperado del sistema.

Información general de los puntos de control

Por motivos de rendimiento, el motor de base de datos realiza modificaciones en las páginas de base de datos en la memoria caché del búfer y no escribe estas páginas en el disco después de cada cambio. En su lugar, el Motor de base de datos emite periódicamente un punto de control en cada base de datos. Un punto de comprobación escribe las páginas modificadas en memoria actuales (denominadas páginas desfasadas) y la información del registro de transacciones de la memoria en el disco y, además, registra información acerca del registro de transacciones.

El Motor de base de datos admite varios tipos de puntos de control: automáticos, indirectos, manuales e internos. En la tabla siguiente se resumen los tipos de puntos de control.

Nombre	Interfaz de Transact-SQL	Descripción
Automático	EXEC sp_configure "`recovery interval`",`seconds`	Emitido automáticamente en segundo plano para cumplir el límite de tiempo superior sugerido por la opción de configuración del `recovery interval` servidor. Los puntos de comprobación automáticos se ejecutan hasta completarse. Los puntos de control automáticos se limitan en función del número de escrituras pendientes y de si el motor de base de datos detecta un aumento de la latencia de escritura por encima de 20 milisegundos. Para más información, consulte Configure the recovery interval Server Configuration Option.
Indirecto	ALTER DATABASE... SET TARGET_RECOVERY_TIME target_recovery_time= { SECONDS \| MINUTES }	Se emiten en segundo plano para cumplir un tiempo de recuperación de destino especificado por el usuario para una determinada base de datos. El tiempo de recuperación de destino predeterminado es 0, lo que provoca que se use la heurística de puntos de comprobación automáticos en la base de datos. Si ha usado ALTER DATABASE para establecer TARGET_RECOVERY_TIME en >0, se usa este valor, en lugar del intervalo de recuperación especificado para la instancia del servidor. Para obtener más información, vea Cambiar el tiempo de recuperación de destino de una base de datos (SQL Server).
Manual	CHECKPOINT [ checkpoint_duration ]	Se emite cuando se ejecuta un comando CHECKPOINT de Transact-SQL. El punto de comprobación manual se produce en la base de datos actual para la conexión. De forma predeterminada, los puntos de comprobación manuales se ejecutan hasta completarse. La limitación funciona de la misma forma que para los puntos de comprobación automáticos. Opcionalmente, el parámetro checkpoint_duration especifica un periodo de tiempo solicitado, en segundos, para que se complete el punto de comprobación. Para más información, consulte CHECKPOINT (Transact-SQL).
Interno	Ninguno.	Se emite por varias operaciones de servidor, como la copia de seguridad y la creación de instantánea de base de datos, para garantizar que las imágenes de disco coinciden con el estado actual del registro.

Nota

La -kopción de configuración avanzada SQL Server permite a un administrador de bases de datos limitar el comportamiento de E/S del punto de control en función del rendimiento del subsistema de E/S para algunos tipos de puntos de control. La opción de configuración -k es válida para los puntos de comprobación y para cualquier punto de comprobación manual e interno sin limitar.

Para los puntos de comprobación automáticos, manuales e internos, solo las modificaciones efectuadas después del último punto de comprobación se deben poner al día durante la recuperación de la base de datos. De este modo se reduce el tiempo necesario para recuperar una base de datos.

Importante

Las transacciones no confirmadas de larga ejecución aumentan el tiempo de recuperación para todos los tipos de puntos de comprobación.

Interacción de las opciones TARGET_RECOVERY_TIME y "recovery interval"

En la tabla siguiente se resume la interacción entre la configuración de sp_configure derecovery interval todo el servidor y la base de datos específica de ALTER DATABASE ... TARGET_RECOVERY_TIME configuración.

target_recovery_time	"recovery interval"	Tipo de punto de comprobación usado
0	0	Puntos de comprobación automáticos cuyo intervalo de recuperación de destino es 1 minuto.
0	>0	Puntos de comprobación automáticos cuyo intervalo de recuperación de destino lo especifica la configuración definida por el usuario de la opción “sp_configure” recovery interval.
>0	No aplicable.	Puntos de comprobación indirectos cuyo tiempo de recuperación de destino lo determina la configuración TARGET_RECOVERY_TIME, expresado en segundos.

Puntos de comprobación automáticos

Un punto de control automático se produce cada vez que el número de registros alcanza el número que calcula el motor de base de datos que puede procesar durante el tiempo especificado en la opción de configuración del recovery interval servidor. En cada base de datos sin un tiempo de recuperación de destino definido por el usuario, el Motor de base de datos genera puntos de control automáticos. La frecuencia de los puntos de control automáticos depende de la recovery interval opción de configuración avanzada del servidor, que especifica el tiempo máximo que una instancia de servidor determinada debe usar para recuperar una base de datos durante un reinicio del sistema. El Motor de base de datos calcula el número máximo de entradas de registro que puede procesar durante el intervalo de recuperación. Cuando una base de datos que usa puntos de control automáticos alcanza este número máximo de registros, el motor de base de datos emite un punto de control en la base de datos. El intervalo de tiempo entre puntos de comprobación puede ser muy variable. Una base de datos con una carga de trabajo de transacciones considerable tendrá más puntos de comprobación frecuentes que otra que se usa principalmente para operaciones de solo lectura.

Además, según el modelo de recuperación simple, un punto de comprobación automático también se pone en cola si el registro se llena al 70%.

Según el modelo de recuperación simple, a menos que algún factor retrase el truncamiento del registro, un punto de comprobación automático trunca la sección no usada del registro de transacciones. Por el contrario, según los modelos de recuperación completa y de registro masivo, después de que se ha establecido una cadena de copia de seguridad, los puntos de comprobación automáticos no provocan el truncamiento del registro. Para más información, consulte El registro de transacciones (SQL Server).

Después de un bloqueo del sistema, el periodo de tiempo necesario para recuperar una determinada base de datos depende en gran medida de la cantidad de E/S necesaria para rehacer las páginas que estaban desfasadas en el momento del bloqueo. Esto significa que la recovery interval configuración no es confiable. No puede determinar una duración de recuperación precisa. Además, cuando punto de comprobación automático está en curso, la actividad de E/S general de los datos aumenta considerablemente y es bastante impredecible.

Repercusión del intervalo de recuperación en el rendimiento de recuperación

Para un sistema de procesamiento de transacciones en línea (OLTP) que usa transacciones cortas, recovery interval es el factor principal que determina el tiempo de recuperación. Sin embargo, la recovery interval opción no afecta al tiempo necesario para deshacer una transacción de larga duración. La recuperación de una base de datos con una transacción de larga duración puede tardar mucho más tiempo que la especificada en la recovery interval opción . Por ejemplo, si una transacción de larga duración tardó dos horas en realizar actualizaciones antes de que la instancia del servidor se deshabilitara, la recuperación real tarda considerablemente más tiempo que el recovery interval valor para recuperar la transacción larga. Para obtener más información sobre la repercusión de una transacción de larga duración en el tiempo de recuperación, vea El registro de transacciones (SQL Server).

Normalmente, los valores predeterminados proporciona un rendimiento de recuperación óptimo. No obstante, el cambio del intervalo de recuperación podría mejorar el rendimiento en las siguientes circunstancias:

Si la recuperación suele tardar considerablemente más de 1 minuto cuando no se están revirtiendo transacciones de ejecución prolongada.
Si observa que los puntos de comprobación frecuentes afectan negativamente al rendimiento de una base de datos.

Si decide aumentar la configuración recovery interval, es recomendable que lo haga gradualmente en pequeños incrementos y evaluando el efecto de cada aumento incremental en el rendimiento de recuperación. Este enfoque es importante porque a medida que aumenta la configuración, la recovery interval recuperación de la base de datos tarda muchas veces más en completarse. Por ejemplo, si cambia recovery interval 10, la recuperación tarda aproximadamente 10 veces más en completarse que cuando recovery interval se establece en cero.

Puntos de comprobación indirectos

Los puntos de control indirectos, nuevos en SQL Server 2012, proporcionan una alternativa configurable en el nivel de base de datos a los puntos de control automáticos. Si se produce un bloqueo del sistema, los puntos de comprobación indirectos proporcionan un tiempo de recuperación más rápido y predecible que los puntos de comprobación automáticos. Los puntos de comprobación indirectos proporcionan las siguientes ventajas:

Una carga de trabajo transaccional en línea en una base de datos que esté configurada para puntos de comprobación indirectos podría experimentar un deterioro del rendimiento. Los puntos de comprobación indirectos garantizan que el número de páginas desfasadas se encuentra por debajo de un umbral determinado para que la recuperación de la base de datos finalice en el tiempo de recuperación de destino. La opción de configuración de intervalo de recuperación usa el número de transacciones para determinar el tiempo de recuperación a diferencia de los puntos de comprobación indirectos, que usan el número de páginas desfasadas. Cuando se habilitan los puntos de comprobación indirectos en una base de datos que recibe un gran número de operaciones de DML, el escritor en segundo plano puede iniciar agresivamente el vaciado de búferes desfasados en el disco para asegurarse de que el tiempo necesario para realizar la recuperación se encuentra dentro del tiempo de recuperación de destino establecido de la base de datos. Esto puede provocar actividad de E/S adicional en determinados sistemas que pueden contribuir a un cuello de botella de rendimiento si el subsistema del disco está funcionando por encima del umbral de E/S o cerca de él.
Los puntos de comprobación indirectos permiten controlar el tiempo de recuperación de base de datos al tener en cuenta el costo de las E/S aleatorias durante REDO. Eso permite que una instancia de servidor permanezca dentro de un límite superior en los tiempos de recuperación de una determinada base de datos (excepto cuando una transacción de ejecución prolongada provoca tiempos de UNDO excesivos).
Los puntos de comprobación indirectos reducen los picos de E/S relacionados con los puntos de comprobación al escribir continuamente las páginas desfasadas en disco en segundo plano.

No obstante, una carga de trabajo transaccional en línea en una base de datos que está configurada para puntos de comprobación indirectos podría experimentar un deterioro del rendimiento. Esto se debe a que el escritor en segundo plano que usa el punto de comprobación indirecto en ocasiones aumenta la carga de escritura total para una instancia de servidor.

Puntos de comprobación internos

Hay distintos componentes del servidor que generan puntos de comprobación internos para garantizar que las imágenes de disco coinciden con el estado actual del registro. Los puntos de comprobación internos se generan en respuesta a los siguientes eventos:

Se han agregado o eliminado archivos de base de datos mediante ALTER DATABASE.
Se realiza una copia de seguridad de la base de datos.
Se crea una instantánea de base de datos, tanto explícita como internamente para DBCC CHECK.
Se realiza una actividad que requiere cerrar la base de datos. Por ejemplo, el valor de AUTO_CLOSE es ON y se ha cerrado la última conexión de usuario a la base de datos, o bien se realiza una modificación de una opción de la base de datos que requiere reiniciarla.
Una instancia de SQL Server se detiene deteniendo el servicio de SQL Server (MSSQLSERVER). Las dos acciones insertan un punto de comprobación en cada base de datos de la instancia de SQL Server.
Poner sin conexión una instancia de clúster de conmutación por error (FCI) de SQL Server.