SQL-Ex blog

Здесь приводится несколько рекомендаций, которым вы можете следовать, чтобы сделать индексы более эффективными в плане улучшения производительности, при создании, внедрении и обслуживании индексов:

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

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

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

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

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

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

Ограничивайте ключевые столбцы столбцами с высоким уровнем селективности. Общее правило эффективного индексирования состоит в ограничении ключевых столбцов столбцами с высокой селективностью, поскольку чем выше уровень селективности столбца, тем более вероятен выбор этого столбца в качестве ключевого. Например, хорошими кандидатами на ключевые столбцы индекса являются столбцы, используемые в предложениях DISTINCT, WHERE, ORDER BY, GROUP BY И LIKE.

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

Разбиение страниц интенсивно использует ресурсы и зависит от размера индекса и других процессов, происходящих в базе данных. Процесс может вызвать существенное понижение производительности. Чтобы предотвратить разбиение или, по крайней мере, уменьшить необходимость в нем, вы должны использовать незаполненный индекс и задавать значение коэффициента заполнения. Значение коэффициента заполнения определяет процент свободного пространства на каждой листовой странице при наполнении данными, резервируя свободное пространство для последующего роста индекса. Процент заполнения может быть установлен в 0, или значение процента от 1 до 100. Для всего сервера значением по умолчанию является 0, что означает полное заполнение листовых страниц. Разреженный индекс (padding) оставляет свободное пространство на каждой странице промежуточных уровней индекса. Опция разреженного индексирования полезна только когда указан коэффициент заполнения, поскольку использует процентное отношение, заданное коэффициентом заполнения. По умолчанию SQL Server гарантирует, что каждая страница индекса имеет достаточно пустого пространства для того, чтобы вместить, по крайней мере, одну строку индекса максимального размера при заданном наборе ключей на промежуточных страницах. Однако при разрежении индекса, если процентное отношение, заданное для коэффициента заполнения недостаточно велико, чтобы вместить строку, SQL Server сам изменяет его на минимально допустимое.

Перестраивайте индексы на основе уровня фрагментации. Фрагментация индекса может происходить в активно используемой базе данных. Поскольку SQL Server поддерживает индексы на постоянной основе при выполнении операций DML, то они отражают изменения данных. Наша главная цель, как администраторов баз данных, проверять фрагментацию индексов и корректировать её с минимальным влиянием на операции пользователей.

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