перестроить индексы ms sql

Задача «Перестроение индекса» (план обслуживания)

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

Задача «Перестроение индекса» использует инструкцию ALTER INDEX. Дополнительные сведения о параметрах, описанных на этой странице, см. в разделе ALTER INDEX (Transact-SQL).

Параметры

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

Создать
Создать новое соединение с сервером для его использования при выполнении этой задачи. Диалоговое окно Создание соединения описано ниже.

Базы данных
Укажите базы данных, для которых должна выполняться эта задача.

Все базы данных

Все системные базы данных

Все пользовательские базы данных

Создается план обслуживания, по которому задачи обслуживания выполняются для всех баз данных, созданных пользователем. Для системных баз данных SQL Server задачи обслуживания выполняться не будут.

Определенные базы данных

Создается план обслуживания, по которому задачи обслуживания должны выполняться только для указанных баз данных. Если выбран этот параметр, необходимо выбрать в списке хотя бы одну базу данных.

Планы обслуживания выполняются только для баз данных, уровень совместимости которых 80 или выше. Базы данных с уровнем совместимости 70 или ниже не отображаются.

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

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

Изменить долю свободного места на странице
Удалите индексы таблиц в базе данных и создайте их повторно с новым, автоматически вычисляемым коэффициентом заполнения, резервирующим указанный объем свободного пространства на страницах индекса. Чем выше процентное значение, тем больше свободного места резервируется на страницах индекса и тем больше будет размер индекса. Допустимые значения: от 0 до 100.

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

разредить индекс
Укажите заполнение индекса.

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

Операции с индексами в сети доступны не во всех выпусках MicrosoftSQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL Server, см. в статье Возможности, поддерживаемые выпусками SQL Server 2016.

Не перестраивать индексы | Перестроить индексы в режиме «вне сети»
Укажите, что нужно делать для типов индексов, которые невозможно перестроить, пока они находятся в сети.

MAXDOP
Укажите значение для ограничения числа процессоров, используемых при параллельном выполнении планов.

Используется низкий приоритет
Выберите этот параметр для ожидания блокировок с низким приоритетом.

Максимальная длительность
Укажите длительность ожидания блокировок с низким приоритетом.

Если количество затронутых объектов велико, построение этого отображения может занять значительное время.

Параметры статистики индексов

В более ранних версиях Microsoft SQL Server операции реорганизации или повторного создания больших индексов могли снижать производительность системы. В SQL Server 2016 реализован ряд улучшений для существенного повышения производительности таких операций.

Кроме того, в более ранних версиях было доступно меньше возможностей управления. Из-за этого операции реорганизации или повторного создания выполнялись даже для индексов с низкой фрагментацией, что было слишком затратно. Новые элементы управления в пользовательском интерфейсе для плана обслуживания позволяют исключать индексы, которые не нужно обновлять, руководствуясь критериями статистики индексов. При этом для внутренних целей используются следующие динамические административные представления (DMV) Transact-SQL:

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

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

Диалоговое окно «Создание соединения»

Имя соединения
Введите имя нового соединения.

Выберите или введите имя сервера
Выберите сервер для подключения при выполнении этой задачи.

Обновить
Обновите список доступных серверов.

Введите данные для входа на сервер
Укажите способ проверки подлинности на сервере.

Использовать встроенную безопасность Windows
Подключиться к экземпляру Компонент SQL Server Database Engine с использованием проверки подлинности Windows.

Пароль
Укажите используемый при проверке подлинности пароль. Этот параметр недоступен.

Источник

DBCC DBREINDEX (Transact-SQL)

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

В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Используйте вместо этого инструкцию ALTER INDEX.

Применимо к: SQL Server (с SQL Server 2008 до текущей версии)

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

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

index_name
Имя перестраиваемого индекса. Имена индексов должны соответствовать правилам для идентификаторов. Если аргумент index_name задан, также должен быть указан аргумент table_name. Если аргумент index_name не задан или имеет значение » «, перестраиваются все индексы таблицы.

fillfactor
Процентная доля пространства на каждой странице индексов при создании или перестроении индекса. Аргумент fillfactor заменяет коэффициент заполнения, указанный при создании индекса, и становится новым значением по умолчанию для данного индекса и любых других некластеризованных индексов, перестраиваемых из-за перестроения кластеризованного индекса.
Если значение аргумента fillfactor равно 0, в инструкции DBCC DBREINDEX используется последнее указанное значение коэффициента заполнения для индекса. Это значение хранится в представлении каталога sys.indexes.
Если аргумент fillfactor задан, также должны быть указаны аргументы table_name и index_name. Если аргумент fillfactor не задан, используется установленный по умолчанию коэффициент заполнения 100. Дополнительные сведения см. в статье Указание коэффициента заполнения для индекса.

WITH NO_INFOMSGS
Подавляет все информационные сообщения со степенями серьезности от 0 до 10.

Remarks

Инструкция DBCC DBREINDEX выполняет перестроение индекса для таблицы или всех индексов, определенных для таблицы. При разрешенном динамическом перестроении индекса индексы с ограничениями PRIMARY KEY или UNIQUE можно перестраивать без необходимости удаления и повторного создания этих ограничений. Это значит, что индекс может быть перестроен без необходимости знания структуры таблицы или ее ограничений. Потребность в этом может возникнуть после массового копирования данных в таблицу.

Инструкция DBCC DBREINDEX позволяет перестроить все индексы таблицы с помощью одной инструкции. Это проще, чем кодирование множества инструкций DROP INDEX и CREATE INDEX. Так как работа выполняется одной инструкцией, инструкция DBCC DBREINDEX автоматически становится атомарной, в то время как отдельные инструкции DROP INDEX и CREATE INDEX необходимо включить в транзакцию, чтобы они стали атомарными. Кроме того, инструкция DBCC DBREINDEX дает большее преимущество в оптимизации по сравнению с отдельными инструкциями DROP INDEX и CREATE INDEX.

В отличие от инструкций DBCC INDEXDEFRAG или от ALTER INDEX с параметром REORGANIZE, DBCC DBREINDEX является операцией вне сети. Если перестраивается некластеризованный индекс, для запрашиваемой таблицы в течение операции удерживается совместная блокировка. Это предотвращает изменения в таблице. Если перестраивается кластеризованный индекс, удерживается монопольная блокировка таблицы. Это предотвращает какой-либо доступ к таблице, делая ее вне сети. Для оперативного перестроения индекса используется инструкция ALTER INDEX REBUILD с параметром ONLINE; она также используется для управления степенью параллелизма в течение операции перестроения индекса.

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

Ограничения

Использование инструкции DBCC DBREINDEX не поддерживается для следующих объектов:

Результирующие наборы

Даже если не задан NO_INFOMSGS (имя таблицы задавать необходимо), инструкция DBCC DBREINDEX возвращает:

Разрешения

Вызывающий должен быть владельцем таблицы, членом предопределенной роли сервера sysadmin, предопределенной роли базы данных db_owner или предопределенной роли базы данных db_ddladmin.

Примеры

A. Перестроение индекса

Б. Перестроение всех индексов

Источник

Перестроить индексы ms sql

Перестроение и дефрагментация индексов, DBCC SHOWCONTIG, Scan Density, Logical Scan Fragmentation и Avg. Page Density, ALTER INDEX REBUILD, DBCC DBREINDEX, ALTER INDEX REORGANIZE, DBCC INDEXDEFRAG

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

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

DBCC SHOWCONTIG (‘HumanResources.Employee’, ‘PK_Employee_EmployeeID’);

Расшифруем то, что возвращает эта команда:

q Pages scanned (Просканировано страниц) — количество страниц в базе данных, используемых индексом;

q Extents switches (Переключения экстентов) — сколько переходов между экстентами пришлось выполнить при просмотре страниц индекса. В идеале страницы индекса должны идти друг за другом в смежных экстентах, поэтому в идеальной ситуации этот параметр не должен превышать количество страниц, деленное на 8. Но такая ситуация встречается редко;

q Scan Density [ Best Count : Actual Count ] (Плотность сканирования [Лучший показатель: Реальный показатель]) — отношение идеально возможного количества переходов между экстентами к реальному. Чем ближе этот показатель к 100%, тем лучше;

q Extent Scan Fragmentation (Фрагментация сканирования экстентов) — это почти то же самое, только оцениваются не страницы индекса, которые идут не по порядку, а экстенты, принадлежащие этому индексу, которые идут не друг за другом. Этот параметр оказывает меньшее влияние на скорость работы (и обычно он сам существенно меньше, чем параметр Logical Scan Fragmentation );

Рис. 11.14. Оценка степени фрагментации индекса из SQL Server Management Studio

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

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

Можно перестроить все индексы для данной таблицы или представления:

ALTER INDEX ALL ON HumanResources.Employee REBUILD;

Dim oServer, oDB, oTable

Set oServer = CreateObject(«SQLDmo.SqlServer2»)

Set oDB = oServer.Databases(«Northwind»)

For Each oTable In oDB.Tables

If oTable.SystemObject = False Then oTable.RebuildIndexes

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

CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL AS

— Объявляем необходимые переменные

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @objectowner VARCHAR(255)

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @indexname CHAR(255)

DECLARE @dbname sysname

DECLARE @tableid INT

DECLARE @tableidchar VARCHAR(255)

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

SELECT @dbname = db_name() ;

IF @dbname IN (‘master’, ‘msdb’, ‘model’, ‘tempdb’)

PRINT ‘Эта процедура не может быть запущена для системных БД’;

— Начинаем проверку уровня фрагментации

— Вначале объявляем курсор

DECLARE tables CURSOR FOR

SELECT convert(varchar,so.id) FROM sysobjects so JOIN sysindexes si ON so.id = si.id WHERE so.type =’U’ AND si.indid 0;

— Затем создаем временную таблицу для хранения информации о фрагментации

CREATE TABLE #fraglist (ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255),IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL);

— Для каждой таблицы в базе данных выполняем команду DBCC SHOWCONTIG

FETCH NEXT FROM tables INTO @tableidchar

WHILE @@FETCH_STATUS = 0

— Проходим по всем индексам для таблицы

INSERT INTO #fraglist

EXEC (‘DBCC SHOWCONTIG (‘ + @tableidchar + ‘) WITH FAST, _

TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)

— Для проверки выводим информацию из временной таблицы

SELECT * FROM # fraglist

— Теперь необходимо произвести дефрагментацию

— Вначале опять объявляем курсор

DECLARE indexes CURSOR FOR

SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity FROM #fraglist f JOIN sysobjects so ON f.ObjectId=so.id WHERE ScanDensity 0

— Выводим для проверки информацию о начале дефрагментации

SELECT ‘Started defragmenting indexes at ‘ + CONVERT(VARCHAR,GETDATE())

— Проходим циклом по всем индексам

FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0

SET QUOTED_IDENTIFIER ON

SELECT @execstr = ‘DBCC DBREINDEX (‘ + «‘» + RTRIM(@objectowner) + ‘.’ + RTRIM(@tablename) + «‘» + ‘, ‘ + RTRIM(@indexname) + ‘) WITH NO_INFOMSGS’

SELECT ‘Выполняем: ‘ SELECT (@ execstr )

SET QUOTED_IDENTIFIER OFF

FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

— Затем закрываем курсор

— Отчитываемся о времени завершения

SELECT ‘Finished defragmenting indexes at ‘ + CONVERT(VARCHAR,GETDATE());

— Удаляем временную таблицу

DROP TABLE # fraglist ;

Если вам нужно выполнить дефрагментацию для всех индексов в интересующей базе данных, для которых значение параметра Scan Density меньше 80%, можно использовать команду:

EXEC sp_deframent_indexes 80.00;

Перестроение индексов — операция достаточно тяжелая. Она требует большого количества времени и места на диске, при этом пользователи не могут работать с таблицами, для которых выполняется такое перестроение. Поэтому во многих случаях имеет смысл производить не перестроение, а реорганизацию индексов при помощи команды ALTER INDEX REORGANIZE (другая устаревшая, но еще поддерживаемая команда — DBCC INDEXDEFRAG ). При реорганизации индексов производится дефрагментация только страниц самого нижнего уровня индекса (листьевых). Такой способ, конечно, в меньшей степени снижает фрагментацию, но его можно применять без отключения пользователей, и он требует меньшего количества ресурсов.

Dim oServer, oDB, oTable

Set oServer = CreateObject(«SQLDmo.SqlServer2»)

Set oDB = oServer.Databases(«AdventureWorks»)

For Each oTable In oDB.Tables

If oTable.SystemObject = False Then

Подробно про статистику будет рассказываться в разд. 11.5.8.

Источник

Оптимизация обслуживания индексов позволяет повысить производительность запросов и снизить уровень потребления ресурсов

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

Сведения в этой статье не применяются к выделенному пулу SQL в Azure Synapse Analytics. Сведения об обслуживании индекса для выделенного пула SQL в Azure Synapse Analytics см. в статье об индексации таблиц выделенного пула SQL в Azure Synapse Analytics.

Основные понятия: фрагментация индекса и плотность страниц

Что такое фрагментация индекса и как она влияет на производительность?

Что такое плотность страниц (или заполненность страниц) и как она влияет на производительность?

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

Чтобы не допустить излишнего снижения плотности страниц, корпорация Майкрософт не рекомендует задавать коэффициент заполнения, отличный от значения 100 (или 0), за исключением тех сценариев, в которых индексы часто подвергаются разбиению страниц, как, например, часто изменяемые индексы с ведущими столбцами, которые содержат непоследовательные значения GUID.

Измерение фрагментации индекса и плотности страниц

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

Фрагментация для индексов rowstore и columnstore определяется по-разному. Для индексов rowstore функция sys.dm_db_index_physical_stats позволяет узнать фрагментацию и плотность страниц для конкретного индекса, для всех индексов в таблице или индексированном представлении, для всех индексов в базе данных или для всех индексов во всех базах данных. Для секционированных индексов sys.dm_db_index_physical_stats() возвращает информацию отдельно для каждой секции.

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

Фрагментация сжатых групп строк в индексе columnstore можно вычислить с помощью следующей формулы:

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

Ознакомьтесь с примерами запросов для определения фрагментации и плотности страниц.

Методы обслуживания индекса: реорганизация и перестроение

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

Для секционированных индексов оба эти метода можно применять ко всем секциям или к одной секции индекса.

Реорганизация индекса

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

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

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

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

Синтаксис Transact-SQL см. в статье ALTER INDEX REBUILD. Дополнительные сведения об операциях с индексами с сохранением подключения см. в статье Выполнение операции с индексами в сети.

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

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

В зависимости от наличия ресурсов и шаблонов рабочей нагрузки при использовании значения выше стандартного MAXDOP в инструкции ALTER INDEX REBUILD может сократиться длительность перестроения за счет более интенсивной загрузки ЦП.

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

Начиная с версии SQL Server 2016 (13.x);, перестраивать индекс columnstore обычно не требуется, так как инструкция REORGANIZE выполняет те же основные действия с сохранением подключения.

Использование перестроения индекса для восстановления после повреждения данных

В предыдущих версиях SQL Server перестроение некластеризованного индекса rowstore иногда применялось, чтобы исправить несоответствия, связанные с повреждением данных.

Начиная с SQL Server 2008, такие несоответствия в некластеризованном индексе можно по-прежнему устранять, перестраивая некластеризованный индекс в автономном режиме. Но вы не сможете устранить несоответствия в некластеризованном индексе, перестроив индекс с сохранением подключения, потому что этот механизм перестроения использует существующий некластеризованный индекс в качестве основы для перестроения, то есть все эти несоответствия сохранятся. Перестроение индекса в автономном режиме иногда может вызвать принудительную проверку кластеризованного индекса (или кучи), при которой данные с несоответствиями в некластеризованном индексе будут заменены правильными данными из кластеризованного индекса или кучи.

Чтобы в качестве источника данных применялся именно кластеризованный индекс или куча, вместо перестроения некластеризованного индекса удалите его и создайте заново. Как и в предыдущих версиях, для устранения несоответствий мы рекомендуем восстанавливать затронутые данные из резервной копии, но иногда несоответствия в некластеризованном индексе удается исправить, перестроив некластеризованный индекс в автономном режиме или создав его заново. Дополнительные сведения см. в разделе DBCC CHECKDB (Transact-SQL).

Автоматическое управление индексами и статистикой

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

Вопросы, связанные с перестроением и реорганизацией индексов columnstore

Автоматическое перестроение всех некластеризованных индексов rowstore в таблице происходит в следующих случаях:

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

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

При перестроении индекса на физическом носителе должно быть достаточно места для хранения двух копий индекса. По завершении перестроения Компонент Database Engine удаляет исходный индекс.

Перестроение или реорганизация малых индексов rowstore не всегда позволяет снизить уровень фрагментации. До версии SQL Server 2014 (12.x) включительно Компонент SQL Server Database Engine выделяет пространство с помощью смешанных экстентов. Поэтому страницы небольших индексов иногда хранятся в нескольких экстентах, что неявным образом делает такие индексы фрагментированными. Смешанные экстенты могут находиться в общем пользовании у восьми объектов, поэтому фрагментацию в малом индексе нельзя уменьшить путем его реорганизации или перестроения.

Вопросы, связанные с перестроением индекса columnstore

При перестроении индекса columnstore Компонент Database Engine считывает все данные из исходного индекса columnstore, включая разностное хранилище. Данные объединяются в новые группы строк, а группы строк сжимаются в columnstore. Компонент Database Engine дефрагментирует таблицу columnstore, физически удаляя строки, которые помечены как удаленные.

Начиная с SQL Server 2019 (15.x), задача переноса кортежей дополняется задачей фонового объединения, которая автоматически сжимает небольшие открытые разностные группы строк, существовавшие некоторое время в соответствии с внутренним порогом, или объединяет сжатые группы строк, из которых было удалено большое количество строк. Со временем это повышает качество индекса columnstore. См. сведения в статье Общие сведения об индексах columnstore.

Перестраивайте секцию, а не всю таблицу

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

Перестроение секции после загрузки или изменения данных гарантирует, что все данные в columnstore хранятся в сжатых группах строк. Когда в процессе загрузки данные вставляются в секцию пакетами, размер которых не превышает 102 400 строк, такая секция может иметь в разностном хранилище несколько открытых групп строк. Перестроение позволяет переместить все строки разностного хранилища в сжатые группы строк в columnstore.

Вопросы, связанные с реорганизацией индекса columnstore

При реорганизации индекса columnstore Компонент Database Engine сжимает каждую закрытую группу строк в разностном хранилище в сжатую группу строк в columnstore. Начиная с SQL Server 2016 (13.x); и в решении База данных SQL Azure с помощью команды REORGANIZE в оперативном режиме выполняются следующие дополнительные действия по дефрагментационной оптимизации:

Что нужно оценить перед началом обслуживания индекса

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

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

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

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

Положительный побочный эффект от перестроения индекса

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

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

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

Стратегия обслуживания индекса

Корпорация Майкрософт рекомендует всем клиентам изучить и применить следующую стратегию обслуживания индексов:

Обслуживание индексов в База данных SQL Azure и Управляемый экземпляр SQL Azure

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

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

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

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

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

Ограничения

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

Индекс нельзя реорганизовать, если для ALLOW_PAGE_LOCKS задано состояние OFF.

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

Ограничения статистики

Примеры

Проверка фрагментации и плотности страниц для индекса rowstore с помощью Transact-SQL

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

Проверка фрагментации индекса columnstore с помощью Transact-SQL

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

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

Обслуживание индексов с помощью SQL Server Management Studio

Реорганизация или перестроение индекса

Реорганизация всех индексов в таблице

Обслуживание индексов с помощью Transact-SQL

Дополнительные примеры использования Transact-SQL для перестроения или реорганизации индексов см. в разделах Примеры: индексы rowstore и Примеры: индексы columnstore.

Реорганизация индекса

Реорганизация всех индексов в таблице

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

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

Подробные сведения см. в статье ALTER INDEX (Transact-SQL).

Источник