Настройка плана обслуживания MS SQL Server для 1С Предприятия

Для сохранения целостности структуры баз данных и обеспечения нормальной производительности необходимо проводить периодическое обслуживание. В этой статье рассмотрим какие задания по обслуживанию необходимо выполнять для баз данных 1С Предприятия, размещенных в MS SQL.

Для сохранения целостности структуры баз данных и обеспечения нормальной производительности необходимо проводить периодическое обслуживание. В этой статье рассмотрим какие задания по обслуживанию необходимо выполнять для баз данных 1С Предприятия, размещенных в MS SQL.

Настройка плана обслуживания баз данных MS SQL Server выполняется через программу Microsoft SQL Management Studio. Рассмотрим задачи, которые мы будем выполнять в рамках регулярного обслуживания баз данных:

  • Создание полного бэкапа (раз в неделю, в воскресенье в 2:00);
  • Создание разностного бэкапа (раз в день, с понедельника по субботу в 2:00);
  • Очистка устаревших бэкапов (раз в день);
  • Дефрагментация индекса (раз в день в 4:00);
  • Обновление статистики (раз в день).

В чем отличие полного бэкапа от разностного?

Полное резервное копирование сохраняет всю базу данных целиком.

Разностное резервное копирование сохраняет все изменения созданные в базе данных с момента последнего полного бэкапа.

Такой подход к резервному копированию позваляет экономить свободное пространство на носителях информации.

Создание полного бэкапа базы.

В обозревателе объектов переходим к пункту "Управление \ Планы обслуживания". В контекстном меню выбираем "Создать план обслуживания".

создание плана обслуживания ms sql

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

В созданном плане нажимаем кнопку "Добавление вложенного плана"

добавление вложенного плана ms sql

Вводим название "Полный бэкап" и описание. Задаем расписание для выполнения задания: Раз в неделю в воскресенье в 2:00.

свойства вложенного плана ms sql

Добавляем в созданный план задание. Для этого с панели элементов перетаскиваем в поле заданий вложенного плана элемент с названием Задача "Резервное копирование базы данных".

создание резервного копирования баз данных ms sql

Открываем задание на редактирование: правой клавишей мыши по заданию, выбираем пункт "Изменить".

  • Тип резервной копии: Полное;
  • Базы данных: если выбрать "Все пользовательские базы данных", то будет выполняться бэкап всех созданных вами баз данных, но есть возможность указать на конкретные базы;
  • Создать файл резервной копии для каждой базы данных: отмечаем пункт "Создавать вложенный каталог для каждой базы данных", чтобы удобнее было ориентироваться в бэкапах и указываем путь как папке, в которой будут храниться резервные копии;
  • Отмечаем пункт "Проверять целостнойсть резервной копии";
  • Устанавливаем параметр "Сжимать резервные копии".
создание полного бэкапа ms sql

Создание разностного бэкапа.

Создание плана на выполнение разностного бэкапа выполняется аналогично полному бэкапу.

Отметим некоторые отличия в настройке:

  • Расписание выполнения заданий: с понедельника по субботу в 2:00;
  • Тип резервной копии выбираем "Разностное"
создание разностного бэкапа ms sql

Очистка устаревших бэкапов.

Для очистки устаревших бэкапов баз 1С Предприятия в MS SQL выбираем на панели элементов плана обслуживания Задачу "Очистка после обслуживания".

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

Перетаскиваем задачу с Панели элементов в план и задаем такие настройки:

  • Удалить файлы следующего типа: Файлы резервных копий;
  • Удалить из папки файлы с определенным расширением: указываем папку хранения бэкапов баз 1С;
  • Включить вложенные папки первого уровня: отмечаем галочкой, потому-что у нас для бэкапов баз создаются отдельные папки
  • Удалить файлы на основе возраста во время выполнения задачи: здесь все ограничивается лишь вашими потребностями и объемом жесткого диска, а мне достаточно 4 недель.
добавление задания на удаление старых бэкапов ms sql

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

последовательность заданий в ms sql

Через стрелки можно задавать условие, при котором будет выполнять следующее задание: ошибка, успешное завершение, выполнение. Изменить условие можно щелкнув правой клавишей мыши по стрелке.

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

Переходим к очень важному и ответственному пункту: Перестроение индекса и обновление статистики.

Дефрагментация индекса (реорганизация или перестроение).

В процессе работы базы данных 1С Предприятия, в результате постоянной записи и удаления данных, образуются пустые (фрагментированные) области. По этой причине может увеличиваться бесполезный объем БД и замедляться скорость взаимодействия с ней.

Для устранения фрагментированных областей баз данных в MS SQL существует возможность проведения Реорганизации индекса и Перестроение индекса.

В чем разница между реорганизацией и перестроением?

Перестроение индекса означает, что фрагментация будет устранена путем удаления и пересоздания индексов.

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

В каких случаях требуется реорганизация индекса?

  • Уровень фрагментации от 5% до 30%, то проводим реорганизацию.
  • Фрагментация свыше 30% необходимо проводить перестроение индекса

Под выполнение этих задач очень подходит инструкция Transact-SQL со следующим содержимым:

DECLARE @SQL NVARCHAR(MAX)
DECLARE @MIN_IND_SIZE integer = 128
DECLARE @MIN_FRAGMENTATION_LEVEL integer = 10
DECLARE @CRITICAL_FRAGMENTATION_LEVEL integer = 30

DECLARE currentIndex CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
    SELECT 'ALTER INDEX [' + ind.name + N'] ON [' + 
		SCHEMA_NAME(obj.[schema_id]) + '].[' + obj.name + '] ' +
        CASE WHEN stat.avg_fragmentation_in_percent > @CRITICAL_FRAGMENTATION_LEVEL
            THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)'
            ELSE 'REORGANIZE'
        END + ';'
    FROM (
        SELECT stat.[object_id], stat.index_id, 
			avg_fragmentation_in_percent = MAX(stat.avg_fragmentation_in_percent)
        FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') stat
        WHERE stat.page_count > @MIN_IND_SIZE AND stat.index_id > 0 
			AND stat.avg_fragmentation_in_percent > @MIN_FRAGMENTATION_LEVEL
        GROUP BY stat.[object_id], stat.index_id
    ) stat
    JOIN sys.indexes ind WITH(NOLOCK) ON stat.[object_id] = ind.[object_id]
		 AND stat.index_id = ind.index_id
    JOIN sys.objects obj WITH(NOLOCK) ON obj.[object_id] = stat.[object_id]

OPEN currentIndex

FETCH NEXT FROM currentIndex INTO @SQL
WHILE @@FETCH_STATUS = 0 BEGIN
	print @sql
    EXEC sys.sp_executesql @SQL
    FETCH NEXT FROM cur INTO @SQL    
END 

CLOSE currentIndex 
DEALLOCATE currentIndex 

Создаем вложенный план с названием "Дефрагментация индекса и обновление статистики" с расписанием раз в день в 4:00 и перетаскиваем в него из Панели элементов Задачу "Выполнение инструкции T-SQL".

добавление в план обслуживания задачи Transact-SQL

Вставляем в задачу приведенную выше инструкцию T-SQL.

инструкция t-sql по дефрагментации баз ms sql

Обновление статистики.

Обновление статистики в базах данных MS SQL, как и дефрагментация индекса, имеет большое значение для повышения производительности работы SQL сервера. Благодаря обновлению статистики SQL Server способен более эффективно выполнять планы запроса.

Выбираем на панели элементов Задача "Обновление статистики" и добавляем ее во вложенный план "Дефрагментация индекса и обновление статистики".

  • Базы данных: все пользовательские базы данных;
  • Обновить: вся собранная статистика;
  • Тип просмотра: полный просмотр.
создание задачи обновления статистики

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

добавление задачи обновление статистики в очередь

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

сохранить план обслуживания ms sql

... и убедиться, что запущен Агент SQL Server.

Агент SQL Server

4 thoughts on “Настройка плана обслуживания MS SQL Server для 1С Предприятия

  1. НикитаЖе

    Всем привет друзья. В пункте "Дефрагментация индекса" в скрипте можно указать отдельную за а не все?

    1. admin Автор записи

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

  2. арсений

    реорганизация и перестроение в одном скрипте?? как-то не понятно написано, "Под выполнение этих задач очень подходит инструкция Transact-SQL со следующим содержимым:" Мне кажется грамотней было бы читателям показать отдельно какой код для реорганизации а какой перестроения, или по порядку идет? если мне нужно только одно, к примеру перестроение

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *