Как переиндексировать базу sql

Name already in use

T-SQL / 001_Переиндексация_базы_данных.sql

• Go to file T
• Go to line L
• Copy path
• Copy permalink

• Open with Desktop
• View raw
• Copy raw contents Copy raw contents

Copy raw contents

Copy raw contents

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

SQL: Reindex a Database

Indexes in databases work kind of like an index in a book. Instead of having to look at each page in a book for something, you can just go to the index – find your topic in an alphabetized list, and go to the page number indicated. Databases use indexes so they do not have to look at every single row in a table that could contain hundreds of thousands up to billions of rows.

The problem is, with all the constant reading and writing to a live database, the indexes quickly become fragmented as they try to keep up will all the new data coming and going. After a while this fragmentation can start to have an effect on your database’s performance.

Rebuild an Index

***Don’t attempt this on a production database while it is in use. Make sure the database is not being used before trying anything in this lesson.

The act of defragging an index in SQL Server is known as rebuilding. You can do it which just a few mouse clicks.

First, let’s find our indexes. You can find Indexes nested under tables in the Object Explorer. I won’t go in depth on Clustered vs Non-Clustered, only know the each table can only have 1 Clustered Index. You can think of it as the master index for that table if it helps.

Right click on an index and go to Properties

Select Fragmentation from the Select a page window. Note my index is 66.67% fragmented.

Click out of that window and right click on your index again. This time click Rebuild

Click Okay and the window and your Index will be rebuilt. Simple enough.

Rebuild All Indexes in a Table

If you want to rebuild all the indexes in a table, you can click on the Index folder and click Rebuild All

Then click okay

Or you can use the follow SQL Code

Reindex Code

DBCC stands for Database Console Commands. It is a list of useful tools you can use to administer a SQL Server. The syntax below is as follows: DBCC DBREINDEX(TABLE NAME, Index you want to rebuild (‘ ‘ = all indexes) , fillfactor)

A quick note on fill factor. A fill factor of 0 or 100 tells SQL to fill every index page completely – leave no extra room. If the data was stagnant that could work, but when data is constantly being written and deleted, the indexes need room for correction. That is why you will often see 80 or 90 used as a fill factor. It gives a little wiggle room for the real life functionality of the database.

Reindex All the Tables

If you want to Reindex all the tables in a database, you can do it using a Cursor and While loop. If you do not know cursors in SQL, check out my previous lesson on cursors: SQL: Learn to use Cursors – List table names

The only new elements you will notice here is that I am combining TABLE_SCHEMA+’.’+TABLE_NAME. I give an example below to show you how it works.

Note, this query takes a few seconds (or minutes depending on speed of machine and database size) to run. You will not see anything until the query is completed.

TABLE_SCHEMA+’.’+TABLE_NAME

First result set, schema and table name are different columns. Second result set has them concatenated with a . in between.

Как переиндексировать базу sql

REINDEX — перестроить индексы

Синтаксис

Описание

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

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

Индекс стал « раздутым » , то есть в нём оказалось много пустых или почти пустых страниц. Это может происходить с B-деревьями в Postgres Pro при определённых, достаточно редких сценариях использования. REINDEX даёт возможность сократить объём, занимаемый индексом, записывая новую версию индекса без «мёртвых» страниц. За подробностями обратитесь к Разделу 23.2.

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

Параметры

Перестраивает указанный индекс. TABLE

Перестраивает все индексы в указанной таблице. Если у таблицы имеется дополнительная таблица « TOAST » , она так же переиндексируется. SCHEMA

Перестраивает все индексы в указанной схеме. Если таблица в этой схеме имеет вторичную таблицу « TOAST » , она также будет переиндексирована. При этом обрабатываются и индексы в общих системных каталогах. Эту форму REINDEX нельзя выполнить в блоке транзакции. DATABASE

Перестраивает все индексы в текущей базе данных. При этом обрабатываются также индексы в общих системных каталогах. Эту форму REINDEX нельзя выполнить в блоке транзакции. SYSTEM

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

Имя определённого индекса, таблицы или базы данных, подлежащих переиндексации. В настоящее время REINDEX DATABASE и REINDEX SYSTEM могут переиндексировать только текущую базу данных, так что их параметр должен соответствовать имени текущей базы данных. CONCURRENTLY

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

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

Выводит отчёт о прогрессе после переиндексации каждого индекса.

Замечания

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

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

Один из вариантов сделать это — выключить сервер Postgres Pro и запустить его снова в однопользовательском режиме, с параметром -P в командной строке. Затем можно выполнить REINDEX DATABASE , REINDEX SYSTEM , REINDEX TABLE или REINDEX INDEX , в зависимости от того, что вы хотите восстановить. В случае сомнений выполните REINDEX SYSTEM , чтобы перестроить все системные индексы в базе данных. Затем завершите однопользовательский сеанс сервера и перезапустите сервер в обычном режиме. Чтобы подробнее узнать, как работать с сервером в однопользовательском интерфейсе, обратитесь к справочной странице postgres .

Можно так же запустить обычный экземпляр сервера, но добавить в параметры командной строки -P . В разных клиентах это может делаться по-разному, но во всех клиентах на базе libpq можно установить для переменной окружения PGOPTIONS значение -P до запуска клиента. Учтите, что хотя этот метод не препятствует работе других клиентов, всё же имеет смысл не позволять им подключаться к повреждённой базе данных до завершения восстановления.

Действие REINDEX подобно удалению и пересозданию индекса в том смысле, что содержимое индекса пересоздаётся с нуля, но блокировки при этом устанавливаются другие. REINDEX блокирует запись, но не чтение родительской таблицы индекса. Эта команда также устанавливает блокировку ACCESS EXCLUSIVE для обрабатываемого индекса, что блокирует чтение таблицы, при котором задействуется этот индекс. DROP INDEX , напротив, моментально устанавливает блокировку ACCESS EXCLUSIVE на родительскую таблицу, блокируя и запись, и чтение. Последующая команда CREATE INDEX блокирует запись, но не чтение; так как индекс отсутствует, обращений к нему ни при каком чтении не будет, что означает, что блокироваться чтение не будет, но выполняться оно будет как дорогостоящее последовательное сканирование.

Для перестраивания одного индекса или индексов таблицы необходимо быть владельцем этого индекса или таблицы. Для переиндексирования схемы или базы данных необходимо быть владельцем этой схемы или базы. Заметьте, что вследствие этого в некоторых случаях не только суперпользователи могут перестраивать индексы таблиц, принадлежащих другим пользователям. Однако из этих правил есть исключение — когда команду REINDEX DATABASE , REINDEX SCHEMA или REINDEX SYSTEM выполняет не суперпользователь, индексы общих каталогов будут пропускаться, если только данный каталог не принадлежит этому пользователю (как правило, это так). Разумеется, суперпользователи могут переиндексировать всё без ограничений.

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

Неблокирующее перестроение индексов

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

Postgres Pro поддерживает перестроение индексов в режиме минимизации блокировок записи. Этот режим включается указанием CONCURRENTLY команды REINDEX . С данным указанием Postgres Pro должен выполнить два сканирования таблицы для каждого индекса, который нужно перестроить, и должен дождаться завершения всех активных транзакций, которые могут использовать данный индекс. В связи с этим в неблокирующем режиме производится в целом больше действий, и длительность переиндексирования значительно увеличивается. Однако благодаря тому, что во время перестроения индекса могут выполняться другие обычные операции, этот режим полезен, когда требуется перестроить индексы в производственной среде. Разумеется, другие операции могут несколько замедлиться из-за дополнительной нагрузки на процессор, память и ввод/вывод, связанной с перестроением индекса.

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

В каталог pg_index добавляется переходное определение индекса, которое затем заменит старое. Для предотвращения каких-либо изменений в схеме во время операции обрабатываемые индексы, а также связанные с ними таблицы защищаются блокировкой SHARE UPDATE EXCLUSIVE на уровне сеанса.

Для каждого нового индекса выполняется первый проход, на котором строится индекс. Когда индекс построен, его флаг pg_index.indisready переходит в состояние « true » , чтобы этот индекс был готов к добавлениям, и таким образом он становится видимым для других сеансов сразу после окончания построившей его транзакции. Это действие выполняется в отдельной транзакции для каждого индекса.

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

Все ограничения, ссылающиеся на индекс, переключаются на определение нового индекса, а также меняются имена индексов. В этот момент флаг pg_index.indisvalid нового индекса принимает значение « true » , а старого — « false » , и производится сброс кеша, в результате чего все сеансы, обращавшиеся к старому индексу, получают новую информацию.

Флаг pg_index.indisready старого индекса сбрасывается в « false » во избежание добавления в него новых кортежей, как только завершатся текущие запросы, которые могли обращаться к этому индексу.

Если при перестроении индексов возникает проблема, например нарушение уникальности в уникальном индексе, REINDEX прерывается, но оставляет после себя « нерабочий » новый индекс в дополнение к уже существующему. Этот индекс будет игнорироваться запросами, так как он может быть неполным; тем не менее он будет обновляться при изменении данных, что повлечёт дополнительные издержки. Команда psql \d будет обозначать такой индекс как INVALID (нерабочий):

Если имя индекса с пометкой INVALID оканчивается на ccnew , это переходный индекс созданный при параллельной операции, и для исправления ситуации рекомендуется удалить его, выполнив DROP INDEX , а затем попытаться ещё раз выполнить REINDEX CONCURRENTLY . Если же имя нерабочего индекса оканчивается на ccold , значит, это исходный индекс, удалить который по какой-то причине не получилось. Такой индекс рекомендуется просто удалить, так как нужный индекс был перестроен успешно.

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

Как и любая длительная транзакция, операция REINDEX с таблицей может повлиять на возможность удаления кортежей параллельной операцией VACUUM с какой-либо другой таблицей.

Команда REINDEX SYSTEM не поддерживает указание CONCURRENTLY , так как системные каталоги нельзя переиндексировать в неблокирующем режиме.

Более того, в неблокирующем режиме нельзя перестроить индексы, связанные с ограничениями-исключениями. Если явно указать имя такого индекса в команде, будет выдана ошибка. Когда в неблокирующем режиме переиндексируется таблица или база данных, содержащая такие индексы, эти индексы пропускаются. (Перестроить такие индексы можно в обычном режиме, без указания CONCURRENTLY .)

Примеры

Перестроение одного индекса:

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

Перестроение всех индексов в определённой базе данных, в предположении, что целостность системных индексов под сомнением:

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

Регламентные работы на сервере 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”». Открыв двойным щелчком, прописываем в него скрипт для очистки кеша, а затем соединяем стрелочкой для указания правильной последовательности выполнения.

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

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

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

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

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

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