Rebuild ms sql index: Reorganize and Rebuild Indexes in the Database
Содержание
3 метода перестроения всех индексов для всех таблиц с помощью T-SQL в базе данных SQL Server
Автор Нисарг Упадхьяй• 26 марта 2021 г.•
12:36•
Разработка баз данных, Индексы, Языки и кодирование, T-SQL, Таблицы
ГлавнаяРазработка баз данных, Индексы, Языки и кодирование, T-SQL, Таблицы3 Методы перестроения всех индексов для всех таблиц с помощью T-SQL в базе данных SQL Server
Существует несколько методов перестроения всех индексов всех таблиц в SQL Server, среди их:
- Использование планов обслуживания SQL Server.
- Использование сценария T-SQL на основе процента фрагментации.
- Использование команды ALTER INDEX.
В этой статье мы рассмотрим эти методы и проиллюстрируем их практическими примерами.
1.
План обслуживания перестроения индексов SQL Server
Первый вариант проверки — это перестроение индексов с помощью планов обслуживания базы данных.
Планы обслуживания доступны в папке управления SQL Server Management Studio.
Для создания плана обслуживания базы данных SQL, запуска SQL Server Management Studio > Расширение экземпляра базы данных > Управление > Правый класс. План .
Укажите имя плана обслуживания. Затем перетащите Задача восстановления индекса в конструктор плана обслуживания. Переименуйте задачу в Ведение индекса .
Следующим шагом является настройка плана обслуживания. Дважды щелкните по нему и настройте параметры Rebuild Index Task следующим образом:
- Выберите базу данных AdventureWorks2017 в раскрывающемся меню База данных.
- Чтобы перестроить индексы всех таблиц, выберите Таблицы и представления в раскрывающемся списке Объект .
- Проверка Сортировка результатов в tempdb .
- MAXDOP – комплект 2 (два).
- В нашем случае мы будем перестраивать индексы, только если значение Фрагментация превышает 20%. Поэтому задайте 20 в соответствующем поле.
- Щелкните OK , чтобы сохранить конфигурацию индекса и закрыть окно Задача перестроения индекса .
Теперь настроим расписание.
Щелкните значок календаря в верхней части конструктора планов обслуживания:
Откроется окно Новое расписание работ . Настроим следующие параметры:
- Запускать задание каждый день. В меню Тип расписания выбираем Повторяющееся . Затем в разделе Frequency выбираем Occurs > Daily .
- Повторяется каждые > 1 (день).
- Ежедневная частота > Происходит один раз в > укажите точное время. В нашем случае это 1 час ночи.
- Нажмите OK .
После этого сохраните план обслуживания.
Созданные планы обслуживания доступны в каталоге SSMS Maintenance Plan . Чтобы просмотреть расписание, связанное с конкретным планом обслуживания, установите флажок 9.0025 Задания Каталог под Агент SQL Server .
Чтобы протестировать задание, щелкните правой кнопкой мыши его имя в каталоге Планы обслуживания и выберите в меню Выполнить :
Выполнение начнется. После успешного завершения вы увидите следующее диалоговое окно:
Это был распространенный метод перестроения индексов с планами обслуживания.
Теперь перейдем к следующему способу — с помощью скриптов T-SQL.
2.
SQL Server ALTER INDEX Rebuild
Команда ALTER INDEX может использоваться для перестроения всех индексов таблицы. Синтаксис следующий:
ALTER INDEX ALL ON [имя_таблицы] REBUILD
Примечание. Параметр имя_таблицы указывает имя таблицы, в которой мы хотим перестроить все индексы в SQL Server.
Например, мы хотим перестроить все индексы [HumanResources].[Employee] . Запрос должен быть следующим:
использовать AdventureWorks2017 идти ALTER INDEX ALL ON [HumanResources].[Employee] REBUILD Идти
3.
Сценарий SQL Server для перестроения всех индексов для всех таблиц на основе фрагментации
Обслуживание индексов требует больших ресурсов. Кроме того, он блокирует таблицу, в которой перестраивает индекс. Чтобы избежать таких осложнений, мы должны перестроить индекс, где фрагментация индекса SQL Server выше 40%.
Чтобы проиллюстрировать этот случай, я создал сценарий T-SQL, который перестраивает индексы со степенью фрагментации выше 30%. Давайте рассмотрим его части и функции.
Объявление переменных и временной таблицы
Во-первых, нам нужно создать временные таблицы и переменные:
- @IndexFregQuery — хранит динамический запрос, используемый для заполнения фрагментированных индексов.
- @IndexRebuildQuery — содержит запрос ALTER INDEX.
- @IndexName — имя индекса, который мы хотим перестроить
- @TableName — имя таблицы, для которой мы хотим перестроить индекс.
- @SchemaName — имя схемы, в которой мы хотим перестроить индекс.
- #Fregmentedindex — таблица с тремя столбцами , в которой хранятся имя индекса, имя таблицы и имя схемы.
Следующий код объявляет наши переменные и временную таблицу:
declare @i int=0 объявить @IndexCount int объявить @IndexFregQuery nvarchar (макс.
)
объявить @IndexRebuildQuery nvarchar (макс.)
объявить @IndexName varchar (500)
объявить @TableName varchar (500)
объявить @SchemaName varchar (500)
создать таблицу #Fregmentedindex(имя_индекса varchar(max),имя_таблицы varchar(max),имя_схемы varchar(max))
Получить список фрагментированных индексов
Следующим шагом будет заполнение списка индексов со степенью фрагментации 30% и выше. Мы должны вставить эти индексы в таблицу #FregmentedIndexes .
Запрос должен заполнить имя схемы, имя таблицы и имя индекса, чтобы вставить их во временную таблицу. Взгляните на этот запрос:
set @IndexFregQuery='SELECT i.[name],o.name,sch.name
FROM [' + @DatabaseName + '].sys.dm_db_index_physical_stats (DB_ID('''+ @DatabaseName +'''), NULL, NULL, NULL, NULL) AS s
ВНУТРЕННЕЕ СОЕДИНЕНИЕ [' + @DatabaseName + '].sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN [' + @DatabaseName + ']. sys.objects AS o ON i.object_id = o.object_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ [' + @DatabaseName + '].sys.schemas AS sch ON o.schema_id=sch.schema_id
ГДЕ (s.avg_fragmentation_in_percent > 30) и i.name не равно null'
вставить в #Fregmentedindex(имя_индекса,имя_таблицы,имя_схемы) exec sp_executesql @IndexFregQuery
sys.objects AS o ON i.object_id = o.object_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ [' + @DatabaseName + '].sys.schemas AS sch ON o.schema_id=sch.schema_id
ГДЕ (s.avg_fragmentation_in_percent > 30) и i.name не равно null'
вставить в #Fregmentedindex(имя_индекса,имя_таблицы,имя_схемы) exec sp_executesql @IndexFregQuery
Создать динамический SQL-запрос
Наконец, мы должны создать динамическую команду ALTER INDEX и выполнить ее.
Для генерации команды мы используем цикл WHILE. Он перебирает таблицу #FregmentedIndexes и заполняет имя схемы, имя таблицы и имя индекса, чтобы сохранить их в @SchemaName , @TableName и @IndexName . Значения параметров добавляются в команду ALTER INDEX.
Код следующий:
set @IndexCount=(выберите count(1) из #Fregmentedindex) Пока (@IndexCount>@i) начинать (выберите первую 1 @TableName=table_name, @IndexName=Index_name, @SchemaName= schema_name из #Fregmentedindex) Установите @IndexRebuildQuery = 'Изменить индекс [' + @IndexName +'] на ['[email protected] +'].
['[email protected]+'].[' + @TableName +'] перестроить'
exec sp_executesql @IndexRebuildQuery
установить @[email protected]+1
удалить из #Fregmentedindex, где [электронная почта защищена] и [электронная почта защищена]
Конец
Я инкапсулировал весь код в хранимую процедуру sp_index_maintenance , созданную в базе данных DBATools . Код следующий:
используйте DBATools
идти
Создайте процедуру sp_index_maintenance_daily
@DatabaseName varchar(50)
как
начинать
объявить @i int=0
объявить @IndexCount int
объявить @IndexFregQuery nvarchar (макс.)
объявить @IndexRebuildQuery nvarchar (макс.)
объявить @IndexName varchar (500)
объявить @TableName varchar (500)
объявить @SchemaName varchar (500)
создать таблицу #Fregmentedindex(имя_индекса varchar(max),имя_таблицы varchar(max),имя_схемы varchar(max))
set @IndexFregQuery='SELECT i.[name],o.name,sch.name
FROM [' + @DatabaseName + '].sys.dm_db_index_physical_stats (DB_ID('''+ @DatabaseName +'''), NULL, NULL, NULL, NULL) AS s
ВНУТРЕННЕЕ СОЕДИНЕНИЕ [' + @DatabaseName + ']. sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN [' + @DatabaseName + '].sys.objects AS o ON i.object_id = o.object_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ [' + @DatabaseName + '].sys.schemas AS sch ON o.schema_id=sch.schema_id
ГДЕ (s.avg_fragmentation_in_percent > 30) и i.name не равно null'
вставить в #Fregmentedindex(имя_индекса,имя_таблицы,имя_схемы) exec sp_executesql @IndexFregQuery
set @IndexCount=(выберите количество(1) из #Fregmentedindex)
Пока (@IndexCount>@i)
начинать
(выберите первую 1 @TableName=table_name, @IndexName=Index_name, @SchemaName= schema_name из #Fregmentedindex)
Установите @IndexRebuildQuery = 'Изменить индекс [' + @IndexName +'] на ['[email protected] +'].['[email protected]+'].[' + @TableName +'] перестроить'
exec sp_executesql @IndexRebuildQuery
установить @[email protected]+1
удалить из #Fregmentedindex, где [электронная почта защищена] и [электронная почта защищена]
Конец
Конец
sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN [' + @DatabaseName + '].sys.objects AS o ON i.object_id = o.object_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ [' + @DatabaseName + '].sys.schemas AS sch ON o.schema_id=sch.schema_id
ГДЕ (s.avg_fragmentation_in_percent > 30) и i.name не равно null'
вставить в #Fregmentedindex(имя_индекса,имя_таблицы,имя_схемы) exec sp_executesql @IndexFregQuery
set @IndexCount=(выберите количество(1) из #Fregmentedindex)
Пока (@IndexCount>@i)
начинать
(выберите первую 1 @TableName=table_name, @IndexName=Index_name, @SchemaName= schema_name из #Fregmentedindex)
Установите @IndexRebuildQuery = 'Изменить индекс [' + @IndexName +'] на ['[email protected] +'].['[email protected]+'].[' + @TableName +'] перестроить'
exec sp_executesql @IndexRebuildQuery
установить @[email protected]+1
удалить из #Fregmentedindex, где [электронная почта защищена] и [электронная почта защищена]
Конец
Конец
Когда процедура готова, мы можем настроить задание SQL.
Разверните Агент SQL Server > щелкните правой кнопкой мыши Задания > Новое задание .
Откроется окно Новое задание , в котором необходимо указать желаемое имя задания.
Чтобы создать шаг задания, перейдите в раздел Steps > кнопка New :
Вы перейдете к New Job Step окно для настройки этого шага.
Введите желаемое имя шага и введите следующий код в текстовое поле:
используйте DBATools идти exec sp_index_maintenance_daily 'AdventureWorks2017'
Чтобы настроить расписание, перейдите к Расписания > щелкните Новый .
Наша работа должна быть выполнена в час ночи. Соответственно настраиваем расписание:
- Тип расписания > Повторяющийся .
- Частота Раздел > Происходит > Ежедневно ; Повторяется каждые > 1 (один).
- Ежедневная частота раздел > Происходит один раз в > 01:00:00.
- Нажмите OK .
Вас переведут обратно в 9-й0025 Новая работа раздел. Нажмите OK там же, чтобы создать задание.
Новое задание доступно в каталоге Jobs в папке SQL Server Agent .
Теперь нам нужно протестировать задание: щелкните его правой кнопкой мыши и выберите Начать задание…
Задание запускается, и при успешном завершении вы увидите следующее сообщение:
Если сравнить кластеризованный индекс с некластерный индекс, мы увидим, что создание уникального кластерного индекса повышает эффективность выполнения запросов.
Индексированные представления в SQL Server могут использоваться для ускорения выполнения запросов. Узнайте также, как управлять индексами с помощью SQL Index Manager.
Резюме
В данной статье представлены три функциональных способа перестроения индексов всех таблиц. Мы изучили их все с пошаговыми инструкциями и практическими примерами индекса SQL, чтобы проиллюстрировать конфигурацию задания. Выбор подходящего варианта остается за вами, и мы надеемся, что эта статья была вам полезна.
Теги: индексы, перестроить индексы, сервер sql Последнее изменение: 24 сентября 2021 г.
sql server — Постоянное перестроение индексов для исправления производительности — почему?
Я полностью с вами, «это устраняет скорее симптом, чем первопричину». Но это «исправление» посредством перестроения индекса имеет множество недостатков. Какие недостатки? Я рад, что вы спросили:
- перестроение индекса — это полностью зарегистрированная операция — ваш T-LOG и его резервные копии (при условии, что модель восстановления = полная) растут
- , если у вас есть планы запросов в кеше планов выполнения на вашем SQL-сервере, и эти планы касаются перестроенных индексов, они помечаются для перекомпиляции, поэтому создается новый план
- во время перестроения вы увеличиваете нагрузку на ввод-вывод и ЦП на свой сервер
- , если вы не используете корпоративную версию SQL, вы не можете перестроить С ONLINE, поэтому вы можете вызвать дополнительную блокировку
объявление.
2) — здесь может показаться, что вы «решили» проблему с перестроением индекса, но перестроение индекса скорее может быть следствием лучшего, более адаптированного плана выполнения для проблемного запроса/запросов.
Если в следующий раз вы столкнетесь с медленным сервером и захотите убедиться, что фрагментация индекса не является основной причиной вашей проблемы, вы можете выбрать план Б вместо перестроения индекса в следующий раз.
План Б
Если у вас возникла эта чрезвычайная ситуация и вы хотите запустить перестроение индекса, попробуйте вместо этого DBCC FREEPROCCACHE, но редко используйте его (желательно только один раз, чтобы доказать правоту). DBCC FREEPROCCACHE удаляет все планы из кэша планов, и у вас есть шанс получить планы, лучше адаптированные к текущей рабочей нагрузке. Если ваша аварийная ситуация прекращается после того, как SQL освобождает кеш плана, то вы доказали, что перестроение индекса не помогает само по себе, а скорее вызывает эффект, аналогичный DBCC FREEPROCCACHE, но с DBCC FREEPROCCACHE, в отличие от перестроения индекса, вы не испытываете недостатков 1-4.
описано выше.
Однако я настоятельно не рекомендую часто запускать DBCC FREEPROCCACHE для «помощи» серверу. Это плохая практика.
Как решить проблему более устойчивым способом, скорее выходит за рамки этого поста, но дайте мне знать, если вас заинтересуют дополнительные ссылки по этой теме, пожалуйста (или воспользуйтесь поиском в Google).
РЕДАКТИРОВАТЬ :Если вы все-таки подтвердите, что DBCC FREEPROCCACHE имеет тот же эффект, что и перестроение индекса (все внезапно начинает работать быстрее), то я осмелюсь сказать, что вы страдаете от прослушивания параметров. У Эрланда Соммарскога есть отличная статья об этом: страница Эрланда
Еще один аргумент в пользу того, почему бы не запустить перестроение индекса, заключается в том, чтобы в первую очередь проверить, насколько фрагментированы индексы. Отличная статья с T-SQL для проверки фрагментации: sqlshack
Кроме того, для полноты картины, DBCC FREEPROCCACHE — не единственный вариант удаления планов выполнения из кеша, и вы можете удалить только 1 план из кеша, если вы иметь дескриптор плана SQL.