SQL Server – мощная система управления базами данных, которая широко используется в различных отраслях. Один из важных процессов в SQL Server – это процесс контрольных точек, который записывает “грязные” страницы из системной памяти (ОЗУ) на диск. В этой статье мы рассмотрим концепцию контрольных точек и как их можно оптимизировать для улучшения производительности.

Понимание контрольных точек

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

В SQL Server 2012 был введен новый тип контрольной точки, называемый косвенной контрольной точкой. Эта автоматическая контрольная точка выполняется в фоновом режиме через определенные интервалы, которые можно настроить с помощью опции TARGET_RECOVERY_TIME свойств базы данных.

Целевое время восстановления

Опция TARGET_RECOVERY_TIME позволяет установить интервал, через который выполняются контрольные точки. По умолчанию эта опция установлена на ноль, что означает использование автоматических контрольных точек. Однако, установив значение больше нуля, будет использоваться косвенная контрольная точка.

Например, в SQL Server 2016 значение по умолчанию для TARGET_RECOVERY_TIME составляет 60 секунд. Это означает, что косвенная контрольная точка будет использоваться каждые 60 секунд для записи “грязных” страниц на диск. Если значение установлено на ноль, будет использоваться конфигурация на уровне сервера ‘recovery interval (min)’.

Вы можете просмотреть и изменить опцию TARGET_RECOVERY_TIME с помощью SQL-запросов. Следующий запрос отображает текущую настройку для всех баз данных:

SELECT name, target_recovery_time_in_seconds FROM sys.databases;

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

ALTER DATABASE <database_name> SET TARGET_RECOVERY_TIME = 60 SECONDS WITH NO_WAIT;

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

Улучшение производительности

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

В серии тестов, проведенных на стендовых средах с аналогичными характеристиками производственных сред, среднее использование ЦП снизилось примерно на 8% после изменения TARGET_RECOVERY_TIME с значения по умолчанию (ноль) на 60 секунд.

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

Заключение

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

Рекомендуется экспериментировать с различными значениями TARGET_RECOVERY_TIME и отслеживать влияние на производительность в вашей конкретной среде. При этом необходимо учитывать размер ваших баз данных и доступные системные ресурсы при внесении этих изменений.

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