Реиндексация sql базы: DBCC DBREINDEX (Transact-SQL) — SQL Server

Регламентные операции СУБД MS SQL Server

Общая информация

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

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

Выполняться регламентные процедуры должны полностью автоматически. С целью автоматизации операций рекомендовано использовать специальные средства, встроенные в  MS SQL Server: MaintenancePlan. Несмотря на то, что существуют и другие способы автоматизировать выполнение  процедур, в этой статье для всех регламентных процедур даны примеры настройки с помощью MaintenancePlan от MS SQL Server 2008.

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

• Очистка процедурного КЭШа
• Обновление статистик
• Реиндексация таблиц баз данных
• Дефрагментация индексов

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

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

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

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

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

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

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

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

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

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

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

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

Теперь создавайте субплан (команда Add Sublan) и называйте его, к примеру, «Обновление статистик». Добавляйте задачу Update Statistics Task, воспользовавшись  панелью задач:

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

Осталось настроить параметры задачи. Нужно два раза кликнуть по задаче в нижнем правом углу окна. В форме, которая появится на экране, необходимо указать имя вашей базы (либо нескольких баз), для которых будет осуществляться процесс обновления статистик. Также вы сможете указать, для каких конкретно таблиц необходимо обновлять статистики (устанавливайте значение 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 (<здесь имя базы данных>, ''?'')'

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Контроль за выполнения регламентных процедур на СУБД уровне

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

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

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

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

Составление планов обслуживания SQL для нужд 1С: Предприятия 8.

х / Хабр

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

1С: Предприятием, решил поделиться опытом со всеми сразу.

Зачем это надо — если в sql не обслуживать базы данных, то его смысл теряется вовсе. Основной инструмент — индексы и их надо держать в актуальном состоянии. Каких-то догматов я не встретил не в практике, не в нете, не на курсах в самой 1С, а потому делюсь своим опытом.

Зачастую база работает в «нормальных» условиях. Что под этим подразумевается:

• Сервер SQL хорошо «питается», т.е. объем ОЗУ предоставляемой для работы SQL сервера выбирать из расчёта 70% от размера всех mdf файлов баз данных.
• Процессор не загружен более чем на 50% в течении 90% времени.
• Имеется достаточное место на дисках (в частности для сортировки используется база temp.db, 1С ее использует вообще для всей своей жизнедеятельности, потому стоит заранее озаботиться местом на диске с этой базой).
• Режим восстановления базы данных — «Простой». (Эмпирически выяснено, что большой ldf файл тормозит 1с-ку, а возможность восстановления по лог-файлу весьма сомнительна).

Так же стоит учитывать несколько нюансов:

• При использовании Standard редакции SQL, при полном перестроении индекса, все пользователи будут отключены от базы, потому стоит это учитывать при решении проведения Weekly плана обслуживания (план будет описан ниже).
• Стоит учитывать, что сервер 1С тоже потребляет память, особенно если используются тонкие клиенты или веб-службы.
• Самому SQL лучше ограничить в параметрах сервера максимальный объем ОЗУ, дабы по достижению критической массы, он заранее начинал очищать ненужные данные из ОЗУ. Да и чтоб разрастаясь не вгонять весь сервер в ступор.

Рационально при нормальных условиях использовать 2 плана обслуживания Weekly (раз в неделю) и Daily (в остальные 6 дней недели).

Weekly

Общий вид

По пунктам плана обслуживания:

1. Перестроение индекса. Смысл задачи в удалении всех имеющихся индексов и установки новых. (грубо говоря инвентаризация и расстановка всего по порядку).
   
   В качестве параметров:
   • Выбор целевой базы (это будет почти во всех задачах, потому далее на этот параметр я не буду обращать внимание в пределах этой статьи).
   • Объект, в котором мы выбираем «Таблицы и представления».
   • Параметры свободного места – при малом объеме жесткого диска можно выбирать пункт «по умолчанию», однако я рекомендую использовать «Изменить долю свободного места на странице», рекомендуемое значение 20%. Это позволит оставить запас свободных страниц, и позволит дольше держать индексы в актуальном состоянии. ВНИМАНИЕ: Увеличивает размер базы данных.
   • Отсортировать результаты в tempdb. Думаю пояснять не требуется, однако предупредить хочу, в это время tempdb, будет очень сильно разрастаться, хоть и сортировка в ней и призвана ускорить процесс, будьте осторожны, имейте запас пространства.
   • Сохранять индекс в режиме «в сети» — фишка доступная для enterprise версии SQL. Позволяет делать переиндексацию без отключения клиентов.

   !!! ВНИМАНИЕ!!! В Standard версии при переиндексации происходит отключение клиентов от базы данных на время работы данного шага.Пример настроек

2. Обновление статистики. Задача сбора информации о состоянии индексов в базе. (В общем-то мало актуальная после переиндексации, но все же я делаю).
   
   Параметры:
   • Объект. Все те же таблицы и представления, что и для перестроения индекса.
   • Обновить. Тут обновляем всю статистику.
   • Тип просмотра – Полный просмотр.

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

3. Выполнение инструкции T-SQL. Это выполнение произвольной команды на языке SQL, в частности нас интересует

   dbcc proccache
   

   Как следует из название – чистка кэша. Пример

4. Проверка целостности базы данных. Тут кажется излишни пояснения – убеждаемся, что ничего не сломалось. В параметрах «включаем индексы» в проверку, не зря же перестраивали.Пример настроек

5. Резервное копирование базы данных. Тут поговорить надо побольше, ввиду многих особенностей. Лучше изучить данный пункт отдельно самостоятельно в других руководствах, формат данной статьи не предусматривает углубленного изучения резервного копирования.
   
   Но о паре нюансов хочу предупредить:
   • SQL не умеет чистить контейнер свой, потому если добавлять резервные копии в файл (оно же обзывается «Устройство резервного копирования»), в итоге забьете все свободное место.
   • SQL помнит о своих резервных копиях, потому сделав ручками бэкап, единоразовый (например, отнести базу в другое место, или чтоб развернуть для теста в еще одну базу из бэкапа), следующий «разностный» будет отсчитываться от него. Дабы предотвратить это, требуется ставить галочку «Только резервное копирование». В задаче резервного копирования такого пункта нет. Вообще в недельном плане рекомендую все же использовать полный тип резервной копии.
   • И хорошо бы проверять копию, пусть спиться спокойнее.
   • Сжатие, в общем-то, использовать можно, но будьте аккуратны, разностные тогда надо тоже сжимать.

   Пример настроек

6. Очистка журнала.
   • Журнал резервного копирования и восстановления.
   • Журнал заданий агента SQL Server.
   • Журнал плана обслуживания.

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

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

Daily

Общий вид

Говорить отдельно не имеет смысла. Почти все аналогично Weekly.

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

Параметры

Так же можно использовать разностное резервное копирование.

На этом все. Повторяюсь, догматов в этом моменте я не видел, этот вариант был разработан и протестирован мной. Актуально для баз размером от 6 до 100 ГБ.

Желаю Вам быстрой и надежной работы.

P.S. В силу того что не являюсь полноценным DBA, возможно мои комментарии весьма поверхностны, с удовольствием прочту замечания в комментариях и личке.

REINDEX — Справочные материалы по базе данных SQL

REINDEX — Справочные материалы по базе данных SQL — Изучите sql, прочитайте руководство по sql, следуйте учебнику по sql или узнайте, как структурировать запрос SQL!

 REINDEX { TABLE | БАЗА ДАННЫХ | ИНДЕКС }  имя  [ СИЛА ]
   

 ПЕРЕИНДЕКС ТАБЛИЦА mytable;
    

 ИНДЕКС ПЕРЕИНДЕКС my_index;
    

 REINDEX DATABASE my_database FORCE;