Контрольные точки базы данных (SQL Server)

Область применения: SQL Server База данных SQL Azure

Контрольная точка создает заведомо корректное состояние, с которого ядро СУБД SQL Server может начать применение изменений, содержащихся в журнале, при восстановлении после неожиданного завершения работы или сбоя.

Обзор

По соображениям производительности ядро СУБД выполняет изменения страниц базы данных в памяти в кэше буфера и не записывает эти страницы на диск после каждого изменения. Вместо этого ядро СУБД периодически выполняет контрольную точку для каждой базы данных. Контрольная точка записывает текущие измененные страницы в памяти (известные как грязные страницы) и данные журнала транзакций из памяти на диск, а также записывает сведения в журнал транзакций.

Ядро СУБД поддерживает несколько типов контрольных точек: автоматических, косвенных, ручных и внутренних. Следующая таблица содержит сводку типов контрольных точек.

Имя. Интерфейс Transact-SQL Описание
Автоматически EXEC sp_configure "интервал восстановления", "секунды" Выдаются автоматически в фоновом режиме для обеспечения соответствия верхнему пределу времени, предлагаемому параметром конфигурации сервера recovery interval . Автоматические контрольные точки выполняются полностью. Автоматические контрольные точки ограничиваются в зависимости от количества незавершённых операций записи, а также от того, обнаруживает ли ядро СУБД увеличение задержки записи свыше 50 миллисекунд.

Дополнительные сведения см. в статье Настройка параметра конфигурации сервера «Интервал восстановления».
Косвенный ALTER DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { СЕКУНД | МИНУТ } Выдаются в фоновом режиме для обеспечения соответствия пользовательскому целевому времени восстановления для конкретной базы данных. Начиная с SQL Server 2016 (13.x), значение по умолчанию составляет 1 минуту. Для более старых версий по умолчанию установлено значение 0, при котором базой данных используются автоматические контрольные точки, а их частота зависит от параметра для интервала восстановления экземпляра сервера.

Дополнительные сведения см. в разделе Изменение целевого времени восстановления базы данных (SQL Server).
Вручную Контрольная точка [ checkpoint_duration ] Выдается при выполнении команды Transact-SQL CHECKPOINT. Ручная контрольная точка срабатывает в текущей базе данных для конкретного соединения. По умолчанию контрольные точки, запускаемые вручную, выполняются до полного завершения. Регулирование работает так же, как и для автоматической контрольной точки. При необходимости параметр checkpoint_duration указывает требуемое время в секундах для завершения контрольной точки.

Дополнительные сведения см. в разделе КОНТРОЛЬНАЯ ТОЧКА (Transact-SQL).
Внутренний нет Создаются различными серверными операциями, такими как резервное копирование и создание снимка базы данных, чтобы гарантировать, что образы дисков соответствуют текущему состоянию журнала.

Параметр -k расширенной настройки SQL Server позволяет администратору базы данных регулировать поведение операций ввода-вывода на основе пропускной способности подсистемы ввода-вывода для некоторых типов контрольных точек. Параметр -k установки применяется к автоматическим контрольным точкам и любым другим образом неуправляемым вручную и внутренним контрольным точкам.

Для автоматических, ручных и внутренних контрольных точек в процессе восстановления базы данных требуется применить только те изменения, которые были внесены после последней контрольной точки. Это способствует сокращению времени, необходимому для восстановления базы данных.

Внимание

Длительные незафиксированные транзакции увеличивают время восстановления для всех типов контрольных точек.

Взаимодействие параметров TARGET_RECOVERY_TIME и интервала восстановления

В следующей таблице приведены сведения о взаимодействии между параметром на уровне sp_configure 'recovery interval' сервера и параметром для конкретной ALTER DATABASE ... TARGET_RECOVERY_TIME базы данных.

ЦЕЛЕВОЕ_ВРЕМЯ_ВОССТАНОВЛЕНИЯ интервал восстановления Тип используемой контрольной точки
0 0 автоматические контрольные точки, для которых целевой интервал восстановления равен 1 минуте.
0 > 0 Автоматические контрольные точки, целевые интервалы восстановления которых задаются определяемым пользователем параметром sp_configure 'recovery interval' .
> 0 Неприменимо Косвенные контрольные точки, для которых целевое время восстановления определяется параметром TARGET_RECOVERY_TIME, выраженным в секундах.

Автоматические контрольные точки

Автоматическая контрольная точка выполняется каждый раз, когда число записей журнала достигает значения, которое ядро СУБД оценивает как обрабатываемое за время, указанное в параметре конфигурации сервера recovery interval. Дополнительные сведения см. в статье Настройка параметра конфигурации сервера «Интервал восстановления».

В каждой базе данных без определяемого пользователем целевого времени восстановления ядро СУБД создает автоматические контрольные точки. Частота зависит от параметра расширенной конфигурации сервера recovery interval , указывающего максимальное время, которое должен использовать данный экземпляр сервера для восстановления базы данных при перезагрузке системы. Ядро СУБД оценивает максимальное количество записей журнала, которые он может обрабатывать в течение интервала восстановления. Когда база данных, использующая автоматические контрольные точки, достигает этого максимального количества записей журнала, ядро СУБД выполняет контрольную точку для этой базы данных.

Интервал времени между автоматическими контрольными точками может сильно изменяться. В базе данных со значительной транзакционной нагрузкой контрольные точки будут создаваться чаще, чем в базе данных, используемой преимущественно для операций чтения. Кроме того, в простой модели восстановления автоматическая контрольная точка также ставится в очередь, если журнал заполняется на 70 %.

В простой модели восстановления, если только какой-либо фактор не задерживает усечение журнала, автоматическая контрольная точка усекает неиспользуемую часть журнала транзакций. В отличие от этого, в полной и массовой модели восстановления после создания цепочки резервных копий журналов автоматические контрольные точки не вызывают усечение журнала. Дополнительные сведения см. в статье Журнал транзакций (SQL Server).

После сбоя системы время, необходимое для восстановления конкретной базы данных, во многом зависит от объема операций произвольного ввода-вывода, необходимых для повторного применения изменений к страницам, которые были грязными на момент сбоя. Это означает, что параметр recovery interval является ненадежным. Он не может определить точную длительность восстановления. Кроме того, во время автоматического создания контрольной точки общая активность ввода-вывода данных значительно и непредсказуемо возрастает.

Влияние интервала восстановления на производительность восстановления

Для системы оперативной обработки транзакций (OLTP) при использовании коротких транзакций параметр recovery interval является основным фактором, определяющим время восстановления. Однако параметр интервала восстановления не влияет на время, необходимое для отмены длительной транзакции. Восстановление базы данных с длительной транзакцией может потребовать гораздо больше времени, чем указано в параметре recovery interval.

Например, если в длительной транзакции потребовалось бы два часа для проведения обновлений до того, как экземпляр сервера станет недоступным, то для фактического восстановления потребуется значительно больше времени, чем обозначено параметром recovery interval , на восстановление этой длительной транзакции. Дополнительные сведения о влиянии длительной транзакции на время восстановления см. в журнале транзакций (SQL Server). Дополнительные сведения о процессе восстановления см. в статье Обзор процессов восстановления (SQL Server).

Как правило, значения по умолчанию обеспечивают оптимальную производительность восстановления. Однако изменение интервала восстановления может способствовать повышению производительности в следующих случаях.

  • Если восстановление обычно занимает значительно дольше 1 минуты, когда не выполняется откат длительных транзакций.

  • Если вы заметили, что частые контрольные точки снижают производительность базы данных.

Если принято решение увеличить параметр recovery interval , то рекомендуется увеличивать его постепенно с небольшими приращениями и оценивать влияние каждого приращения на производительность восстановления. Этот подход важен, потому что при увеличении значения параметра recovery interval время восстановления базы данных увеличивается пропорционально указанному значению. Например, если изменяется значение параметра recovery interval , равное 10 минутам, то время выполнения восстановления увеличится приблизительно в 10 раз по сравнению со значением recovery interval , установленным равным 1 минуте.

Косвенные контрольные точки

Косвенные контрольные точки, представленные в SQL Server 2012 (11.x), предоставляют настраиваемую альтернативу на уровне базы данных автоматическим контрольным точкам. Их можно настроить, задав параметр конфигурации базы данных целевое время восстановления. Дополнительные сведения см. в разделе Изменение целевого времени восстановления базы данных (SQL Server). В случае сбоя системы косвенные контрольные точки обеспечивают восстановление за потенциально меньшее и более предсказуемое время, чем автоматические контрольные точки.

Косвенные контрольные точки имеют следующие преимущества.

  • Непрямые контрольные точки обеспечивают, чтобы количество «грязных» страниц не превышало определенного порога, благодаря чему восстановление базы данных завершается в пределах целевого времени восстановления.

    Параметр конфигурации recovery interval использует количество транзакций для определения времени восстановления вместо косвенных контрольных точек, которые основываются на количестве "грязных" страниц. Если в базе данных, получающей большое количество операций DML, включены непрямые контрольные точки, процесс фоновой записи может начать агрессивно сбрасывать грязные буферы на диск, чтобы гарантировать, что время, необходимое для выполнения восстановления, не превышает целевого времени восстановления, заданного для базы данных. Это может вызвать дополнительную активность операций ввода-вывода в определенных системах, что способно привести к созданию узких мест с точки зрения производительности, если подсистема диска превысила пороговое значение операций ввода-вывода или приближается к нему.

  • Косвенные контрольные точки позволяют надежно управлять временем восстановления базы данных за счет учета стоимости произвольного ввода-вывода во время REDO. Это позволяет экземпляру сервера оставаться в пределах верхних границ времени восстановления для каждой конкретной базы данных (кроме тех случаев, когда из-за длительной транзакции чрезмерно возрастает время выполнения операций UNDO).

  • Косвенные контрольные точки уменьшают связанные с контрольными точками скачки нагрузки ввода-вывода, поскольку грязные страницы постоянно записываются на диск в фоновом режиме.

При этом в базе данных, которая настроена на использование косвенных контрольных точек, может снизиться производительность обработки транзакционной нагрузки в режиме "в сети". Это происходит потому, что фоновый процесс записи, используемый непрямой контрольной точкой, иногда увеличивает общую нагрузку на запись на экземпляр сервера.

Внимание

Косвенная контрольная точка используется по умолчанию для новых баз данных, созданных в SQL Server 2016 (13.x), включая базы данных model и tempdb.

Базы данных, которые были обновлены на месте или восстановлены из предыдущей версии SQL Server, будут использовать предыдущее поведение автоматической контрольной точки, если явно не изменено для использования косвенной контрольной точки.

Улучшена масштабируемость косвенных контрольных точек

В версиях до SQL Server 2019 (15.x) могут возникать ошибки, связанные с тем, что планировщик не уступает управление, если имеется база данных, порождающая большое число изменённых («грязных») страниц, например tempdb. SQL Server 2019 (15.x) предоставляет улучшенную масштабируемость для косвенной контрольной точки, что должно помочь избежать этих ошибок в базах данных, имеющих тяжелую UPDATE/INSERT рабочую нагрузку.

Внутренние контрольные точки

Внутренние контрольные точки создаются различными компонентами сервера для обеспечения того, чтобы образ диска соответствовал текущему состоянию журнала. Внутренние контрольные точки создаются в ответ на следующие события:

  • Файлы базы данных были добавлены или удалены с помощью ALTER DATABASE.

  • Создается резервная копия базы данных.

  • Моментальный снимок базы данных создается либо явным образом, либо внутренне для DBCC CHECKDB.

  • Выполняется действие, требующее остановки базы данных. Примерами могут служить присвоение параметру AUTO_CLOSE значения ON и закрытие последнего соединения пользователя с базой данных или изменение параметра базы данных, требующее перезапуска базы данных.

  • Экземпляр SQL Server останавливается путем остановки службы SQL Server (MSSQLSERVER). Это действие вызывает контрольную точку в каждой базе данных в экземпляре SQL Server.

  • Перевод экземпляра отказоустойчивого кластера SQL Server (FCI) в автономный режим.

Следующие шаги

См. также

  • Last updated on