Давайте посмотрим поближе, насколько можно ускорить производительность вашей системы с помощью 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.
Итоговая таблица с результатами:
|
№ теста |
Описание теста |
Результат, время |
|
1 |
Все данные находятся в памяти. |
1.5 сек. |
|
2 |
Все данные находятся на диске, ("холодный" кэш, в памяти данных нет). |
2 мин. 02 сек. |
|
3 |
Размер оперативной памяти ограничен. В кэше находится примерно 8% от всего объема данных. |
1 мин. 56 сек. |
|
4 |
Размер оперативной памяти ограничен как в тесте №3, но включен BPE - практически все данные находятся в BPE, а не в памяти. |
4.6 сек. |
|
5 |
База данных расположена на SSD, ("холодный" кэш, в памяти данных нет). |
6.3 сек. |
Итак, какой вывод можно сделать из тестирования. Использование BPE может заметно ускорить случайную выборку данных из таблицы, если памяти недостаточно для полного кэширования таблицы. При этом очень интересны результаты четвертого и пятого тестов - получается, что использовать BPE может быть выгоднее, чем располагать базу на SSD. Но опять же, стоит оговориться, что мое тестирование не отражает вашей ситуации и данные могут заметно различаться. Поэтому перед внедрением стоит протестировать опцию на вашей конкретной нагрузке и только после этого принимать решение. Если вы тоже тестировали BPE и у вас получаются другие результаты - предлагаю поделиться и обсудить это в комментариях к заметке.