BACKUP –да знаем, BUFFERCOUNT- нет, не знаем.

Операцию резервного копирования знает каждый администратор и разработчик.

Кто-то делает это через графический интерфейс, кто-то через команду BACKUP DATABASE.  Если база данных небольшая, то команда backup происходит довольно быстро и каких либо проблем не создает, но если база данных уже более 500 Гб, то создание резервной копии может создавать проблемы и создание резервной копии будет занимать уже достаточное время, еще хуже будет если размер базы данных будет 1Тб-ы, а то и 10-100- и терабайт, тогда уже необходимо думать над оптимизацией команды резервного копирования.

Мы не будем заострять внимание на схеме резервного копирования, а остановимся на некоторых дополнительных параметрах резервного копирования. Мало кто использует дополнительные параметры в команде. Полное описание параметров команды BACKUP DATABASE есть на сайте MS.

Среди этих параметров есть параметры:

  BUFFERCOUNT = { buffercount | @buffercount_variable }  

  MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable } 

Их описание:

BUFFERCOUNT = { buffercount | @buffercount_variable }
Указывает общее число буферов ввода-вывода, которые будут использоваться для операции резервного копирования. Можно указать любое целое положительное значение, однако большое число буферов может вызвать ошибку нехватки памяти из-за чрезмерного виртуального адресного пространства в процессе Sqlservr.exe.

MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
Указывает наибольший объем пакета данных в байтах для обмена данными между SQL Server и носителем резервного набора. Поддерживаются значения, кратные 65 536 байтам (64 КБ), вплоть до 4 194 304 байт (4 МБ).

Итак, давайте их проверим и протестируем. Для начала необходимо понять с какими значениями данных параметров запускается команда Backup когда мы их не указываем. Для этого необходимо включить флаги трассировки перед созданием резервной копии:

DBCC TRACEON (3213, 3605, -1);

Флаг 3213 собирает информацию об операции резервного копирования, а флаг 3605 позволяет эту информацию вывести в файл журнала SQL Server.

Получаем примерно такого вида информацию:

После того как мы получили текущие параметры выполнения команды BACKUP по умолчанию, можно провести эксперименты на команды отличные от по умолчанию.

Ниже приводится результаты создания резервных копий для базы данных 5,2 Тб с файлом журнала транзакций 10 Гб свободным место в базе 8 Гб.

Делаются два теста. 1-ый тест с параметром Buffer size 1 мб, второй тест 4 мб. В каждом тесте 4 под теста  по три запуска с разными параметрами BufferCount. Значение по умолчанию BufferCount=60, далее повышал данное значение (80,120,180)

Колличество ядер на сервере 80 с HT.

Таблица тестов:

Test 1:BufferSize 1024kb	BufferCount	Total buffer space(Мb)	Duration1(min)	Duration2(min)	Duration3(min)	AverageDur(min)
Default BufferCount	60	180	60	83	76	73,00
Count of Cores	80	240	46	44	44	44,67
	120	360	39	43	38	40,00
	180	540	38	43	38	39,67
Test 2: BufferSize 4096kb
Default BufferCount	60	720	43	46	50	46,33
Count of Cores	80	960	62	44	49	51,67
	120	1440	39	41	39	39,67
	180	2160	38	39	41	39,33

График на основе результатов:

Из результатов тестирования видно, что уже изменяя один из параметров, мы сокращаем время созданий резервной копии примерно на 40%.

Так же заметно, что достаточно поменять один из параметров: либо buffercount, либо MAXTRANSFERSIZE. Одновременное изменения не приводят к дополнительным улучшениям создания копий. Оптимальный вариант получить значение buffercount по умолчанию и увеличить его в два раза.  Увеличение в более раз не сокращает время создания резервной копии, а только больше выделяет памяти на процесс резервного копирования в ОС(значение Total buffer space(Мb), которое берет вне процесса SQL server. Но все это нужно тестировать, благо влияние и ресурсов на это много не  надо.

Хочу отметить здесь самое главное, что данные улучшения мы получим, если нет проблем с резервным копированием в других местах, таких как задержки ввода\вывода на источнике резервного копирования, на дисках под восстанавливаемой базой данных, сетевые задержки, достаточно памяти. При восстановлении по сети, операция резервного копирования потребляет до 1,8 2 Гб\сек, т.е уже на сетевом адаптере 1 Гб вы получите слабое место для резервного копирования.

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

Удачного резервного копирования.