Параллельный просмотр (сканирование) буферного пула — это новая функция SQL Server 2022, призванная обеспечить лучшую масштабируемость и значительно повысить производительность обслуживания вашей рабочей нагрузки.

Буферный пул и просмотр буферного пула

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

Сканирование буферного пула — это распространённая внутренняя операция, потенциально требующая перебора миллионов буферов. Например, экземпляр SQL Server с пространством буфера в 1 ТБ требует просмотра всего буферного пула с перебором больше 130 миллионов буферов. Это справедливо для любой редакции SQL Server, независимо от использования буферного пространства, поскольку, чтобы найти буферы определенной базы данных, просмотр буферного пула всегда перебирает весь массив дескрипторов буферов.

Больше всего выиграют от новой возможности параллельного сканирования буферного пула в SQL Server 2022 те операции, которые просматривают буферы на серверах с большими объемами оперативной памяти.

Где особенно полезен Buffer Pool Parallel Scan

Наибольшую пользу от параллельного просмотра буферного пула получат операции:

запуск/завершение работы базы данных
создание новой базы данных
операции удаления файлов
операции резервного копирования/восстановления
события аварийного переключения Always On
DBCC CHECKDB и DBCC Check Table
операции восстановления журнала и другие внутренние операции, типа контрольной точки.

В прежних выпусках SQL Server операции, требующие полного просмотра буферного пула, могли выполняться медленно, особенно на машинах с большим объемом оперативной памяти. Это иногда приводило к негативным эффектам даже для операций восстановления журнала транзакций или операции переключения групп доступности. В настоящее время нет способов устранить подобные проблемы в версиях до SQL Server 2022, а распространённый «костыль» с удалением буферов командой DBCC DROPCLEANBUFFERS, обычно, приводит к некоторому снижению производительности, поскольку при последующих запросах придется перечитывать данные из файлов базы, используя не быстрые операции ввода-вывода.

Почему просмотр буферного пула работал медленно

Просмотр буферного пула в SQL Server 2019 и более ранних версиях всегда является последовательной операцией. Чем больше разме�� пула, тем сильнее это мешает, причем размер операции не столь важен: это можно заметить даже при создании новой пустой базы данных.

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

Теперь просмотр буферного пула в SQL Server 2022 масштабируется, значительно повышая производительность этого процесса, и это позволяет клиентам получить большую отдачу от инвестиций в оборудование. Повышение производительности будет в 10–30 раз.

Преимущества параллельного просмотра буферного пула SQL Server 2022

Просмотр буферного пула распараллеливается и распределяется между несколькими ядрами.
Выигрыш будет ощутим, как для небольших, так и для больших операций с базами данных на машинах с большим объёмом оперативной памяти.
Новшество включает и диагностику просмотра буферного пула, которая облегчает поддержку и анализ событий просмотра пула.
Наиболее полезным новшество будет для критически важных OLTP приложений, провайдеров услуг баз данных и хранилищ данных.

Ниже приведен пример с созданием новой базы данных на сервере HPE ProLiant DL580 с 2 ТБ оперативки, из которых 1,84 ТБ было отведено под буферный пул SQL Server.

Создание новой базы данных на компьютере с SQL Server 2019 заняло 17,204 секунды, тогда как создание базы данных на SQL Server 2022 занял чуть более 1 секунды.

Аналогичным образом, на том же сервере было смоделировано аварийное выключение базы данных, мы перевели базу в офлайн, а затем снова вернули в рабочий режим.

Для SQL Server 2019 это заняло 3 минуты 15 секунд. В SQL Server 2022 то же событие заняло чуть больше 28 секунд.

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

Это одно из тех преимуществ, которое многие почувствуют, просто обновившись до SQL Server 2022, поскольку эта возможность включена там по умолчанию.

Как это работает

Функционально просмотр буферного пула распараллеливается за счет использования нескольких ядер. На каждом ядре в одном потоке будет просматриваться по 8 миллионов буферов (64 ГБ). При этом последовательный просмотр все равно будет использоваться, если буферов будет меньше 8 миллионов.

Это одно из тех новшеств, которое нам очень нравится, и вы, опять же, заметите, что SQL Server 2022 работает явно быстрее, особенно когда приложения регулярно просматривают буферный пул на больших серверах с объемом памяти более 1 ТБ.

В старых версиях SQL Server уже можно отслеживать события медленного просмотра буферного пула

После последних накопительных обновлений, выпущенных для редакций начиная с SQL Server 2016 SP3, просмотры огромных буферных пулов будут фиксироваться в журнале ошибок. Новая диагностика оставит сообщение в ERRORLOG всякий раз, когда просмотр буферного пула занимает больше 10 секунд.

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

Кроме того, для событий параллельного просмотра буферного пула в SQL Server 2022 добавлены новые Extended Events, которые отслеживают начало/завершение просмотра, регистрируют события об ошибках и операции очистки кэша.

Основное событие, на за которым стоит следить — buffer_pool_scan_complete, которое генерируется, когда просмотр буферного пула занимает больше секунды.

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

Дополнительная информация

Operations that trigger buffer pool scan may run slowly on large-memory computers
YouTube video: SQL Server 2022 Storage Engine Capabilities (Ep. 4) | Data Exposed
SQL Server Buffer Pool in action
SQL Server 2022 Performance – Buffer Pool Scan
SQL Server 2022: Buffer Pool Scans on Large-Memory Computers

Новое в SQL Server 2022: улучшаем масштабируемость с Buffer Pool Parallel Scan