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

2015-10-13

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

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

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

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

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

Замечание

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

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

Это важно

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

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

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

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

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

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

Кроме того, при простой модели восстановления автоматическая контрольная точка также ставится в очередь, если журнал заполняется на 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. Это позволяет экземпляру сервера оставаться в пределах верхней границы времени восстановления для данной базы данных (за исключением случаев, когда длительная транзакция вызывает чрезмерное время отката).
Непрямые контрольные точки снижают скорость ввода-вывода, постоянно записывая грязные страницы на диск в фоновом режиме.

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

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

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

Файлы базы данных были добавлены или удалены с помощью ALTER DATABASE.
Выполняется резервное копирование базы данных.
Создается моментальный снимок базы данных, явно или внутренним способом для DBCC CHECK.
Действие, требующее завершения работы базы данных, выполняется. Например, AUTO_CLOSE включен, а последнее подключение пользователя к базе данных закрыто или изменение параметра базы данных требует перезапуска базы данных.
Экземпляр SQL Server останавливается путем остановки службы SQL Server (MSSQLSERVER). Любое действие вызывает контрольную точку в каждой базе данных в экземпляре SQL Server.
Вывод экземпляра отказоустойчивого кластера SQL Server (FCI) из оперативного режима.