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

Статья
10/13/2015

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

Обзор контрольных точек

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

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

Имя	Интерфейс Transact-SQL	Описание
Автоматически	EXEC sp_configure *'`recovery interval`','`seconds`'*	Выпускается автоматически в фоновом режиме в соответствии с верхним временем, предлагаемым параметром `recovery interval` конфигурации сервера. Автоматические контрольные точки выполняются до их завершения. Автоматические контрольные точки регулируются в зависимости от количества невыполненных операций записи и того, обнаруживает ли компонент Компонент Database Engine увеличение задержки записи выше 20 миллисекунд. Дополнительные сведения см. в статье Configure the recovery interval Server Configuration Option.
Косвенные	ALTER DATABASE ... SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDS \| MINUTES }	Выдаются в фоновом режиме для обеспечения соответствия пользовательскому целевому времени восстановления для конкретной базы данных. Целевое время восстановления по умолчанию равно 0, что приводит к использованию эвристики автоматических контрольных точек в базе данных. Если вы использовали ALTER DATABASE, чтобы задать для TARGET_RECOVERY_TIME значение >0, используется это значение, а не интервал восстановления, указанный для экземпляра сервера. Дополнительные сведения см. в разделе Изменение целевого времени восстановления базы данных (SQL Server).
Вручную	CHECKPOINT [ checkpoint_duration ]	Возникает при выполнении команды Transact-SQL CHECKPOINT. Ручная контрольная точка срабатывает в текущей базе данных для конкретного соединения. По умолчанию ручная контрольная точка выполняется до ее завершения. Регулирование работает так же, как и для автоматической контрольной точки. При необходимости параметр checkpoint_duration указывает требуемое время в секундах для завершения контрольной точки. Дополнительные сведения см. в разделе CHECKPOINT (Transact-SQL).
Внутренние	Нет.	Выдаются различными операциями сервера, такими как резервное копирование и создание моментального снимка базы данных, для обеспечения соответствия образа диска текущему состоянию журнала.

Примечание

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

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

Важно!

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

Взаимодействие параметров TARGET_RECOVERY_TIME и «recovery interval»

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

target_recovery_time	«recovery interval»	Тип используемой контрольной точки
0	0	автоматические контрольные точки, для которых целевой интервал восстановления равен 1 минуте.
0	>0	Автоматические контрольные точки, целевой интервал восстановления которых указан с помощью пользовательского параметра sp_configurerecovery interval option.
>0	Неприменимо.	Косвенные контрольные точки, для которых целевое время восстановления определяется параметром TARGET_RECOVERY_TIME, выраженным в секундах.

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

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

Кроме того, в простой модели восстановления автоматическая контрольная точка становится в очередь, если журнал заполняется на 70 процентов.

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

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

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

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

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

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

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

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

Непрямые контрольные точки, новые в SQL Server 2012 г., предоставляют настраиваемую альтернативу на уровне базы данных автоматическим контрольным точкам. В случае сбоя системы косвенные контрольные точки обеспечивают восстановление за потенциально меньшее и более предсказуемое время, чем автоматические контрольные точки. Косвенные контрольные точки имеют следующие преимущества.

В базе данных, которая настроена на использование косвенных контрольных точек, может снизиться производительность обработки транзакционной нагрузки в режиме «в сети». Косвенные конечные точки сохраняют количество «грязных» страниц ниже определенного порогового значения, чтобы восстановление базы данных выполнялось в течение заданного времени восстановления. Параметр конфигурации интервала восстановления использует количество транзакций для определения времени восстановления вместо косвенных конечных точек, которые основываются на количестве «грязных» страниц. Если косвенные конечные точки включены в базе данных, получающей большое число операций DML, средство фоновой записи может начать агрессивно сбрасывать «грязные» буферы обмена на диск, чтобы гарантировать, что время, необходимое для выполнения восстановления, находится в пределах целевого периода восстановления базы данных. Это может вызвать дополнительную активность операций ввода-вывода в определенных системах, что способно привести к созданию узких мест с точки зрения производительности, если подсистема диска превысила пороговое значение операций ввода-вывода или приближается к нему.
Косвенные контрольные точки позволяют надежно управлять временем восстановления базы данных, поскольку устраняются затраты времени на ввод-вывод с непредсказуемым объемом при выполнении операций REDO. Это позволяет экземпляру сервера оставаться в пределах верхних границ времени восстановления для каждой конкретной базы данных (кроме тех случаев, когда из-за длительной транзакции чрезмерно возрастает время выполнения операций UNDO).
При использовании косвенных контрольных точек снижаются пиковые объемы ввода-вывода, вызванные выполнением контрольных точек, поскольку измененные незафиксированные страницы постоянно записываются на диск в фоновом режиме.

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

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

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

При добавлении или удалении файлов баз данных с использованием инструкции ALTER DATABASE.
При создании резервной копии базы данных.
Явное или внутреннее создание моментального снимка базы данных для команды DBCC CHECK.
При выполнении действия, требующего отключения базы данных. Примерами могут служить присвоение параметру AUTO_CLOSE значения ON и закрытие последнего соединения пользователя с базой данных или изменение параметра базы данных, требующее перезапуска базы данных.
Экземпляр SQL Server останавливается путем остановки службы SQL Server (MSSQLSERVER). И в том, и в другом случае будет создана контрольная точка для каждой базы данных на экземпляре SQL Server.
Перевод экземпляра отказоустойчивого кластера (FCI) SQL Server в автономный режим.