Давайте посмотрим поближе, насколько можно ускорить производительность вашей системы с помощью BPE. Для тестирования я буду использовать виртуальную машину с 4 Гб оперативной памяти. Я создал отдельную базу данных с одной таблицей. В таблице всего 2 столбца [id] и [n]. Первый столбец имеет целочисленный тип данных и является первым ключом с кластеризованным индексом на нем. Второй столбец просто выполняет роль каких-то данных в системе.

use [master];
go

create database [BufferPoolExtensions_test];
go

use [BufferPoolExtensions_test];
go

create table [dbo].[TestTable] (
	[id] int identity(1,1) not null,
	[n] [char](1000) not null,
	
	constraint [PK_TestTable] primary key clustered ([id])
);
go

insert into [dbo].[TestTable] ([n])
values (replicate('A', 1000));
go 1000000

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

Тестирование будет заключаться в том, что я буду выполнять 10000 случайных запросов на выборку одной строки данных. Это хороший пример OLTP нагрузки на чтение. Данный запрос я буду выполнять несколько раз и считать среднее арифметическое результатов:

select [n]
from [dbo].[TestTable]
where [id] = cast(rand(checksum(newid())) * 1000000 as int) + 1;
go 10000

Итак, результаты первого теста, когда все данные в оперативной памяти и BPE отключен: 1.4 секунды. Это так называемая идеальная ситуация, когда памяти достаточно и все данные находятся в кэше.

Во втором результате я слегка модифицирую запрос и добавлю в него команду очистки Buffer Pool, чтобы посмотреть на «худший» вариант, когда в кэше нет никаких данных.

dbcc dropcleanbuffers;
go

select [n]
from [dbo].[TestTable]
where [id] = cast(rand(checksum(newid())) * 1000000 as int) + 1;
go 10000

Результат 2 минуты и 2 секунды.

Во третьем тесте я ограничу размер максимально доступной памяти для SQL Server до 256 Мб – это почти в 4 раза меньше, чем размер данных и попробую повторить тест. При этом стоит отметить, что во время теста в кэше не находилось больше чем 90 Мб от всех данных в таблице, что составляет примерно только 8% от всего объема. Результат: 1 минута 56 секунд. Получилось вполне ожидаемо.

В четвертом тесте я включу Buffer Pool Extension размером 4 Гб и сделаю полное сканирование таблицы перед началом теста, чтобы все данные были либо в памяти, либо в BPE. Стоит отметить, что практически все данные размещаются в BPE, т.е. SQL Server старается поместить все чистые страницы туда и не держать их в памяти. Результат теста: 4.6 секунды. Это в 3 раза хуже, чем если бы данные находились в памяти, но в 25 раз лучше, чем случайно считывать их с диска, если памяти не хватает. Вполне достойно на мой взгляд.

И последний, пятый тест. Всю базу данных я размещу на SSD диске, отключу BPE и буду очищать кэш каждый раз перед запуском. Результат: 6.3 секунды – даже медленнее, чем использовать BPE.

Итоговая таблица с результатами:

Итак, какой вывод можно сделать из тестирования. Использование BPE может заметно ускорить случайную выборку данных из таблицы, если памяти недостаточно для полного кэширования таблицы. При этом очень интересны результаты четвертого и пятого тестов – получается, что использовать BPE может быть выгоднее, чем располагать базу на SSD. Но опять же, стоит оговориться, что мое тестирование не отражает вашей ситуации и данные могут заметно различаться. Поэтому перед внедрением стоит протестировать опцию на вашей конкретной нагрузке и только после этого принимать решение. Если вы тоже тестировали BPE и у вас получаются другие результаты – предлагаю поделиться и обсудить это в комментариях к заметке.

В следующей статье я расскажу о способах мониторинга систем с включенным Buffer Pool Extension.