Оптимизация запросов к дисковому пространству SQL Server

блог о bi, №1 в рунете

Управление дисковым пространством – важная задача для поддержки SQL Server. Производительность и стабильность базы данных зависит от количества запросов к диску и наличие свободного места в хранилище. В этой статье мы рассмотрим способы оптимизации взаимодействия сервера с дисковым пространством.

Recovery model

Модель восстановления — это свойство базы данных, которое:

управляет процессом регистрации транзакций;
определяет, требуется ли для журнала транзакций резервное копирование;
определяет, какие типы операций восстановления доступны;

Приведём типы моделей восстановления:

Simple

Не создаются резервные копии журналов.

Список недоступных функций:

группы AlwaysOn или зеркальное отображение базы данных;
восстановление носителя без потери данных;
восстановление за определённый период.

Изменения с момента создания последней резервной копии не защищены. В случае аварийной ситуации эти изменения придется вносить повторно.

Full

Создаются резервные копии журналов.

Возможно восстановление за определённый период.

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

Bulk-logged

Создаются резервные копии журналов.

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

Исходя из нашего опыта, типы «Full» и «Bulk-logged» нагружают диски с логами базы. Это приводит к снижению производительности. Рекомендуем устанавливать в ваших проекты значение «Simple».

Параметр «Recovery model» устанавливается в параметрах базы данных:

Delayed Durability

«Delayed Durability» - параметр устойчивости транзакций. Показатель может принимать следующие значения:

«Disable» - при использовании этой настройки все фиксируемые в базе данных транзакции являются полностью устойчивыми независимо от настроек уровня фиксации (DELAYED_DURABILITY= [ON | OFF]);
«Allowed» - при использовании этой настройки устойчивость каждой транзакции определяется на уровне транзакций — DELAYED_DURABILITY = {OFF | ON};
«Forced» - если выбран этот параметр, все транзакции, которые фиксируются в базе данных, являются отложенными устойчивыми.

Определим, какие бывают типы устойчивости транзакций:

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

Мы рекомендуем использовать для своих проектов тип «Forced». По внутреннему опыту нашей компании, на аппаратном уровне, указанный параметр снижает средние размеры трансферных блоков, посылаемые на файлы журнала транзакций, и как следствие оптимизирует очереди на диске с логами БД в 4-8 раз.

Параметр «Delayed Durability» устанавливается в параметрах базы данных:

Вернуться к блогу