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

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Un punto de comprobación crea un buen punto conocido desde donde Motor de base de datos de SQL ServerSQL Server Database Engine puede empezar a aplicar cambios incluidos en el registro durante la recuperación después de un cierre inesperado o un bloqueo del sistema.A checkpoint creates a known good point from which the Motor de base de datos de SQL ServerSQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

Información generalOverview

Por motivos de rendimiento, Motor de base de datosDatabase Engine realiza modificaciones en las páginas de la base de datos en memoria, en la memoria caché de búfer, y no escribe estas páginas en el disco después de cada cambio.For performance reasons, the Motor de base de datosDatabase Engine performs modifications to database pages in memory-in the buffer cache-and does not write these pages to disk after every change. En su lugar, Motor de base de datosDatabase Engine emite periódicamente un punto de comprobación en cada base de datos.Rather, the Motor de base de datosDatabase Engine periodically issues a checkpoint on each database. 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 la información del registro de transacciones.A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also records the information in the transaction log.

Motor de base de datosDatabase Engine admite varios tipos de puntos de comprobación: automáticos, indirectos, manuales e internos.The Motor de base de datosDatabase Engine supports several types of checkpoints: automatic, indirect, manual, and internal. En la tabla siguiente se resumen los tipos de puntos de comprobación:The following table summarizes the types of checkpoints:

NombreName Transact-SQLTransact-SQL InterfazInterface DescripciónDescription
AutomáticoAutomatic EXEC sp_configure ' recovery interval ',' seconds 'EXEC sp_configure ' recovery interval ','seconds' Se emite automáticamente en segundo plano para cumplir el límite de tiempo superior que sugiere la opción de configuración de servidor recovery interval .Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Los puntos de comprobación automáticos se ejecutan hasta completarse.Automatic checkpoints run to completion. Los puntos de comprobación automáticos están limitados según el número de operaciones de escritura pendientes y en función de si Motor de base de datosDatabase Engine detecta un aumento de la latencia de escritura superior a 50 milisegundos.Automatic checkpoints are throttled based on the number of outstanding writes and whether the Motor de base de datosDatabase Engine detects an increase in write latency above 50 milliseconds.

Para más información, consulte Configure the recovery interval Server Configuration Option.For more information, see Configure the recovery interval Server Configuration Option.
IndirectoIndirect ALTER DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }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.Issued in the background to meet a user-specified target recovery time for a given database. Empezando por SQL Server 2016 (13.x)SQL Server 2016 (13.x), el valor predeterminado es 1 minuto.Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the default value is 1 minute. El valor predeterminado es 0 para versiones antiguas, que indica que la base de datos utilizará puntos de comprobación automáticos, cuya frecuencia depende del valor de intervalo de recuperación de la instancia de servidor.The default is 0 for older versions, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance.

Para más información, consulte Cambiar el tiempo de recuperación de destino de una base de datos (SQL Server).For more information, see Change the Target Recovery Time of a Database (SQL Server).
ManualManual CHECKPOINT [duración_del_punto_de_comprobación]CHECKPOINT [checkpoint_duration] Se emite cuando se ejecuta un comando CHECKPOINT de Transact-SQLTransact-SQL .Issued when you execute a Transact-SQLTransact-SQL CHECKPOINT command. El punto de comprobación manual se produce en la base de datos actual para la conexión.The manual checkpoint occurs in the current database for your connection. De forma predeterminada, los puntos de comprobación manuales se ejecutan hasta completarse.By default, manual checkpoints run to completion. La limitación funciona de la misma forma que para los puntos de comprobación automáticos.Throttling works the same way as for automatic checkpoints. Opcionalmente, el parámetro checkpoint_duration especifica un periodo de tiempo solicitado, en segundos, para que se complete el punto de comprobación.Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.

Para más información, consulte CHECKPOINT (Transact-SQL).For more information, see CHECKPOINT (Transact-SQL).
InternoInternal Ninguno.None. 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.Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.

Nota

-kSQL ServerSQL Server habilita a un administrador de base de datos para limitar el comportamiento de E/S de los puntos de comprobación según el rendimiento de E/S para algunos tipos de puntos de comprobación.The -k SQL ServerSQL Server advanced setup option enables a database administrator to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. 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.The -k setup option applies to automatic checkpoints and any otherwise unthrottled manual and internal checkpoints.

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.For automatic, manual, and internal checkpoints, only modifications made after the latest checkpoint need to be rolled forward during database recovery. De este modo se reduce el tiempo necesario para recuperar una base de datos.This reduces the time required to recover a database.

Importante

Las transacciones no confirmadas de larga ejecución aumentan el tiempo de recuperación para todos los tipos de puntos de comprobación.Long-running, uncommitted transactions increase recovery time for all checkpoint types.

Interacción de las opciones TARGET_RECOVERY_TIME y "recovery interval"Interaction of the TARGET_RECOVERY_TIME and 'recovery interval' Options

En la tabla siguiente se resume la interacción entre la configuración de sp_configure " recovery interval " que afecta a todo el servidor y la configuración específica ALTER DATABASE ... TARGET_RECOVERY_TIME.The following table summarizes the interaction between the server-wide sp_configure ' recovery interval ' setting and the database-specific ALTER DATABASE ... TARGET_RECOVERY_TIME setting.

target_recovery_timeTARGET_RECOVERY_TIME "recovery interval"'recovery interval' Tipo de punto de comprobación usadoType of Checkpoint Used
00 00 Puntos de comprobación automáticos cuyo intervalo de recuperación de destino es 1 minuto.automatic checkpoints whose target recovery interval is 1 minute.
00 >0>0 Los 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 "intervalo de recuperación" sp_configure.Automatic checkpoints whose target recovery interval is specified by the user-defined setting of the sp_configure 'recovery interval' option.
>0>0 No aplicable.Not applicable. Puntos de comprobación indirectos cuyo tiempo de recuperación de destino lo determina la configuración TARGET_RECOVERY_TIME, expresado en segundos.Indirect checkpoints whose target recovery time is determined by the TARGET_RECOVERY_TIME setting, expressed in seconds.

Puntos de comprobación automáticosAutomatic checkpoints

Un punto de comprobación automático se produce cada vez que el número de entradas de registro alcanza el número que Motor de base de datosDatabase Engine calcula que puede procesar durante el tiempo especificado en la opción de configuración del servidor recovery interval .An automatic checkpoint occurs each time the number of log records reaches the number the Motor de base de datosDatabase Engine estimates it can process during the time specified in the recovery interval server configuration option. Para más información, consulte Configure the recovery interval Server Configuration Option.For more information, see Configure the recovery interval Server Configuration Option.

En cada base de datos sin un tiempo de recuperación de destino definido por el usuario, Motor de base de datosDatabase Engine genera puntos de comprobación automáticos.In every database without a user-defined target recovery time, the Motor de base de datosDatabase Engine generates automatic checkpoints. La frecuencia de los puntos de comprobación automáticos depende de la opción de configuración de servidor avanzada recovery interval , que especifica el tiempo máximo que una determinada instancia de servidor debe usar para recuperar una base de datos durante un reinicio del sistema.The frequency depends on the recovery interval advanced server configuration option, which specifies the maximum time that a given server instance should use to recover a database during a system restart. Motor de base de datosDatabase Engine calcula el número máximo de entradas de registro que puede procesar durante el intervalo de recuperación.The Motor de base de datosDatabase Engine estimates the maximum number of log records it can process within the recovery interval. Cuando una base de datos que usa puntos de comprobación automáticos alcanza el número máximo de entradas de registro, Motor de base de datosDatabase Engine emite un punto de comprobación en la base de datos.When a database using automatic checkpoints reaches this maximum number of log records, the Motor de base de datosDatabase Engine issues an checkpoint on the database.

El intervalo de tiempo entre puntos de comprobación puede ser muy variable.The time interval between automatic checkpoints can be highly 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.A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations. 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 %.Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

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.Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log. Por el contrario, según los modelos de recuperación completa y de registro masivo, después de que se haya establecido una cadena de copia de seguridad, los puntos de comprobación automáticos no provocan el truncamiento del registro.By contrast, under the full and bulk-logged recovery models, once a log backup chain has been established, automatic checkpoints do not cause log truncation. Para más información, consulte El registro de transacciones (SQL Server).For more information, see The Transaction Log (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.After a system crash, the length of time required to recover a given database depends largely on the amount of random I/O needed to redo pages that were dirty at the time of the crash. Esto significa que la configuración recovery interval no es confiable.This means that the recovery interval setting is unreliable. No puede determinar una duración de recuperación precisa.It cannot determine an accurate recovery duration. 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.Furthermore, when an automatic checkpoint is in progress, the general I/O activity for data increases significantly and quite unpredictably.

Repercusión del intervalo de recuperación en el rendimiento de recuperaciónImpact of recovery interval on recovery performance

En un sistema de procesamiento de transacciones en línea (OLTP) con transacciones cortas, la opción recovery interval es el factor principal que determina la duración de la recuperación.For an online transaction processing (OLTP) system using short transactions, recovery interval is the primary factor determining recovery time. Pero la opción recovery interval no afecta al tiempo necesario para deshacer una transacción de ejecución prolongada.However, the recovery interval option does not affect the time required to undo a long-running transaction. La recuperación de una base de datos con una transacción de ejecución prolongada puede tardar mucho más que lo especificado en el valor de intervalo de recuperación.Recovery of a database with a long-running transaction can take much longer than the time specified in the recovery interval setting.

Por ejemplo, si una transacción de ejecución prolongada tardó dos horas en realizar actualizaciones antes de que se deshabilitara el servidor, la recuperación real tarda un tiempo considerablemente superior al valor de recovery interval para recuperar la transacción de ejecución prolongada.For example, if a long-running transaction took two hours to perform updates before the server instance became disabled, the actual recovery takes considerably longer than the recovery interval value to recover the long transaction. Para obtener más información sobre la repercusión de una transacción de ejecución prolongada en el tiempo de recuperación, vea El registro de transacciones (SQL Server).For more information about the impact of a long running transaction on recovery time, see The Transaction Log (SQL Server). Para más información sobre el proceso de recuperación, vea Información general sobre restauración y recuperación (SQL Server).For more information about the recovery process, see Restore and Recovery Overview (SQL Server).

Normalmente, los valores predeterminados proporciona un rendimiento de recuperación óptimo.Typically, the default values provides optimal recovery performance. No obstante, el cambio del intervalo de recuperación podría mejorar el rendimiento en las siguientes circunstancias:However, changing the recovery interval might improve performance in the following circumstances:

  • Si la recuperación suele tardar considerablemente más de 1 minuto cuando no se están revirtiendo transacciones de ejecución prolongada.If recovery routinely takes significantly longer than 1 minute when long-running transactions are not being rolled back.

  • Si observa que los puntos de comprobación frecuentes afectan negativamente al rendimiento de una base de datos.If you notice that frequent checkpoints are impairing performance on a database.

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.If you decide to increase the recovery interval setting, we recommend increasing it gradually by small increments and evaluating the effect of each incremental increase on recovery performance. Este planteamiento es importante ya que a medida que aumenta la configuración recovery interval , se multiplica el tiempo que tarda en completarse la recuperación de la base de datos.This approach is important because as the recovery interval setting increases, database recovery takes that many times longer to complete. Por ejemplo, si cambia intervalo de recuperación a 10 minutos, la recuperación tarda aproximadamente 10 veces más en completarse que cuando intervalo de recuperación se establece en 1 minuto.For example, if you change recovery interval to 10 minutes, recovery takes approximately 10 times longer to complete than when recovery interval is set to 1 minute.

Puntos de comprobación indirectosIndirect checkpoints

Los puntos de control indirectos, presentados en SQL Server 2012 (11.x)SQL Server 2012 (11.x), proporcionan una alternativa de nivel de base de datos configurable a los puntos de comprobación automáticos.Indirect checkpoints, introduced in SQL Server 2012 (11.x)SQL Server 2012 (11.x), provide a configurable database-level alternative to automatic checkpoints. Esto se puede configurar mediante la especificación de la opción de configuración de la base de datos Tiempo de recuperación de destino.This can be configured by specifying the target recovery time database configuration option. Para obtener más información, vea Cambiar el tiempo de recuperación de destino de una base de datos (SQL Server).For more information, see Change the Target Recovery Time of a Database (SQL Server). 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.In the event of a system crash, indirect checkpoints provide potentially faster, more predictable recovery time than automatic checkpoints. Los puntos de comprobación indirectos proporcionan las siguientes ventajas:Indirect checkpoints offer the following advantages:

  • Una carga de trabajo transaccional en línea en una base de datos que esté configurada para puntos de comprobación indirectos puede experimentar un deterioro del rendimiento.An online transactional workload on a database configured for indirect checkpoints can experience performance degradation. 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.Indirect checkpoints ensure that the number of dirty pages are below a certain threshold so the database recovery completes within the target recovery time.

La opción de configuración 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.The recovery interval configuration option uses the number of transactions to determine the recovery time, as opposed to indirect checkpoints which makes use of the number of dirty pages. 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.When indirect checkpoints are enabled on a database receiving a large number of DML operations, the background writer can start aggressively flushing dirty buffers to disk to ensure that the time required to perform recovery is within the target recovery time set of the database. 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.This can cause additional I/O activity on certain systems which can contribute to a performance bottleneck if the disk subsystem is operating above or nearing the I/O threshold.

  • 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.Indirect checkpoints enable you to reliably control database recovery time by factoring in the cost of random I/O during 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).This enables a server instance to stay within an upper-bound limit on recovery times for a given database (except when a long-running transaction causes excessive UNDO times).

  • 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.Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background.

Pero una carga de trabajo transaccional en línea en una base de datos que esté configurada para puntos de comprobación indirectos puede experimentar un deterioro del rendimiento.However, an online transactional workload on a database configured for indirect checkpoints can experience performance degradation. 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.This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.

Importante

El punto de comprobación indirecto es el comportamiento predeterminado para nuevas bases de datos creadas en SQL Server 2016 (13.x)SQL Server 2016 (13.x), incluidas las bases de datos Model y TempDB.Indirect checkpoint is the default behavior for new databases created in SQL Server 2016 (13.x)SQL Server 2016 (13.x), including the Model and TempDB databases.
Las bases de datos que se actualizan in situ o se restauraron a partir de una versión anterior de SQL ServerSQL Server usarán el comportamiento de punto de comprobación automático anterior, a menos que explícitamente se modifique para usar el punto de comprobación indirecto.Databases that were upgraded in-place, or restored from a previous version of SQL ServerSQL Server, will use the previous automatic checkpoint behavior unless explicitly altered to use indirect checkpoint.

Escalabilidad mejorada de puntos de control indirectosImproved indirect checkpoint scalability

Antes de SQL Server 2019 (15.x)SQL Server 2019 (15.x), es posible que experimente errores de programador que no rinde cuando hay una base de datos que genera un gran número de páginas desfasadas, como tempdb.Prior to SQL Server 2019 (15.x)SQL Server 2019 (15.x), you may experience non-yielding scheduler errors when there is a database that generates a large number of dirty pages, such as tempdb. SQL Server 2019 (15.x)SQL Server 2019 (15.x)se presenta una mejor escalabilidad para los puntos de control indirectos, lo que debería evitar estos errores en las bases de datos con una gran carga de trabajo UPDATE/INSERT.introduces improved scalability for indirect checkpoint, which should help avoid these errors on databases that have a heavy UPDATE/INSERT workload.

Puntos de comprobación internosInternal checkpoints

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.Internal Checkpoints are generated by various server components to guarantee that disk images match the current state of the log. Los puntos de comprobación internos se generan en respuesta a los siguientes eventos:Internal checkpoint are generated in response to the following events:

  • Se han agregado o eliminado archivos de base de datos mediante ALTER DATABASE.Database files have been added or removed by using ALTER DATABASE.

  • Se realiza una copia de seguridad de la base de datos.A database backup is taken.

  • Se crea una instantánea de base de datos, tanto explícita como internamente para DBCC CHECKDB.A database snapshot is created, whether explicitly or internally for DBCC CHECKDB.

  • Se realiza una actividad que requiere cerrar la base de datos.An activity requiring a database shutdown is performed. 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.For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

  • Se detiene una instancia de SQL ServerSQL Server deteniendo el servicio SQL ServerSQL Server (MSSQLSERVER).An instance of SQL ServerSQL Server is stopped by stopping the SQL ServerSQL Server (MSSQLSERVER) service . Las dos acciones insertan un punto de comprobación en cada base de datos de la instancia de SQL ServerSQL Server.Either action causes a checkpoint in each database in the instance of SQL ServerSQL Server.

  • Poner fuera de línea una instancia de clúster de conmutación por error (FCI) de SQL ServerSQL Server .Bringing a SQL ServerSQL Server failover cluster instance (FCI) offline.

Related tasksRelated tasks

Para cambiar el intervalo de recuperación en una instancia de servidorTo change the recovery interval on a server instance

Para configurar puntos de comprobación indirectos en una base de datosTo configure indirect checkpoints on a database

Para emitir un punto de comprobación manual en una base de datosTo issue a manual checkpoint on a database

Vea tambiénSee also

El registro de transacciones (SQL Server) The Transaction Log (SQL Server)
Guía de arquitectura y administración de registros de transacciones de SQL ServerSQL Server Transaction Log Architecture and Management Guide