В одной из своих предыдущих статей я уже описывал результаты пробного тестирования производительности операций чтения при использовании 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.

Попробую рассказать немного о том, какие средства мониторинга доступны в SQL Server 2014, чтобы проводить диагностику и оценивать работу Buffer Pool Extension.

В первую очередь появилось системное представление sys.dm_os_buffer_pool_extension_configuration, которое возвращает информацию о конфигурации BPE. Оно показывает включен или нет BPE, расположение файла и его размер. И хотя из описания следует, что может быть несколько файлов BPE, включить их невозможно, по крайней мере в CTP1. Возможно позже ситуация изменится и описание представления будет доработано.

Также в системное представление sys.dm_os_buffer_descriptors добавляется столбец is_in_bpool_extension, что позволяет получить информацию об отдельных страницах. Т.е. теперь можно получить список отдельных страниц с разрезом по базам данных и по отдельным объектам с учетом, сколько и какие конкретно страницы находятся в BPE. Например, вот такой запрос позволяет увидеть, какой процент страниц базы данных находится в памяти, а какой в BPE.

use [BufferPoolExtension_test];
go

select
	db_name(database_id) as [db_name],
	is_in_bpool_extension,
	cast(count(*) * 100.0 /
		(select count(*) from sys.dm_os_buffer_descriptors where database_id = db_id())
		as decimal(5, 2))	as [buffer pool, %]
from sys.dm_os_buffer_descriptors
where
	database_id = db_id()
group by
	database_id, is_in_bpool_extension;
go

Результат 22% закэшированных страниц в памяти, а остальное в BPE:

Extended Events набирают все большую популярность и для мониторинга BPE доступно 4 события.

Кроме того, можно воспользоваться следующими счетчиками производительности в Performance Monitor.

• SQLServer:Buffer Manager\Extension page writes/sec
• SQLServer:Buffer Manager\Extension page reads/sec
• SQLServer:Buffer Manager\Extension outstanding IO counter
• SQLServer:Buffer Manager\Extension page evictions/sec
• SQLServer:Buffer Manager\Extension allocated pages
• SQLServer:Buffer Manager\Extension free pages
• SQLServer:Buffer Manager\Extension in use as percentage
• SQLServer:Buffer Manager\Extension page unreferenced time

Давайте посмотрим поближе, насколько можно ускорить производительность вашей системы с помощью 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.

Наверное, многие слышали фразу «640K ought to be enough for anybody», которую ошибочно предписывают Биллу Гейтсу. И хотя оперативная память постоянно дешевеет, но для сервера баз данных ее практически никогда не бывает достаточно. SQL Server обладает хорошей интеллектуальной системой кэширования часто используемых данных, но их размер почти всегда превышает размер доступной оперативной памяти, которую иногда бывает проблематично расширить. Одной из интересных особенностей, которые появятся в SQL Server 2014, является Buffer Pool Extension, которая позволяет в случае ограниченного набора памяти кэшировать страницы с данными на SSD диске. В этой статье я постараюсь сделать детальный обзор этой технологии на примере SQL Server 2014 CTP1.

Buffer Pool – область памяти (самая большая), которую SQL Server в основном использует для хранения страниц с данными. Любая страница, которая считывается с диска сначала помещается туда, а уже потом используется по назначению (для считывания или изменения данных). Страницы в памяти могут находиться в двух состояниях: так называемые «чистые» страницы, данные в которых никогда не изменялись, и «грязные», в которые были внесены изменения, но еще не сохранены обратно на диск с помощью процессов checkpoint или lazy writer. Buffer Pool Extension позволяет «увеличить» размер Buffer Pool используя для этого SSD диск. Это может быть полезно, если у вас есть возможность добавить SSD диск в ваш сервер, но нет возможности расширить оперативную память или вынести все часто используемые данные на SSD в силу каких-то ограничений. Например, можно использовать один дешевый SSD в качестве кэша в BPE не предъявляя к нему никаких требований по надежности, в то время как размещение всей или части базы данных потребует применения более дорогих и надежных систем.

Включить опцию очень легко. Для этого достаточно использовать команду ALTER SERVER CONFIGURATION и указать расположение файла и его размер, который будет использоваться в качестве Buffer Pool Extension.

alter server configuration
set buffer pool extension
on ( filename = 'X:\MyCache.bpe' , size = 64 gb );
go

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

alter server configuration
set buffer pool extension off;
go

Итак, для чего может быть полезна данная опция и какие у нее плюсы.

1. Ее рекомендуется использовать для OLTP систем, в которых преобладают запросы на чтение данных.
2. Нет никаких рисков потери данных, т.к. BPE кэширует только «чистые» страницы. При сбое диска BPE просто отключается.
3. Не требуется вносить никаких изменений в приложения – все начинает работать сразу, как только вы включаете опцию.

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

1. Возможность будет доступна только в редакции Enterprise Edition.
2. Вы не можете задать файл для BPE размером меньше, чем размер текущей оперативной памяти. По крайней мере судя по моим тестам с использованием SQL Server 2014 CTP1, вы будете получать ошибку вида:

   Msg 868, Level 16, State 1, Line 18
   Buffer pool extension size must be larger than the current memory allocation threshold 2048 MB. Buffer pool extension is not enabled.
   

3. Максимальный поддерживаемый размер оперативной памяти 128 Гб, т.е. обладатели систем с большим размером памяти воспользоваться опцией не смогут. И это довольно неприятный момент, т.к. такой и больший объемы вполне доступны покупателям даже для серверов начального уровня.
4. Размер файла BPE не может превышать 32 размера вашей оперативной памяти. Т.е. максимально допустимый размер для него будет 32 x 128 Гб = 4 Тб. Рекомендуется задавать размер BPE не более 4-10 размеров оперативной памяти.
5. Естественно располагать файл следует на быстром SSD диске, иначе смысл опции полностью теряется. И, хотя нет никаких требований к надежности, следует учесть, что если вдруг диск выйдет из строя – вы останетесь без BPE, что может существенно повлиять на производительность вашей системы, если она сильно зависит от этой опции.

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