Настройка параметров приращения баз данных MS SQL Server

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

Цель статьи объяснить

- в мегабайтах или процентах указывать размер приращения?!
- размер самого приращения - 1, 30, или 500 мб?!

Немного теории.

При создании базы данных, как мы знаем, создается база, с параметрами базы данных model, а именно:
Размер файла данных 5 мб,
Размер приращения файла данных 1 мб,
Размер файла логов(транзакций) 1 мб,
Размер приращения файла логов(транзакций)  10 %
Рост файлов неограничен
Модель восстановления Full.

Так же эти параметры можно поменять при создании.

Далее по мере работы с базой, роста объема данных, сервер SQL Server автоматически увеличивает размеры файлов баз данных на основе установленных параметров.
По умолчанию, размер файлов не ограничен, и это, если не проводит определенных задач администрирования, может привести к заполнению всего свободного места на диске.

Итак, по вопросам:

Указывать приращение в процентах или в мегабайтах?!

Ответ – можно так и так, в зависимости от размера базы данных.

Если размер файла базы  300 мб, то приращение 10% это будет 30 мб, а если база данных 30 Гб, то 10 % будет 3000 Гб, разница есть? А если 100 Гб или более.
За какое время ОС прирастит к файлу в объем 10 Гб или более? Ответ - не мгновенно.

Но и это еще не все. К примеру, есть база dwh объем 500 Гб, прирост которой небольшой и бывает редко, установлен прирост 10 %., т.е 50 Гб должен быть прирост файла когда в файле будет не хватать места. В итоге мы получим ситуацию, как описал выше, ОС долгое время будет найти место для данного файла на диске и его разметить. А что если на диске нет свободных 50 Гб, а только 40? Гб, в итоге база данных не будет доступна для изменения. Если был бы указано приращение в мб, к примеру 512 мб, то мы бы провели операцию приращения места в файлу довольно быстро, а оставшиеся место на диске использовали бы для других задач.

Думаю, понятно какие отрицательные свойства будут при приращение в процентах.

Размер приращения в мегабайтах?!

На основе вышеописанного про процент приращения, размер приращения в мегабайтах так же зависит от объемы базы данных.

Указывать объем приращения нужно такой, чтобы процесс приращения не выполнялся часто, а также сам процесс приращения к файлу не приводил к ожиданиям запросов и их задержкам.

Если база данных небольшая, к примеру до 10 Гб, то приращение 100-300 мб, будет достаточным, если база большая и имеет большое количество транзакций, то приращение стоит указывать 500 -1000 мб, но не более 1 Гб.

Почему не более 1 Гб? Из-за выше указанных причин по приращению большого объема к файлу возникают задержки обработки транзакций.

К, примеру, из опыта обращения за консультацией, попался сервер с базой данных несколько террабайт и размером файла лога 500 гб, а размер приращения файла лога 5 гб, в файле лога которого были сообщения:

Autogrow of file ‘bd_name_file' in database 'bd_name' took 129356 milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

Что это значит?

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

Из опыта общения с сотрудниками Майкрософт, они так же давали данную цифру, что объем приращения файлов sql server должен быть не более 1 Гб.

Некоторые скажут, а как же мне быть, у мне надо большое приращение перед массовой вставкой?

Ответ прост: самим следить за приращением, т.е перед вставкой делать операцию приращения несколько раз небольшими порциями, но лучше сначала сделать резервную копию лога, который очищает (помечает) неактивные и завершенные транзакции , позволяя использовать место в файле лога повторно.

Еще, причина устанавливать нужный размер приращения на базе связана с виртуальными журналами транзакций в файле лога(транзакций). Более подробно можете посмотреть информацию в MSDN по теме физическая архитектура журнала транзакций.

Кратко, то журнал транзакций sql server обрабатывает через так называемые виртуальные журналы транзакций, количество  и размер которых определяет  sql server автоматически на основе размер файла приращения и самого размера файла лога. MS SQL Server стремится работать с небольшим количеством виртуальных журналов, но что будет, если размер приращения маленький и приращение выполняется часто – будет много виртуальных журналов. Результат таких неоптимальных настроек можно увидеть при восстановление из резервной копии базы, у которой много виртуальных журналов, после восстановления в логе ms sql server будет сообщение вида:

Database db_test2 has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Визуально мы можем увидеть следующую картину – что база данных согласно логам восстановлена на 100 %, но долгое время идут какие процессы внутри базы данных, которые могут занимать время в несколько раз больше самого восстановления бд до 100 % – причина как раз большое количество виртуальных журналов в файле лога(транзакций).

В реальности, это может повлиять и на доступность системы и время восстановления базы данных – т.е  recovery time.

Вы легко это можете проверить:
 создайте базу данных с Full модель восстановления, размер приращения файла лога сделать 1 мб, создайте полную копию базы данных, создайте таблицу и запустите процесс вставки данных, до тех пор, пока файл лога не достигнет размера 5 гб. После этого сделайте полную копию базы данных и восстановите базу из этой копии. В файле лога вы увидите вышеуказанное сообщение, а визуально, то что прогресс восстановления 100 %, а база все еще не доступна.

У меня на тестовой базе, общее время восстановления было 1 минута 44 секунд, при этом до 100% база данных восстановилась за 20 секунда, 1 минуту 20 секунд  база  данных была недоступна.

При приращении в 500 мб полностью база данных восстановилась за 56 секунд. Время может немного не точное, взято из MS SQL Management Studio, но порядок примерно такой.

Это мы сделали на тестовой базе, в реальности на базе данных с файлом лога(транзакций) более 100 Гб данное время будет занимать довольно много времени, что может оказаться критическим.

Еще, причина, установка приращения не более 1 гб, связанно с безопасностью данных. По умолчанию при приращение к файлу нового объема, ОС должна новое место на диске обнулить, т.е все что там было записать нулями. Если объем приращения довольно большой, то время приращения будет очень заметно. Данную информацию можно посмотреть в MSDN по теме Perform Volume Maintenance Task. Если вы уверены в безопасности вашей информации на диске, то вы можете отключить данную операци, предоставив учетной записи под которой работает MS SQL Server права Perform Volume Maintenance Task в Local Security Policy Вашего сервера. После этого скорость приращения так же увелисится.

Так же попытайтесь ответить на вопросы:

- Почему у вас происходит приращение файлов данных и файла транзакций(логов)?

- Не делайте ли вы лишние операции урезания файлов(shrink) и если делаете, то зачем?
Может стоит их отменить, а размер файла логов сделать достаточным, чтобы он не рост и его хватало между операциями резервного копирования файла логов?

- Какая модель восстановления у вас стоит на базе данных и нужна ли вам Full модель восстановления?

По умолчанию, в базе данных model стоит модель восстановления Full, все вновь создающиеся базы данных создаются с данной моделью восстановления. Пользователи создают базы данных,   часто не думая о модели восстановления, а бекап файла транзакций вообще не делают, в итоге размер файла транзакций может достигать в десятки и сотни больше размера файла данных.

Общие выводы:

Свойство размера приращения файлов баз данных очень важный параметр, который влияет на производительность, целостность и доступность данных в базе данных sql server.

Поэтому проверьте настройки базы данных по пунктам:

- Установите нужную модель восстановления базы данных

- Установите изначально необходимый размер файла логов и файла при создании базы данных, чтобы избежать ненужных операций приращения в дальнейшем
- Устанавливайте размер приращения в мегабайтах, но не более 1 гб приращения и не слишком маленький, во избежание создания большого количества виртуальных журналов. Если файлы данных часто увеличиваются в размерах, делайте приращений в ручном режиме в моменты малой нагрузки и порциями не более 1 гб
- Настройте резервное копирование файла логов(транзакций) для ненужного роста файла логов
- Установите нужные параметры на базе данных model. Это вас избавит проблем от неправильных настроек при создании новых баз данных. Я как правило, ставлю модель восстановления Simple, нужные размеры инициализации файлов бд, а также размеры приращения файлов в мегабайтах.
- Уберите ненужные операции Shrink
- Определитесь с правами Perform Volume Maintenance Task учетной записи sql server.

upd, интересный :

Как-то у одного заказчика при настройке зеркалирования на одной базе данных небольшого размера (40 мб), вылетала ошибка "error 1418", что зеркальный или конечная точка доступа недоступна, и соответственно зеркалирование не включалось.

В логах ms sql server-а была ошибка "error 1443" и "error 1474".

На этом же сервере была еще одна база данных, на ней зеркалирование настроено было без проблем. Отсюда было понятно, что проблема в базе данных, хотя может быть и вне установленных обновлениях ms sql server(была версия с sp1 еше)

Как оказалось, проблема была в размере приращения и VLF в файле логов.

Более подробно описано было здесь .

Решил следующим образом:

Установил правильные размеры приращения, сделал шрин файла логов.

После этого зеркалирование было настроено без проблем.

Если у вас есть проблемы c ms sql server, то здесь помогут.

После всего этого вы избавитесь от возможных проблем в работе вашего ms sql server-а связанные с настройками приращения файлов базы данных.

Всего хорошего!