Урок 1: Физическая архитектура индексов

Урок 1: Физическая архитектура индексов

 

В рамках этого урока будут рассмотрены следующие вопросы

} Какие методы организации страниц данных внутри секции существуют?

} Как происходит поиск строки в таблице базы данных?

} Какие преимущества дает индексирование таблиц базы данных?

} Физическая структура индексов

} Как можно оптимизировать использование индексов?

Структура хранения данных

 

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

 

 

Таблицы SQL Server используют один из двух методов организации страниц данных внутри секции:

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

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

 

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

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

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

Кучи

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

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

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

На следующей иллюстрации демонстрируется, как компонент SQL Server Database Engine использует IAM-страницы для получения строк данных из кучи с одной секцией.

 

 

Оптимизация индексов

 

Урок 2: Создание, изменение и удаление индексов

 

В рамках этого урока будут рассмотрены следующие вопросы

} Из каких источников можно получить информацию об индексах таблиц БД?

} Как создать, изменить и удалить индекс?

} Что такое фрагментация, и какие типы фрагментации индексов существуют?

} Какие методы устранения фрагментации индексов существуют?

Источники информации об индексах

Создание индексов

 

Создание индексов осуществляется командой CREATE INDEX.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON <object> (column [ ASC | DESC ] [,...n ])

[ INCLUDE (column_name [,...n ]) ]

[ WHERE <filter_predicate> ]

[ WITH (<relational_index_option> [,...n ]) ]

[ ON { filegroup_name | default } ]

 

<object>::=

{

[ database_name. [ schema_name ]. | schema_name. ]

table_or_view_name

}

 

<relational_index_option>::=

{

PAD_INDEX = { ON | OFF }

| FILLFACTOR = fillfactor

| SORT_IN_TEMPDB = { ON | OFF }

| IGNORE_DUP_KEY = { ON | OFF }

| STATISTICS_NORECOMPUTE = { ON | OFF }

| DROP_EXISTING = { ON | OFF }

| ONLINE = { ON | OFF }

| ALLOW_ROW_LOCKS = { ON | OFF }

| ALLOW_PAGE_LOCKS = { ON | OFF }

| MAXDOP = max_degree_of_parallelism

}

UNIQUE

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

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

 

CLUSTERED

Создает кластеризованный индекс, при создании кластеризованного индекса все существующие некластеризованные индексы таблицы перестраиваются. Если аргумент CLUSTERED не указан, создается некластеризованный индекс.

 

NONCLUSTERED

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

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

По умолчанию, используется значение NONCLUSTERED.

 

index_name

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

 

column

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

Столбцы с типами данных для больших объектов ntext, text, varchar(max), nvarchar(max), varbinary(max), xml или image не могут быть ключевыми столбцами для индекса. Кроме того, определение представления не может включать столбцы типов ntext, text и image, даже если они указаны в инструкции CREATE INDEX.

 

[ ASC | DESC ]

Определяет сортировку значений заданного столбца индекса: по возрастанию или по убыванию. Значение по умолчанию - ASC.

INCLUDE( column [, ... n ])

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

 

WHERE <filter_predicate>

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

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

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

 

Параметры SET	Обязательное значение
ANSI_NULLS	ON
ANSI_PADDING	ON
ANSI_WARNINGS	ON
ARITHABORT	ON
CONCAT_NULL_YIELDS_NULL	ON
NUMERIC_ROUNDABORT	OFF
QUOTED_IDENTIFIER	ON

 

ON filegroup_name

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

 

ON"default"

Создает заданный индекс в файловой группе, используемой по умолчанию.

 

<object>::=

Полное или неполное имя индексируемого объекта.

 

<relational_index_option>::=

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

PAD_INDEX = { ON | OFF }

Определяет разреженность индекса. Значение по умолчанию — OFF.

ON

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

OFF или fillfactor не указан

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

Параметр PAD_INDEX имеет смысл только в случае, если указан параметр FILLFACTOR, так как использует процентное значение, указанное в нем. Если процент, заданный аргументом FILLFACTOR, недостаточно велик для размещения одной строки, компонент Database Engine внутренне переопределит это значение, чтобы обеспечить минимум. Количество строк на странице индекса промежуточного уровня никогда не бывает менее двух даже при самых малых значениях аргумента fillfactor.

Для обратной совместимости синтаксиса аргумент WITH PAD_INDEX эквивалентен аргументу WITH PAD_INDEX = ON.

 

FILLFACTOR= fillfactor

Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой страницы индекса во время создания или перестроения индекса. Параметр fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию равно 0. Если параметр fillfactor равен 100 или 0, компонент Database Engine создает индексы с полностью заполненными страницами конечного уровня.

Аргумент FILLFACTOR действует только при создании или перестройке индекса. Компонент Database Engine не сохраняет динамически указанный процентный объем свободного места на страницах. Значение коэффициента заполнения можно увидеть в представлении каталога sys.indexes.

 

SORT_IN_TEMPDB = { ON | OFF }

Указывает, сохранять ли временные результаты сортировки в базе данных tempdb. Значение по умолчанию - OFF.

ON

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

OFF

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

 

IGNORE_DUP_KEY = { ON | OFF }

Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Значение по умолчанию - OFF.

ON

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

OFF

Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится сообщение об ошибке. Будет выполнен откат всей операции INSERT.

STATISTICS_NORECOMPUTE = { ON | OFF }

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

ON

Устаревшие статистики не пересчитываются автоматически.

OFF

Автоматическое обновление статистических данных включено.

 

DROP_EXISTING = { ON | OFF }

Указывает, что названный существующий кластеризованный или некластеризованный индекс удаляется и перестраивается. Значение по умолчанию - OFF.

ON

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

OFF

Выдается ошибка, если индекс с указанным именем уже существует.

Тип индекса не может быть изменен с помощью аргумента DROP_EXISTING.

ONLINE = { ON | OFF }

Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами (только в выпусках SQL Server Enterprise и Developer). Значение по умолчанию - OFF.

 

ALLOW_ROW_LOCKS = { ON | OFF }

Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

ON

Блокировки строк допустимы при доступе к индексу. Необходимость в блокировке строк определяет компонент Database Engine.

OFF

Блокировки строк не используются.

ALLOW_PAGE_LOCKS = { ON | OFF }

Указывает, разрешена ли блокировка страниц. Значение по умолчанию - ON.

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

OFF -блокировки страниц не используются.

 

MAXDOP = max_degree_of_parallelism

MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Параллельные операции с индексами доступны только в редакциях Developer, Enterprise и DataCenter. Максимальное число процессоров завис от редакции (Enterprise – 64, DataCenter – 256). Значение по умолчанию 0 - в зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

Вычисляемые столбцы

Индексы могут создаваться на вычисляемых столбцах. Вычисляемые столбцы могут иметь свойство PERSISTED - это значит, что компонент Database Engine хранит вычисленные значения в таблице и обновляет их при обновлении любых столбцов, от которых зависит вычисляемый столбец. Компонент Database Engine использует эти сохраняемые значения, когда создает индекс по столбцу и когда запрос обращается к индексу.

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

Для материализованных вычисляемых столбцов необходимо, чтобы следующие параметры SET имели значения, указанные выше в разделе «Обязательные параметры SET для индексированных представлений».

Вычисляемые столбцы, производные от типов данных image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml, могут индексироваться как ключевые или включенные неключевые столбцы, если тип данных вычисляемого столбца приемлем как тип данных для ключевого столбца индекса или неключевого столбца. Если размер ключа индекса превышает 900 байт, выдается предупреждение.

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

CREATE TABLE T (a int, b int, c AS a/b);INSERT INTO T VALUES (1, 0);

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

 

CREATE TABLE T (a int, b int, c AS a/b);CREATE UNIQUE CLUSTERED INDEX IdxT ON T(c);INSERT INTO T VALUES (1, 0);

 

Примеры:

Изменение индексов

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

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

ALTER INDEX { index_name | ALL }

ON <object>

{ REBUILD

[ [PARTITION = ALL]

[ WITH (<rebuild_index_option> [,...n ]) ]

| [ PARTITION = partition_number

[ WITH (<single_partition_rebuild_index_option>

[,...n ])

]

]

]

| DISABLE

| REORGANIZE

[ PARTITION = partition_number ]

| SET (<set_index_option> [,...n ])

}

 

<object>::=

{

[ database_name. [ schema_name ]. | schema_name. ]

table_or_view_name

}

 

<rebuild_index_option >::=

{

PAD_INDEX = { ON | OFF }

| FILLFACTOR = fillfactor

| SORT_IN_TEMPDB = { ON | OFF }

| IGNORE_DUP_KEY = { ON | OFF }

| STATISTICS_NORECOMPUTE = { ON | OFF }

| ONLINE = { ON | OFF }

| ALLOW_ROW_LOCKS = { ON | OFF }

| ALLOW_PAGE_LOCKS = { ON | OFF }

| MAXDOP = max_degree_of_parallelism

| DATA_COMPRESSION = { NONE | ROW | PAGE }

[ ON PARTITIONS ({ <partition_number_expression> | <range> }

[,...n ]) ]

}

<range>::=

<partition_number_expression> TO <partition_number_expression>

}

 

<single_partition_rebuild_index_option>::=

{

SORT_IN_TEMPDB = { ON | OFF }

| MAXDOP = max_degree_of_parallelism

| DATA_COMPRESSION = { NONE | ROW | PAGE } }

}

 

<set_index_option>::=

{

ALLOW_ROW_LOCKS = { ON | OFF }

| ALLOW_PAGE_LOCKS = { ON | OFF }

| IGNORE_DUP_KEY = { ON | OFF }

| STATISTICS_NORECOMPUTE = { ON | OFF }

}

index_name

Имя индекса, настройки которого требуется изменить.

 

ALL

Указывает все индексы, связанные с таблицей или представлением, независимо от типа индекса.

 

database_name

Имя базы данных.

 

schema_name

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

 

table_or_view_name

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

 

REBUILD [ WITH(<rebuild_index_option> [,... n ])]

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

При перестроении XML-индекса или пространственного индекса параметры ONLINE = ON и IGNORE_DUP_KEY = ON недопустимы.

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

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

PARTITION

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

PARTITION = ALL, перестроение всех секций.

 

partition_number

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

 

WITH (<single_partition_rebuild_index_option>)

SORT_IN_TEMPDB, MAXDOP и DATA_COMPRESSION - параметры, которые могут быть указаны при перестроении одиночной секции (PARTITION = n).

 

DISABLE

Помечает индекс как отключенный и недоступный для использования компонентом Database Engine. Любой индекс может быть отключен, при этом определение отключенного индекса остается в системном каталоге без базовых индексных данных. Отключение кластеризованного индекса блокирует доступ пользователя к данным базовой таблицы. Чтобы активировать индекс, следует использовать инструкцию ALTER INDEX REBUILD или CREATE INDEX WITH DROP_EXISTING.

 

REORGANIZE

Указывает, что конечный уровень индекса будет реорганизован. Инструкция ALTER INDEX REORGANIZE всегда выполняется в режиме в сети. Это означает, что долгосрочные блокировки таблицы не удерживаются и запросы или обновления базовой таблицы могут продолжаться во время выполнения транзакции ALTER INDEX REORGANIZE. REORGANIZE не может быть вызвана для индекса с ALLOW_PAGE_LOCKS со значением OFF.

 

SET(<set_index option> [,... n ])

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

 

PAD_INDEX = { ON | OFF }

Определяет разреженность индекса. Значение по умолчанию — OFF.

ON

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

OFF или fillfactor не указан

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

Параметр PAD_INDEX имеет смысл только в случае, если указан параметр FILLFACTOR, так как использует процентное значение, указанное в нем. Если процент, заданный аргументом FILLFACTOR, недостаточно велик для размещения одной строки, компонент Database Engine внутренне переопределит это значение, чтобы обеспечить минимум. Количество строк на странице индекса промежуточного уровня никогда не бывает менее двух даже при самых малых значениях аргумента fillfactor.

Для обратной совместимости синтаксиса аргумент WITH PAD_INDEX эквивалентен аргументу WITH PAD_INDEX = ON.

 

FILLFACTOR= fillfactor

Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой страницы индекса во время создания или перестроения индекса. Параметр fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию равно 0. Если параметр fillfactor равен 100 или 0, компонент Database Engine создает индексы с полностью заполненными страницами конечного уровня.

Аргумент FILLFACTOR действует только при создании или перестройке индекса. Компонент Database Engine не сохраняет динамически указанный процентный объем свободного места на страницах. Значение коэффициента заполнения можно увидеть в представлении каталога sys.indexes.

 

SORT_IN_TEMPDB = { ON | OFF }

Указывает, сохранять ли временные результаты сортировки в базе данных tempdb. Значение по умолчанию - OFF.

ON

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

OFF

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

 

IGNORE_DUP_KEY = { ON | OFF }

Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Значение по умолчанию - OFF.

ON

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

OFF

Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится сообщение об ошибке. Будет выполнен откат всей операции INSERT.

STATISTICS_NORECOMPUTE = { ON | OFF }

Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию - OFF.

ON

Устаревшие статистики не пересчитываются автоматически.

OFF

Автоматическое обновление статистических данных включено.

 

DROP_EXISTING = { ON | OFF }

Указывает, что названный существующий кластеризованный или некластеризованный индекс удаляется и перестраивается. Значение по умолчанию - OFF.

ON

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

OFF

Выдается ошибка, если индекс с указанным именем уже существует.

Тип индекса не может быть изменен с помощью аргумента DROP_EXISTING.

ONLINE = { ON | OFF }

Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами (только в редакциях SQL Server Enterprise и Developer). Значение по умолчанию - OFF.

 

ALLOW_ROW_LOCKS = { ON | OFF }

Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

ON

Блокировки строк допустимы при доступе к индексу. Необходимость в блокировке строк определяет компонент Database Engine.

OFF

Блокировки строк не используются.

ALLOW_PAGE_LOCKS = { ON | OFF }

Указывает, разрешена ли блокировка страниц. Значение по умолчанию - ON.

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

OFF -блокировки страниц не используются.

 

MAXDOP = max_degree_of_parallelism

MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Параллельные операции с индексами доступны только в редакциях Developer, Enterprise и DataCenter. Максимальное число процессоров завис от редакции (Enterprise – 64, DataCenter – 256). Значение по умолчанию 0 - в зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

 

DATA_COMPRESSION

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

NONE - индекс или заданные секции не сжимаются.

ROW - для индекса или заданных секций производится сжатие строк.

PAGE - для индекса или заданных секций производится сжатие страниц.

ON PARTITIONS ({ <partition_number_expression> | <range> } [,...n])

Указывает секции, к которым применяется параметр DATA_COMPRESSION. Если индекс не секционирован, аргумент ON PARTITIONS создаст ошибку. Если не указано предложение ON PARTITIONS, то параметр DATA_COMPRESSION применяется ко всем секциям секционированного индекса.

 

< partition_number_expression > можно указать одним из следующих способов:

· Указать номер секции, например ON PARTITIONS (2).

· Указать номера нескольких секций через запятые, например ON PARTITIONS (1, 5).

· Указать диапазоны и отдельные секции: ON PARTITIONS (2, 4, 6 TO 8).

 

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

ALTER INDEX PK_Department_DepartmentID

ON Department

DISABLE;

В следующем примере устанавливаются несколько параметров для индекса AK_SalesOrderHeader_SalesOrderNumber.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON

Sales.SalesOrderHeader

SET (STATISTICS_NORECOMPUTE = ON,

IGNORE_DUP_KEY = ON,

ALLOW_PAGE_LOCKS = ON);

Восстановление индексов

Компонент SQL Server Database Engine автоматически поддерживает состояние индексов при выполнении операций вставки, обновления или удаления в отношении базовых данных. Со временем эти изменения могут привести к тому, что данные в индексе окажутся фрагментированными (разбросанными по базе данных). Существуют два типа фрагментации:

· внутренняя – внутренняя фрагментация означает неполное заполнение страниц индекса, что приводит к увеличению высоты сбалансированного дерева и, как следствие, росту количества операций ввода-вывода.

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

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

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

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

Выявление фрагментации

Первым шагом в определении, какой метод дефрагментации следует использовать, будет анализ индекса на предмет степени фрагментации. Системная функция 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 REORGANIZE
> 30%	ALTER INDEX REBUILD

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

Пример:

Следующий пример запрашивает через функцию динамического управления sys.dm_db_index_physical_stats среднюю фрагментацию для всех индексов в таблице Cources. В соответствии с предыдущей таблицей, рекомендуемым решением проблемы будет реорганизация IX_Id_Unique_Clustered и перестроение IX_Unique_Code.

SELECT a.index_id, name, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Cources'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;GO

Результат

index_id name avg_fragmentation_in_percent------------------------------------------------------------------------------------------1 IX_Id_Unique_Clustered 15.0769230769230773 IX_Unique_Code 99.666666666666657 (2 row(s) affected)

 

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

Реорганизация индексов производится при помощи инструкции ALTER INDEX с предложением REORGANIZE. Это предложение эквивалентно DBCC INDEXDEFRAG.

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

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

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

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

Реорганизация индексов производится при помощи инструкции ALTER INDEX с предложением REBUILD. Это предложение эквивалентно DBCC DBREINDEX.

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

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

Чтобы добиться доступности, подобной варианту с реорганизацией, следует перестраивать индексы в режиме в сети – это означает, что базовые таблицы и связанные индексы будут доступны для запросов и изменения данных во время операций с индексами. Режим перестроения индекса в сети доступен только в редакциях SQL Server Enterprise и Developer.

При удалении или перестроении больших индексов (более чем из 128 экстентов) Database Engine откладывает фактическое освобождение страниц, избегая блокировки размещения, требуемой для удаления больших объектов, с помощью разделения процесса на две стадии: логическую и физическую.

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