В одной из своих предыдущих статей я уже описывал результаты пробного тестирования производительности операций чтения при использовании BPE. Но в реальной жизни данные нужно не только читать, но и обновлять, поэтому я решил проверить, а может ли BPE также ускорить операции обновления данных. Тут сразу же стоит принять во внимание несколько особенностей, перед тем как проводить тесты. Во-первых, все операции вставки, обновления и удаления логгируемые, а это значит, что возникнет нагрузка на журнал транзакций и многое будет зависеть от дисковой подсистемы, на которой он будет расположен. Поэтому, проведя несколько пробных замеров, я вынес журнал транзакций на SSD диск, чтобы минимизировать его влияние на операции обновления. Во-вторых, перед тем как обновить данные, SQL Server сначала считывает страницу с диска в Buffer Pool, после этого вносит изменения и помечается страницу с данными как «грязную» (dirty). Страница позже будет записана на диск процессами Checkpoint или Lazy Writer, и первый из этих процессов тоже косвенно может повлиять на результаты тестирования, поэтому я его отключил. В-третьих, я написал запрос на изменение данных таким образом, чтобы новые данные были по размеру такими же, как и старые, чтобы не происходило расщепления страниц. В-четвертых, Buffer Pool Extension может хранить только «чистые» страницы, поэтому операции обновления будут «вымывать» данные из него. Но о том, как SQL Server будет вести себя в этой ситуации, чуть позже, а пока расскажу о конфигурации тестового стенда.

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

use [master];
go

create database [BufferPoolExtension_test];
go

use [BufferPoolExtension_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 Гб и такой размер позволит либо поместить всю таблицу в память, либо только часть, регулируя параметр MAX MEMORY.

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

update [dbo].[TestTable]
set [n] = REPLICATE(char(65 + cast(rand(checksum(newid())) * 26 as tinyint)), 1000)
where [id] = cast(rand(checksum(newid())) * 1000000 as int) + 1;
go 10000

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

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

dbcc dropcleanbuffers;
go

update [dbo].[TestTable]
set [n] = REPLICATE(char(65 + cast(rand(checksum(newid())) * 26 as tinyint)), 1000)
where [id] = cast(rand(checksum(newid())) * 1000000 as int) + 1;
go 10000

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

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

В четвертом тесте я включу Buffer Pool Extension размером 4 Гб и несколько раз сделаю полное сканирование таблицы перед началом теста, чтобы все данные были либо в памяти, либо в BPE. Стоит отметить, что практически все данные размещаются в BPE, т.е. SQL Server старается поместить все чистые страницы туда и не держать их в памяти. И вот тут началось самое интересное – время выполнения тестовой нагрузки колебалось очень сильно: во время первого запуска оно было 22 секунды, потом резко подскочило до 40, а затем и до 55 секунд, еще через пару запусков до минуты с небольшим и на этом месте «замерло». В этот момент в BPE находилось ~75% данных, а в памяти еще около 8%. Дальнейшие запуски продолжили «вымывать» кэш, но время выполнения теста уже колебалось не очень значительно и составляло в среднем 1 минуту 15 секунд. После некоторых тестов я попробовал вручную запустить процесс Checkpoint, чтобы проверить, сможет ли он после записи «грязных» страниц на диск перемещать их снова в BPE, но увы он делал это очень вяло. Но, если при этом попробовать снова запускать нагрузку имитирующую случайные чтения, то данные снова перемещались в BPE.

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

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

Итак, какой вывод можно сделать из тестирования. Использование BPE может ускорить случайное изменение данных из таблицы, если памяти недостаточно для полного кэширования таблицы и данные уже присутствуют в BPE. При этом ускорение будет не таким значительным, как при случайном чтении данных. Для OLTP систем характерно то, что процент изменений гораздо меньше, чем чтений, поэтому смешанная нагрузка, например, 80% чтений и 20% обновлений может достаточно хорошо ускорится при использовании BPE.