Регламентные операции на уровне СУБД для MS SQL Server. Реиндексация sql базы

MS SQL Server. Оптимизация работы

Предисловие

Платформа 1С:Предприятие 8.x поддерживает работу с несколькими СУБД. Самым используемым является продукт компании Microsoft. В статье будут рассмотрены распространенные причины неоптимальной работы SQL-сервера и пути их решения на примере MS SQL Server 2008.

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

По опыту, самыми распространенными причинами неоптимальной работы SQL-сервера являются:

Неактуальная статистика о распределении значений и индексов в таблице базы данных.
Устаревание процедурного КЭШа планов запросов.
Высокая фрагментация индексов таблиц.
Периодическая необходимость в перестроении всех индексов таблиц.

Теперь по порядку.

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

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

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

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN' Наибольшее влияние статистики распределения данных на производительность СУБД можно увидеть с применением вложенных запросов. Если платформа 1С:Предприятие 8.x сформирует запрос к SQL-базе многоуровневой вложенности, плюс к этому еще и соединением двух вложенных запросов, то можно утверждать, что скорость его работы напрямую зависит от актуального состояния собранной статистики. Для поддержки статистик в актуальном состоянии рекомендуется создать регламентное задание, которое будет производить обновление статистик в указанное время. Поскольку данный процесс не блокирует таблицы БД, то его можно запускать практически в любое время. Ниже на видео демонстрируется настройка плана обслуживания по обновлению статистик средствами СУБД MS SQL Server 2008.

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

Очистка процедурного КЭШа

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

DBCC FREEPROCCACHE Ниже Вы можете посмотреть на видео процесс добавления к уже созданному субплану обновления статистик - задачу очистки процедурного кэша.

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

Дефрагментация индексов

Интенсивная работа с базой данных приводит к снижению эффективности использования индексов из-за фрагментации. Поэтому рекомендуется регулярно проводить дефрагментацию индексов. Если необходимо провести дефрагментацию для всех таблиц некоторой базы, то можно использовать следующую команду:

sp_msforeachtable N'DBCC INDEXDEFRAG ([ИМЯ БАЗЫ ДАННЫХ], ''?'')' Запуск процесса дефрагментации индексов не блокирует таблицы, что позволяет запускать в любое время. Однако стоит учитывать увеличение нагрузки на сервер СУБД. Рекомендуется проводить дефрагментацию не реже одного раза в неделю. Ниже представлено видео по настройке регламентной операции дефрагментации индексов для существующего плана обслуживания.

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

Реиндексация таблиц

Наиболее существенный прирост производительности можно получить за счет полного перестроения индексов. Реиндексацию рекомендуется выполнять регулярно. Стоит учитывать, что процедура проведения реиндексации таблиц БД блокирует. Чтобы работа пользователей не была остановлена, лучше всего проводить данную операцию вне рабочего времени. SQL-команда на запуск процесса перестроения индекса следующая:

sp_msforeachtable N'DBCC DBREINDEX (''?'')'

Ниже вы можете посмотреть видео по добавлению к существующему плану обслуживания задачи реиндексации таблиц БД.

После проведения реиндексации таблиц, выполнять дефрагментацию индексов нет необходимости.

Выводы

Выполнение рассмотренных регламентных операций позволит существенно повысить производительность сервера СУБД. Для контроля работы SQL-сервера рекомендуется просматривать журнал событий, где можно посмотреть статистику работы плана обслуживания по указанному расписанию.

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

devel1c.blogspot.com

Перечень необходимых задач регламентного обслуживания MS SQL Server — AUsevich

Использование клиент-серверного варианта работы системы 1С:Предприятие накладывает дополнительные обязательства по обслуживанию информационной системы. То, какие задачи и как часто должны выполняться для поддержания высокой производительности системы, мы рассмотрим в данной статье на примере СУБД MS SQL Server.

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

Процедуры обслуживания

Попробуем разобраться какие процедуры обслуживания нам необходимо выполнять и как часто. Для удобства объединим процедуры в связанные группы, впоследствии они станут субпланами обслуживания. О том как создать план обслуживания можно прочесть в статье «Механизм «Планы обслуживания» MS SQL Server».

Субплан «В течение дня»

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

Субплан «Ежедневный» (6 из 7 дней в неделе)

Для поддержания базы данных в эффективном для работы состоянии, необходимо ежедневно (в нерабочее время) выполнять следующие задачи:

Реорганизация/Дефрагментация индекса (Reorganize Index). Database Engine автоматически обновляет индекс при операциях INSERT, DELETE, UPDATE. Со временем эти операции могут привести к фрагментации индекса. Значительно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления. Таким образом, необходимо в план обслуживания включить задание по устранению фрагментации индексов. Альтернативой дефрагментации индексов является реиндексация/перестроение индекса (rebuild index), но она имеет такие недостатки как: значительно большее время выполнения; сброс статистики использования индексов, которая требуется для целей описанных в статье «Поиск отсутствующих индексов таблиц в базе на MS SQL Server». Также в качестве альтернативы возможно использование хранимой процедуры условного (от степени фрагментации) выбора способа устранения фрагментации (реорганизация/перестроение) индекса. Более подробно можно прочитать в «Реорганизация/дефрагментация индекса СУБД MS SQL Server»

Обновление статистики (Update Statistics). Скорость выполнения запроса зависит от построенного для него плана запроса, который, в свою очередь, опирается на информацию о существующих индексах, а также на статистику. Если статистика устарела, существует вероятность выбора не оптимального плана запроса, что приведет к снижению производительности. Таким образом, необходимо включить задание обновления статистики.
Разностное резервное копирование (Differential backup). Надеюсь, нет необходимости рассказывать почему надо делать периодическое резервное копирование базы данных, альтернативой разностному бэкапу является полное резервное копирование. Разностное резервное копирование включает в себя только изменения между текущим состоянием базы данных и состоянием базы данных на момент последнего полного резервного копирования, таким образом, задача разностного бэкапа выполняется быстрее, а сам файл занимает меньше места. В то же время есть и недостаток разностного бэкапа — без полного бэкапа он бесполезен.

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

Субплан «Еженедельный» (7-ой день недели)

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

Проверка целостности базы данных (Check Database Integrity). Используется для проверки размещения и структурной целостности пользовательских и системных таблиц, а также индексов в базе данных.
Реиндексация/Перестроение индекса (Rebuild Index). Достаточно долгая и требовательная операция. Она удаляет и заново строит индексы. В результате данной операции удаляется статистика использования индексов.
Обновление статистики (Update Statistics)
Full backup (Полное резервное копирование)
Очистка процедурного кэша

Субплан «Экстренное резервное копирование и сжатие транзакционного лога»

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

Создание плана обслуживания по данному перечню будет приведено в будущих статьях…

ausevich.ru

Реиндексация базы sql 1С

Обслуживание баз данных 1С на MS SQL сервере.

Индексация в Базах Данных

Тестирование и исправление 1С

Видеокурс по SQL Essential. Урок 7. Индексирование

SQL и 1с 8 - бэкап и восстановление из журналов транзакций

Как сделать тестирование и исправление информационной базы, реиндексацию, пересчет итогов, реструкту

SQL Урок 9 | VIEW Представление SQL | Для Начинающих

ВидеоОтветы 12 "При запуске 1С возникает внутренняя ошибка компоненты dbeng8"

Базы данных для начинающих. Внутренние индексы SQL таблицы. Таблицы WITHOUT ROWID в SQLite.

Как читать план запроса в SQL Server

Также смотрите:

1С поле табличного документа заполнить
1С как сравнить конфигурации
Сложности ведения бухгалтерского учета в 1С упп
1С макет расшифровка ячейки
Закрывается 1С при запуске
Харитонов 1С бухгалтерия
Обработки epf для 1С 8 2
Регистрационный номер программы 1С где посмотреть
Эмулятор для 1С сервер 64
1С язык платформы
1С редактирование в форме списка
1С присвоить дату
1С 8 3 поиск по началу строки
1С росстат код органа
Какие циклы бывают 1С 8

Главная » Видео » Реиндексация базы sql 1С

buhchita.ru

Технологические вопросы крупных внедрений

Краткое содержание:

Инструкция по выполнению регламентных операций на уровне СУБД.Информация применима к клиент-серверному варианту 1С:Предприятия 8 при использовании СУБД MS SQL Server.

Общие сведения

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

Если в работающей системе наблюдаются какие-либо симптомы проблем с производительностью, следует проверить, что в системе правильно настроены и регулярно выполняются все рекомендуемые регламентные операции на уровне СУБД.

Выполнение регламентных процедур должно быть автоматизировано. Для автоматизации этих операций рекомендуется использовать встроенное средства MS SQL Server: Maintenance Plan. Существуют так же другие способы автоматизации выполнения этих процедур. В настоящей статье для каждой регламентной процедуры дан пример ее настройки при помощи Maintenance Plan для MS SQL Server 2005.

Для MS SQL Server рекомендуется выполнять следующие регламентные операции:

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

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

MS SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, что MS SQL Server стабильно строил наиболее оптимальный план выполнения всех запросов.

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

Для того, чтобы гарантировать максимально правильную работу оптимизатора MS SQL Server рекомендуется регулярно обновлять статистики базы данных MS SQL.

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

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

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

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

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

Настройка автоматического обновления статистик (MS SQL 2005)

Запустите MS SQL Server Management Studio и подключитесь к серверу СУБД. Откройте папку Management и создайте новый план обслуживания:

Создайте субплан (Add Sublan) и назовите его «Обновление статистик». Добавьте в него задачу Update Statistics Task из панели задач:

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

Настройте параметры задачи. Для этого следует два раза кликнуть на задачу в правом нижнем углу окна. В появившейся форме укажите имя базу данных (или несколько баз данных) для которых будет выполняться обновление статистик. Кроме этого вы можете указать для каких таблиц обновлять статистики (если точно неизвестно, какие таблицы требуется указать, то устанавливайте значение All).

Обновление статистик необходимо проводить с включенной опцией Full Scan.

Сохраните созданный план. При наступлении указанного в расписании срока обновление статистик будет запущено автоматически.

Очистка процедурного КЭШа

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

Возможна ситуация, при которой MS SQL Server, ориентируясь на устаревшую статистическую информацию, построит неоптимальный план запроса. Этот план будет сохранен в процедурном КЭШе и использован при повторном вызове такого же запроса. Если Вы обновили статистику, но не очистили процедурный кэш, то SQL Server может выбрать старый (неоптимальный) план запроса из КЭШа вместо того, чтобы построить новый (более оптимальный) план.

Таким образом, рекомендуется всегда после обновления статистик очищать содержимое процедурного КЭШа.

Для очистки процедурного КЭШа MS SQL Server необходимо выполнить следующий SQL запрос:

DBCC FREEPROCCACHE

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

Настройка очистки процедурного КЭШа (MS SQL 2005)

Поскольку процедурный КЭШ необходимо очищать при каждом обновлении статистики, данную операцию рекомендуется добавить в уже созданный субплан «Обновление статистик». Для этого следует открыть субплан и добавить в его схему задачу Execute T-SQL Statement Task. Затем следует соединить задачу Update Statistics Task стрелочкой с новой задачей.

В тексте созданной задачи Execute T-SQL Statement Task следует указать запрос «DBCC FREEPROCCACHE»:

Дефрагментация индексов

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

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

sp_msforeachtable N'DBCC INDEXDEFRAG (<имя базы данных>, ''?'')'

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

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

Настройка дефрагментации индексов (MS SQL 2005)

В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Reorganize Index Task:

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

Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.

Реиндексация таблиц базы данных

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

sp_msforeachtable N'DBCC DBREINDEX (''?'')'

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

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

Настройка реиндексации таблиц (MS SQL 2005)

В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Rebuild Index Task:

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

Необходимо осуществлять регулярный контроль выполнения регламентных процедур на уровне СУБД. Ниже приведен пример контроля выполнения плана обслуживания для MS SQL Server 2005.

Откройте созданный вами план обслуживания и выберите из контекстного меню пункт «View History»:

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

Успешно выполненные задачи и задачи, выполненные с ошибками, будут помечены соответствующими иконками. Для задач, выполненных с ошибками, доступна подробная информация об ошибке.

ravepoint.narod.ru