Реиндексация 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 | БАЗА ДАННЫХ | ИНДЕКС }  имя  [ СИЛА ]
   

Входы

ТАБЛИЦА

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

БАЗА ДАННЫХ

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

ИНДЕКС

Повторное создание указанного индекса.

имя

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

FORCE

Принудительное перестроение системных индексов. Без этого ключевого слова
REINDEX пропускает системные индексы, которые не помечены как недействительные.
FORCE не имеет значения для REINDEX INDEX или при переиндексации
пользовательские индексы.

Выходы

ПЕРЕИНДЕКС

Сообщение возвращается, если таблица успешно переиндексирована.

Описание

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

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

Если вы подозреваете повреждение индекса пользовательской таблицы, вы можете
просто перестройте этот индекс или все индексы в таблице, используя
REINDEX INDEX или REINDEX TABLE.

Примечание: Другой подход к работе с поврежденным индексом пользовательской таблицы:
просто бросить и воссоздать его. На самом деле это может быть предпочтительнее, если
вы хотели бы поддерживать некоторое подобие нормальной работы на
стол тем временем. REINDEX получает эксклюзивную блокировку
в таблице, в то время как CREATE INDEX блокирует только запись
не читает таблицы.

Ситуация усложняется, если вам нужно восстановить поврежденный
индекс системной таблицы. В данном случае важно для бэкенда
выполнение восстановления, чтобы не использовать какие-либо подозрительные индексы.
(Действительно, в таком сценарии вы можете обнаружить, что серверные части
сбой сразу при запуске из-за зависимости от поврежденного
индексы.) Для безопасного восстановления постмастер должен быть закрыт, а
автономный бэкенд PostgreSQL должен быть
вместо этого начал, давая ему
параметры командной строки -O и -P (эти параметры позволяют
модификаций и предотвращения использования системных индексов соответственно). Затем
выдать REINDEX INDEX, REINDEX TABLE или
ПЕРЕИНДЕКСИРОВАНИЕ БАЗЫ ДАННЫХ в зависимости от того, сколько вы хотите реконструировать.
Если вы сомневаетесь, используйте REINDEX DATABASE FORCE для принудительной реконструкции.
всех системных индексов в базе данных. Затем выйдите из автономного бэкэнда
и перезапустите почтмейстер.

Так как это, вероятно, единственная ситуация, когда большинство людей будут использовать
автономный бэкэнд, некоторые примечания по использованию могут быть в порядке:

  • Запустите бэкэнд командой вроде

      postgres -D $PGDATA -O -P my_database  

    Укажите правильный путь к области базы данных с помощью -D или
    убедитесь, что установлена ​​переменная среды PGDATA.
    Также укажите имя конкретной базы данных, в которой вы хотите работать.

  • Вы можете выполнить любую команду SQL, не только REINDEX.

  • Имейте в виду, что автономный бэкенд обрабатывает новую строку как команду
    терминатор входа; нет интеллигентности про точки с запятой,
    как в psql. Чтобы продолжить команду
    в нескольких строках необходимо вводить обратную косую черту непосредственно перед каждой
    новая строка, кроме последней.
    Кроме того, у вас не будет никаких удобств редактирования в командной строке.
    (например, без истории команд).

  • Чтобы выйти из бэкенда, введите EOF ( обычно Control + D ).

См. справочную страницу postgres для получения дополнительной информации.

Использование

Воссоздайте индексы для таблицы mytable :

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

Перестроить один индекс:

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

Перестроить все системные индексы (это будет работать только в автономном бэкенде):

 REINDEX DATABASE my_database FORCE;
    

Совместимость

SQL92

В SQL92 нет REINDEX.




Главная
Интернет-ресурсы
Общее
Учебники для начинающих
MySQL
PostgreSQL
Oracle
mSQL
Microsoft SQL
Свяжитесь с нами

Случайная котировка момента:

Годы пика умственной активности, несомненно, приходятся на возраст от четырех лет.
и восемнадцать. В четыре мы знаем все вопросы, в восемнадцать — все ответы.

 
http://www.sql.org/
 
С гордостью разработано и размещено OmegaSphere, поставщиками всех ваших потребностей в веб-хостинге, сертификатах SSL и доменных именах!

sql server — Плохо ли сжимать + переиндексировать после удаления значительной части данных?

спросил

Изменено
5 лет назад

Просмотрено
2к раз

У меня есть тестовая среда, работающая на SQL Server 2017 Express, которая использует только последние данные (задание удаляет данные старше трех месяцев один раз в месяц).

Задание выполняет следующие действия:

  1. С помощью цикла while удаляются данные старше трех месяцев
  2. Уменьшает базу данных, если ее размер превышает некоторый порог
  3. Переиндексирует все таблицы

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

Последний запуск начался с базы данных размером 9 ГБ. Удаление занимает 3-4 минуты, а переиндексация около 30 секунд. База данных была уменьшена примерно до 5 ГБ.

Поскольку это тестовая среда, я могу позволить себе этот простой без каких-либо проблем.

Вопрос: Плохо ли сжимать + переиндексировать после удаления значительной части данных?

  • sql-сервер
  • индекс
  • сокращение
  • sql-сервер-2017

5

Хорошо, спросите себя: какой смысл в сжатии, если вы знаете, что переиндексация (и любые другие изменения данных, на самом деле) снова расширит файл после сжатия?

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

В вашей ситуации (где вам нужно сохранить размер файла данных менее 10 ГБ из-за выбранной вами лицензии и ни по какой другой причине) нет смысла уменьшать размер до 5 ГБ только для того, чтобы снова увеличить его до 9 ГБ. Я бы предпочел иметь какой-нибудь активный монитор, который предупреждает вас, когда файл данных снова возвращается в пространство 9 ГБ (или просто держите его на уровне 9 ГБ!) и используемое пространство фактически приближается к 9 ГБ.

Вместо того, чтобы думать об этом как о требовании «сохранить последние три месяца», думайте об этом как о «сохранении 9Требование «ГБ данных». Которое вам придется пересматривать, поскольку объемы ваших данных за период времени (день/неделя/независимо) в любом случае будут расти.