Sqlо индексах и производительности


Содержание

SQL – Индексы

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

Индекс помогает ускорить для запросов SELECT и предложения WHERE, но это замедляет ввод данных, с заявлениями UPDATE и INSERT. Индексы могут быть созданы или удалены без влияния на данные.

Создание индекса предполагает заявление CREATE INDEX, которое позволяет назвать индекс, чтобы указать таблицу и какой столбец или столбцы индексировать и указать, является ли индекс в порядке возрастания или убывания.

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

Команда CREATE INDEX

Основной синтаксис CREATE INDEX выглядит следующим образом:

Одноколоночные индексы

Индекс для одного столбца создается на основе только одного столбца таблицы. Базовый синтаксис выглядит следующим образом.

Уникальные индексы

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

Составные индексы

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

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

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

Неявные индексы

Неявные индексы – это индексы, которые автоматически создаются на сервере базы данных при создании объекта. Индексы автоматически создаются для первичного ключа и ограничения уникальности.

Команда DROP INDEX

Индекс может быть удален с помощью SQL команды DROP. Следует соблюдать осторожность при удалении индекса, поскольку производительность может либо замедлиться или улучшиться.

Базовый синтаксис выглядит следующим образом:

Вы можете посмотреть пример ограничения INDEX, чтобы увидеть некоторые реальные примеры по индексам.

Когда следует избегать индексов?

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

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

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Основы индексов в Microsoft SQL Server

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

Что такое индексы в базе данных?

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

Типы индексов в Microsoft SQL Server

В Microsoft SQL Server существуют следующие типы индексов:

  • Кластеризованный (Clustered) – это индекс, который хранит данные таблицы в отсортированном, по значению ключа индекса, виде. У таблицы может быть только один кластеризованный индекс, так как данные могут быть отсортированы только в одном порядке. По возможности каждая таблица должна иметь кластеризованный индекс, если у таблицы нет кластеризованного индекса, такая таблица называется «кучей». Кластеризованный индекс создается автоматически при создании ограничений PRIMARY KEY (первичный ключ) и UNIQUE, если до этого кластеризованный индекс для таблицы еще не был определен. В случае создания кластеризованного индекса для таблицы (кучи), в которой есть некластеризованные индексы, то после создания все их необходимо перестроить.
  • Некластеризованный (Nonclustered) – это индекс, который содержит значение ключа и указатель на строку данных, содержащую значение этого ключа. У таблицы может быть несколько некластеризованных индексов. Создаваться некластеризованные индексы могут как на таблицах с кластеризованным индексом, так и без него. Именно этот тип индекса используется для повышения производительности часто используемых запросов, так как некластеризованные индексы обеспечивают быстрый поиск и доступ к данным по значениям ключа;
  • Фильтруемый (Filtered) – это оптимизированный некластеризованный индекс, который использует предикат фильтра для индексирования части строк в таблице. Если хорошо спроектировать такой тип индекса, то он может повысить производительность запросов, а также снизить затраты на обслуживание и хранение индексов по сравнению с полнотабличными индексами;
  • Уникальный (Unique) – это индекс, который обеспечивает отсутствие повторяющихся (одинаковых) значений ключа индекса, гарантируя тем самым уникальность строк по данному ключу. Уникальными могут быть как кластеризованные, так и некластеризованные индексы. Если создавать уникальный индекс по нескольким столбцам, индекс гарантирует уникальность каждой комбинации значений в ключе. При создании ограничений PRIMARY KEY или UNIQUE SQL сервер автоматически создает уникальный индекс для ключевых столбцов. Уникальный индекс может быть создан только в том случае, если у таблицы на текущий момент отсутствуют дублирующие значения по ключевым столбцам;
  • Колоночный (Columnstore) – это индекс, основанный на технологии хранения данных в виде столбцов. Данный тип индекса эффективно использовать для больших хранилищ данных, поскольку он может увеличить производительность запросов к хранилищу до 10 раз и также до 10 раз уменьшить размер данных, так как данные в Columnstore индексе сжимаются. Существуют как кластеризованные колоночные индексы, так и некластеризованные;
  • Полнотекстовый (Full-text) – это специальный тип индекса, который обеспечивает эффективную поддержку сложных операций поиска слов в символьных строковых данных. Процесс создания и обслуживания полнотекстового индекса называется «заполнением». Существует такие типы заполнения как: полное заполнение и заполнение на основе отслеживания изменений. По умолчанию SQL сервер полностью заполняет новый полнотекстовый индекс сразу после его создания, но на это может потребоваться значительный объем ресурсов, в зависимости от размеров таблицы, поэтому есть возможность откладывать полное заполнение. Заполнение на основе отслеживания изменений используется для обслуживания полнотекстового индекса после его первоначального полного заполнения;
  • Пространственный (Spatial) – это индекс, который обеспечивает возможность более эффективного использования конкретных операций на пространственных объектах в столбцах с типом данных geometry или geography. Данный тип индекса может быть создан только для пространственного столбца, также таблица, для которой определяется пространственный индекс, должна содержать первичный ключ (PRIMARY KEY);
  • XML – это еще один специальный тип индекса, который предназначен для столбцов с типом данных XML. Благодаря XML-индексу повышается эффективность обработки запросов к XML столбцам. Существует два вида XML-индекса: первичные и вторичные. Первичный XML-индекс индексирует все теги, значения и пути, хранимые в XML столбце. Он может быть создан, только если у таблицы есть кластеризованный индекс по первичному ключу. Вторичный XML-индекс может быть создан, только если у таблицы есть первичный XML-индекс и используется он для повышения производительности запросов по определенному типу обращения к XML-столбцу, в связи с этим существует несколько типов вторичных индексов: PATH, VALUE и PROPERTY;
  • Также существуют специальные индексы для таблиц, оптимизированных для памяти (In-Memory OLTP) такие как: Хэш (Hash) индексы и некластеризованные индексы, оптимизированные для памяти, которые создаются для сканирования диапазона и упорядоченного сканирования.

Создание и удаление индексов в Microsoft SQL Server

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

Примечание! В качестве SQL сервера у меня выступает версия Microsoft SQL Server 2020 Express.

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

Для создания индексов в Microsoft SQL Server существует два способа: первый – это с помощью графического интерфейса среды SQL Server Management Studio (SSMS), и второй – это с помощью языка Transact-SQL, мы с Вами разберем оба способа.

Исходные данные для примеров

Давайте представим, что у нас есть таблица с товарами под названием TestTable, в которой есть три столбца:

    Product >Пример создания кластеризованного индекса

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

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

Для примера давайте просто создадим кластеризованный индекс, без создания первичного ключа. Сначала сделаем это с помощью Management Studio.

Открываем SSMS и в обозревателе объектов находим нужную таблицу и щелкаем правой кнопкой мыши по пункту «Индексы», выбираем «Создать индекс» и тип индекса, в нашем случае «Кластеризованный».

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

После ввода всех необходимых параметров жмем «ОК», в итоге будет создан кластеризованный индекс.

Точно также можно было бы создать кластеризованный индекс, используя инструкцию T-SQL CREATRE INDEX, например, вот так

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

Пример создания некластеризованного индекса с включенными столбцами

Сейчас давайте рассмотрим пример создания некластеризованного индекса, при этом мы укажем столбцы, которые не будет являться ключевыми, но будут включаться в индекс. Это полезно в тех случаях, когда Вы создаете индекс для конкретного запроса, например, для того чтобы индекс полностью покрывал запрос, т.е. содержал все столбцы (это называется «Покрытием запроса»). Благодаря покрытию запроса повышается производительность, так как оптимизатор запросов может найти все значения столбцов в индексе, при этом не обращаясь к данным таблиц, что приводит к меньшему числу дисковых операций ввода-вывода. Но помните, что включение в индекс неключевых столбцов влечет за собой увеличение размера индекса, т.е. для хранения индекса потребуется больше места на диске, а также может повлечь и снижение производительности операций INSERT, UPDATE, DELETE и MERGE на базовой таблице.

Для того чтобы создать некластеризованный индекс с помощью графического интерфейса Management Studio, мы также находим нужную таблицу и пункт индексы, только в данном случае мы выбираем «Создать -> Некластеризованный индекс».

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

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

На Transact-SQL это будет выглядеть следующим образом.

Пример удаления индекса в Microsoft SQL Server

Для того чтобы удалить индекс можно щелкнуть правой кнопкой по нужному индексу и нажать «Удалить», затем подтвердить свое действия нажав «ОК».

или также можно использовать инструкцию DROP INDEX, например

Следует отметить, что инструкция DROP INDEX неприменима к индексам, которые были созданы путем создания ограничений PRIMARY KEY и UNIQUE. В данном случае для удаления индекса нужно использовать инструкцию ALTER TABLE с предложением DROP CONSTRAINT.

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

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

В каких случаях использовать реорганизацию индекса, а в каких перестроение?

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

В данном случае нас интересует столбец avg_fragmentation_in_percent, т.е. процентная доля логической фрагментации.

Так вот, Microsoft рекомендует:

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

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

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

Реорганизация индекса – это процесс дефрагментации индекса, который дефрагментирует конечный уровень кластеризованных и некластеризованных индексов по таблицам и представлениям, физически переупорядочивая страницы концевого уровня в соответствии с логическим порядком (слева направо) конечных узлов.

Для реорганизации индекса можно использовать как графический инструмент SSMS, так и инструкцию Transact-SQL.

Реорганизация индекса с помощью Management Studio

Реорганизация индекса с помощью Transact-SQL

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

Перестроение индекса – это процесс, при котором происходит удаление старого индекса и создание нового, в результате чего фрагментация устраняется.

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

Первый. Используя инструкцию ALTER INDEX с предложением REBUILD. Эта инструкция заменяет инструкцию DBCC DBREINDEX. Обычно для массового перестроения индексов используется именно этот способ.

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

В Management Studio функционал для перестроения также доступен. Правой кнопкой по нужному индексу «Перестроить».

На этом материал по основам индексов в Microsoft SQL Server закончен, если Вас интересует язык T-SQL, то рекомендую почитать мою книгу «Путь программиста T-SQL», удачи!

Sqlо индексах и производительности

Одним из способов повышения производительности приложений SQL Server Compact 4.0 является оптимизация используемых запросов. В следующих разделах рассматриваются конкретные методы повышения производительности при обработке запросов.

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

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

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

Как и большинство других методов оптимизации, данный метод не лишен недостатков. Например, увеличение количества индексов может ускорить выполнение запросов, содержащих команду SELECT . Однако команды управления данными ( INSERT , UPDATE и DELETE ) будут выполняться при этом значительно медленнее, поскольку при каждой операции необходимо будет изменять большее число индексов. Поэтому в тех случаях, когда большая часть запросов содержит инструкцию SELECT , создание дополнительных индексов может повысить скорость обработки запросов. Однако, если приложение выполняет большое количество операций по управлению данными, количество индексов не следует увеличивать без необходимости.

SQL Server Compact поддерживает класс событий showplan, помогающий оценивать и оптимизировать запросы. SQL Server Compact использует ту же схему класса showplan, что и SQL Server 2008 R2, но поддерживает SQL Server Compact не все операторы. Дополнительные сведения о схеме Microsoft Showplan Schema см. по адресу http://schemas.microsoft.com/sqlserver/2004/07/showplan/.

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

Создание индексов с высокой избирательностью

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

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

Чтобы определить избирательность индекса, следует выполнить для таблиц SQL Server Compact хранимую процедуру sp_show_statistics. Например, чтобы оценить избирательность столбцов Customer ID и Ship Via, можно выполнить следующие хранимые процедуры.

sp_show_statistics_steps ‘orders’, ‘customer id’;

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

(90 rows affected)

sp_show_statistics_steps ‘orders’, ‘reference3’;

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

(3 rows affected)

Результаты показывают, что столбец Customer ID имеет значительно более низкую степень повторяемости. Это означает, что индекс по данному столбцу будет иметь большую избирательность, чем индекс по столбцу Ship Via.

Создание многостолбцовых индексов

Многостолбцовые индексы являются дальнейшим развитием одностолбцовых индексов. Они используются для вычисления критериев фильтра, соответствующих набору префиксов ключевых столбцов. Например, комбинированный индекс CREATE INDEX Idx_Emp_Name ON Employees («Last Name» ASC, «First Name» ASC) помогает исполнять следующие запросы.

. WHERE «Last Name» = ‘Doe’

. WHERE «Last Name» = ‘Doe’ AND «First Name» = ‘John’

. WHERE «First Name» = ‘John’ AND «Last Name» = ‘Doe’

Однако при исполнении следующего запроса этот индекс не поможет.

. WHERE «First Name» = ‘John’

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

Отказ от индексирования небольших таблиц

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

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

Предположим, что таблица имеет следующую структуру.


INTEGER (4 байта)

INTEGER (4 байта)

SMALLINT (2 байта)

В таблице 2820 строк. В соответствии с формулой, для хранения данных этой таблицы необходимо приблизительно 16 страниц:

= ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15,15 страниц

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

После создания ограничений для первичных и внешних ключей SQL Server Compact автоматически создает для них индексы и позволяет использовать при оптимизации запросов все преимущества, предоставляемые индексами. Не следует забывать, что чем меньше длина первичных и внешних ключей, тем быстрее выполняется объединение.

Использование индексов и предложений фильтров

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

Аргумент поиска (SARG) ограничивает область поиска, поскольку он указывает точное соответствие, диапазон значений или пересечение двух или более элементов, объединенных инструкцией AND. Аргумент поиска может принимать одну из следующих форм.

В качестве операторов SARG могут использоваться следующие операторы: =, >, =, ‘1/1/2002’

«Customer ID» > ‘ABCDE’ AND «Customer ID» , NOT EXISTS, NOT IN, NOT LIKE и внутренние функции.

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

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

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

Многие операции запросов могут выполняться без сохранения промежуточных результатов. Такие операции называют конвейерными. Примерами конвейерных операций являются проекции, выборки и объединения. Запросы, основанные на этих командах, могут сразу возвращать результаты. Другие операции (такие как SORT и GROUP-BY ), прежде чем вернуть результат родительским операциям, должны обработать все входные данные. Про такие операции говорят, что они требуют материализации. Из-за необходимости материализации при выполнении запросов, основанных на подобных операциях, как правило, присутствует начальная задержка. После начальной задержки подобные запросы, как правило, очень быстро возвращают результаты.

В запросах, требующих малого времени отклика, следует избегать материализации. Например, выполнение команды ORDER-BY с использованием индекса обеспечивает меньшее время отклика, чем с использованием сортировки. Более подробно этот вопрос рассматривается ниже.

Уменьшение времени отклика путем индексирования столбцов, используемых в операциях ORDER-BY, GROUP-BY, DISTINCT

Операции ORDER-BY , GROUP-BY , и DISTINCT представляют собой различные типы сортировки. Обработчик запросов SQL Server Compact использует при сортировке два способа. Если записи уже отсортированы с помощью индекса, обработчику необходимо использовать только индекс. В противном случае обработчик должен сначала отсортировать записи, используя временную рабочую таблицу. Подобная предварительная сортировка может вызвать значительную начальную задержку на устройствах с медленными процессорами и ограниченным объемом памяти, поэтому ее следует избегать в запросах и приложениях, требующих малого времени отклика.

В контексте многостолбцовых индексов при выборе индексов для операций ORDER-BY или GROUP-BY , столбцы, для которых выполняются операции ORDER-BY или GROUP-BY , должны соответствовать набору префиксов столбцов индексов и следовать в том же порядке. Например, индекс CREATE INDEX Emp_Name ON Employees («Last Name» ASC, «First Name» ASC) поможет оптимизировать следующие запросы.

. ORDER BY / GROUP BY «Last Name» .

. ORDER BY / GROUP BY «Last Name», «First Name» .

Данный индекс не поможет в оптимизации следующих запросов.

. ORDER BY / GROUP BY «First Name» .

. ORDER BY / GROUP BY «First Name», «Last Name» .

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

. DISTINCT «Last Name», «First Name» .

. DISTINCT «First Name», «Last Name» .

Данный индекс не поможет в оптимизации следующих запросов.

. DISTINCT «First Name» .

. DISTINCT «Last Name» .

Если запрос самостоятельно возвращает уникальные строки, не следует использовать ключевое слово DISTINCT , поскольку это всего лишь вызовет дополнительную нагрузку на систему.

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

Примечание

Обработчик запросов SQL Server Compact всегда изменяет подчиненный запрос IN таким образом, чтобы использовалась команда JOIN. Поэтому с запросами, содержащими подчиненные запросы с предложением IN, данный подход использовать не нужно.

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

SELECT «Order ID» FROM Orders O

WHERE EXISTS (SELECT «Order ID»

FROM «Order Details» OD

AND Discount >= 0.25)

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

SELECT DISTINCT O.»Order ID» FROM Orders O INNER JOIN «Order Details»

OD ON O.»Order WHERE Discount >= 0.25

Ограничение использования команд Outer JOIN

Команды OUTER JOIN обрабатываются не так, как команды INNER JOIN, поскольку оптимизатор не пытается изменить порядок объединения таблиц, указанных в команде OUTER JOIN, как это происходит при обработке команды INNER JOIN. Сначала выполняется обращение к внешней таблице (левая таблица в команде LEFT OUTER JOIN и правая таблица в команде RIGHT OUTER JOIN), а затем — к внутренней таблице. Использование фиксированного порядка объединения может приводить к созданию неоптимальных планов выполнения.

Дополнительные сведения о запросах, содержащих INNER JOIN, см. в статье базы знаний Майкрософт.

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

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

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

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

Открыть базовую таблицу Orders.

Найти требуемую строку, используя нужное значение поля Order ID.

Получить значение поля Customer ID.

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

Вариант с использованием запроса является более простым, но более медленным, поскольку обработчик запросов SQL Server Compact преобразует декларативные инструкции SQL в приведенные выше три операции. Затем эти операции выполняются в указанной последовательности. Выбор метода, используемого для решения конкретной задачи, зависит от того, что является более важным для работы приложения — простота или производительность.

Sqlо индексах и производительности

ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008) База данных SQL Azure Хранилище данных SQL Azure Parallel Data Warehouse

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

Простые примеры:

Основные сценарии:

  • В SQL Server 2020 и базы данных SQL Azure используйте некластеризованный индекс в индексе для повышения производительности запросов хранилища данных. В разделе индексы Columnstore — хранилище данных

Необходимо создать другой тип индекса?

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

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

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

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

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

Если аргумент CLUSTERED не указан, создается некластеризованный индекс.

Примечание

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

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

НЕКЛАСТЕРИЗОВАННЫЙ
Создание индекса, задающего логическое упорядочение для таблицы. Логический порядок строк в некластеризованном индексе не влияет на их физический порядок.

Каждая таблица может содержать до 999 некластеризованных индексов независимо от способа их создания: неявно с помощью ограничений PRIMARY KEY и UNIQUE или явно с помощью инструкции CREATE INDEX.

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

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

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

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

До 32 столбцов могут быть объединены в один составной ключ индекса. Все столбцы ключа составного индекса должны находиться в одной таблице или одном и том же представлении. Максимальный общий размер значений составного индекса равен 900 байт для кластеризованного индекса и 1700 для некластеризованного индекса. Ограничены 16 столбцами и 900 байт для версий до База данных SQL версии 12 и SQL Server 2020.

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

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

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

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

Имена столбцов в списке INCLUDE не могут повторяться и не могут использоваться одновременно как ключевые и неключевые. Некластеризованные индексы всегда содержат столбцы кластеризованного индекса, если для таблицы определен кластеризованный индекс. Дополнительные сведения см. в статье Create Indexes with Included Columns.

Допускаются данные всех типов, за исключением text, ntextи image. Индекс должен создаваться или перестраиваться в автономном режиме (ONLINE = OFF) Если любой из заданных неключевых столбцов имеет varchar(max), nvarchar(max), или varbinary(max) типов данных.

Вычисляемые столбцы, являющиеся детерминированными и точными или неточными, могут быть включенными столбцами. Вычисляемые столбцы, производные от изображение, ntext, текст, varchar(max), nvarchar(max), varbinary(max), и xml типов данных может быть включено в неключевых столбцов, при условии, что тип данных вычисляемого столбца является допустимым в качестве включенного столбца. Дополнительные сведения см. в разделе Indexes on Computed Columns.

Сведения о создании XML-индекса см. в разделе CREATE XML INDEX (Transact-SQL).

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

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

Далее приведено несколько примеров использования предикатов фильтра для таблицы Production.BillOfMaterials :

WHERE StartDate > ‘20000101’ AND EndDate

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN (‘20000404’, ‘20000905’) AND EndDate IS NOT NULL

Отфильтрованные индексы не применяются к XML-индексам и полнотекстовым индексам. Для индексов UNIQUE только выбранные строки должны иметь уникальные значения индексов. Отфильтрованные индексы не поддерживают параметр IGNORE_DUP_KEY.

Д partition_scheme_name(column_name)

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Задает схему секционирования, которая определяет файловые группы соответствующие секциям секционированного индекса. Схема секционирования должна существовать в базе данных путем выполнения инструкции CREATE PARTITION SCHEME или ALTER PARTITION SCHEME. column_name указывает столбец, по которому будет секционирован секционированного индекса. Этот столбец должен соответствовать типу данных, длине и точности аргумента секции функции, partition_scheme_name используется. column_name не ограничивается столбцы в определении индекса. Любой столбец базовой таблицы можно указать, за исключением случая секционирования индекса UNIQUE, когда column_name должен быть выбран из используемых в уникальном ключе. Это ограничение дает возможность компоненту Компонент Database Engine проверять уникальность значений ключа только в одной секции.

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

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

Примечание

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

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

Примечание
Область применения: начиная с SQL Server 2008 до SQL Server 2020.

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

ON «default«

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

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

Слово «default» в этом контексте не является ключевым. Идентификатор файловой группы по умолчанию она должен иметь разделители, как в выражениях ON »по умолчанию» или ON [по умолчанию]. Если указано значение «default» (по умолчанию), параметр QUOTED_IDENTIFIER должен иметь значение ON для текущего сеанса. Это параметр по умолчанию. Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER (Transact-SQL).

[FILESTREAM_ON < filestream_filegroup_name | partition_scheme_name | «NULL»>]

Область применения: начиная с SQL Server 2008 до SQL Server 2020.

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


filestream_filegroup_name имя файловой группы FILESTREAM. В файловой группе должен быть определен один файл для файловой группы с помощью CREATE DATABASE или ALTER DATABASE инструкции; в противном случае произойдет ошибка.

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

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

Предложение FILESTREAM_ON NULL может быть указано в инструкции CREATE INDEX, если создается кластеризованный индекс и таблица не содержит столбец FILESTREAM.

Дополнительные сведения см. в разделе FILESTREAM (SQL Server).

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

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

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

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

Чтобы создать индекса для представления, это представление оно должно быть определено с параметром SCHEMABINDING. Прежде чем создавать любой некластеризованный индекс для представления, необходимо создать уникальный кластеризованный индекс. Дополнительные сведения об индексированных представлениях см. в разделе «Примечания».

Начиная с версии SQL Server 2020, объект может быть таблица, которая сохраняется в кластеризованный индекс.

База данных SQL Azureподдерживает формат трехкомпонентного имени имябазыданных. [schema_name]. object_name при имябазыданных — текущая база данных или имябазыданных база данных tempdb и object_name начинается с #.

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

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

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

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

ОТКЛЮЧЕНИЕ или fillfactor не указан
Страницы промежуточного уровня заполняются почти полностью, при этом остается достаточно места по крайней мере для одной строки максимального размера, возможного в этом индексе при заданном наборе ключей на промежуточных страницах.

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

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

Аргумент FILLFACTOR ** = ** fillfactor

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

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

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

Создание кластеризованного индекса с аргументом FILLFACTOR меньше 100 влияет на объем пространства хранения, занимаемого данными, т. к. компонент Компонент Database Engine перераспределяет данные, когда создает кластеризованный индекс.

Важно
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

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

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

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

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

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

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

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

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

IGNORE_DUP_KEY нельзя установить в значение ON для индексов, создаваемых для представлений, неуникальных индексов, XML-индексов, пространственных индексов и фильтруемых индексов.

Для просмотра значения IGNORE_DUP_KEY используйте sys.indexes.

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

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

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

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

Чтобы восстановить автоматическое обновление статистики, следует установить STATISTICS_NORECOMPUTE в значение OFF или выполнить UPDATE STATISTICS без предложения NORECOMPUTE.

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

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

STATISTICS_INCREMENTAL = OFF >
Когда ON, являются статистики создаются как статистики отдельно по секциям. Когда OFF, дерево статистик удаляется и SQL Server повторно вычисляет статистики. Значение по умолчанию — OFF.

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

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

Статистики, созданные в доступных для чтения базах данных-получателях AlwaysOn.

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

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

Статистика, созданная по представлениям.

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

Статистики, созданные с пространственными индексами или XML-индексами.

ПРЕДЛОЖЕНИЕ DROP_EXISTING = OFF >
Есть возможность удалить перестроения существующего кластеризованного или некластеризованного индекса со спецификациями изменяемого столбца и именем для индекса. Значение по умолчанию — OFF.

ON
Указывает, удалите и заново создайте существующий индекс, который должен иметь имя, совпадающее с именем параметра index_name.

OFF
Указано, удалите и заново создайте существующий индекс. SQL Server выводится сообщение об ошибке, если индекс с указанным именем уже существует.

С помощью инструкции DROP_EXISTING можно изменить.

  • Rowstore некластеризованный индекс в кластеризованный rowstore.

Предложение DROP_EXISTING нельзя изменить:

Rowstore кластеризованный индекс в некластеризованный rowstore.

Кластеризованный индекс для любого типа индекса rowstore.

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

ONLINE = OFF >
Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.

Важно

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

ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это включает запросы или обновления применительно к обрабатываемой базовой таблице и индексам. В начале операции совмещаемая блокировка (S) удерживается на исходном объекте в течение очень короткого времени. В конце операции на источнике на короткое время удерживается совмещаемая блокировка (S), если создается некластеризованный индекс. Если в режиме в сети создается или удаляется кластеризованный индекс и, если перестраивается кластеризованный или некластеризованный индекс, удерживается блокировка SCH-M (изменения схемы). При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.

OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Блокировку изменения схемы (Sch-M) в таблице получает операция с индексами вне сети, которая создает, перестраивает или удаляет кластеризованный индекс либо перестраивает или удаляет некластеризованный индекс. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT.

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

Индекс локальной временной таблицы.

Исходные уникальные кластеризованные индексы представлений.

Отключенные кластеризованные индексы.

Кластеризованные индексы, если базовая таблица содержит типы данных LOB: изображение, ntext, тексти Пространственные типы.

varchar(max) и varbinary(max) столбцы не могут быть частью индекса. В SQL Server (начиная с версии SQL Server 2012) и в База данных SQL, если таблица содержит varchar(max) или varbinary(max) , кластеризованный индекс, содержащий другие столбцы, можно построить или перестроить с использованием ONLINE параметр. База данных SQLне разрешает ONLINE вариант, если базовая таблица содержит varchar(max) или varbinary(max) столбцов.

Дополнительные сведения см. в статье Perform Index Operations Online.

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

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

ON
Блокировки строк допустимы при доступе к индексу. Компонент Компонент Database Engine определяет, когда используются блокировки строки.

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

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

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

ON
Блокировки страниц возможны при доступе к индексу. Компонент Компонент Database Engine определяет, когда используются блокировки страниц.

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

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Переопределяет Настройка max degree of parallelism параметр конфигурации сервера параметр конфигурации в течение операции с индексами. MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Максимальное число процессоров — 64.

max_degree_of_parallelism может быть:

1
Подавляет формирование параллельных планов.

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

0 (по умолчанию)
В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

Параллельные операции с индексами доступны не во всех выпусках Microsoft SQL Server. Список функций, поддерживаемых различными выпусками SQL Server, в разделе выпусков и компонентов поддерживается для SQL Server 2020.

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

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

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

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

Дополнительные сведения о сжатии см. в разделе сжатие данных.

В РАЗДЕЛАХ ( <

| <>> > [ ,. n ] )

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

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

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

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

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

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

<>>можно указать как номера секций, разделенными ключевым словом, например: ON PARTITIONS (6-8).

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

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

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

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

Индексы поддерживают расширенные свойства.

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

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

Ограничения и ограничения:


  • Параметр FILESTREAM_ON не является действительным, если создается некластеризованный индекс в таблице хранится как кластеризованный индекс.

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

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

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

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

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

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

Статистические данные в SQL Server 2020 не создаются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.

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

Обязательные параметры SET для отфильтрованных индексов

Параметры SET в столбце Required Value необходимы при возникновении любого из следующих условий.

Создание отфильтрованного индекса.

Операция INSERT, UPDATE, DELETE или MERGE изменяет данные в отфильтрованном индексе.

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

Предупреждение
Параметры SET Обязательное значение Значение сервера по умолчанию По умолчанию

Значение OLE DB и ODBC

По умолчанию

Значение DB-Library

ANSI_NULLS ON ON ON OFF
ANSI_PADDING ON ON ON OFF
ANSI_WARNINGS* ON ON ON OFF
ARITHABORT ON ON OFF OFF
CONCAT_NULL_YIELDS_NULL ON ON ON OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ON ON ON OFF

*Если уровень совместимости базы данных равен 90 или более, при установке параметра ANSI_WARNINGS в состояние ON параметр ARITHABORT также устанавливается в состояние ON. Если уровень совместимости базы данных установлен в состояние 80 или более раннее, то параметр ARITHABORT необходимо явным образом установить в состояние ON.

Если параметры SET неверны, может произойти следующее.

Отфильтрованный индекс не будет создан.

Компонент Компонент Database Engine сформирует ошибку и выполнит откат любой инструкции INSERT, UPDATE, DELETE или MERGE, которая изменила значения данных в индексе.

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

Дополнительные сведения об отфильтрованных индексах см. в разделе Создание отфильтрованных индексах.

Максимальный размер ключа индекса составляет 900 байт для кластеризованного индекса и 1700 байт для некластеризованного индекса. (Перед База данных SQL версии 12 и SQL Server 2020 ограничение всегда была 900 байт.) Индексы на varchar , превышающих ограничение байтов может быть создан, если существующие данные в столбцах не превышает предел, во время создания индекса, однако последующие операции вставки или обновления на столбцы, для которых общий размер превышает предел не будет работать. Ключ кластеризованного индекса не может включать в себя столбцы varchar , для которых существуют данные в единице размещения ROW_OVERFLOW_DATA. Если кластеризованный индекс создается для столбца типа varchar и существующие данные располагаются в единице размещения IN_ROW_DATA, то все последующие операции вставки или обновления для данного столбца, выталкивающие данные за пределы строки, будут завершаться ошибкой.

Некластеризованные индексы могут включать неключевые столбцы на конечном уровне индекса. Эти столбцы не рассматривает Компонент Database Engine при вычислении размера ключа индекса. Дополнительные сведения см. в статье Create Indexes with Included Columns.

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

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

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

Вычисляемые столбцы, основанные на выражениях языка Transact-SQL, функциях CLR и методах определяемых пользователем типов данных CLR, помеченных пользователем как детерминированные.

Вычисляемые столбцы, основанные на выражениях, которые определены компонентом Компонент Database Engine как детерминированные, но не являются точными.

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

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

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

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

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

Дополнительные сведения см. в разделе Indexes on Computed Columns.

Неключевые столбцы, называемые «включенными столбцами», могут добавляться на конечный уровень некластеризованного индекса для повышения производительности запроса благодаря тому, что индекс включает все необходимые данные для запроса. Т. е. все столбцы, указанные в запросе, включаются в индекс в качестве ключевых или неключевых столбцов. Таким образом оптимизатор запросов может найти все необходимые данные путем просмотра индекса, не обращаясь к данным таблицы или кластеризованного индекса. Дополнительные сведения см. в статье Create Indexes with Included Columns.

На сервере SQL Server 2005 представлены новые параметры индексов и изменен способ установки параметров. Обратной совместимости синтаксиса WITH option_name эквивалентен аргументу WITH ( ** = ** ON ). Устанавливая параметры индекса, необходимо соблюдать следующие правила.

Новые параметры индекса можно указать только с помощью аргумента WITH (option_name ** = ** ON | OFF).

Нельзя задавать параметры с помощью нового синтаксиса и совместимого старого в одной и той же инструкции. Например, указание WITH (DROP_EXISTING, ONLINE ** = ** ON) приводит к ошибке инструкции.

При создании XML-индекса параметры должны указываться с помощью аргумента WITH (option_name ** = ** ON | OFF).

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

Если индекс принудительно налагает ограничение PRIMARY KEY или UNIQUE и его определение никак не меняется, он удаляется и создается вновь с сохранением существующих ограничений. Но если изменить определение индекса, инструкция вызовет ошибку. Чтобы изменить ограничение PRIMARY KEY или UNIQUE, удалите ограничение и добавьте ограничение вместе с новым определением.

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

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

Предложение DROP_EXISTING не сортирует данные заново, если те же ключевые столбцы индекса используются в том же порядке с тем же порядком сортировки по возрастанию или убыванию, за исключением случаев, когда инструкция индекса задает некластеризованный индекс и параметр ONLINE равен OFF. Если кластеризованный индекс отключен, операция CREATE INDEX WITH DROP_EXISTING должна выполняться с параметром ONLINE в значении OFF. Если некластеризованный индекс отключен и не связан с отключенным кластеризованным индексом, операция CREATE INDEX WITH DROP_EXISTING может выполняться с параметром ONLINE в значении OFF или ON.

Если удаляются или перестраиваются индексы со 128 или более экстентами, компонент Компонент Database Engine откладывает фактическое освобождение страниц и связанных с ними блокировок до фиксации транзакции.

Следующие правила применяются к операциям с индексами в режиме в сети.

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

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

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

Дополнительные сведения см. в статье Perform Index Operations Online.

Если заданы аргументы ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при обращении к индексу разрешены блокировки на уровне строк, страниц и таблиц. Компонент Компонент Database Engine выбирает соответствующую блокировку и может повышать уровень с блокировки строки или страницы до блокировки таблицы.

Если заданы аргументы ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при обращении к индексу разрешены только блокировки на уровне таблиц.

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

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

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

Неконечные страницы индекса не сжаты на уровне страниц, но могут быть сжаты на уровне строк.

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

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

На секционированные индексы налагаются следующие ограничения.

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

Инструкции ALTER INDEX <> > . Инструкция REBUILD PARTITION . производит перестроение указанной секции индекса.

Инструкции ALTER INDEX <> > . Инструкция REBUILD WITH . производит перестроение всех секций индекса.

Оценить состояние сжатия таблицы, индекса или секции можно с помощью хранимой процедуры sp_estimate_data_compression_savings .

Необходимо разрешение ALTER для таблицы или представления. Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner .

Хранилище данных SQLи Параллельное хранилище данных, невозможно создать:

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

Не удается создать индекс для представления.

Чтобы просмотреть сведения о существующих индексов, можно выполнить запрос sys.indexes (Transact-SQL) представления каталога.

База данных SQL не поддерживает параметры файловой группы и filestream.

A. Создание rowstore простого некластеризованного индекса

В следующем примере создается некластеризованный индекс на VendorID столбец Purchasing.ProductVendor таблицы.

Б. Создание rowstore простого некластеризованного составного индекса

В следующем примере создается некластеризованный составной индекс на SalesQuota и SalesYTD столбцы Sales.SalesPerson таблицы.

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

В следующем примере создается некластеризованный индекс на VendorID столбец ProductVendor в таблицу Purchasing базы данных.

Г. Добавление столбца индекса

В следующем примере создается индекс IX_FF с двумя столбцами из таблицы dbo. Таблица FactFinance. Следующий оператор перестраивает индекс в один столбец и сохраняет существующее имя.

Д. Создание уникального некластеризованного индекса

В следующем примере создается уникальный некластеризованный индекс на столбце Name таблицы Production.UnitMeasure в базе данных AdventureWorks2012. Индекс требует уникальности данных, вставляемых в столбец Name .

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

В результате выдается сообщение об ошибке:

Е. Использование параметра IGNORE_DUP_KEY

В следующем примере демонстрируется влияние параметра IGNORE_DUP_KEY со значениями ON и OFF на операцию вставки нескольких строк во временную таблицу. В таблицу #Test вставляется одна строка, которая намеренно приведет к появлению повторяющихся значений при выполнении второй многострочной операции вставки INSERT . Счетчик строк таблицы возвращает количество вставленных строк.

Ниже приведены результаты второй инструкции INSERT .

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

Те же инструкции выполняются вновь, но теперь с аргументом IGNORE_DUP_KEY , равным OFF .

Ниже приведены результаты второй инструкции INSERT .

Обратите внимание, что ни одна из строк таблицы Production.UnitMeasure не была вставлена, хотя ограничение индекса UNIQUE было нарушено только одной строкой.

Ж. Использование предложения DROP_EXISTING для удаления и повторного создания индекса

В следующем примере удаляется и создается повторно существующий индекс на столбце ProductID таблицы Production.WorkOrder в базе данных AdventureWorks2012 с использованием параметра DROP_EXISTING . Указываются также аргументы FILLFACTOR и PAD_INDEX .

З. Создать индекс для представления

В следующем примере создаются представление и индекс этого представления. Включено два запроса, использующих созданное индексированное представление.

И. Создание индекса с включенными (неключевыми) столбцами

В следующем примере создается некластеризованный индекс с одним ключевым столбцом ( PostalCode ) и четырьмя неключевыми столбцами ( AddressLine1 , AddressLine2 , City , StateProvinceID ). Далее следует запрос, все данные для которого есть в индексе. Выводить индекс, выбранный оптимизатором запросов, на запроса в меню SQL Server Management Studioвыберите Показать действительный план выполнения перед выполнением запроса.

К. Создание секционированного индекса

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

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Л. Создание отфильтрованного индекса

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

М. Создание сжатого индекса

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

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

Следующий пример демонстрирует создание индекса для секционированной таблицы с помощью сжатия страниц для секции 1 индекса и сжатия строк для секций индекса со 2 по 4 .

Н. Базовый синтаксис

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

В следующем примере создается некластеризованный индекс на VendorID столбец ProductVendor таблицы.

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


В следующем примере создается некластеризованный индекс на VendorID столбец ProductVendor в таблицу Purchasing базы данных.

Т. Добавление столбца индекса

В следующем примере создается индекс IX_FF с двумя столбцами из таблицы dbo. Таблица FactFinance. Следующий оператор демонстрирует перестроение индекса с тем же именем и один столбец.

Sqlо индексах и производительности

Ничто так не «убивает» базу, как «плохое» индексирование (С)

Создать правильные индексы — это только половина дела. Нужно еще и правильно ими управлять.

В процессе работы с базой, особенно, если данные в ней довольно часто модифицируются/добавляются/удаляются, со временем индексы приходят в некоторую «негодность». Увеличивается их «фрагментарность», ухудшается их влияние на скорость исполнения запросов к БД.
Оптимальным считается, когда уровень фрагментации индекса не превышает 10%, но для поддержания такого показателя необходимо проводить периодическую их дефрагментацию и реорганизацию.
(подробнее про реорганизацию и дефрагментацию)

Основным инструментом в процессе управления фрагментацией индексов выступает функция sys.dm_db_index_physical_stats (подробнее)

Для определения списка индексов с уровнем фрагментарности выше оптимальных 10% в своей работе я воспользовалась вот таким запросом:

DECLARE @db_name varchar ( 50 ) = N ‘db_name’ ,
@table_name varchar ( 250 ) = N ‘db_name.dbo.tbl_name’

SELECT IndStat . database_ >,
IndStat . object_ >,
QUOTENAME ( s . name ) + ‘.’ + QUOTENAME ( o . name ) AS [ object_name ] ,
IndStat . index_ >,
QUOTENAME ( i . name ) AS index_name ,
IndStat . avg_fragmentation_in_percent ,
IndStat . partition_number ,
( SELECT count ( * ) FROM sys . partitions p
WHERE p . object_ >= IndStat . object_ >AND p . index_ >= IndStat . index_ >) AS partition_count
FROM sys . dm_db_index_physical_stats
( DB_ >( @db_name ) , OBJECT_ >( @table_name ) , NULL , NULL , ‘LIMITED’ ) AS IndStat
INNER JOIN sys . objects AS o ON ( IndStat . object_ >= o . object_ >)
INNER JOIN sys . schemas AS s ON s . schema_ >= o . schema_id
INNER JOIN sys . indexes i ON ( i . object_ >= IndStat . object_ >AND i . index_ >= IndStat . index_ >)
WHERE IndStat . avg_fragmentation_in_percent > 10 AND IndStat . index_ >> 0

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

  • CONTROL на специфический объект БД.
  • VIEW DATABASE STATE для получения информации обо всех объектах определенной БД (@object_ >Так же перед использованием желательно обновить статистику БД.

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

USE [ DATABASE ] ;
GO

SET NOCOUNT ON ;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar ( 130 ) ;
DECLARE @objectname nvarchar ( 130 ) ;
DECLARE @indexname nvarchar ( 130 ) ;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar ( 4000 ) ;
DECLARE @dbid smallint;

— Выбираем индексы с уровнем фрагментации выше 10%
— Определяем текущую БД

SET @db >= DB_ >( ) ;
SELECT
[ object_ >] AS object >,
index_ >AS index >,
partition_number AS partitionnum ,
avg_fragmentation_in_percent AS frag , page_count
INTO #work_to_do
FROM sys . dm_db_index_physical_stats ( @db >, NULL , NULL , NULL , N ‘LIMITED’ )
WHERE avg_fragmentation_in_percent > 10.0
AND index_ >> 0 — игнорируем heap
AND page_count > 25 ; — игнорируем маленькие таблицы

— объявляем курсор для списка обрабатываемых partition
DECLARE partitions CURSOR FOR SELECT object >, index >, partitionnum , frag FROM #work_to_do;

— цикл по partition
WHILE ( 1 = 1 )
BEGIN
FETCH NEXT
FROM partitions
INTO @object >, @index >, @partitionnum , @frag;
IF @@FETCH_STATUS 0 BREAK;
SELECT @objectname = QUOTENAME ( o . name ) , @schemaname = QUOTENAME ( s . name )
FROM sys . objects AS o
JOIN sys . schemas AS s ON s . schema_ >= o . schema_id
WHERE o . object_ >= @objectid;

SELECT @indexname = QUOTENAME ( name )
FROM sys . indexes
WHERE object_ >= @object >AND index_ >= @indexid;
SELECT @partitioncount = count ( * )
FROM sys . partitions
WHERE object_ >= @object >AND index_ >= @indexid;

— 30% считаем пределом для определения типа обновления индекса.
IF @frag 30.0
SET @command = N ‘ALTER INDEX ‘ + @indexname + N ‘ ON ‘ + @schemaname + N ‘.’ + @objectname + N ‘ REORGANIZE’ ;
IF @frag >= 30.0
SET @command = N ‘ALTER INDEX ‘ + @indexname + N ‘ ON ‘ + @schemaname + N ‘.’ + @objectname + N ‘ REBUILD’ ;
IF @partitioncount > 1
SET @command = @command + N ‘ PARTITION=’ + CAST ( @partitionnum AS nvarchar ( 10 ) ) ;

EXEC ( @command ) ;
PRINT N ‘Выполнено: ‘ + @command;
END;

CLOSE partitions;
DEALLOCATE partitions;

— удаляем временную таблицу
DROP TABLE #work_to_do;
GO

Еще посмотреть примеры скриптов/хранимок можно тут или вот тут.

Операцию по устранению дефрагментации индексов рекомендуется проводить регулярно (например, раз в неделю/месяц — в зависимости от величины операций модификации над хранимыми данными). Индексы, за состоянием которых не следят, могут очень существенно «просадить» производительность БД при исполнении запросов.

Индексы SQL и повышение производительности

У меня есть некоторые вопросы о индексах SQL и их эффективности. Надеюсь, вы, ребята, можете ответить на них!: D

  • Какая разница при создании индекса всей моей таблицы и индекса моей таблицы и нескольких столбцов? Являются ли эти индексы, указав только несколько столбцов, быстрее?

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

Здесь мое ноу-хау, применимое к SQL Server:

прежде всего, индекс в SQL Server может содержать не более 900 байт в своей записи индекса. Это само по себе делает невозможным наличие индекса со всеми столбцами.

Больше всего: такой индекс не имеет никакого смысла. Чего вы пытаетесь достичь?

Рассмотрим это: если у вас есть индекс на (LastName, FirstName, Street, City) , этот индекс будет не использоваться для ускорения запросов на

Этот индекс будет полезен для поиска по

  • (LastName) , или
  • (LastName, FirstName) , или
  • (LastName, FirstName, Street) , или
  • (LastName, FirstName, Street, City)

но на самом деле ничего другого — конечно, если вы ищете только Street или просто City !

Порядок столбцов в вашем индексе имеет большое значение, и оптимизатор запросов не может просто использовать какой-либо столбец где-то посередине индекса для поиска.

Рассмотрите свою телефонную книгу: она, вероятно, может быть отправлена ​​по LastName, FirstName, может быть, на Street. Так что эта индексация поможет вам найти все «Джо» в вашем городе? Все люди, живущие на «Мейн-стрит»? Нет — вы можете сначала искать LastName — тогда вы получите более конкретную информацию внутри этого набора данных. Просто наличие индекса по всему не ускоряет поиск всех столбцов вообще.

Если вы хотите иметь возможность искать по Street — вам нужно добавить отдельный индекс на (Street) (и, возможно, другой столбец или два, которые имеют смысл).

Если вы хотите иметь возможность искать по Occupation или что-то еще — для этого вам нужен другой конкретный индекс.

Просто потому, что ваш столбец существует в индексе, не означает, что ускорит все поиски этого столбца!

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

Не просто слепо индексировать каждый столбец только потому, что вы можете — это гарантия застойной производительности системы. Любой индекс также требует обслуживания и поддержки, поэтому чем больше индексов у вас есть, тем больше операций INSERT, UPDATE и DELETE страдают (замедляются), так как все эти индексы необходимо обновить.

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

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

Если говорить простым языком, то запросы к MySQL условно можно разделить на 2 типа — операции выборки и операции обновления. К первым относятся операции SELECT , ко вторым — UPDATE , INSERT , DELETE . Довольно много информации по этому поводу есть в официальной документации, а так же в российском комьюнити. Но если в официальной документации обычно описывается как правильно использовать индексы и составлять правильные запросы, то в рамках этой статьи мне бы хотелось рассказать о личном опыте того, как можно оптимизировать «всё и разом» если более логично подойти к организации хранения своих данных и выборе типов данных.

Любой разработчик в курсе, что основным слабым звеном в доступе к данным являются дисковые операции. Именно количество дисковых операций, необходимых для выполнения запроса, в основном и влияет на скорость выполнения этого запроса. И если на непосредственные операции чтения/записи к самим данным мы повлиять не можем, то мы можем легко повлиять на операции предшествующие им — а именно операции с индексами. Именно индексы позволяют движку MySQL почти напрямую получать ячейку где хранятся требуемые данные, а не искать их используя fullscan по всему файлу с данными. Именно поэтому любой опытный программист и администратор работающий с базами данных скажет что индекс должен помещаться целиком в память, чтоб исключить лишние дисковые операции. В MySQL за размер буффера для хранения индекса отвечает системная переменная key_buffer_size . Но бесконечно его увеличивать не получится — в железе есть ограничение по максимальному количеству ОЗУ, а вот ограничения по максимальному размеру таблиц в базе данных довольно далеко выходят за рамки обычных жестких дисков, поэтому для нас они почти бесконечны. И рано или поздно возникает проблема выхода размеров индекса за рамки key_buffer_size что приводит к резкому снижению скорости выполнения запросов.

Как уменьшить размер индексов в базе данных

Уменьшить размер индексов можно по нескольким направлениям:

  1. Удалить неиспользуемые индексы
  2. Уменьшить размер целочисленных индексов
  3. Оптимизировать хранение строковых индексов

Рассмотрим каждое из направлений подробнее:

Какие индексы можно удалить

Прежде всего стоит понимать что в одной операции сравнения на одной таблице может максимально использоваться лишь только один индекс. Например в запросе

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

Рассмотрим еще один частовстречающуюся неверную конфигурацию:

В том что в случае если по индексу is_active MySQL увидит несколько тысяч строк, а таблица большая, — оптимизатор будет делать fullscan, т.к. в этом случае выигрыш от последовательного чтения всей таблицы будет больше, чем необходимость делать несколько тысяч отдельных операций случайного доступа к диску. Таким образом индексы всех тех столбцов, которые содержат в себе лишь несколько различных значений (имеют высокую «повторяемость значений»), можно смело удалять. К таковым, например, относятся различные столбцы хранящие флаги (булевы значения), или значения типа enum. Как вариант — эти столбцы можно добавить к другому составному индексу, если они часто используются как дополнительный фильтр при выборке с другими столбцами.

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

был добавлен составной индекс ( added_time , is_approved ), то новый полностью покрывает старый — MySQL сможет комфортно использовать этот новый индекс индекс по первому столбцу. В данном случае старый можно смело удалить. Подробнее о том как работают составные индексы можно почитать на сайте документации MySQL

Как уменьшить размер целочисленных индексов

Не стоит забывать что уменьшая тип данных Primary Key небольшой таблицы, мы так же будем уменьшать и тип данных всех внешних ключей к ней. Например если в таблице status 10 строк, а столбец id имеет тип int то, казалось бы, сменив его на тип tinyint мы выиграем всего 30 байт индекса. Но ведь мы сменим и все столбцы status_id в связанных таблицах, а если в тех таблицах суммарно несколько десятков миллионов строк, то можно выиграть десятки и сотни мегабайт в объеме индексного хранилища.

Как правильно выбрать тип для индексного столбца

Для того чтоб верно выбрать тип столбца — нужно понимать сколько уникальных значений в нем будет за время полное время жизни проекта. Потенциальное количество уникальных значений должно целиком покрываться максимальным значением unsigned -варианта типа. Разумеется, если мы говорим об экономии индексного пространства, то значение auto_increment_increment не должно быть более 1.

Пространство которое можно покрыть целочисленными unsigned-типами:

Tinyint идеально подойдет под небольшие списки статусов и типов каких-либо сущностей. Smallint будет хорош для списков пользователей внутренней биллинговой системы. Mediumint должно хватить для списков пользователей под проект среднего размера со свободной регистрацией. Int будет достаточным для идентификации почти любых вручную генерируемых объектов. И, наконец, bigint подойдет для идентификации самых массовых сущностей или массово генерируемых событий. Так же bigint подойдет как замена строковых индексов.

Как оптимизировать хранение строковых индексов

Когда необходимо сделать индекс по строке, например по столбцу с типом varchar , то, для сокращения размера индекса, часто индексируют дополнительный столбец char(32) содержащий md5-преобразование от строки. Тем не менее, результат md5-преобразования занимает 32 байта на каждую строку, что тоже весьма затратно. md5 — это набор из 32-х 16-ричных цифр, позволяющий нам охватить пространство в 2^256 или больше чем 10^38. Но для большинства задач хватает намного меньшего диапазона значений. Например, можно сократить char(32) до char(16) и получить экономию в два раза в размере ключа. Но в 16 байтах мы будем хранить 16 символов или 16 16-ричных цифр, которые кодируют 8 байт информации. А значит можно использовать для этого bigint , а не char(16) ! Сделать это довольно просто — сначала получаем md5 от нашей строки, далее обрезаем его до 16 символов, затем выполняем преобразование из 16-ричной системы счисления в 10-тичную:

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

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

Блог о технологиях .NET

4 апреля 2012 г.

Оптимизация баз Microsoft SQL Server: Часть 1. Зачем нужны индексы?

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

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

1) Создание репликаций базы данных

2) Шардинг базы данных

3) Индексирование таблиц базы данных с целью оптимизации запросов

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

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

Индексы создаются для таблицы и, по сути, являются копией этой таблицы. Различие состоит в том, что индекс может включать не все столбцы исходной таблицы, а лишь те которые указал разработчик. Рассмотрим пример. У нас есть таблица Stuffs, которая содержит 10 колонок и упорядоченный индекс IDX_StuffsSalary с ключом Salary, который содержит только два столбца из таблицы Stuffs:

Блог Алексея Куренкова

При выполнении запроса, как мы знаем, оптимизатор SQL Server исходя из существующих индексов и имеющейся свежей статистики пытается за разумное время найти лучший план запроса, конечно если этот план уже не «сидит» в кэше сервера, и запрос выполняется по этому плану и план сохраняется в кэш сервера. Если план уже построен для этого запроса ранее, то запрос выполняется по существующему плану.

Нам в этой теме интересен следующий момент: Во время компиляции плана запроса, при переборе возможных индексов, если лучшего индекса не нашлось (по мнению сервера), то в плане запроса помечается этот не найденный индекс, и сервер ведет статистику по таким индексам – сколько раз сервер бы воспользовался этим индексом и сколько стоил этот запрос. Эти отсутствующие индексы – missing indexes мы сейчас и разберем, что с ними делать и как с ними работать.

Предлагаю на примере разобраться с отсутствующими индексами. Создадим пару таблиц в нашей, БД на локальном или тестовом сервере:

if object_id ( ‘orders_detail’ ) is not null drop table orders_detail ;

if object_id ( ‘orders’ ) is not null drop table orders ;

create table orders

id int identity primary key ,

seller nvarchar ( 50 )

create table orders_detail

id int identity primary key ,

order_id int foreign key references orders ( id ),

product nvarchar ( 30 ),

cost as qty * price

select 1 id union all

select id + 1 from cte where id 20000

dateadd ( day , abs ( convert ( int , convert ( binary ( 4 ), newid ()))% 365 ), ‘2020-01-01’ ) dt ,

abs ( convert ( int , convert ( binary ( 4 ), newid ()))% 5 )+ 1 seller_id

) t ( id , seller ) on t . id = c . seller_id

option ( maxrecursion 0 )

o . id as order_id ,

abs ( convert ( int , convert ( binary ( 4 ), newid ()))% 5 )+ 1 product_id ,

abs ( convert ( int , convert ( binary ( 4 ), newid ()))% 20 )+ 1 qty

from orders o cross join

select top ( abs ( convert ( int , convert ( binary ( 4 ), newid ()))% 5 )+ 1 ) *

values ( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 6 ),( 7 ),( 8 )

( 2 , ‘ Молоко ‘ , 80 ),

( 4 , ‘ Макароны ‘ , 40 ),

) t ( id , product , price ) on t . id = c . product_id

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

Предлагаю посмотреть простой запрос и его план:


select count (*) from orders o join orders_detail d on o . id = d . order_id

where d . cost > 1800

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

Мы не будем строить этот индекс, который дала подсказка в SSMS , а посмотрим будет ли рекомендован индекс этот динамическими представлениями, связанными с отсутствующими индексами. Эти представления:

select * from sys . dm_db_missing_index_group_stats

select * from sys . dm_db_missing_index_details

select * from sys . dm_db_missing_index_groups

Мы из этого видим, что в 1м представлении у нас есть статистика по отсутствующих индексах, а именно:

Сколько бы раз произвелся поиск если бы предложенный индекс существовал?

Сколько раз использовалось бы сканирование если бы предложенный индекс существовал.

Дата время последней потребности в этом индексе

Текущая реальная стоимость плана запроса без предлагаемого индекса.

2е представление это уже тело индекса по сути:

Колонки включенные для увеличения покрытия индекса

3е представление — это связь 1го и 2х представлений.

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

from sys . dm_db_missing_index_group_stats

isnull ( equality_columns , » )+ ‘,’ + isnull ( inequality_columns , » ) as ix_col

from sys . dm_db_missing_index_details

‘use [‘ + db_name ( igd . database_id )+ ‘];

create index [‘ + ‘ix_’ + replace ( convert ( varchar ( 10 ), getdate (), 120 ), ‘-‘ , » )+ ‘_’ + convert ( varchar , igs . group_handle )+ ‘] on ‘ +

when left( ix_col , 1 )= ‘,’ then stuff ( ix_col , 1 , 1 , » )

when right( ix_col , 1 )= ‘,’ then reverse ( stuff ( reverse ( ix_col ), 1 , 1 , » ))

+ ‘) ‘ + isnull ( ‘include(‘ + igd . included_columns + ‘)’ , » )+ ‘ with(online=on, maxdop=0)

join sys . dm_db_missing_index_groups link on link . index_group_handle = igs . group_handle

join igd on link . index_handle = igd . index_handle

where igd . database_id = db_id ()

order by igs . avg_total_user_cost * igs . user_seeks desc

В порядке эффективности индексов выведены отсутствующие индексы. Идеально когда этот резалтсет ничего не возвращает, на нашем примере этот резалтсет возвратит минимум один индекс:

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

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

create index ix_01 on tbl1 ( a , b ) include ( c )

create index ix_02 on tbl1 ( a , b ) include ( d )

create index ix_03 on tbl1 ( a )

И эти индексы используются для поиска/ seek , то вполне очевидно, что логичнее заменить эти индексы на один который покроет все 3 предложенных:

create index ix_1 on tbl1 ( a , b ) include ( c , d )

Т.е. как минимум ревью предлагаемых индексов перед тем как их накатить на боевой сервер. Хотя…. Повторюсь, например на сервер TFS я накатывал потерянные индексы и общая производительность выростала, а время на такую оптимизацию затрачено минимум. Хотя, впоследствии с ТФС 2015 на ТФС 2020 я столкнулся с тем что обновление не проходило из-за новых индексов. Но их легко можно найти были по маске

select * from sys . indexes where name like ‘ix[_]2020%’

Как повысить производительность SQL Server

Сегодня я хочу предложить читателям 10 рекомендаций по настройке, которые помогут увеличить производительность SQL Server на 90%.

Оптимизировать производительность Microsoft SQL Server 2000 и SQL Server 7.0 не так-то просто, но в большинстве случаев администратор может добиться неплохих результатов, приложив совсем немного усилий. Здесь действует старое правило 90:10, т. е. 90% успеха достигается после затраты всего 10% усилий. Но нельзя забывать, что повысить производительность еще на 10% удастся, лишь затратив остальные 90% усилий при настройке.

Правило 90:10 неприменимо в отношении любых баз данных и даже ранних версий SQL Server. Для того чтобы добиться приемлемой производительности некоторых продуктов, необходимо настроить десятки — а то и сотни — параметров сервера и другие многочисленные функции SQL. В отличие от названных продуктов, SQL Server 2000 и SQL Server 7.0 — самонастраивающиеся системы, которые имеют неплохую производительность при работе с параметрами, принятыми по умолчанию.

Чтобы поднять производительность выше среднего стандартного уровня, потребуется уделить SQL Server совсем немного внимания. Если администратору понадобится дополнительная информация, рекомендую обратиться к материалам, перечисленным во врезке «Знание — сила».

1. О важности аппаратных средств

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

Если продукт установлен на хорошей машине, то модернизация аппаратных средств редко позволяет повысить производительность более чем на 10%. Но если приложение на базе SQL Server работает на сервере, к которому одновременно обращаются несколько сот пользователей, а сервер оснащен лишь одним жестким диском и минимальным объемом оперативной памяти, 64 Мбайт, то простое расширение оперативной памяти до 128 Мбайт приведет к резкому повышению быстродействия.

В идеальном случае, для каждых 10 одновременных соединений следует увеличивать оперативную память на 10 Мбайт; кроме того, необходима дополнительная память для хранения всех пользовательских данных, системных данных и индексов. Рекомендуется выбрать дисковую конфигурацию, которая позволит сохранить пользовательские данные (файлы .mdf и .ndf) и журналы (файлы .ldf) на других физических дисках, управляемых отдельными контроллерами. Файлы пользователей нужно хранить на RAID-массиве. Следует также потратиться на два самых быстрых процессора, какие только доступны для компании. Это минимальные требования к аппаратным средствам.

2. Не увлекайтесь настройками

Разработчики Microsoft заложили в SQL Server 2000 и SQL Server 7.0 способность к самонастройке. Например, механизм SQL Server может определить оптимальный режим использования памяти, допустимое число блокировок и частоту контрольных точек.

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

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

Исключение составляет параметр max async I/O. При работе с SQL Server 7.0 он настраивается в зависимости от уровня сложности и числа контроллеров в подсистеме ввода/вывода. Значение max async I/O определяет максимальное число ожидающих обработки асинхронных запросов ввода/вывода, которые могут быть направлены сервером к любому файлу. Если база данных охватывает несколько файлов, то параметр применяется к каждому из них.

По умолчанию значение max async I/O составляет 32 (всего 32 операции записи и 32 операции чтения могут ожидать обработки для каждого файла), оно оптимально для многих систем. Чтобы выяснить, нужно ли изменить стандартное значение для данной системы, следует заглянуть в SQL Server Books Online (BOL). SQL Server 2000 не имеет параметра max async I/O и определяет оптимальное значение автоматически.

3. Уделите время проектированию

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

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

Денормализованная структура отличается от ненормализованной. В денормализованную структуру избыточность вносится ради улучшения конкретных рабочих характеристик. Например, если необходимо регулярно отыскивать невыполненные заказы по имени потребителя, и в одной таблице хранятся ID потребителя и его имя, а в другой таблице — ID потребителя и информация о состоянии заказа, то SQL Server должен объединить эти таблицы, чтобы извлечь имена потребителей с невыполненными заказами. В некоторых случаях операции объединения могут отнимать много времени. В денормализованной модели имя потребителя можно добавить в таблицу, содержащую информацию о состоянии заказа и ID потребителя, тогда объединять таблицы не потребуется.

4. Формируйте индексы

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

Создав полезные индексы, можно повысить производительность на несколько порядков вместо нескольких процентов. Например, в отсутствие индекса SQL Server придется прочитать все 10 000 страниц таблицы. Если благодаря индексации число прочитанных страниц уменьшится до 10, то это будет равносильно увеличению скорости обработки страниц на 100 000%.

Знание архитектуры индексов и методов оптимизации запросов SQL Server поможет сформировать оптимальные индексы, а начинающие администраторы могут воспользоваться для этого мастером Index Tuning Wizard. Чтобы открыть его из SQL Server Enterprise Manager, следует щелкнуть на кнопке Wizard панели инструментов и заглянуть в раздел Management Wizards.

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

5. Эффективно используйте SQL

SQL — язык, ориентированный на обработку наборов, а не отдельных строк. T-SQL, предложенный Micro-soft диалект языка SQL, использует серверные курсоры для обращения к одной строке за один раз; однако большинство решений, в которых применяются серверные курсоры, будут на несколько порядков медленнее, чем решения, в которых для выполнения тех же задач используются предложения SELECT и UPDATE. Применение таких функций языка, как подчиненные запросы, производные таблицы и выражения CASE для манипулирования наборами строк, ускорит подготовку решений и поможет добиться максимальной производительности SQL Server.

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

Эту задачу можно решить и с помощью предложения UPDATE и коррелированного подчиненного запроса. В данном предложении таблица titles базы данных pubs используется в качестве таблицы продуктов; для каждого наименования суммируются величины в поле qty таблицы sales.

6. Изучайте тонкости T-SQL

Microsoft T-SQL — усовершенствованная версия стандартного языка ANSI-SQL. Использование его возможностей позволяет существенно повысить производительность системы.

Например, предположим, что необходимо выставить все продукты на продажу, установив цену на них в зависимости от прошлогоднего объема продаж. Цена должна быть на 25% ниже текущей, если число проданных единиц меньше 3000; если объем продаж был от 3000 до 1000 единиц, то цена продукта должна быть снижена на 20%; скидка 10% предоставляется на продукты, объем продаж которых превысил 10 000 единиц. Очевидное решение — использовать предложение UPDATE с соответствующими значениями скидок после индивидуального просмотра строк продуктов с помощью курсора. Однако выражение T-SQL CASE позволяет вычислить соответствующие скидки с помощью одного оператора.

В приведенном ниже примере предложение UPDATE использует таблицу titles базы данных pubs, в которой есть поле цены, обновляемое предложением, и поле ytd_sales, где хранится информация о продажах за прошлый год. Этот запрос не будет работать, если предварительно была выполнена операция из рекомендации 5; в поле ytd_sales будет находиться набор других величин.

Другие элементы T-SQL, повышающие эффективность запросов, — оператор TOP, используемый вместе с ORDER BY; индексированные представления (только SQL Server 2000); разделенные (partitioned) представления.

7. Правильно применяйте блокировки

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

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

По умолчанию SQL Server сохраняет монопольные блокировки — устанавливаемые при вставке, обновлении и удалении данных — до конца транзакции. Блокировки, допускающие чтение, — устанавливаемые при выборе данных, — действуют только до тех пор, пока не завершено чтение выбранных данных.

Уровень изоляции транзакций может быть изменен, и действие блокировок, допускающих чтение, может быть продлено до конца транзакции — это значит, что никто не может изменить данные после считывания. Таким образом, метод изменения уровней изоляции хорош в том случае, если нужно зарезервировать данные только для личного пользования. Однако его не стоит применять в многопользовательских системах. Я рекомендую установить уровень изоляции транзакций Committed Read (принимаемый по умолчанию) и изменять его только в случае крайней необходимости.

8. Сократите число перекомпиляций

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

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

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

Заранее подготовленные планы позволяют сэкономить время, но иногда стоит провести перекомпиляцию. В некоторых случаях SQL Server перекомпилирует план для процедуры самостоятельно. Profiler сообщит, когда происходили перекомпиляции, а с помощью System Monitor можно определить частоту перекомпиляций.

9. Грамотно программируйте приложения

Чем более глубокими знаниями об устройстве SQL Server обладает автор клиентских программ, тем выше качество составленных им исходных текстов. Например, он не допустит взаимодействия с пользователем посреди транзакции (см. п. 7).

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

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

Один из видов клиентского курсора, Fast Forward-Only, предназначен для последовательного извлечения данных при одноразовом считывании. С помощью этого курсора можно исключить два обращения к серверу; SQL Server выдает первую строку, когда курсор открывается, а когда SQL Server выдает последнюю строку, курсор закрывается. Даже если считывается всего несколько строк, при частом использовании программного фрагмента, содержащего курсор Fast Forward-Only, исключение двух обращений уже позволит повысить производительность.

10. Повышайте свою квалификацию

Если приведенные выше советы не помогли в решении конкретных проблем, рекомендую обратиться к многочисленным общедоступным источникам, которыми пользуются опытные специалисты по SQL Server. В частности, посмотрите конференции Micro-soft. Отыскать на сервере msnews.microsoft.com конференции, в названиях которых содержится обозначение sqlserver, можно с помощью любой программы чтения новостей (например, Microsoft Outlook Express).

Эти рекомендации — всего лишь вершина айсберга, которая открывает не более 10% всех возможностей. Теперь, когда известно, какие параметры SQL Server можно настроить, предстоит разобраться в том, как это сделать. Пройдет немного времени, и, вероятно, у администратора возникнет желание приложить те 90% усилий, которые необходимы, чтобы выжать из продукта последние 10% производительности.

Кэлен Дилани — независимый консультант и инструктор по SQL Server. Имеет сертификаты MCT и MCSE. Автор книги «Inside SQL Server 2000» (Microsoft Press). С ней можно связаться по адресу: kalen@sqlmag.com.

ЗНАНИЕ — СИЛА

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

1. О важности аппаратных средств

Дополнительные рекомендации по аппаратной конфигурации можно получить на Web-сайте Microsoft Developer Network (MSDN), MSDN Online. Обратите внимание на отчет «Microsoft SQL Server 7.0 Storage Engine Capacity Planning Tips» (http://msdn.microsoft.com/library/ default.asp?url=/library/ techart/storageeng.htm).

2. Не увлекайтесь конфигурированием

Более подробная информация о конфигурировании SQL Server содержится в материалах SQL Server Books Online (BOL) и статье Генри Ло «Microsoft SQL Server 7.0 Performance Tuning Gu >

3. Уделите время проектированию

К сожалению, нет книги по SQL Server или курса Microsoft Official Curriculum (MOC) начального уровня, в которых тема проектирования реляционных баз данных была бы освещена достаточно полно. Вероятно, разработчики Microsoft не уделили внимания этой теме, так как она не зависит от конкретных программных продуктов. Хорошей отправной точкой для начинающих разработчиков послужит колонка Мишель А. Пуле «Solutions by Design» в журнале SQL Server Magazine. Статьи можно найти по адресу: http://www.sqlmag.com/articles/ index.cfm?author >

4. Формируйте индексы

Тем, кто желает получить дополнительную информацию об индексации и оптимизаторе запросов SQL Server, советую для начала прочитать все, что написано об индексах в BOL. Фирма Microsoft выпустила две «белые книги» о мастере Index Tuning Wizard: «Index Tuning Wizard for Microsoft SQL Server 7.0» (http://msdn.microsoft.com/library/ default.asp?url=/library/techart/ msdn_sqlindex.htm) и «Index Tuning Wizard for Microsoft SQL Server 2000» http://msdn.microsoft.com/library/ default.asp?url=/library/techart/itforsql.htm). Дополнительную информацию по этой теме можно получить из курсов MOC Course 2013: Optimizing Microsoft SQL Ser-ver 7.0 и Course 2073: Programming Microsoft SQL Server 2000 Database. Более подробно об этих курсах можно узнать по адресу: http://www.microsoft.com/trainingandservices.

5. Эффективно используйте SQL

Не ограничивайтесь книгами по языку T-SQL. Для ознакомления с принципами программирования на ANSI-SQL я рекомендую прочитать книгу Джо Селко «Joe Celko’s SQL for Smarties: Advanced SQL Program-ming», второе издание (издательство Morgan Kaufmann Publishers, 1999).

6. Изучайте тонкости T-SQL

Есть работы, в которых приводятся примеры программирования на T-SQL. Это книги Ицика Бен-Гана и д-ра Тома Моро «Advanced Transact-SQL for SQL Server 2000» (издательство Apress, 2000) и Кена Хендерсона «The Guru’s Guide to Transact-SQL» (издательство Addison-Wesley, 1999).

7. Правильно применяйте блокировки

Прочитайте все доступные материалы о применяемых по умолчанию механизмах блокировки SQL Server, в том числе документы BOL, колонку Inside SQL Server Кэлен Дилани в SQL Server Magazine (http://www.sqlmag.com) и следующие статьи Microsoft: «INF: How to Monitor SQL Server 7.0 Blocking» (http://support.microsoft.com/support/ kb/articles/q251/0/04.asp), «INF: Un-derstanding and Resolving SQL Server 7.0 and 2000 Blocking Problems» (http://support.microsoft.com/support/ kb/articles/q224/4/53.asp) и «INF: How to Monitor SQL Server 2000 Blocking» (http://support.microsoft.com/support/ kb/articles/q271/5/09.asp).

8. Сократите число перекомпиляций

О перекомпиляции хранимых процедур можно прочитать в BOL. В статье Microsoft «INF: Troubleshooting Stored Procedure Recompilation» (http://support.microsoft.com/support/ kb/articles/q243/5/86.asp) рассказано о том, как свести перекомпиляцию хранимых процедур к минимуму.

9. Грамотно программируйте приложения

Дополнительную информацию о настройке клиентских программ, используемых вместе с SQL Server, предоставляет подготовленное для TechEd 2000 слайд-шоу Дамьена Линдауэра «Building High Performance Applications with SQL Server 2000» (http://commnet.us.teched.mscorpevents. com.slides/5-303%20w.ppt). Полезный материал — статья Microsoft «Troubleshooting Application Performance with SQL Server» (http://support.microsoft.com/support/ kb/articles/q224/5/87.asp).

10. Повышайте свою квалификацию

Помимо просмотра телеконференций на сервере msnews.microsoft.com можно обратиться на Web-страницу SQL Server Newsgroups компании Microsoft (http://www.microsoft.com/sql/ support/newsgroups.htm) и отыскать конференции там. Тем, кто предпочитает работать с материалами на базе Web, рекомендую обратиться на дискуссионные форумы Windows 2000 Magazine (http://www.win2000mag.net/ forums/application/main.cfm?cfapp=57).

Поделитесь материалом с коллегами и друзьями

Илон Маск рекомендует:  Что такое код farheapcheck
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL