Ms sql как изменить план обслуживания

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В процессе работы базы данных 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«.

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

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

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

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

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

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

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

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

В данной статье будет рассмотрен механизм автоматизации выполнения регламентных операций в СУБД MS SQL Server под названием «Maintenance Plans» (Планы обслуживания).

Планы обслуживания (maintenance plans)

В SQL Server есть механизм под названием «Maintenance Plans» (Планы обслуживания), который предназначен для организации выполнения группы заданий по расписанию (или без него). Для того чтобы перейти к данному механизму необходимо в Management Studio: в «Обозревателе объектов» (Object Explorer) выбрать свой экземпляр, далее перейти по дереву в ветку «Управление» (Management), а в ней к «Планы обслуживания» (Maintenance plans).

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

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

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

Добавить новый план можно кликнув правой клавишей мыши на «Планы обслуживания» и выбрав «Создать план обслуживания» (New Maintenance Plan). В открывшемся окне необходимо ввести имя нового плана, нажать «ОК», после чего откроется рабочая область создания Плана обслуживания. Давайте создадим какой-нибудь простой субплан, например, обновления статистики и очистки процедурного кэша. В созданном плане выберем добавленный по умолчанию субплан, переименуем его в «UpdateStatistics» и добавим описание «Обновление статистики и очистка процедурного кэша», учетную запись оставим по умолчанию. Установим расписание, например, каждый день в 01:00.

Закроем окна расписания и свойств субплана нажатиями на кнопки «ОК».

Перейдем к созданию непосредственно самого плана выполнения (добавление задач, очередности и связей):

1. Из панели инструментов «перетащим» задачу «Обновление статистики» (Update Statistics Task). Далее, двойным кликом по задаче перейдем к редактированию ее свойств. В поле «базы данных» выберем нашу базу, остальные свойства оставим по умолчанию
2. Из панели инструментов «перетащим» задачу «Выполнение инструкции T-SQL» (Execute T-SQL Statement Task). Двойным кликом войдем в свойства задачи, в поле «Инструкция T-SQL» введем: «DBCC FREEPROCCACHE»
3. Протянем «стрелку» от задачи обновления статистики к инструкции T-SQL

Здесь также хочу упомянуть что есть 3 типа «стрелок»:

1. Успешное выполнение (Success) — значение по умолчанию, обозначается зеленым цветом, управление переходит к следующей задаче только в случае успешного выполнения текущей.
2. Ошибка (Failure) — обозначается красным цветом, управление переходит к следующей задаче только в случае ошибки выполнения. Удобно использовать для оповещения оператора.
3. Выполнение (Completion) — обозначается серым цветом, управление переходит к следующей задаче независимо от результата выполнения текущей.

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

Наш план обслуживания готов, осталось его только сохранить.

Механизм заданий

Перейдем теперь к непосредственному механизму исполняющему Планы обслуживания. После сохранения плана обслуживания, на каждый из субпланов в ветке «Агент SQL Server» (SQL Server Agent) → «Задания» (Jobs) создаются элементы заданий.

В свойствах Задания можно дополнительно настроить:

1. Добавить/изменить шаги выполнения. При создании через Планы обслуживания задание состоит из одного шага — выполнение пакета служб SSIS
2. Добавить дополнительные расписания в то же задание
3. Настроить уведомления об успешном/ошибочном завершении
4. Включить/отключить выполнение

Но главным удобством является то что задание можно запустить в ручном режиме в любой момент, для этого необходимо кликнуть правой клавишей мыши на нужном задании и в контекстном меню выбрать «Запустить задание на шаге» (Start Job at Step).

Проверка работоспособности задания

После создания нашего плана, необходимо проверить его работоспособность. Естественно, ожидание результата автоматического выполнения (если настроено расписание) не лучшее решение. Именно здесь нам и поможет вышеописанная возможность ручного запуска задания. После завершения задания можно увидеть результат его выполнения, для этого необходимо правым щелчком мыши открыть контекстное меню задания и выбрать «Просмотр журнала» (View history).

Планы обслуживания баз данных

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

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

• Оптимизации.
• Проверки целостности.
• Резервное копирование баз данных.
• Резервное копирование журнала транзакций.

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

Для создания плана обслуживания используется мастер Database Maintenance Plan Wizard. В данном разделе вы узнаете, как использовать этот мастер, а затем узнаете, как выводить на экран задания плана обслуживания и как редактировать этот план.

Использование мастера Database Maintenance Plan Wizard для создания плана обслуживания

Для использования мастера Database Maintenance Plan Wizard выполните следующие шаги.

1. Запустите этот мастер из Enterprise Manager с помощью одного из следующих методов.
   • Выберите из меню Tools пункт Database Maintenance Planner (Планировщик обслуживания баз данных).
   • Щелкните на имени базы данных в левой панели и затем щелкните на New Maintenance Plan (Новый план обслуживания) под заголовком Maintenance (Обслуживание) в правой панели. Если вы не видите заголовка Maintenance, убедитесь в том, что у вас выбран пункт Taskpad (Панель задач) в меню View (Вид) Enterprise Manager. Возможно, вам придется также выполнить прокрутку, чтобы увидеть заголовок Maintenance.
   • Щелкните на имени базы данных, выберите пункт Wizards (Мастера) из меню Tools, раскройте в появившемся диалоговом окне Select Wizard (Выбор мастера) папку Management (Управление) и затем выберите Database Maintenance Plan Wizard.
   • Раскройте сервер в левой панели, раскройте папку Management, щелкните правой кнопкой мыши на Database Maintenance Plans (Планы обслуживания баз данных) и затем выберите из появившегося контекстного меню пункт New Maintenance Plan.
   • Щелкните правой кнопкой мыши на имени соответствующей базы данных, выберите пункт All Tasks (Все задачи) и затем выберите из этого меню Maintenance Plan.

   Открыв мастер, вы увидите начальное окно мастера Database Maintenance Plan Wizard (рис. 30.7).

   

   Рис.
   30.7.
   Начальное окно мастера Database Maintenance Plan Wizard

2. Щелкните на кнопке Next (Далее), чтобы появилось окно Select Databases (Выбор баз данных) (рис. 30.8). Здесь вы можете выбрать базу или базы данных, для которых хотите создать план обслуживания.

   

   Рис.
   30.8.
   Окно Select Databases (Выбор баз данных)

3. Щелкните на кнопке Next, чтобы появилось окно Update Data Optimization Information (Обновление информации по оптимизации данных) (рис. 30.9). Вы можете выбирать следующие типы оптимизации для базы или баз данных, выбранных на предыдущем шаге.
   • Reorganize data and index pages (Реорганизация страниц данных и индексов).Этот флажок указывает, что все индексы и все таблицы базы данных будут удалены и воссозданы с использованием указанного коэффициента заполнения (или количества свободного места на каждой странице), что может повысить производительность обновлений. В случае таблиц, предназначенных только для чтения, реорганизация страниц не является необходимостью. В случае таблиц, для которых часто выполняются вставки или изменения, свободное место, которое первоначально было доступно на ваших индексных страницах, постепенно заполняется, и начинает происходить фрагментация страниц. Установите этот флажок, чтобы выполнить повторное создание ваших индексов и образовать свободное место для будущего роста во избежание задержек и нагрузок, вызываемых фрагментацией страниц.

     Вы можете выбрать между повторным созданием индексов с исходным количеством свободного места или указать новый процент свободного места на одну страницу. Если задать слишком большой процент, то вы рискуете снизить производительность операций чтения. Установив этот флажок, вы не можете установить следующий флажок – Update statistics used by query optimizer.

     Совет.Удаление и повторное создание индексов может оказаться более длительным, чем использование DBCC DBREINDEX, см. раздел «Перестроение индексов» (Rebuilding Indexes)
     «Создание и использование индексов»
     . Вы можете также создать свое собственное задание по перестроению индексов вместо использования этого средства.

     

     Рис.
     30.9.
     Окно Update Data Optimization Information (Реорганизация страниц данных и индексов)

   • Update statistics used by query optimizer (Обновление статистики, используемой оптимизатором запросов) При установке этого флажка SQL Server выполнит перерасчет статистики распределения по всем индексам в соответствующей базе данных. SQL Server использует эту информацию для выбора оптимального плана исполнения для запросов. Если вы не изменили принятый по умолчанию параметр для обновления статистики (рассмотренный выше в этой лекции), то SQL Server автоматически генерирует статистику путем выборки небольшого процента данных в таблице, соответствующей каждому индексу.

     Флажок этого окна можно использовать для того, чтобы SQL Server выполнял выборку с использованием большего процента данных (указанного вами) или чтобы задать частоту, с которой SQL Server должен обновлять эту статистику, вместо автоматического выбора частоты в SQL Server. Чем больше процент выборки данных, тем точнее будет статистика, но при этом SQL Server потратит больше времени. Эта информация может помочь в повышении производительности при интенсивном изменении данных в индексируемых колонках. Вы можете проверить используемый план исполнения для ваших запросов с помощью анализатора запросов SQL Query Analyzer, чтобы определить, насколько эффективно используются индексы и насколько необходима установка данного флажка. Установив этот флажок, вы не сможете установить предыдущий флажок.

   • Remove unused space prom database files (Удалить неиспользуемое пространство из файлов базы данных) Этот флажок применяется для удаления неиспользуемого пространства; этот процесс также известен как уплотнение (сжатие) файла (file shrink). Вы можете задать, насколько большим должно стать неиспользуемое пространство, чтобы произошло сжатие файла, а также процент пространства, которое должно остаться свободным после сжатия. Удалив свободное пространство, вы можете использовать DBCC SHRINKFILE для снижения размера данного файла. Если нужно, вы можете сделать его меньше, чем при первоначальном создании. Это позволит использовать для других целей дисковое пространство, которое раньше было занято файлом. Кроме того, сжатие файла за счет удаления неиспользуемого пространства может повысить производительность. В случае таблиц, предназначенных только для чтения, реорганизация страниц не является необходимостью.

     Вы можете задать время, за которое должны выполняться эти задачи, щелкнув на кнопке Change (Изменить) и введя новое расписание в появившемся диалоговом окне Edit Recurring Job Schedule (Редактировать расписание повторяющихся заданий) (рис. 30.10). Эти задачи следует выполнять в периоды небольшой загруженности системы, например, в выходные дни или ночью, поскольку это требует определенного времени и может увеличивать время отклика на запросы пользователей.

     

     Рис.
     30.10.
     Диалоговое окно Edit Recurring Job Schedule (Редактировать расписание повторяющихся заданий)

1. Главная
2. Полезная информация
3. Как правильно настроить MS SQL Server для «1С»: планы обслуживания

12.03.2019

Часто неопытные системные администраторы подразумевают перевод базы «1С» в клиент-серверный режим работы как панацею, избавляющую от всех проблем и ускоряющую «1С» во много раз. Однако, без правильной настройки, мониторинга и регулярного обслуживания базы «1С» в клиент-серверном режиме начинают работать еще «медленнее», чем в файловом. В рамках этой статьи рассмотрим оптимальную настройку своевременных регламентных операций на уровне самой СУБД MS MSQL. Крайне важно выполнять регламентное обслуживание в системах под значительной нагрузкой, где работают больше 10-ти пользователей, ведь в подобных системах обычных действий (выполняемых СУБД MS SQL) становится недостаточно для эффективной работы.

Планы обслуживания/Maintenance Plan в MS SQL Server

Итак, «Сервер 1С:Предприятие» и SQL Server установлены и настроены, базы перенесены, пользователи работают. Ускорение «1С» и комфорт в работе получен. Но, с течением времени документы начинают открываться медленнее, подбор номенклатуры «зависает», а отчеты формируются «целую вечность». Чтобы этого избежать, следует настроить и автоматизировать регламентные процедуры по обслуживанию базы в SQL Server.

Вообще, планы обслуживания нужно подстраивать под конкретное оборудование и базы данных. Оставим это на усмотрение профессионалов администрирования баз данных. В общем случае, для базы данных не более 200 Гб в MS SQL Server рекомендуется выполнять следующие регламентные операции:

• Проверка целостности базы данных
• Реорганизация индекса/Восстановить индекс
• Обновление статистики

Рекомендуется регулярно контролировать своевременность и правильность выполнения данных регламентных процедур.

Проверка целостности базы данных/DBCC CHECKDB

Периодичность: 1 раз в неделю.

Время запуска: в технологическом окне — во время минимальной нагрузки.

Как настроить: Microsoft SQL Server Management Studio — «Управление» — «Планы обслуживания» — правой кнопкой мыши «Мастер планов обслуживания».

Имя — можно заполнить на свое усмотрение, например «Проверка целостности базы данных» или «CheckDB». Для настройки расписания запуска проверки — кнопка «Изменить». Выполняется — еженедельно; повторять — каждое воскресенье. Однократное задание, например, в 01:00.

При выборе задач по обслуживанию устанавливаем флаг «Проверка целостности базы данных».

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

Если настройка прошла без ошибок, выйдет сообщение об успешной проверке.

Реорганизация индекса/Восстановить индекс

Что такое индексы? Индексы — это структурированные данные, которые ускоряют процесс запроса, предоставляя быстрый доступ к строкам данных в таблице, аналогично предметному указателю или оглавлению в книгах. Индексы составляют больше половины объема большинства баз «1С». Для каждого индекса обязательно хранится его статистика.

Подробнее об индексах в MS SQL Server

MS SQL Server самостоятельно создает и изменяет индексы при работе с базой. С течением времени данные в индексе становятся фрагментированными, т.е. разбросанными по базе данных. Существенно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления работы базы. Если фрагментация составляет от 5 до 30%, то рекомендуется ее устранить с помощью реорганизации, при фрагментации свыше 30% необходимо полное перестроение индексов.

Подробнее о реорганизации и перестроении индексов

В простейшем случае получить информацию по фрагментации индексов можно с помощью кода:

DECLARE @db_id SMALLINT;

SET @db_id = DB_ID(N'MyBaseSQL');

IF @db_id IS NULL
BEGIN;
    PRINT N'Неправильное имя базы';
END;

ELSE
BEGIN;
	SELECT
		object_id AS [ID объекта],
		index_id AS [ID индекса],
		index_type_desc AS [Тип индекса],
		avg_fragmentation_in_percent AS [Фрагментация в %]
		
	FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL , 'LIMITED')
	 
	ORDER BY [avg_fragmentation_in_percent] DESC;
END;
GO

Для выполнения кода нажимаем «Создать запрос» — вставляем код. В строке «SET @db_id = DB_ID(N’MyBaseSQL’);» вместо «MyBaseSQL» нужно указать имя своей базы данных. Кнопка «Выполнить».

Почему регулярно стоит использовать именно реорганизацию индекса?

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

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

Важно! При использовании модели восстановления «Полная» (правой кнопкой мыши по базе данных — «Свойства» — «Параметры»), чтобы файл журнала транзакций не вырастал до неприличных размеров, необходимо выполнять «Резервное копирование базы данных (полное)» после каждой процедуры реорганизации или перестроения индекса.

Вывод: Если фрагментация более 30%, нужно выполнить разовое полное перестроение индексов (восстановить индекс). После перестроения планово использовать только реорганизацию.

Периодичность: 1 раз в сутки.

Время запуска: в технологическом окне — во время минимальной нагрузки.

Как настроить: Microsoft SQL Server Management Studio — «Управление» — «Планы обслуживания» — правой кнопкой мыши «Мастер планов обслуживания».

Имя — можно заполнить на свое усмотрение, например «Реорганизация индекса» или «Index Reorganize». Для настройки расписания запуска проверки — кнопка «Изменить». Выполняется — ежедневно; повторять — каждый 1 день. Однократное задание, например, в 02:00.

При выборе задач по обслуживанию устанавливаем флаг «Реорганизация индекса».

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

Если настройка прошла без ошибок, выйдет сообщение об спешной проверке.

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

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

Подробнее о статистике в MS SQL Server

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

Частоту обновления статистики нужно определять экспериментальным путем в зависимости от нагрузки, но общая рекомендация для баз «1С» — один раз в сутки.

Периодичность: 1 раз в сутки.

Время запуска: в технологическом окне — во время минимальной нагрузки.

Как настроить: Microsoft SQL Server Management Studio — «Управление» — «Планы обслуживания» — правой кнопкой мыши «Мастер планов обслуживания».

Имя — можно заполнить на свое усмотрение, например «Обновление статистик» или «Update Statistics». Для настройки расписания запуска проверки — кнопка «Изменить». Выполняется — ежедневно; повторять — каждый 1 день. Однократное задание, например, в 03:00.

При выборе задач по обслуживанию устанавливаем флаг «Обновление статистик».

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

Если настройка прошла без ошибок, выйдет сообщение об спешной проверке.

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

Фирма «1С» в нескольких источниках (Регламентные операции на уровне СУБД для MS SQL Server) советует после обновления статистики дополнительно проводить очистку процедурного кэша. Это имело смысл для старых версий SQL Server, например версии 2005. С версии 2008 при включенной настройке «асинхронное обновление статистики» очищать процедурный кэш необязательно. При асинхронном обновлении статистики запросы компилируются с существующей статистикой, даже если она устарела. Если на момент компиляции запроса статистика оказывается устаревшей, оптимизатор запросов может выбрать неоптимальный план запроса. Запросы, которые компилируются после выполнения асинхронного обновления, будут усовершенствованы благодаря использованию обновленной статистики.

Контроль выполнения планов обслуживания

Просматривать результаты выполнения обслуживания можно нажав правой кнопкой мыши на «Управление» — «Планы обслуживания» — «Просмотр журнала».

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

Почему не стоит использовать сжатие базы данных (шринк/shrink)?

Пожалуй, единственным случаем, когда есть смысл использовать сжатие базы данных — масштабные изменения в базе данных. Например: после восстановления из DT-дампа, свертки или реструктуризации информационной базы.

В остальных случаях:

• сжатие файла базы данных (MDF) приводит к увеличению индексов;
• сжатие файла журнала транзакций (LDF) не нужно при правильной настройке резервного копирования и обслуживании индексов. При использовании полной модели восстановления (Full Recovery Model) базы SQL важно делать регулярные резервные копии файла журнала транзакций и только перестроение индексов. Тогда, файл LDF будет соизмерим с размером файла базы данных и не будет бесконтрольно расти.

Ко всему прочему, практически сразу после выполнения операции «шринк/shrink» файлам приходится вновь расти. Что приводит к дополнительным операциям чтения и записи дисковой системы, которые в свою очередь замедляют работу пользователей.

Что дальше?

В будущих статьях мы рассмотрим несколько тем:

• тонкая настройка SQL Server;
• поиск узких мест в производительности связки сервер «1С:Предприятие»/MS SQL Server;
• настройка резервного копирования и возможность восстановить базу на любой момент времени.

Следите за обновлениями.

Ниже будет рассказано, как создать план обслуживания в Microsoft SQL Server 2008 R2 с помощью программы «Среда SQL Sever Management Studio». В данной статье я просто постараюсь наглядно описать алгоритм создания плана обслуживания с помощью Мастера планов обслуживания, не вдаваясь в теоретическую часть вопроса. Получить больше информации по данной области можно изучив электронную документацию по SQL Server на сайте MSDN.

В описанный ниже план будут входить всего 2 задачи.

• Резервное копирование базы данных.
• Проверка целостности базы данных.

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

0. Оглавление

1. Исходные данные
2. Проверка работы Агента SQL Server
3. Создание плана обслуживания
4. Запуск выполнения плана обслуживания

1. Исходные данные

1. Операционная система семейства Windows (в моем примере используется Microsoft Windows Server 2008 R2)
2. Установленный Microsoft SQL Server 2008 R2 (об установке SQL Server можно прочитать здесь)
3. Существующая база данный в SQL Server (о создании баз данных в SQL Server читайте здесь)
4. Настроенная компонента Database Mail, в случае если требуется уведомлять по электронной почте операторов о результатах выполнения плана обслуживания (о том как настроить компоненту Database Mail и создать оператора системы я писал здесь).

2. Проверка работы Агента SQL Server

Первое что нам необходимо сделать, это убедиться что Агент SQL Server установлен и работает. Для этого запустим оснастку «Службы» («Пуск» (Start) — «Администрирование» (Administrative Tools) — «Службы» (Services) ) и в списке служб найдем службу «Агент SQL сервер» (SQL Server Agent).

Откроем свойства этой службы (кликнув по ней 2 раза) и убедимся что:

• Тип запуска стоит «Автоматически» (Startup type: Automatic);
• Состояние «Работает» (Service status: Started);

В противном случае, необходимо изменить параметры как на скриншоте выше и сохранить настройки нажав «Применить» (Apply) .

Теперь запустим программу «Среда SQL Sever Management Studio» ( «Пуск» (Start) — «Все программы» (All programs) — «Microsoft SQL Server 2008 R2» — «Средства SQL Server 2008 R2«) и введем данные для авторизации.

После чего, еще раз убедимся что Агент SQL Server работает (в обозревателе объектов должна быть вкладка «Агент SQL Server» (SQL Server Agent) с зеленой иконкой слева.

3. Создание плана обслуживания

Теперь перейдем непосредственно к созданию плана обслуживания. В обозревателе объектов (Object Explorer) раскроем вкладку «Управление» (Management), кликнем правой кнопкой мыши по вкладке «Планы обслуживания» (Maintenance Plans) и в контекстном меню выберем «Мастер планов обслуживания» (Maintenance Plan Wizard) .

В запустившемся мастере планов обслуживания на странице приветствия нажимаем «Далее» (Next) и в следующем окне вводим имя и описание нового плана.

Затем необходимо определиться с расписанием, по которому будет выполняться данный план обслуживания. Для этого установим переключатель на «Единое расписание для всего плана или без расписания» (Single schedule for the entire plan ore no schedule) и нажмем «Изменить…» (Change…) для назначения расписания.

Откроется окно «Свойства расписания задания» . Здесь зададим те параметры, согласно которым должен выполняться план обслуживания и нажмем «ОК» . В моем примере это:

• Выполняется — «Еженедельно»  (Occurs — Weekly);
• Повторяется каждые — «1 нед.» в «Воскресенье» (Recurs every: 1 week(s) on Sunday);
• Выполняться один раз в день в: — «2:00:00» (Occurs onсe at: «2:00:00»);

Еще раз убедимся, что расписание задано верно, и нажмем «Далее» (Next) .

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

1. Проверка целостности базы данных (Check Database Integrity);
2. Резервное копирование базы данных (полное) (The Back Up Database (Full));

Заметьте, что для каждой задачи приводится ее краткое описание в поле снизу. Выбрав необходимые задачи, жмем «Далее» (Next) .

Теперь необходимо задать порядок выполнения задач, используя кнопки «Вверх…» (Move Up)  и «Вниз…» (Move Down). Установив порядок, жмем «Далее» (Next) .

Здесь требуется задать параметры для каждой задачи в плане. Первая задача в нашем списке это «Копирование БД (полное)» (Back Up Database (Full)).

Прежде всего необходимо выбрать базы данных для резервного копирования, нажав на кнопку выбора списка «Определенные базы данных» (Select one ore more). Выбрав необходимые для резервного копирования базы данных, нажимаем «ОК» .

Ниже зададим размещение и срок хранения резервных копий, а также установим дополнительные параметры:

1. Если установить переключатель «Создать файл резервной копии для каждой базы данных» (Create a backup file for every database) , то при выполнении задания в выбранной директории будет создаваться несколько файлов резервных копий с именами, соответствующими названиям баз данных. Ну а установка флага «Создавать вложенный каталог для каждой базы данных» (Create a sub-directory for each database) разложит файлы по отдельным папкам. Обратите внимание, что необходимо оставить заполненным расширение файла резервной копии.
2. Установка флага «Срок действия резервного набора данных истекает» (Backup set will expire) указывает SQL-серверу, когда этот набор может быть перезаписан без явного пропуска проверки на истечение срока.
3. Для наибольшей надежности, можно установить флаг «Проверять целостность резервной копии» (Verify backup integrity).
4. Также рекомендую выбрать режим «Сжимать резервные копии» (Compress backup) для экономии дискового пространства, если используемая версия SQL Server поддерживает данную функцию.

Если дисковое пространство ограничено, можно также выбрать один файл для хранения резервной копии, который будет перезаписываться после каждого выполнения плана обслуживания. Для этого установим соответствующий переключатель на «Создать резервную копию баз данных в одном или нескольких файлах» (Back up databases across one ore more files) и указжем соответствующее имя файла (будьте внимательны, файл резервной копии следует задавать с расширением .bak), а также выберем режим «Перезаписать» в случае, если файлы резервной копии существуют (If backup files exist: Overwrite).

Определившись с настройками жмем «Далее» (Next).

Теперь очередь задачи «Проверка целостности базы данных» (Database Check Integrity). Для нее всего лишь необходимо выбрать базу данных. В моем примере это все та же база данных, что и на предыдущем шаге. Определившись с базами, жмем «Далее» (Next).

На следующей странице возможно выбрать директорию, куда будет сохраняться лог выполнения задания, а также указать оператора SQL Server для отправки отчета по электронной почте. Задав параметры, снова жмем «Далее» (Next).

Проверим еще раз все настройки плана обслуживания, и если все верно, нажимаем «Готово» (Finish).

Мастер начнет построение плана обслуживания. Если мастер не обнаружит ошибок, то  увидим сообщение об успешном построении плана. В противном случае необходимо устранить ошибки и повторить процедуру снова. Закроем окно, нажав «Закрыть» (Close).

4. Запуск выполнения плана обслуживания

Для запуска выполнения плана обслуживания перейдем в программу «Среда Microsoft SQL Server Management Studio». Здесь, раскрыв вкладку «Планы обслуживания» (Maintenance Plans) увидим наш только что созданный план. Чтобы проверить его работу, кликнем по нему правой кнопкой мыши, и в контекстном меню выберем пункт «Выполнить» (Execute) .

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

А в соответствующих директориях должны появиться файл резервной копии

и файл лога выполнения плана.

Открыв, этот файл, вы должны увидеть примерно следующее:

Если все так, поздравляю! План обслуживания SQL Server создан и работает.

Microsoft SQL Server 2019 позволяет использовать встроенные средства резервного копирования и обслуживания баз данных — Maintenance Plans. Описываемый план обслуживания успешно используется для баз данных 1С, но не ограничивается ими.

Ранее уже была подобная статья для Microsoft SQL Server 2014:

Maintenance Plans — резервное копирование и обслуживание баз данных в Microsoft SQL Server 2014

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

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

Данный план решает задачи:

1. Резервное копирование баз данных.
2. Ежедневное сжатие баз данных и лога транзакций.
3. Оптимизация индексов.
4. Сброс статистики.
5. Обслуживание баз данных.

Резервное копирование будет осуществляться на сетевую шару другого сервера от имени пользователя, под которым запущен экземпляр SQL сервера.

Планы обслуживания

Создавать планы обслуживания буду с нуля. Вернее я создам один план обслуживания, а в нём будет пять субпланов. Но мне это название «субпланы» или «подпланы» не нравится, я их просто называю «планы».

Итак, у нас сервер, на котором несколько баз данных 1С в режиме восстановления FULL. Планы обслуживания и резервного копирования не настроены.

Работаем в Management Studio. Коннектимся к серверу и переходим в Management → Maintenance Plans. Нажимаем правую кнопку, выбираем New Maintenance Plan…

Указываем название плана, я пишу «Backup». OK.

Создаётся основной план обслуживания.

Внутри создаём пять планов. 1C_DAILY, 1C_HOURLY, 1C_MONTHLY — для резервного копирования и обслуживания. 1C_INDEX — для работы с индексами. 1C_STAT — для работы со статистикой.

1C_DAILY

Настраиваем расписание. План будет выполняться ежедневно по ночам в 1:10.

После создания расписания создаём сам план обслуживания:

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

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

Во вкладке Destination задаём путь для хранения резервных копий. Я сохраняю ежедневные бэкапы в папке daily. Расширения файлов — bak.

Во вкладке Options указываем дополнительные параметры и срок хранения. Я буду хранить ежедневные бэкапы 94 дня. После истечения срока бэкапы пометятся как expired.

Третий шаг в плане — снова резервное копирование. На этот раз лога транзакций. Для этого в типе резервного копирования выбираем опцию Transaction Log.

Во вкладке Destination задаём путь для хранения резервных копий логов. Я сохраняю ежедневные бэкапы логов в той же папке daily. Расширения файлов — trn.

Во вкладке Options указываем дополнительные параметры и срок хранения. Я буду хранить хранить ежедневные бэкапы логов также 94 дня.

А дальше план выполнения разделяется на две ветки, которые выполняются параллельно, поскольку не мешают друг другу.

В первой ветке выполняется сжатие баз данных. Без этого логи будут бесконечно расти. Освобождённое место отдаём операционной системе.

После сжатия выполняется блок T-SQL команд:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Во второй ветке проводится обслуживание баз данных. Сначала удаляются старые ежедневные бэкапы старше 94 дней.

Затем удаляются старые ежедневные бэкапы логов старше 94 дней.

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

Для того, чтобы можно было отправлять уведомления на почту, вам может потребоваться предварительно настроить Database Mail.

Включаем Database Mail в Microsoft SQL Server 2019

1C_HOURLY

Настраиваем расписание. План будет выполняться один раз в час каждую 17-ю минуту.

После создания расписания создаём сам план обслуживания:

План простой, всего два пункта. Сначала делаем резервную копию лога транзакций.

Во вкладке Destination задаём путь для хранения резервных копий логов. Я сохраняю ежечасные бэкапы логов в папке hourly. Расширения файлов — trn.

Во вкладке Options указываем дополнительные параметры и срок хранения. Я буду хранить хранить ежечасные бэкапы логов 7 дней.

Потом проводим обслуживание, удаляем старые ежечасные бэкапы логов старше 7 дней.

1C_MONTHLY

Настраиваем расписание. План будет выполняться один раз в месяц первого числа в 5:10 утра.

После создания расписания создаём сам план обслуживания:

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

После проверки целостности делаем резервное копирование файлов данных. Для этого в типе резервного копирования выбираем опцию Full.

Во вкладке Destination задаём путь для хранения резервных копий. Я сохраняю ежемесячные бэкапы в папке monthly. Расширения файлов — bak.

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

Третий шаг в плане — резервное копирование лога транзакций. Для этого в типе резервного копирования выбираем опцию Transaction Log.

Во вкладке Destination задаём путь для хранения резервных копий логов. Я сохраняю ежемесячные бэкапы логов в той же папке monthly. Расширения файлов — trn.

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

Следующим шагом чистим историю. Удаляем всё что старше двух лет.

Отправляем уведомление.

1C_INDEX

Настраиваем расписание. План будет выполняться один раз в неделю по субботам в 4:10 утра.

После создания расписания создаём сам план обслуживания:

Всего три шага. Перестроение индексов. Перестраиваю в 8 потоков.

Реорганизация индексов.

Уведомление.

1C_STAT

Настраиваем расписание. План будет выполняться один раз в неделю по воскресеньям в 21:00.

После создания расписания создаём сам план обслуживания:

Три шага. Сначала Обновление статистики.

После выполняется блок T-SQL команд:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Уведомление.

Вместо заключения

Если у кого-то есть советы и дельные мысли по поводу обслуживания и резервного копирования баз данных 1С — пишите.

Содержание:

1.       Создание полного резервирования 1С

2.       Очистка устаревших резервных копий 1С

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

Для сохранения целостности и предотвращения потери данных существуют внутренние функции MS SQL 1С.

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

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

·         полное резервирование 1С;

·         очистка устаревших резервных копий 1С;

·         обновление статистики 1С.  

1.    Создание полного резервирования 1С

Откроем «Обозреватель объектов», выбрав подпункт «Планы обслуживания», создаем план обслуживания.

Добавим вложенный план. Вводим название «Полный» – это будет полный резерв и описание плана, а также расписание для выполнения задания.

В созданный план добавим задачу из существующих задач плана обслуживания.

Нажимаем правой клавишей мыши на задание для редактирования, далее выберем «Изменить».

• Тип резервной копии: Полное;
• Базы данных: Указываем базу или все базы;
• отметим пункт «Проверять целостность резервной копии»;
• установим параметр «Сжимать резервные копии».  

2.    Очистка устаревших резервных копий 1С

Аналогично добавим типовую задачу «Очистка после обслуживания»:

• Удалить файлы следующего типа: Указываем, какие файлы удалить (в нашем случае – резервные копии);
• Удалить из папки файлы с определенным расширением: указываем папку, где находятся резервные копии;
• Удалить файлы на основе возраста во время выполнения задачи: указать, сколько хранить резервные копии;

Таким же образом настраивается удаление отчета плана обслуживания.

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

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

Для повышения производительности базы данных выполняется задание Обновление статистики 1С.

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

В задании выберем:

·         Полный просмотр;

·         Вся собранная статистика.

Все задания не забываем сохранять. После формирования задания необходимо проверить чтоб был запущен Агент SQL сервера.

Специалист компании ООО «Кодерлайн»

Васецкий Александр Александрович