Что такое реиндексация базы данных

Что такое реиндексация базы данных

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

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

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

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

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

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

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

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

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

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

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

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

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

Оптимальную частоту обновления статистик определяют в зависимости от характера и величины нагрузки на систему. Она определяется экспериментально. Обычно рекомендуется проводить обновление статистик не реже раза в 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 запрос:

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

Настройка очистки КЭШа в MS SQL 2005

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

Откройте субплан и добавьте в его схему задачу под названием Execute T-SQL Statement Task. Далее соедините задачу с Update Statistics Task при помощи стрелочки.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Регламентные работы на сервере MS SQL Server

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

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

Читайте также:  Удаление сообщений на айфоне

Основные регламентные работы на сервере MS SQL:

Назначение и периодичность регламентных процедур

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

Любые регламентные работы имеет смысл только со “здоровой” базой данных, а для этого необходимо для проверки размещения и структурной целостности таблиц и индексов предварительно провести Проверку целостности базы данных.

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

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

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

Рекомендованный период: не реже 1 раза в сутки.

Очистка процедурного кэша

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

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

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

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

Рекомендованный период: не реже 1 раза в неделю, а при большой нагрузке и раз в сутки.

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

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

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

Рекомендованный период: не реже 1 раза в неделю.

Резервное копирование баз

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

Рекомендуемый период: не реже 1 раза в сутки.

Настройка регламентных работ

Настройка регламентных работ на SQL-сервере проводим в MS SQL Server Management Studio. Подключаемся к сервер и заходим в папку “Управление -> Планы обслуживания”. Создать план обслуживания можно “вручную” или при помощи мастера, часто получается комбинация этих способов.

Обновление статистики и Очистку процедурного кэша делаем в одном плане, например раз в сутки на час ночи. Обновление статистики делаем при помощи мастера для всех баз, открываем полученное задание и добавляем с Панели элементов еще один элемент «Задача “Выполнение инструкции T-SQL”». Открыв двойным щелчком, прописываем в него скрипт для очистки кеша, а затем соединяем стрелочкой для указания правильной последовательности выполнения.

Задачи Дефрагментация индексов и Реиндексация таблиц – это по-сути, некоторым образом две взаимоисключающие задачи, поскольку обе выполняют дефрагментацию индексов таблиц баз данных. Поэтому Реиндексацию согласно рекомендации можем проводить раз в неделю в воскресенье ночью, а Дефрагментацию среди недели. Можно сроки цикличности варьировать, можно разделить объекты на группы и задавать частоту заданий отдельно для каждой. В любом случае необходимо руководствоваться здравым смыслом и степенью нагрузка на базы и их таблицы. Для того, что бы просмотреть, какие операции и с какой периодичностью требуются индексу, необходимо периодически проверять физическую статистику индекса: правой кнопкой мыши на базе данных и переходим в Отчеты -> Стандартные отчеты -> Физическая статистика индекса.

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

Оптимизация выполнения регламентных работ

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

Читайте также:  Как установить терморегулятор на холодильник

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

Более детально об оптимизации регламентных работ – в нашей следующей статье.

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

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

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

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

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

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

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

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

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

Затвитить пост!

Похожие записи:

При модели восстановления «Полная» начинает распухать лог-файл базы и соответственно бекап лога. Например, на базе 90Гб лог файл пухнет без бекапа на 80Гб, а с бекапом надо иметь ввиду, что эти 80Гб могут исчерпать дисковое пространство. Кстати, время выполнения ребилда около 3,5 часов

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

Дисковое пространство нынче не так уж и дорого, а долгие бэкапы можно хранить например в облаке для архивов (холодное хранение) у того же mail.ru, сервис называется IceBox и стоит 3 руб./Гб в месяц.

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

Регулярный бекап с полной моделью размазывает этот восьмидесятимегабайтный лог по кускам, но размер от этого не становится меньше, даже больше, учитывая перекрытия. Куча новичков бегут с советом поменять модель на «Простая», не понимая в чём различия.

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

Подпишись на RSS!

Подпишись на RSS и получай обновления блога!

Получать обновления по электронной почте:

Новое в блоге

Рейтинг заметок

  • Памяти Стива Джобса(5,00 out of 5)
  • Ежедневник(4,00 out of 5)
  • Завтрак(3,00 out of 5)
  • Фильтры сообщений в Mozilla Thunderbird(2,50 out of 5)
  • Сетевые адаптеры intel 82575eb и windows server 2008r2(2,50 out of 5)
  • Просмотры

    • Настройка многопользовательских сетевых ключей 1С — 34 801 просмотров
    • Перенос баз PostgreSQL на другой диск Windows — 21 890 просмотров
    • В windows 7 не сохраняется пароль RDP — 21 622 просмотров
    • 1С, ошибка «Недостаточно памяти» — 15 416 просмотров
    • Как изменить стандартное расположение файлов .pst в Outlook 2010 — 14 517 просмотров
    • Указанный сервер не может выполнить требуемую операцию — 11 784 просмотров
    • Требуется ms sql server 6.5 service pack 5a — 11 542 просмотров
    • Публикация базы 1С на отдельном веб-сервере — 8 258 просмотров
    • Чтобы осуществить запрошенное действие, WordPress необходим доступ к вашему серверу — 7 921 просмотров
    • 1С + PostgreSQL — syntax error at or near «SECOND» at character. — 7 536 просмотров
    • Облако меток

      Для вывода облака тегов WP_Cumulus необходим Flash Player 9 или выше.

    • Ссылка на основную публикацию
      Что такое медиана числового ряда
      Среднее арифметическое ряда чисел – это сумма данных чисел, поделенная на количество слагаемых. Среднее арифметическое называют средним значением числового ряда....
      Что делать если игры не скачиваются
      Play Market — официальный магазин приложений для Андроида и главный источник загрузки новых игр и программ на смартфоны и планшеты...
      Что делать если заглючил планшет
      Если завис планшет леново, самсунг, асус, престижио, дигма и так далее, да еще и в самое неподходящее время радости конечно...
      Что такое номер ssid
      Компьютеры и телефоны уже давно прочно вошли в нашу жизнь. Помимо смартфонов и ноутбуков, существуют еще десятки устройств, которые имеют...
      Adblock detector