I have a very large SQL Server 2008 R2 database (1.5TB) and will be copying some data from column to column within the same table. I’ve been told that the schema has a large number of indexes and was wondering if there is a default query or script that will rebuild all the indexes. Have also been advised to update the statistics at the same time?
Each of the 30 tables has one clustered index and 13x non-clustered indexes
Содержание
- 5 Answers 5
- Выявление фрагментации
- Ограничения
- Безопасность
- Разрешения
- Проверка фрагментации индекса
- Проверка фрагментации индекса
- Реорганизация или перестроение индекса
- Реорганизация всех индексов в таблице
- Перестроение индекса
- Реорганизация дефрагментированного индекса
- Реорганизация всех индексов в таблице
- Перестроение дефрагментированного индекса
- Перестроение всех индексов в таблице
- Перед началом работы Править
- Использование среды SQL Server Management Studio Править
- Использование Transact-SQL Править
- Использование среды SQL Server Management Studio Править
- Использование Transact-SQL Править
5 Answers 5
Try the following script:
Also
I prefer(After a long search) to use the following script, it contains @fillfactor determines how much percentage of the space on each leaf-level page is filled with data.
for more info, check the following link:
and if you want to Check Index Fragmentation on Indexes in a Database, try the following script:
For more information, Check the following link:
Also a good script, although my laptop ran out of memory, but this was on a very large table
Daniel’s script appears to be a good all encompassing solution, but even he admitted that his laptop ran out of memory. Here is an option I came up with. I based my procedure off of Mohammad Nizamuddin’s post on TechNet. I added an initial cursor loop that pulls all the database names into a temporary table and then uses that to pull all the base table names from each of those databases.
You can optionally pass the fill factor you would prefer and specify a target database if you do not want to re-index all databases.
Replace the "YOUR DATABASE NAME" in the query below.
ПРИМЕНЯЕТСЯ К: .a45b6379-ac63-48d7-ab97-33b3563b36ee(ru-ru).jpeg)
SQL Server (начиная с 2008) База данных SQL Azure Хранилище данных SQL Azure Parallel Data Warehouse
В этом разделе описывается реорганизация или перестроение фрагментированного индекса в SQL Server 2016 с помощью среды SQL Server Management Studio или Transact-SQL. Компонент Компонент SQL Server Database Engine автоматически поддерживает состояние индексов при выполнении операций вставки, обновления или удаления в отношении базовых данных. Со временем эти изменения могут привести к тому, что данные в индексе окажутся разбросанными по базе данных (фрагментированными). Фрагментация имеет место в тех случаях, когда в индексах содержатся страницы, для которых логический порядок, основанный на значении ключа, не совпадает с физическим порядком в файле данных. Значительно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления откликов приложения.
Можно устранить фрагментацию путем реорганизации или перестроения индекса. Для секционированных индексов, построенных на основе схемы секционирования, можно использовать любой из этих методов для всего индекса или отдельной его секции. При перестроении старый индекс удаляется, и создается новый. Таким образом, устраняется фрагментация, восстанавливается место на диске путем сжатия страниц с учетом указанного или существующего коэффициента заполнения, переупорядочиваются индексные строки в последовательных страницах. Если указывается ключевое слово ALL, то все индексы для таблицы удаляются и перестраиваются в одной транзакции. Для реорганизации индекса требуется минимальный объем системных ресурсов. При реорганизации концевой уровень кластеризованных и некластеризованных индексов на таблицах и представлениях дефрагментируется путем физической реорганизации страниц конечного уровня, в результате чего они выстраиваются в соответствии с логическим порядком конечных узлов (слева направо). Кроме того, реорганизация сжимает страницы индекса. Их сжатие производится в соответствии с текущим значением коэффициента заполнения.
В этом разделе
Перед началом работы выполните следующие действия.
Для проверки фрагментации индекса используется:
Среда SQL Server Management Studio
Для реорганизации или перестроения индекса используется:
Среда SQL Server Management Studio
Выявление фрагментации
Первым шагом в определении, какой метод дефрагментации следует использовать, будет анализ индекса на предмет степени фрагментации. Системная функция sys.dm_db_index_physical_stats позволяет выявить фрагментацию конкретного индекса, всех индексов в таблице или индексированном представлении, всех индексов в базе данных или всех индексов во всех базах данных. Для секционированных индексов sys.dm_db_index_physical_stats также предоставляет сведения о фрагментации каждой секции.
Результирующий набор, возвращаемый функцией sys.dm_db_index_physical_stats, включает следующие столбцы:
| Столбец | Описание |
|---|---|
| avg_fragmentation_in_percent | Процентная доля логической фрагментации (неупорядоченные страницы в индексе). |
| fragment_count | Число фрагментов (физически последовательные конечные страницы) в индексе. |
| avg_fragment_size_in_pages | Среднее число страниц в одном фрагменте индекса. |
Выяснив степень фрагментации, используйте нижеследующую таблицу для определения наиболее подходящего метода устранения фрагментации.
| Значение avg_fragmentation_in_percent | Корректирующая инструкция |
|---|---|
| > 5% и 30% | ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* Индекс может быть перестроен как в сети, так и вне сети. Реорганизация индекса всегда выполняется в режиме в сети. Чтобы добиться доступности, подобной варианту с реорганизацией, следует перестраивать индексы в режиме в сети.
Такие значения являются примерным руководством для определения положения, в которое необходимо переключиться между ALTER INDEX REORGANIZE и ALTER INDEX REBUILD. Однако фактические значения могут различаться в каждом конкретном случае. Важно определить наилучшее пороговое значение для используемой среды экспериментальным путем. При очень низких уровнях фрагментации (менее 5 %) эти команды использоваться не должны, так как выгода от дефрагментации столь низкого уровня почти всегда в достаточной степени компенсируется за счет реорганизации или перестроения индекса.
| Примечание |
|---|
Обычно фрагментация небольших индексов является неконтролируемой. Страницы индексов малого размера хранятся в смешанных экстентах. Смешанные экстенты могут совместно использоваться восемью объектами, поэтому фрагментация в небольшом индексе может не сократиться после реорганизации или перестроения индекса.
Ограничения
Перестроение индексов с более чем 128 экстентами осуществляется в два этапа: логическое и физическое перестроение. На этапе логического перестроения существующие единицы распределения, используемые индексом, помечаются для освобождения, строки данных копируются и сортируются, а затем перемещаются в новые единицы распределения, созданные для хранения перестроенного индекса. На этапе физического перестроения единицы распределения, ранее помеченные для освобождения, физически удаляются посредством выполняемых в режиме в сети коротких транзакций, и многочисленные блокировки для этого не требуются.
Параметры индекса не могут быть указаны при реорганизации индекса.
Инструкция ALTER INDEX REORGANIZE требует, чтобы в файле данных, где содержится индекс, было свободное пространство, потому что операция может выделять временные рабочие страницы только в том же файле (а не в другом файле файловой группы, к примеру). Поэтому несмотря на то, что файловая группа может иметь доступные свободные страницы, может отобразиться ошибка 1105 "Не удалось выделить место для объекта . в базе данных , так как файловая группа PRIMARY заполнена".
Создание и перестройка невыровненных индексов для таблицы, количество секций в которой превышает 1000, возможны, но не поддерживаются. Это может привести к снижению производительности или чрезмерному потреблению памяти во время таких операций.
| Примечание | |
|---|---|
| avg_fragmentation_in_percent | Процентная доля логической фрагментации (неупорядоченные страницы в индексе). |
| fragment_count | Число фрагментов (физически последовательные конечные страницы) в индексе. |
| avg_fragment_size_in_pages | Среднее число страниц в одном фрагменте индекса. |
Выяснив степень фрагментации, используйте нижеследующую таблицу для определения наиболее подходящего метода устранения фрагментации.
Реорганизация индекса всегда выполняется в режиме в сети. Чтобы добиться доступности, подобной варианту с реорганизацией, следует перестраивать индексы в режиме в сети.
Однако фактические значения могут различаться в каждом конкретном случае. Важно определить наилучшее пороговое значение для используемой среды экспериментальным путем.При очень низких уровнях фрагментации (менее 5 %) эти команды использоваться не должны, так как выгода от дефрагментации столь низкого уровня почти всегда в достаточной степени компенсируется за счет реорганизации или перестроения индекса.
| Примечание |
|---|
| Страницы индексов малого размера хранятся в смешанных экстентах. Смешанные экстенты могут совместно использоваться восемью объектами, поэтому фрагментация в небольшом индексе может не сократиться после реорганизации или перестроения индекса. |
Ограничения Править
- На этапе логического перестроения существующие единицы распределения, используемые индексом, помечаются для освобождения, строки данных копируются и сортируются, а затем перемещаются в новые единицы распределения, созданные для хранения перестроенного индекса. На этапе физического перестроения единицы распределения, ранее помеченные для освобождения, физически удаляются посредством выполняемых в режиме в сети коротких транзакций, и многочисленные блокировки для этого не требуются.
- Параметры индекса не могут быть указаны при реорганизации индекса.
Безопасность Править
Разрешения Править
Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner.
Использование среды SQL Server Management Studio Править
Проверка фрагментации индекса Править
- В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо проверить фрагментацию индекса.
- Разверните папку Таблицы.
- Разверните таблицу, в которой нужно проверить фрагментацию индекса.
- Разверните папку Индексы.
- Щелкните правой кнопкой мыши индекс, для которого нужно проверить фрагментацию, и выберите пункт Свойства.
- В разделе Выбор страницы выберите пункт Фрагментация.
На странице Фрагментация доступны следующие сведения.
- Заполненность страниц 100 % означает, что страницы индекса полностью заполнены. 50 % означает, что каждая страница индекса заполнена в среднем наполовину. Общая фрагментация Отображает количество страниц индекса, хранимых не в порядке. Средний размер строки Средний размер строки конечного уровня. Глубина Количество уровней индекса, включая конечный уровень. Перенаправленные записи (Такое состояние возникает во время обновления, когда не хватает места для сохранения новой строки в исходном расположении.) Фантомные строки Эти строки будут удалены потоком очистки, когда сервер будет не загружен. Это значение не включает в себя строки, сохраняемые из-за ожидающей выполнения транзакции изоляции моментальных снимков. Тип индекса Возможными значениями являются Кластеризованный индекс, Некластеризованный индекс иПервичный XML. Таблицы могут также сохраняться в виде кучи (без индексов), однако после этого данная страница «Свойства индекса» не может быть открыта. Строки конечного уровня Количество строк конечного уровня. Максимальный размер строки Максимальный размер строки конечного уровня. Минимальный размер строки Минимальный размер строки конечного уровня. Страниц Общее число страниц данных. Идентификатор секции Идентификатор секции сбалансированного дерева, содержащего индекс. Фантомные строки версии Количество фантомных записей, которые сохраняются из-за невыполненной транзакции изоляции моментальных снимков.
Использование Transact-SQL Править
Проверка фрагментации индекса Править
- В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.
Эта инструкция должна возвратить результирующий набор, подобный приведенному ниже.
Дополнительные сведения см. в разделе sys.dm_db_index_physical_stats (Transact-SQL).
Использование среды SQL Server Management Studio Править
Реорганизация или перестроение индекса Править
- В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо реорганизовать индекс.
- Разверните папку Таблицы.
- Разверните таблицу, в которой нужно реорганизовать индекс.
- Разверните папку Индексы.
- Щелкните правой кнопкой мыши индекс, который необходимо реорганизовать, и выберите пункт Реорганизовать.
- В диалоговом окне Реорганизация индексов убедитесь, что нужный индекс приведен в сетке Индексы для реорганизации, и нажмите кнопку ОК.
- Установите флажок Сжать данные в столбцах больших объектов, чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.
- Нажмите кнопку ОК.
Реорганизация всех индексов в таблице Править
- В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо реорганизовать индексы.
- Разверните папку Таблицы.
- Разверните таблицу, в которой нужно реорганизовать индексы.
- Щелкните правой кнопкой мыши папку Индексы и выберите команду Реорганизовать все.
- Для удаления индекса из сетки Индексы для реорганизации выделите индекс и нажмите клавишу DELETE.
- Установите флажок Сжать данные в столбцах больших объектов, чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.
- Нажмите кнопку ОК.
Перестроение индекса Править
- В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо реорганизовать индекс.
- Разверните папку Таблицы.
- Разверните таблицу, в которой нужно реорганизовать индекс.
- Разверните папку Индексы.
- Щелкните правой кнопкой мыши индекс, который необходимо реорганизовать, и выберите пункт Реорганизовать.
- В диалоговом окне Перестроение индексов убедитесь, что нужный индекс приведен в сетке Индексы для перестроения, и нажмите кнопку ОК.
- Установите флажок Сжать данные в столбцах больших объектов, чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.
- Нажмите кнопку ОК.
Использование Transact-SQL Править
Реорганизация дефрагментированного индекса Править
- В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.
Реорганизация всех индексов в таблице Править
- В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.
Перестроение дефрагментированного индекса Править
- В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
- На стандартной панели выберите пункт Создать запрос.
- В этом примере перестраивается один индекс в таблице Employee.
Перестроение всех индексов в таблице Править
- В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
- На стандартной панели выберите пункт Создать запрос.
- Тем самым выполняется перестроение всех индексов, связанных с таблицей. Указываются три параметра.
Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).
