Sqlглава 5 о предложениях определения данных и оптимизации запросов


Содержание

Sqlглава 5 о предложениях определения данных и оптимизации запросов

Оптимизация запросов в SQL Server 2005, статистика баз данных SQL Server 2005, CREATE STATISTICS, UPDATE STATISTICS, SET NOCOUNT ON, планы выполнения запросов, количество логических чтений (logical reads), хинты оптимизатора (optimizer hints), MAXDOP, OPTIMIZE FOR, руководства по планам выполнения (plan guides), sp_create_plan_guide

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

Отношение к оптимизации запросов у многих специалистов неоднозначное. С одной стороны, работа программного модуля Query Optimizer , который генерирует планы выполнения запросов, вызывает множество справедливых нареканий и в SQL Server 2000, и в SQL Server 2005. Query Optimizer часто выбирает не самые оптимальные планы выполнения запросов и в некоторых ситуациях проигрывает аналогичным модулям из Oracle и Informix . С другой стороны, ручная оптимизация запросов — процесс чрезвычайно трудоемкий. Вы можете потратить много времени на такую оптимизацию и, в конце концов, выяснить, что ничего оптимизировать не удалось: план, предложенный Query Optimizer изначально, оказался наиболее оптимальным (так бывает в большинстве случаев). Кроме того, может случиться так, что созданный вами вручную план выполнения запросов через какое-то время (после добавления новой информации в базу данных) окажется неоптимальным и будет снижать производительность при выполнении запросов.

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

Статистика — это специальная служебная информация о распределении данных в столбцах таблиц. Представим, например, что выполняется запрос, который должен вернуть всех Ивановых, проживающих в городе Санкт-Петербурге. Предположим, что у 90% записей в этой таблице одно и то же значение в столбце Город — «Санкт-Петербург» . Конечно, с точки зрения выполнения запроса вначале выгоднее выбрать в таблице всех Ивановых (их явно будет не 90%), а затем уже проверять значение столбца Город для каждой отобранной записи. Однако для того, чтобы узнать, как распределяются значения в столбце, нужно вначале выполнить запрос. Поэтому SQL Server самостоятельно инициирует выполнение таких запросов, а потом сохраняет информацию о распределении данных (которая и называется статистикой) в служебных таблицах базы данных.

Для баз данных SQL Server 2005 по умолчанию устанавливаются параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS . При этом статистика для столбцов баз данных будет создаваться и обновляться автоматически. Для самых больших и важных баз данных может получиться так, что операции по созданию и обновлению статистики могут мешать текущей работе пользователей. Поэтому для таких баз данных иногда эти параметры отключают, а операции по созданию и обновлению статистики выполняют вручную в ночное время. Для этого используются команды CREATE STATISTICS и UPDATE STATISTICS .

Теперь поговорим об оптимизации запросов.

Первое, что необходимо сделать, — найти те запросы, которые в первую очередь подлежат оптимизации. Проще всего это сделать при помощи профилировщика, установив фильтр на время выполнения запроса (фильтр Duration в окне Edit Filter (Редактировать фильтр), которое можно открыть при помощи кнопки Column Filters на вкладке Events Selection окна свойств сеанса трассировки). Например, в число кандидатов на оптимизацию могут попасть запросы, время выполнения которых составило более 5 секунд. Кроме того, можно использовать информацию о запросах, которая предоставляется Database Tuning Advisor .

Затем нужно проверить, устанавлен ли для ваших соединений, хранимых процедур и функций параметр NOCOUNT . Установить его можно при помощи команды SET NOCOUNT ON . При установке этого параметра, во-первых, отключается возврат с сервера и вывод информации о количестве строк в результатах запроса (т. е. не отображается строка «N row(s) affected» на вкладке Messages ( C ообщения) окна работы с кодом при выполнении запроса в Management Studio ). Во-вторых, отключается передача специального серверного сообщения DONE_IN_PROC , которое по умолчанию возвращается для каждого этапа хранимой процедуры. При вызове большинства хранимых процедур нужен только результат их выполнения, а количество обработанных строк для каждого этапа никого не интересует. Поэтому установка параметра NOCOUNT для хранимых процедур может серьезно повысить их производительность. Повышается скорость выполнения и обычных запросов, но в меньшей степени (до 10%).

После этого можно приступать к работе с планами выполнения запросов.

План выполнения запроса проще всего просмотреть из SQL Server Management Studio . Для того чтобы получить информацию об ожидаемом плане выполнения запроса, можно в меню Query (Запрос) выбрать команду Display Estimated Execution Plan (Отобразить ожидаемый план выполнения). Если вы хотите узнать реальный план выполнения запроса, можно перед его выполнением установить в том же меню параметр Include Actual Execution Plan (Включить реальный план выполнения). В этом случае после выполнения запроса в окне результатов в SQL Server Management Studio появится еще одна вкладка Execution Plan (План выполнения), на которой будет представлен реальный план выполнения запроса. При наведении указателя мыши на любой из этапов можно получить о нем дополнительную информацию (рис. 11.15).

Рис. 11.15. План выполнения запроса в SQL Server Management Studio

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

q вначале в окне Management Studio выполните команду SET STATISTICS IO ON . В результате после каждого выполнения запроса будет выводиться дополнительная информация. В ней нас интересует значение только одного параметра — Logical Reads . Этот параметр означает количество логических чтений при выполнении запросов, т. е. сколько операций чтения пришлось провести при выполнении данного запроса без учета влияния кэша (количество чтений и из кэша, и с диска). Это наиболее важный параметр. Количество физических чтений (чтений только с диска) — информация не очень представительная, поскольку зависит от того, были ли перед этим обращения к данным таблицам или нет. Статистика по времени также является величиной переменной и зависит от других операций, которые выполняет в это время сервер. А вот количество логических чтений — наиболее объективный показатель, на который в наименьшей степени влияют дополнительные факторы;

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

Хинтов оптимизатора в SQL Server 2005 предусмотрено много. Прочитать информацию о них можно в Books Online (в списке на вкладке Index (Индекс) нужно выбрать Query Hints [ SQL Server ] (Хинты запросов [ SQL Server ]), Join Hints (Хинты джойнов) или Table Hints [ SQL Server ] (Табличные хинты [ SQL Server ])). Чаще всего используются следующие хинты:

q NOLOCK , ROWLOCK , PAGLOCK , TABLOCK , HOLDLOCK , READCOMMITTEDLOCK , UPDLOCK , XLOCK — эти хинты используются для управления блокировками (см. разд. 11.5.7);

q FAST количество_строк — будет выбран такой план выполнения запроса, при котором максимально быстро будет выведено указанное количество строк (первых с начала набора записей). Если пользователю нужны именно первые записи (например, последние заказы), то для их максимально быстрой загрузки в окно приложения можно использовать этот хинт;

q FORCE ORDER — объединение таблиц при выполнении запроса будет выполнено точно в том порядке, в котором эти таблицы перечислены в запросе;

q MAXDOP (от Maximum Degree of Parallelism — максимальная степень распараллеливания запроса) — при помощи этого хинта указывается максимальное количество процессоров, которые можно будет использовать для выполнения запроса. Обычно этот хинт используется в двух ситуациях:

· когда из-за переключения между процессорами ( context switching ) скорость выполнения запроса сильно снижается. Такое поведение было характерно для SQL Server 2000 на многопроцессорных системах;

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

q OPTIMIZE FOR — этот хинт позволяет указать, что запрос оптимизируется под конкретное значение передаваемого ему параметра (например, под значение фильтра для WHERE );

q USE PLAN — это самая мощная возможность. При помощи такого хинта можно явно определить план выполнения запроса, передав план в виде строкового значения в формате XML . Хинт USE PLAN появился только в SQL Server 2005 (в предыдущих версиях была возможность явно определять планы выполнения запросов, но для этого использовались другие средства). План в формате XML можно написать вручную, а можно сгенерировать автоматически (например, щелкнув правой кнопкой мыши по графическому экрану с планом выполнения, представленному на рис. 11.15, и выбрав в контекстном меню команду Save Execution Plan As (Сохранить план выполнения как)).

В SQL Server 2005 появилась новая важная возможность, которая позволяет вручную менять план выполнения запроса без необходимости вмешиваться в текст запроса. Очень часто бывает так, что код запроса нельзя изменить: он жестко «прошит» в коде откомпилированного приложения. Чтобы справиться с этой проблемой, в SQL Server 2005 появилась хранимая процедура sp_create_plan_guide . Она позволяет создавать так называемые руководства по планам выполнения ( plan guides ), которые будут автоматически применяться к соответствующим запросам.

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

q насколько часто в планах выполнения запроса встречается операция Table Scan (Полное сканирование таблицы). Вполне может оказаться, что обращение к таблице при помощи индексов будет эффективнее;

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

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

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

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

q если приложение передает на сервер команды EXECUTE , то имеет смысл подумать о том, чтобы заменить их на вызов хранимой процедуры sp_executesql . Она обладает преимуществами в производительности по сравнению с обычной командой EXECUTE ;

q повышения производительности иногда можно добиться, устранив необходимость повторной компиляции хранимых процедур и построения новых планов выполнения запросов. Нужно обратить внимание на применение параметров, постараться не смешивать в коде хранимой процедуры команды DML и DDL и следить за тем, чтобы параметры подключения SET ANSI_DEFAULTS , SET ANSI_NULLS , SET ANSI_PADDING , SET ANSI_WARNINGS и SET CONCAT_NULL_YIELDS_NULL не изменялись между запросами (любое изменение таких параметров приводит к тому, что старые планы выполнения считаются недействительными). Обычно проблема может возникнуть тогда, когда эти параметры устанавливаются на уровне отдельного запроса или в коде хранимой процедуры.

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

Опыт и рекомендации по оптимизации SQL-запросов

Валерий Михеичев,
ведущий специалист по Oracle
OCAO “Ингосстрах”

Источник: Статья предоставлена автором для публикации в FORS Magazine.

В этой статье изложен многолетний опыт оптимизации SQL-запросов в процессе работы с базами данных Oracle 9i, 10g и 11g. В качестве рабочего инструмента для получения планов запросов мною используется всем известные программные продукты Toad и PLSQL Developer.

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

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

Программные средства, позволяющие получить планы выполнения запросов, можно разделить на 2 группы:

  • средства, позволяющие получить предполагаемый план выполнения запроса;
  • средства, позволяющие получить реальный план выполнения запроса;

К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Это важный момент, поскольку надо учитывать, что реальный план выполнения может отличаться от того, что показывают эти программные средства. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются:

  • Cost – стоимость выполнения и
  • Cardinality (или Rows) – кардинальность.

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

полученного в Toad

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

  • CPU Cost — процессорная стоимость выполнения;
  • IO Cost — стоимость ввода-вывода;
  • Temp Space – показатель использования дискового пространства.

Если дисковое пространство используется (при нехватке оперативной памяти для выполнения запроса, как правило, для проведения сортировок, группировок и т.д.), то с большой вероятностью можно говорить о неэффективности запроса. Указанные дополнительные параметры с соответствующей настройкой можно увидеть в PL/SQL Developer и Toad при их соответствующей настройке. Для PL/SQL Developer в окне с планом выполнения надо выбрать изображение гаечного ключа, войти в окно Preferensec добавить дополнительные параметры в Select Column, после чего и нажать OK. В Toad в плане выполнения по правой кнопке мыши выбирается директива Display Mode, а далее Graphic, после чего появляется дерево, в котором по каждому листу нажатием мышки можно увидеть дополнительные параметры: CPU Cost, IO Cost, Cardinality. Структура плана запроса, указанного выше, в виде дерева приведена ниже.

Предполагаемый план выполнения запроса с Cost и Cardinality можно также получить, выполнив после анализируемого запроса другой запрос, формирующий план выполнения:

Дополнительно в плане выполнения запроса выдается значение SQL_ID запроса, который можно использовать для получения реального плана выполнения запроса с набором как основных (Cost, Cardinality), так и дополнительных показателей через запрос:

Реальный план выполнения запроса и указанный выше перечень характеристик для анализа ресурсоемкого запроса дают динамические представления Oracle: V$SQL_PLAN и V$SQL_PLAN_MONITOR (последнее представление появилось в Oracle 11g).

План выполнения запроса получается из представления Oracle по запросу:

где SQL_ID – это уникальный идентификатор запроса, который может быть получен из разных источников, например, из представления V$SQL:

Трассировочный файл — это еще одно средство получение реального плана выполнения. Это довольно сильное средство диагностики и оптимизации запроса. Для получения трассировочного файла ( в Toad или PL/SQL Developer) запускается PL/SQL блок:

где первая, третья и последняя строки являются стандартными, а во второй строке пишется идентификатор (любые символы), который включается в имя трассировочного файла. Так, если в качестве идентификатора напишем M_2013, то имя трассировочного файла будет включать этот идентификатор и будет иметь вид: oraxxx_xxxxxx_ M_2013.trc. Результат пишется в соответствующую директорию сервера, которая находиться из запроса

Трассировочный файл для удобства чтения расшифровывается утилитой Tkprof (при определенном навыке анализировать можно без расшифровки, в этом случае имеем более детальную информацию).

Ещё одним из средств получения реального плана выполнения запроса с получением рекомендаций по его оптимизации является средство Oracle SQLTUNE.

Для анализа запроса запускается PL/SQL блок (например, в Toad или PL/SQL Developer) , в котором имеются стандартные строки и анализируемый запрос. Для рассматриваемого выше запроса блок PL/SQL примет вид:

Все строки (кроме текста запроса) являются стандартными.

Далее запуск запрос, который выдает на экран текст рекомендаций:

Для работы SQLTUNE необходимо как минимум из под SYSTEM выдать права на работу с SQLTUNE схеме, в которой запускается PL/SQL блок. Например, для выдачи прав на схему HIST выдается GRANT ADVISOR TO HIST;

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

Анализ плана выполнения запроса.

Анализ плана выполнения запроса имеет определенную последовательность действий. Рассмотрим на примере плана выполнения запроса из представление V$SQL_PLAN для ранее приведенного запроса

  1. При анализе план просматриваетcя снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими Cost, CPU Cost.
  2. Как видно из плана, резкий скачек этих значений имеется в 4-ой строке. Причиной такого скачка является 5-я строка с INDEX FULL SCAN, указывающая на наличие полного сканирование индекса X_DICTI_NAME таблицы DICTI. С этих строк и надо начинать поиск причины ресурсоемкости запроса. После нахождения строки с большим Cost и CPU Cost продолжается просмотр плана снизу вверх до следующего большого CPU Cost и т.д. При этом, если CPU Cost в строке близок к CPU Cost первой строки (максимальное значение), то найденная строка является определяющей в ресурсоемкости запроса и с ней в первую очередь надо искать причину ресурсоемкости запроса.
  3. Помимо поиска больших Cost и CPU Cost в строках плана следует просматривать первый столбец Operation плана на предмет наличия в нем HASH JOIN. Соединение по HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOPS. Вместе с тем, HASH JOIN эффективно при наличии таблиц, хотя бы одна из которых помещаются в память БД или при наличии соединения таблиц с низкоселективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса. В связи с чем, при наличии высокоселективных индексов, целесообразно посмотреть, а не улучшит ли план выполнения хинт USE_NL, приводящий к соединению по вложенным циклам NESTED LOOPS. Если план будет лучше, то оставить этот хинт. При этом в хинте USE_NL в скобках обязательно должны перечисляться все алиасы таблиц, входящих во фразу FROM, в противном случае может возникнуть дефектный план соединения. Этот хинт может быть усилен хинтами ORDERED и INDEX. Следует обратить так же внимание на MERGE JOIN. При большом CPU Cost в строке с MERGE JOIN стоит проверить действие хинта USE_NL для улучшения эффективности запроса.
  4. Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец Operation: FULL — для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN — для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, неправильное их применение. При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.
  5. Наличие в столбце Operation операции MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при наличии во фразе From трех и более таблиц, когда отсутствуют связи между какой-то из пар таблиц.
    Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.

Оптимизация запроса

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

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

Неэффективная статистика.

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

  1. Устаревшая статистика. Время последнего сбора статистики определяется значением поля Last_Analyzed для таблиц и индексов, которое находиться из Oracle таблиц ALL_TABLES (DBA_TABLES) и ALL_INDEXES (DBA_INDEXES) соответственно. Oracle ежедневно в определенные часы в рабочие дни и в определенные часы в выходные сам собирает статистику по таблицам. Но для этого DML операции с таблицей должны привести к изменению не менее 10% строк таблицы. Однако, мне приходилось сталкиваться с ситуацией, когда в течение дня таблица неоднократно и существенно меняет число строк или таблица столь большая, что 10% изменений наступает через длительное время. В этом случае приходилось обновлять статистику, используя процедуры сбора статистики внутри пакетов, а ряде случае использовать JOB, запускающийся в определенные часы для анализа и обновления статистики.
    Статистика по таблице и индексу (на примере таблицы AGREEMENT и индекса X_AGREEMENT в схеме HIST) обновляется соответственно процедурами:
    • для таблицы:
    • для индекса:

где число 10 в процедуре указывает на процент сбора статистики. С учетом времени сбора статистики и числа строк в таблице (индексе) были выработаны рекомендации для таблиц (индексов) по проценту сбора статистики: если число строк более 100 млн. процент сбора устанавливать 2 -5, при числе строк с 10 млн. до 100 млн. процент сбора устанавливать 5-10, менее 10 млн. процент сбора устанавливать 20 -100. При этом, чем выше процент сбора, тем лучше, однако, при этом растет и может быть существенным время сбора статистики.

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

    Для таблиц процент сбора статистики (на примере таблицы AGREEMENT в схеме HIST) вычисляется запросом:

    Процент сбора статистики по индексу находиться по запросу

    Необходимо пересобрать статистику по таблице или индексу с плохой статистикой.

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

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

    Проблемы с индексами

    Проблемы с индексами в плане выполнения проявляются при наличии в столбце Options значений FULL, FULL SCAN, FAST FULL SCAN и SKIP SCAN в силу следующих причин:

    1. Отсутствие нужного индекса. Требуемое действие — создать новый индекс;
    2. Индекс имеется, но он неэффективно построен. Причинами неэффективности индекса могут быть:
      Малая селективность столбца, на котором построен индекс, т.е. в столбце много одинаковых значений, мало уникальных значений. Решение в данной ситуации — убрать индекс из таблицы или столбец, на основе которого построен индекс, ввести в составной индекс.
      Столбец селективный, но он входит в составной индекс, в котором этом столбец не является первым (ведущим) в индексе. Решение – сделать этот столбец ведущим или создать новый индекс, где столбец будет ведущим;
    3. Построен эффективный индекс, но он работает не эффективно в силу следующих причин:
      Индекс заблокирован от использования. Блокируют использование индекса следующие операции над столбцом, по которому используется индекс: SUBSTR, NVL, DECODE, TO_CHAR,TRUNC,TRIM, ||конкатенация, + цифра к цифровому полю и т.д.
      Решение – модифицировать запрос, освободиться от блокирующих операций или создать индекс по функции, блокирующей индекс.
      Не собрана или неактуальная статистика по индексу. Решение – собрать статистику по индексу запуском процедуры, указанной выше.
      Имеется хинт, блокирующий работу индекса, например NO_INDEX.
      Неэффективно настроены параметры инициализации базы данных БД (особенно отвечающие за эффективную работу индексов, например, optimizer_index_caching и optimizer_index_cost_adj). По моему опыту использования Oracle 10g и 11g эффективность работы индексов повышалась, если optimizer_index_caching=95 и optimizer_index_cost_adj=1.
    4. Имеются сильные индексы, но они соперничают между собой.
      Это происходит тогда, когда в условии where имеется строка, в которой столбец одной таблицы равен столбцу другой таблицы. При этом на обоих столбцах построены сильные или уникальные индексы. Например, в условии Where имеется строка AND A.ISN=B.ISN. При этом оба столбца ISN разных таблиц имеют уникальные индексы. Однако, эффективно может работать индекс только одного столбца (левого или правого в равенстве). Индекс другого столбца, в лучшем случае, даст FAST FULL SCAN. В этой ситуации, чтобы эффективно заработали оба индекса, потребуется вести дополнительное условие для одного из столбцов.
    5. Индекс имеет большой фактор кластеризации CLUSTERING_FACTOR.
      По каждому индексу Oracle вычисляет фактор кластеризации (ФК), определяющий число перемещений от одного блока к другому в таблице при выборе индексом строк из таблицы. Минимальное значение ФК равно числу блоков таблицы, максимальное — числу строк в таблице. CLUSTERING_FACTOR определяется по запросу:

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

  • Индекс давно не перестраивался (индекс сильно фрагментирован за счёт многократных удалений в таблице). В этом случае может быть проведена либо перестройка Rebuild индексов (осуществляется по команде ALTER INDEX owner. имя индекса REBUILD ONLINE с освобождением пространства, в котором находиться индекс) , либо COALESCE — процедура уменьшения числа листовых блоков в индексе путем их объединения без освобождения пространства (эта процедура не блокирует таблицу в процессе выполнения и выполняется по команде ALTER INDEX owner. имя индекса COALESCE).
  • Проблемы с хинтами в запросе

    Проблемы с хинтами могут быть следующие:

    1. Неэффективный хинт. Он может привести к существенному снижению производительности. Причины возникновения не эффективности хинтов:
      — хинт был написан, когда БД работала на 9-ом Oracle, при переходе на Oracle 10g и выше хинт стал тормозом (это могут быть хинты Rule, Leading и др.). Leading –мощный хинт, но при переходе на другую версию Oracle в некоторых случаях приводит в резкому снижению производительности и перед применение этих хинтов необходимо учитывать вероятность изменения со временем статистики системы и ее объектов (таблиц и индексов), используемых в запросе;
      — в хинте USE_NL содержится не полный перечень алиасов;
      в составном хинте используется неправильный порядок следования хинтов, в результате чего хинты блокирую эффективную работу друг. Например, хинт Leading полностью игнорируются при использовании двух или более конфликтующих подсказок Leading или при указании в нем более одной таблицы.
      хинт написан давно, после чего была модификация запроса (например, отсутствует или изменился индекс, указанный в хинте).
    2. В запросе отсутствует хинт, который бы повысил эффективность работы запроса. В ряде случаем наличие хинта повышает эффективность запроса и обеспечивает стабилизацию планов выполнения (например, при изменении статистики).
    3. При создании хинта в запросе есть ряд рекомендаций:
      В хинте INDEX могут быть перечислены несколько индексов. Оптимизатор сам выберет соответствующий индекс. Можно поставить хинт NO_INDEX, если надо заблокировать использование какого-то индекса.
      — При наличии Distinct в запросе Distinct ставиться после хинта (т.е. хинт всегда идет после Select).
      Наиболее эффективные и часто используемыми являются хинты: Ordered, Leading, Index , No_Index, Index_FFS, Index_Join, Use_NL, Use_Hash, Use_Merge, First_Rows(n), Parallel, Use_Concat, And_Equal, Hash_Aj и другие. При этом, например, индекс Index_FFS кроме быстрого полного сканирования индекса позволяет ему выполняться параллельно, в силу чего можно получить существенный выигрыш в производительности. Пример такого использования для секционированной таблицы где T-алиас таблицы.
      Изменение параметров инициализации базы данных в пределах запроса позволяет сделать хинт /*+ opt_param(‘Параметр инициализацци’ N) */ , например, /*+ opt_param(‘optimizer_index_caching’ 10) */. Данный хинт используется для проверки производительности работы запроса в случае, когда запрос разрабатывается или тестируется на базе с одним значением параметров инициализации, а работает на базе с другими значениями.


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

    Неэффективно написанный запрос.

    Причин неэффективности запроса несколько:

    • неэффективное соединение таблиц;
    • использование NOT и NOT IN в условии where;
    • блокировка индекса в силу использования неправильных функций к столбцу, по которому построен индекс;
    • большая вложенность запроса или большая его длина;
    • большой объем выбираемых данных, требующих подключения в работу дисков, в том числе для выполнения агрегированных функций (order by, group by и т.д.);
    • неэффективные хранимые процедуры, используемые в запросе и др.
    1. Среди причин неэффективности особое внимание следует уделить неэффективному соединению таблиц (наличие HASH или MERGE соединений там, где предпочтительнее NESTED LOOP — о чем сказано выше). Кроме того эффективность соединения может зависеть от порядка таблиц во фразе FROM. Чтобы оптимизатор работал с таблицами в том порядке, в каком они находятся во фразе From используется хинт Ordered.
    2. Эффективность соединения зависит от полноты связи во фразе WHERE между таблицами. При недостаточной связке в плане выполнения появляется MERGE JOIN CAPTESIAN (о чем было сказано выше). Особое внимание при модификации запроса следует уделить фразе NOT IN в условии where. Как вариант освобождения от NOT IN можно использовать прием, при котором пишется первый запрос без NOT IN, а за ним после MINUS пишется тот же запрос с IN (вычитание из полного числа строк строки, получаемые после использования условия IN, который работает быстрее, чем NOT IN).
    3. В целях ускорения работы запроса использовать (там, где это можно) вместо UNION фразу UNION ALL (UNION операция более медленная, т.к. осуществляется путем сортировки).
    4. Рекомендуется уменьшать число таблиц во фразе FROM. Это позволит сделать план выполнения прозрачным для оптимизатора и его анализа. В первую очередь убрать из FROM таблицы, столбцы которых не используются после фразы Select. В этом случае можно использовать подзапросы с этими таблицами после Select или во фразе where. Задание диапазона дат, начиная с 01.01.0001, приводит к неэффективному плану выполнения. Надо сделать минимальную границу даты, т.е. как можно ближе к реальной дате.
    5. В целях повышения производительности запроса не делать длинные запросы, т.к. длинный запрос увеличивает время разбора запроса оптимизатором, время передачи по каналам и занимает избыточную память.
    6. В целях повышения производительности работы запроса шире использовать кэширование всех видов: последовательностей, таблиц, результатов выполнения запросов. Кэширование результатов выполнения запросов появилось в Oracle 11g и позволяет извлекать результат первого выполнения запроса из оперативной памяти. Это особенно эффективно при большом числе выполнения запроса и отсутствие в момент многократного выполнения запроса операций DML над таблицей.

    Оптимизация SQL запросов для MS SQL Server с помощью индексов

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

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

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

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

    Когда я узнаю о том, что на сервере есть медленно выполняющийся запрос, первое на что я смотрю – статистику io. Это такое слабое звено, позволяет быстро определить легкие проблемные места SQL запросов.

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

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

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

    У меня в тестовой базе данных почти 200 тысяч записей и поиск по ней происходит мгновенно. Если посмотреть на время выполнения, то будет ноль секунд, что очень хорошо. Но давайте включим отображение статистики, и посмотрим на нее. Я всегда включаю статистику io и время time:

    Теперь после выполнения SQL запроса в SQL Management Studio внизу окна будет появляться не только результат, но и на закладке Messages будет показана статистика выполнения:

    В моем случае статистика выглядела так:

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

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

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

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

    Более подробно о создании индексов можно почитать здесь: Индексы в MS SQL Server и еще немного интересной информации о индексах здесь: опции индексов.

    Здесь я только скажу, что при создании индексов на таблицу, где много данных и с большим количеством выполняемых запросов может стать проблемой. Создание индекса по умолчанию потребует блокировки, что может стать препятствием. Индекс может не создаваться, потому что данные заблокированы или сайт может лечь, если индекс будет долго создаваться. Чтобы этого не произошло, нужно добавлять опцию: (online = on)

    Снова выполняем запрос и смотрим на статистику:

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

    Попробуем взглянуть на план выполнения – графическое представление того, как сервер реально выполнял запрос. Для этого включаем опцию отображения плана – в меню Query выбираем Include Actual Execution Plan (или нажимаем Ctrl+M). Если теперь выполнить запрос, то появится еще одна вкладка – Execution Plan:

    Здесь у нас две ветки и читать их нужно справа на лево. Самый правый блок – это то, с чего началось выполнение – Index Seak по индексу IX_Member_WSEmail. Наш простой запрос ищет данные по колонке WSEmail и эта колонка есть в индексе IX_Member_WSEmail, поэтому имеет смысл использовать его. И как мы уже увидели после создания индекса, результат как говориться на io. В индексе находятся индексируемые колонки и первичный ключ. Это все, что узнает сервер, когда находит строку по индексу. Но наш запрос выводит совершенно все колонки и чтобы найти оставшиеся данные, серверу приходится по первичному ключу находить их. Этот процесс быстрый – Key Lookup, потому что это первичный ключ, но он все же отнимает немного времени. И скоро мы увидим сколько. Получается, что серверу необходимо выполнить как бы две операции – поиск по индексу основного ключа, а потом по этому ключу найти данные колонок. А если выводить на экран только колонки WSEmail и первичный ключ MemberID? Эти данные уже есть индексе и второй Key Lookup не понадобиться. Посмотрим, как это будет выглядеть в статистике:

    Статистика падает с 7 чтений до 3:

    А план выполнения начинает выглядеть идеально просто:

    Допустим, нам необходимо вывести на экран имя человека, в моем случае это колонка WSFirstName:

    Теперь запросу нужно будет снова делать два поиска – чтобы найти первичный ключи, а потом по нему найти реальную строку, чтобы выцепить имя. И это в принципе не страшно, потому что поиск по первичному ключу занял всего 4 операции чтения, но что, если выводиться 1000 строк? Тогда уже будет 4000 операций. А если запрос у нас не такой простой и в нем потом еще есть left join на какую-то другую таблицу с именами, где, по имени храниться судьба человека (такой гороскоп по имени).

    Можно создать индекс, который будет индексировать по email и по имени:

    И хотя запрос фильтрует данные только по e-mail, этот индекс все же будет работать и позволит нам быстро найти данные, и в индексе будет уже имя и поэтому второй поиск уже не нужен будет. Круто, но не совсем эффективно. Имя меняется не часто и если мы по нему реально не ищем, то по нему индексировать смысла нет, но есть возможность сказать серверу, чтобы он хранил вместе с индексом еще и колонку WSFirstName, для этого есть такая фишка, как include:

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

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

    Еще один пример, который может выиграть от такого индекса:

    Мы выводим все колонки и от второго поиска по первичному ключу всех данных не убежать. Все колонки включать в индекс смысла нет, потому что это превратить его практически в первичный, просто не кластерный. Но.. Когда сервер отфильтрует данные по WSEmail по первому индексу и найдет допустим 1000 строк, он может тут же сократить эти данные и проверить имя и результат сократиться до (допустим) 100 строк. То есть Key Lookup может выполняться только 100 раз. Если колонка WSFirstName не включена в индекс, то эту операцию придется уже делать 1000 раз.

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

    Теперь строка e-mail адреса передается в качестве nvarchar и это серьезно ударит по производительности. Изменился только тип строки, которую мы сравниваем, а посмотрите как обрушилась статистика:

    Или вот такой пример, так обычно делают многие движки, когда выполняют запросы и примерно так будет выполнять запросы популярный Dapper или даже .NET фреймворк:

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

    Когда я работал над сайтом регистрации продуктов для Sony, то допустил такую ошибку. На главной странице сайте есть автокомплитер, где пользователь может ввести код товара. Пока пользователь вводит, на заднем плане происходит поиск и когда я запустил этот сайт, то он нереально затормозил, хотя этот сайт не такой уж и популярный и по посещаемости самый слабый из всех, что я делал для Sony. Начали исследовать, а оказалось, что для этого проекта я перешел на Dapper, который по умолчанию все переменные делал nvarchar, а в базе данных у нас все было просто varchar (сайт только для США). В результате, даже небольшой нагрузки на сайт хватало для серьезного падения производительности. Пришлось хакать Dapper, чтобы он не создавал переменные Unicode, а делал их простыми varchar

    Если тип колонки и искомого значения совпадают, то будет использоваться Index Seek. Если не совпадают, то Index Scan – сканирование по индексу. Что используется для поиска можно увидеть в Execution Plan

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

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

    Здесь я с помощью cast привожу email переменную к правильному типу, чтобы он совпадал с типом данных колонки.

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

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

    Sqlглава 5 о предложениях определения данных и оптимизации запросов

    Методы оптимизации запросов к SQL Server — Советы для написания эффективных и быстрых запросов

    Инновационный центр — Группа организаций Baba Farid

    Автор перевода: Прищепа В.В.

    Аннотация — SQL можно использовать для извлечения данных из любых баз данных. Чтобы получить один и тот же результат, можно написать различные SQL запросы. Для наилучшей производительности необходимо использовать лучшие, наиболее быстрее и эффективные запросы. Так что необходимо конфигурировать запросы на основании требований пользователей и решаемых задач. Эта статья раскрывает каким же образом SQL запросы могут быть оптимизированы для лучшей производительности. Оптимизация запросов тема очень глубокая, но мы будем стараться охватить наиболее важные моменты. В этой статье мы не будем сосредотачиваться на глубоком анализе базы данных, а обсудим простые советы по настройке запросов и приемы, которые могут быть применены, чтобы получить немедленный выигрыш в производительности.

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

    II. ОБЗОР ВЫПОЛНЕНИЯ ЗАПРОСОВ С ИСПОЛЬЗОВАНИЕМ СТАТИСТИКИ ЧТЕНИЯ/ЗАПИСИ

    Важным параметром является количество логических операций чтения производящихся по запросу. Возможность просматривать этот параметр предусмотрена в SQL Server Management Studio. Для определения числа логических операций чтения, вы можете включить или выключить отображение параметра STATISTICS IO с помощью такого запроса:

    SET STATISTICS IO ON

    Рассмотрим следующий запрос:

    SELECT * FROM tablename

    В окне результата SQL Server Management Studio вернулось следующее сообщение: «Table ‘tablename’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0«.

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

    III. ОБЩИЕ РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ

    Используйте конкретные имена столбцов вместо * в запросе SELECT

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

    SELECT col_1, col_2, col_3, col_4, subject FROM table_name;

    SELECT * FROM table_name.

    Используйте альтернативные методы для возврата общего количества строк таблицы вместо COUNT (*)

    SELECT COUNT (*) делает полное сканирование таблицы, это может занять много времени для больших таблиц. Если нам нужно узнать количество строк таблицы, мы можем использовать альтернативный способ – системную таблицу sysindexes. В ней присутствует столбец ROWS, содержащий общее количество строк для каждой таблицы в системе. Таким образом, мы можем использовать следующий оператор выбора:

    SELECT rows FROM sysindexes
    WHERE ) AND indid

    SELECT * FROM table1 t1
    WHERE t1.col_id IN (SELECT t2.col_id FROM table2 t2).

    Используйте EXISTS вместо DISTINCT при запросе с объединением таблиц, которые имеют отношения один-ко-многим

    SELECT d.col_id, d.col2 FROM table1 d
    WHERE EXISTS (SELECT ‘X’ FROM table2 e WHERE e.col2 = d.col2);

    SELECT DISTINCT d.col_id, d.col2 FROM table1 d, table2 e
    WHERE e.col2 = e.col2.

    Попробуйте использовать UNION ALL вместо UNION, если это возможно

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

    SELECT id, col1 FROM table1
    UNION ALL SELECT id, col1 FROM table2;

    SELECT id, col1, col2 FROM table1
    UNION SELECT id, col1 FROM table2.

    Тщательно подбирайте условия в инструкции WHERE

    Например, для сравнения чисел пишите:

    SELECT id, col1, col2 FROM table
    WHERE col2 > 10;

    SELECT id, col1, col2 FROM table
    WHERE col2 != 10.

    Для сравнения строк:

    SELECT id, col1, col2 FROM table
    WHERE col1 LIKE ‘Nav%’;

    SELECT id, col1, col2 FROM table
    WHERE SUBSTR(col1,1,3) = ‘Nav’.

    Для сравнения чисел в диапазоне:

    SELECT Col1, Col2 FROM table
    WHERE Col3 BETWEEN MAX (Col3) and MIN (Col3);

    SELECT Col1, Col2 FROM table
    WHERE Col3 >= MAX (Col3) and Col3 <= MIN (Col3).

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

    SELECT id, Col1, Col2 FROM table
    WHERE Col2 < 25000;

    SELECT id, Col1, Col2 FROM table
    WHERE Col2 + 10000 < 35000.


    IV. ЕЩЕ НЕСКОЛЬКО СОВЕТОВ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ/ТАБЛИЦ/ХРАНИМЫХ ПРОЦЕДУР

    • Таблица должна иметь минимум один кластеризованный индекс и соответствующее число не кластеризованных индексов;
    • Избегайте использования триггеров, если это возможно. Включите логику триггера в хранимую процедуру;
    • Таблица должна иметь ключевое поле;
    • Старайтесь использовать переменные таблицы вместо временных. Переменные занимают меньше системных ресурсов и ресурсов логов;
    • Избегайте использования VIEW, постарайтесь заменить их таблицами;
    • Избегайте инструкции DISTINCT, используйте ее только если это действительно необходимо;
    • Используйте TOP в иснтрукции SELECT, если необходимо выбрать некоторое количество строк в начале таблицы;
    • Оформите повторяющийся код в пользовательскую процедуру. Это поможет улучшить производительность, ускорить вашу работу, уменьшить сетевой трафик;
    • Использование TRUNCATE вместо DELETE позволит ускорить удаление строк из таблицы, потому что удаление происходит без записи информации в лог-файл;
    • Избегайте использования курсоров, если это возможно, они сильно замедляют производительность;
    • Когда разрабатывается запрос с подзапросами:
      • Используйте коррелированный подзапрос только тогда, когда возвращаемый результат будет относительно небольшим и/или другие критерии быстродействия подзапроса будут эффективными;
      • Используйте не коррелированные подзапросы при работе с большими таблицами, из которых ожидается большой результат и/или подзапрос имеет низкие показатели эффективности;
      • Убедитесь в том, что несколько подзапросов расположены в наиболее эффективном порядке;
      • Переписывание подзапроса с JOIN иногда может повысить эффективность;
    • Для хранения символьных и строковых данных используйте char/varchar вместо nchar/nvarchar, если нет необходимости в использовании UNICODE. В первом случае для хранения символов используется один байт, во втором – два;
    • Можно попытаться использовать инструкцию RETURN для возвращения целочисленного значения вместо того, чтобы это значение было частью результирующего набора данных;
    • Очистите систему от неиспользуемых индексов, они занимают место на диске и замедляют операции DML;
    • Создавайте индексы для целочисленных полей, это способствует меньшему объему индекса на диске, меньшему количеству операций чтения при использовании индекса;
    • Если часто используется объединение одних и тех же таблиц, то стоит создать индекс для объединяемых столбцов.

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

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

    Оптимизация SQL-запросов

    10-10-12 Теги статьи: кодинг, mysql, алгоритмы

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

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

    Общая оптимизация

    Каждая SQL-операция имеет так называемый «коэффициент полезности» – уровень эффективности данной операции. Чем больше балл, тем «полезней» операция, а значит, SQL-запрос выполняется быстрее.

    Практически любое условие состоит из двух операндов и знака операции между ними.

    Примеры

    Чтобы лучше понять таблицы, рассмотрим пример расчета рейтинга запроса.

    … WHERE smallint_column = 12345

    5 баллов за поле слева (smallint_column), 2 балла за точный цифровой операнд(smallint_column), 10 баллов за операцию сравнения (=) и 10 баллов за значение справа (12345). Итого получили 27 баллов. Теперь рассмотрим более сложный пример:

    . WHERE char_column >= varchar_column || «x»

    5 баллов за поле слева (char_column), 0 баллов за символьный операнд (char_column), 5 баллов за операцию больше или равно (>=) , 3 балла за логическое выражение (varchar_column || «x») , 0 баллов за символьный операнд (varchar_column). В итоге получим 13 баллов.

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

    Основной закон при оптимизации запросов — закон преобразования. Неважно, как мы представляем условие, главное чтобы результат остался прежним. И снова рассмотрим пример. Есть запрос: . WHERE column1 . Используя перестановку, получаешь запрос: …WHERE 5 . Результат запроса будет один и тот же, а продуктивность разной, потому что использование точного значения (5) влияет на производительность.

    Если ты изучал С или С++, то знаешь, что выражение x=1+1-1-1 во время компиляции станет x=0. Удивительно, что лишь некоторые БД способны выполнять такие операции. При выполнении запроса БД будет выполнять операции сложения и вычитания и тратить твое драгоценное время. Поэтому всегда лучше сразу рассчитывать такие выражения там, где это возможно. Не … WHERE a — 3 = 5 , а … WHERE a = 8 .

    Еще одна возможность оптимизировать запрос — придерживаться общей идеи составления условий в SQL. Другими словами, условие должно иметь вид: . Например, запрос «. WHERE column1 — 3 = -column2» лучше привести к виду: . WHERE column1 = -column2 + 3 .

    И эти приемы оптимизации работают практически всегда и везде.

    Оптимизируем условия

    Теперь настало время произвести оптимизацию самих условных операторов SQL. Большинство запросов используют директиву SQL WHERE, поэтому, оптимизируя условия, можно добиться значительной производительности запросов. При этом почему-то лишь небольшая часть приложений для БД используют оптимизацию условий.

    AND

    Очевидно, что в серии из нескольких операторов AND условия должны располагаться в порядке возрастания вероятности истинности данного условия. Это делается для того, чтобы при проверке условий БД не проверяла остальную часть условия. Эти рекомендации не относится к БД Oracle, где условия начинают проверяться с конца. Соответственно, их порядок должен быть обратным – по убыванию вероятности истинности.

    OR

    Ситуация с данным оператором прямо противоположна ситуации с AND. Условия должны располагаться в порядке убывания вероятности истинности. Фирма Microsoft настойчиво рекомендует использовать данный метод при построении запросов, хотя многие даже не знают об этом или, по крайней мере, не обращают на него внимание. Но опять-таки это не относится к БД Oracle, где условия должны располагаться по возрастанию вероятности истинности.

    Еще одним условием для оптимизации можно считать тот факт, что если одинаковые колонки располагаются рядом, запрос выполняется быстрее. Например, запрос «.. WHERE column1 = 1 OR column2 = 3 OR column1 = 2» будет выполняться медленней, чем запрос «WHERE column1 = 1 OR column1 = 2 OR column2 = 3» . Даже если вероятность истинности условия column2 = 3 выше, чем column1 = 2.

    Еще в школе мне рассказывали про распределительный закон. Он гласит, что A AND (B OR C) — то же самое, что и (A AND B) OR (A AND C ). Опытным путем было установлено, что запрос вида «. WHERE column1 = 1 AND (column2 = «A» OR column2 = «B»)» выполняется несколько быстрее, чем «. WHERE (column1 = 1 AND column2 = «A») OR (column1 = 1 AND column2 = «B»)» . Некоторые БД сами умеют оптимизировать запросы такого типа, но лучше перестраховаться.

    NOT

    Эту операцию всегда следует приводить к более «читабельному» виду (в разумных пределах, конечно). Так, запрос «. WHERE NOT (column1 > 5)» преобразуется в «. WHERE column1 . Более сложные условия можно преобразовать используя правило де Моргана, которое ты тоже должен был изучить в школе. Согласно этому правилу NOT(A AND B) = (NOT A) OR (NOT B) и NOT(A OR B) = (NOT A) AND (NOT B) . Например, условие «. WHERE NOT (column1 > 5 OR column2 = 7)» преобразуется в более простую форму: . WHERE column1 7 .

    IN

    Многие наивно полагают, что запрос «. WHERE column1 = 5 OR column1 = 6» равносилен запросу «. WHERE column1 IN (5, 6)» . На самом деле это не так. Операция IN работает гораздо быстрее, чем серия OR. Поэтому всегда следует заменять OR на IN, где это возможно, несмотря на то, что некоторые БД сами производят эту оптимизацию. Там, где используется серия последовательных чисел, IN следует поменять на BETWEEN. Например, «. WHERE column1 IN (1, 3, 4, 5)» оптимизируется к виду: …WHERE column1 BETWEEN 1 AND 5 AND column1 <> 2 . И этот запрос действительно быстрее.

    LIKE

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

    CASE

    Сама эта функция может использоваться для повышения скорости работы запроса, когда в нем есть более одного вызова медленной функции в условии. Например, чтобы избежать повторного вызова slow_function() в запросе «. WHERE slow_function(column1) = 3 OR slow_function(column1) = 5» , нужно использовать CASE:

    . WHERE 1 = CASE slow_function(column1)

    WHEN 3 THEN 1

    WHEN 5 THEN 1

    END

    Сортировка

    ORDER BY используется для сортировки, которая, как известно, занимает время. Чем больше объем данных, тем больше времени займет сортировка, поэтому нужно обязательно ее оптимизировать. На скорость сортировки в запросах влияет три фактора:

    количество выбранных записей;

    Самой ресурсоемкой сортировкой является сортировка строк. Несмотря на то, что текстовые поля имеют фиксированную длину, длина содержимого этих полей может быть различной (в пределах размера поля). Поэтому неудивительно, что сортировка колонки VARCHAR(100) будет медленней, чем сортировка колонки VARCHAR(10) (даже если данные будут одинаковые). А происходит это из-за того, что при сортировке сама база данных выделяет память для своих операций в соответствии с максимальным размером поля независимо от содержимого. Поэтому при объявлении полей всегда следует использовать размер, который нужен, и не выделять лишние байты про запас.

    На компьютерах с ОС Windows поля типа INTEGER занимают 32 бита, а поля типа SMALLINT – 16 бит. Логично предположить, что сортировка полей типа SMALLINT должна происходить быстрее. На самом деле сортировка INTEGER происходит быстрее, чем SMALLINT. Также сортировка INTEGER происходит быстрее, чем CHAR.

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

    Группирование

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

    Первое, что следует помнить, — нужно использовать как можно меньше колонок для группировки. Также следует избегать лишних условий. Например, в запросе SELECT secondary_key_column, primary_key_column, COUNT(*) FROM Table1 GROUP BY secondary_key_column, primary_key_column колонка secondary_key_column совершенно не нужна. Причина простая: secondary_key_column является уникальным полем, оно может не иметь значений NULL, а значит, некоторые данные могут просто потеряться. Но если убрать secondary_key_column из секции GROUP BY, некоторые БД могут выдать ошибку о том, что невозможно указывать это поле, если оно не объявлено в секции GROUP BY. Для решения этой проблемы можно написать запрос в таком виде: SELECT MIN(secondary_key_column), primary_key_column, COUNT(*) FROM Table1 GROUP BY primary_key_column . Этот запрос быстрее и «правильнее» с точки зрения конструирования запросов.

    В большинстве БД операции WHERE и HAVING не равноценны и выполняются не одинаково. Это значит, что следующие два запроса логически одинаковы, но выполняются с разной скоростью:

    SELECT column1 FROM Table1 WHERE column2 = 5 GROUP BY column1 HAVING column1 > 6

    SELECT column1 FROM Table1 WHERE column2 = 5 AND column1 > 6 GROUP BY column1

    Второй запрос работает быстрее, чем первый. HAVING следует использовать в тех редких случаях, когда условие (в примере column1 > 6) сложно выразить без ущерба производительности.

    Если требуется группирование, но без использования агрегатных функций ( COUNT(), MIN(), MAX и т.д.), разумно использовать DISTINCT. Так, вместо SELECT column1 FROM Table1 GROUP BY column1 лучше использовать SELECT DISTINCT column1 FROM Table1 .

    При использовании MIN() и MAX() учитываем, что эти функции лучше работают по отдельности. Это значит, что их лучше использовать в раздельных запросах или в запросах с использованием UNION.

    При использовании функции SUM() большей производительности можно добиться используя SUM(x + y) , а не SUM(x) + SUM(y) . Для вычитания лучше противоположное: SUM(x) – SUM(y) быстрее, чем SUM(x – y).

    Соединения таблиц (JOINS)

    Вот где сложно что-то сказать про оптимизацию, так это при использовании JOIN . Дело в том, что скорость выполнения таких операций во многом зависит от организации самой таблицы: использование foreign-key, primary-key, количество вложенных соединений и т.д. Иногда лучшей производительности можно добиться используя вложенные циклы непосредственно в программе. Иногда быстрее работают JOINs. Однозначного совета по тому, как использовать разные способы соединения таблиц, не существует. Все зависит от конкретного случая и архитектуры БД.

    Подзапросы (SUBQUERIES)

    Раньше далеко не все БД могли похвастаться поддержкой подзапросов, а сейчас практически любая современная БД это умеет. Даже MySQL, которая несколько лет воплощала подзапросы в жизнь, наконец разжилась их поддержкой. Основная проблема при оптимизации подзапросов — не оптимизация непосредственно самого кода запроса, а выбор правильного способа для реализации запроса. Задачи, для которых используются подзапросы, также могут решаться с помощью вложенных циклов или JOIN’ов. Когда используешь JOIN, даешь возможность БД выбрать механизм, которым будет производиться соединение таблиц. Если же используешь подзапросы, то явно указываешь на использование вложенных циклов.

    Ниже аргументы в пользу того или иного способа. Выбирай сам в зависимости от ситуации.

    • Если запрос содержит условие WHERE, встроенный оптимизатор БД будет оптимизировать запрос в целом, в то время как в случае использования подзапросов запросы будут оптимизироваться отдельно.
    • Некоторые БД более эффективно работают с JOINs, нежели с подзапросами (например, Oracle).
    • После JOIN’а информация окажется в общем «списке», что нельзя сказать про подзапросы.
    • Подзапросы допускают более свободные условия.
    • Подзапросы могут содержать GROUP BY, HAVING, что намного сложнее реализовать в JOIN’ах.
    • Подзапросы могут использоваться при UPDATE, что невозможно при использовании JOIN’ов.
    • В последнее время оптимизация подзапросов самими БД (их встроенным оптимизатором) заметно улучшилась.

    Основное преимущество JOIN’ов в том, что не надо указывать БД то, каким именно способом производить операцию. А основное преимущество подзапросов в том, что цикл подзапроса может иметь несколько итераций (повторений), что, в свою очередь, может существенно увеличить производительность.

    Заключение

    В этой статье показаны самые распространенные способы увеличения производительности SQL-запросов. Тем не менее, чтобы оптимизировать запросы, есть еще очень много разных уловок и трюков. Оптимизация запросов больше похожа на искусство, чем на науку. У каждой базы данных свои встроенные оптимизаторы, которые могут помочь в этом нелегком деле, но всю работу за тебя никто не сделает. Как говорил старенький преподаватель по физике: «Чтобы решать задачи, их нужно решать».

    Не рекомендуется использовать ORDER BY в связке с такими операциями, как DISTINCT или GROUP B Y, потому что данные операторы могут создавать побочные эффекты для сортировки. Как следствие, ты можешь получить неправильно отсортированный набор данных, который может оказаться критическим в некоторых ситуациях. Такое следствие не относится к оптимизации, но забывать о нем не стоит.

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

    У любой SQL-операции есть «коэффициент полезности». Чем выше коэффициент, тем «полезней» операция: запрос выполняется быстрее.

    В отличие от компиляторов, не все БД умеют упрощать выражения типа x=1+1-1-1 до x=0. Следовательно, они тратят драгоценное время на выполнение пустых операций. Оптимизируй их заранее.

    При использовании функции SUM() можно добиться большей производительности с помощью SUM(x + y) , а не SUM(x) + SUM(y) .

    Но если функции SUM() требуются для вычитания, используй противоположное: SUM(x) – SUM(y). SUM(x – y) работает медленнее.

    У каждой БД есть свои встроенные оптимизаторы, но они далеки от совершенства. Поэтому оптимизируй заранее.

  • количество колонок после оператора ORDER BY;
  • длина и тип колонок, указанных после оператора ORDER BY.

  • Если запрос содержит условие WHERE, встроенный оптимизатор БД будет оптимизировать запрос в целом, в то время как в случае использования подзапросов запросы будут оптимизироваться отдельно.
  • Некоторые БД более эффективно работают с JOINs, нежели с подзапросами (например, Oracle).
  • После JOIN’а информация окажется в общем «списке», что нельзя сказать про подзапросы.
  • Подзапросы допускают более свободные условия.
  • Подзапросы могут содержать GROUP BY, HAVING, что намного сложнее реализовать в JOIN’ах.
  • Подзапросы могут использоваться при UPDATE, что невозможно при использовании JOIN’ов.
  • В последнее время оптимизация подзапросов самими БД (их встроенным оптимизатором) заметно улучшилась.

    Обзор основных SQL запросов

    Каждый сайт в Интернете, любой проект, обрабатывающий значительный объем информации, вынужден хранить эту информацию в тех или иных базах данных (БД). Подавляющее большинство проектов информацию сохраняют в БД реляционного типа, делая записи в различных подобиях таблиц. Как внесение новых записей, так и обращение к имеющимся, осуществляется с благодаря использованию запросов, составляемых конструкциями SQL (structured query language) – непроцедурного декларативного языка структурированных запросов. В нашем случае это подразумевает, что, используя конструкции SQL, мы будем обращаться к БД, сообщая что нужно сделать с данными, но не указывая способ, как именно это нужно сделать.

    Фактически, SQL является набором стандартов, для написания запросов к БД. Последняя действующая редакция стандартов языка SQL — ISO/IEC 9075:2020.

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

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

    Наиболее применяемые на сегодня СУБД, использующие свои стандарты (расширения) SQL:

    MySQL – СУБД, принадлежащая компании Oracle.

    PostgreSQL – свободная СУБД, поддерживаемая и развиваемая сообществом.

    Microsoft SQL Server – СУБД, принадлежащая компании Microsoft. Применяет диалект Transact-SQL (T-SQL).

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

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

    Здесь мы будем рассматривать запросы, применяя конструкции из спецификаций диалекта T-SQL.

    Коснемся классификации SQL запросов.

    Выделяют такие виды SQL запросов:

    DDL (Data Definition Language) язык определения данных. Задачей DDL запросов является создание БД и описание ее структуры. Запросами такого вида устанавливаются правила того, в каком виде различные данные будут размещаться в БД.

    DML (Data Manipulation Language) — язык манипулирования данными. В число запросов этого типа входят различные команды, используя которые непосредственно производятся некоторые манипуляции с данными. DML-запросы нужны для добавления изменений в уже внесенные данные, для получения данных из БД, для их сохранения, для обновления различных записей и для их удаления из БД. В число элементов DML-обращений входит основная часть SQL операторов.

    DCL (Data Control Language) — язык управления данными. Включает в себя запросы и команды, касающиеся разрешений, прав и других настроек СУБД.

    TCL (Transaction Control Language) — язык управления транзакциями. Конструкции такого типа применяют чтобы управлять изменениями, которые производятся с использованием DML запросов. Конструкции TCL позволяют нам производить объединение DML запросов в наборы транзакций.

    Основные типы SQL запросов по их видам:

    Ниже мы рассмотрим практические примеры применения SQL запросов для взаимодействия с БД используя запросы двух категорий – DDL и DML.

    Создание и настройка базы данных

    Нам нужна будет для примеров БД MS SQL Server 2020 и MS SQL Server Management Studio 2020.

    Рассмотрим последовательность действий того, как создать SQL запрос. Воспользовавшись Management Studio, для начала создадим новый редактор скриптов. Чтобы это сделать, на стандартной панели инструментов выберем «Создать запрос». Или воспользуемся клавиатурной комбинацией Ctrl+N.

    Нажимая кнопку «Создать запрос» в Management Studio, мы открываем тестовый редактор, используя который можно производить написание SQL запросов, сохранять их и запускать.

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

    Создадим новую БД с именем «b_library» для библиотеки книг. Чтобы это делать наберем в редакторе такой SQL запрос:

    Далее выделим введенный текст и нажмем F5 или кнопку «Выполнить». У нас создастся БД «b_library».

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

    В БД «b_library» создадим таблицу авторов «tAuthors» с такими столбцами: AuthorId, AuthorFirstName, AuthorLastName, AuthorAge:

    Заполним нашу таблицу таким авторами: Александр Пушкин, Сергей Есенин, Джек Лондон, Шота Руставели и Рабиндранат Тагор. Для этого используем такой SQL запрос:

    Мы можем посмотреть в «tAuthors» записи, путем отправления в СУБД простого SQL запроса:

    В нашей БД «b_library» мы создали первую таблицу «tAuthors», заполнили «tAuthors» авторами книг и теперь можем рассмотреть различные примеры SQL запросов, которыми мы сможем взаимодействовать с БД.

    Примеры простых запросов SQL к базам данных.

    Рассмотрим основные запросы SQL.

    SELECT

    1) Выведем все имеющиеся у нас БД:

    2) Выведем все таблицы в созданной нами ранее БД «b_library»:

    3) Выводим еще раз имеющиеся у нас записи по авторам книг из созданной выше «tAuthors»:

    4) Выведем информацию о том, сколько у нас имеется записей строк в «tAuthors»:

    5) Выведем из «tAuthors» две записи, начиная с четвертой. Используя ключевое слово OFFSET, пропустим первые три записи, а благодаря использованию ключевого слова FETCH – обозначим выборку только следующих 2 строк (ONLY):

    6) Выведем из «tAuthors» все записи с сортировкой в алфавитном порядке по первой букве имени автора:

    7) Выведем из «tAuthors» данные, предварительно по AuthorId отсортировав их по убыванию:

    8) Выберем записи из «tAuthors», значение AuthorFirstName у которых соответствует имени «Александр»:

    9) Выберем из «tAuthors» записи, где имя автора AuthorFirstName начинается с «се»:

    10) Выберем из «tAuthors» записи, в которых имя автора (AuthorFirstName) заканчивается на «ат»:

    11) Сделаем выборку всех строк из «tAuthors», значение AuthorId в которых равняется 2 или 4:

    12) Выберем в «tAuthors» такую запись AuthorAge, значение которой — наибольшее:

    13) Проведем выборку из «tAuthors» по столбцам AuthorFirstName и AuthorLastName:

    14) Получим из «tAuthors» все строки, у которых AuthorId не равняется трем:

    INSERT

    INSERT – это вид запроса SQL, при применении которого СУБД выполняет добавление новых записей в БД.

    Добавим в «tAuthors» нового автора – Уильяма Шекспира, 51 год. Соответственно в поле AuthorFirstName добавится Уильям, в AuthorLastName добавится Шекспир, в AuthorAge – 51. В AuthorId, в нашем случае, автоматически добавится значение, инкрементированное от предыдущего на 1.

    UPDATE

    UPDATE – SQL запрос, позволяющий внести изменения или дописывать новую информацию в те записи, которые уже существуют.

    Внесем корректировки в шестую запись (Author >

    Затем, обратимся к БД, чтобы вывести все имеющиеся записи:

    Мы видим изменения информации в записи автора под номером 6.

    DELETE

    DELETE – SQL запрос, выполняя который в СУБД производится операция удаления определенной строки из таблицы в БД.

    Обратимся к «tAuthors» с командой на удаление строки, где Author >

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

    Мы видим, что запись автора под номером 5 теперь отсутствует в «tAuthors» и, соответственно, не выводится с другими записями.

    DROP

    DROP – ключевое слово в SQL, применяемое для удаления данных с помощью запроса. К примеру удаление некоторой таблицы из БД.

    После рассмотрения ряда простых запросов к БД мы можем полностью удалить нашу таблицу «tAuthors» целиком, выполнив простой SQL запрос:

    Далее рассмотрим сложные запросы SQL.

    Примеры сложных запросов к базе данных MS SQL

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

    Сложные запросы получаются следующими способами:

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

    Рассмотрим в SQL примеры сложных запросов.

    Воспользуемся нашей предыдущей таблицей «tAuthors» и создадим дополнительно еще одну таблицу с книгами этих авторов – «tBooks». В качестве идентификатора авторов книг используем значение AuthorId из «tAuthors», а название книги — BookTitle.

    Заполним «tBooks» такими книгами:

    1) Сделаем выборку из БД всех книг, у которых имя автора — «Александр»:

    2) Сделаем выборку данных из «tBooks» всех книг, авторами которых являются люди, с именами «Александр» или «Сергей»:

    3) Сделаем выборку по книгам из таблицы «tBooks», у которых именами авторов являются НЕ «Сергей» и НЕ «Александр»:

    4) Возьмем таблицу «tBooks» и сделаем из нее выборку всех книг с указанием как имен, так и фамилий авторов этих книг из «tAuthors»:

    Выводы

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

    Руководство по архитектуре обработки запросов Query Processing Architecture Guide

    ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

    Компонент Компонент SQL Server Database Engine SQL Server Database Engine обрабатывает запросы к различным архитектурам хранения данных, таким как локальные таблицы, секционированные таблицы и таблицы, распределенные по нескольким серверам. The Компонент SQL Server Database Engine SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. В следующих разделах описано, как SQL Server SQL Server обрабатывает запросы и оптимизирует повторное использование запросов с помощью кэширования плана выполнения. The following topics cover how SQL Server SQL Server processes queries and optimizes query reuse through execution plan caching.

    Режимы выполнения Execution modes

    Компонент SQL Server Database Engine SQL Server Database Engine может обрабатывать инструкции Transact-SQL Transact-SQL в двух разных режимах: The Компонент SQL Server Database Engine SQL Server Database Engine can process Transact-SQL Transact-SQL statements using two distinct processing modes:

    • выполнение в построчном режиме; Row mode execution
    • выполнение в пакетном режиме. Batch mode execution

    Выполнение в построчном режиме Row mode execution

    Построчный режим выполнения — это метод обработки запросов, применяемый с традиционными таблицами RDMBS, при котором данные сохраняются в строковом формате. Row mode execution is a query processing method used with traditional RDMBS tables, where data is stored in row format. При выполнении запроса к данным в таблицах, хранящих строки, операторы дерева выполнения и дочерние операторы считывают каждую требуемую строку по всем столбцам, указанным в схеме таблицы. When a query is executed and accesses data in row store tables, the execution tree operators and child operators read each required row, across all the columns specified in the table schema. Из каждой считанной строки SQL Server SQL Server затем извлекает столбцы, необходимые для результирующего набора, как указано в инструкции SELECT, предикате JOIN или предикате фильтра. From each row that is read, SQL Server SQL Server then retrieves the columns that are required for the result set, as referenced by a SELECT statement, JOIN predicate, or filter predicate.

    Построчный режим выполнения очень эффективен в сценариях OLTP, но может быть не так эффективен при обращении к большим объемам данных, например при работе с хранилищем данных. Row mode execution is very efficient for OLTP scenarios, but can be less efficient when scanning large amounts of data, for example in Data Warehousing scenarios.

    Выполнение в пакетном режиме Batch mode execution

    Пакетный режим выполнения — это метод обработки запросов, при котором обрабатываются сразу несколько строк (поэтому он и называется пакетным). Batch mode execution is a query processing method used to process multiple rows together (hence the term batch). Каждый столбец из пакета сохраняется как вектор в отдельной области памяти. Таким образом, обработка в пакетном режиме основана на векторах. Each column within a batch is stored as a vector in a separate area of memory, so batch mode processing is vector-based. Кроме того, при обработке в пакетном режиме применяются алгоритмы, оптимизированные для многоядерных ЦП и увеличенной пропускной способности памяти, что характерно для современного оборудования. Batch mode processing also uses algorithms that are optimized for the multi-core CPUs and increased memory throughput that are found on modern hardware.

    Выполнение в пакетном режиме тесно интегрировано и оптимизировано для взаимодействия с форматом хранения columnstore. Batch mode execution is closely integrated with, and optimized around, the columnstore storage format. Обработка в пакетном режиме применяется к сжатым данным, когда это возможно, и исключает необходимость применения оператора обмена, используемого в построчном режиме выполнения. Batch mode processing operates on compressed data when possible, and eliminates the exchange operator used by row mode execution. Это позволяет повысить уровень параллелизма и производительность. The result is better parallelism and faster performance.


    Когда запрос выполняется в пакетном режиме и получает доступ к данным в индексах columnstore, операторы дерева выполнения и дочерние операторы считывают сразу несколько строк по сегментам столбцов. When a query is executed in batch mode, and accesses data in columnstore indexes, the execution tree operators and child operators read multiple rows together in column segments. SQL Server SQL Server считывает только столбцы, которые требуются в результатах, как указано в инструкции SELECT, предикате JOIN или предикате фильтра. reads only the columns required for the result, as referenced by a SELECT statement, JOIN predicate, or filter predicate.
    Дополнительные сведения об индексах columnstore см. в статье Архитектура индексов columnstore. For more information on columnstore indexes, see Columnstore Index Architecture.

    Пакетный режим выполнения очень эффективен в сценариях хранилищ данных, в которых считываются и вычисляются большие объемы данных. Batch mode execution is very efficient Data Warehousing scenarios, where large amounts of data are read and aggregated.

    Обработка инструкций SQL SQL Statement Processing

    Обработка одиночной инструкции Transact-SQL Transact-SQL — наиболее распространенный способ, с помощью которого SQL Server SQL Server выполняет инструкции Transact-SQL Transact-SQL . Processing a single Transact-SQL Transact-SQL statement is the most basic way that SQL Server SQL Server executes Transact-SQL Transact-SQL statements. Шаги, используемые для обработки одиночной инструкции SELECT , которая обращается только к таблицам локальной базы (а не к представлениям и не к удаленным таблицам), иллюстрируют основной процесс. The steps used to process a single SELECT statement that references only local base tables (no views or remote tables) illustrates the basic process.

    Приоритет логических операторов Logical Operator Precedence

    При использовании в инструкции нескольких логических операторов первым вычисляется NOT , затем AND и, наконец, OR . When more than one logical operator is used in a statement, NOT is evaluated first, then AND , and finally OR . Арифметические и побитовые операторы выполняются до логических. Arithmetic, and bitwise, operators are handled before logical operators. Дополнительные сведения см. в разделе Приоритет операторов. For more information, see Operator Precedence.

    В приведенном ниже примере условие цвета относится к модели продукта 21, но не к модели продукта 20, так как оператора AND имеет приоритет над оператором OR . In the following example, the color condition pertains to product model 21, and not to product model 20, because AND has precedence over OR .

    Можно изменить смысл запроса, добавляя скобки, чтобы добиться вычисления OR сначала. You can change the meaning of the query by adding parentheses to force evaluation of the OR first. В приведенном ниже запросе будут найдены модели 20 и 21 красного цвета. The following query finds only products under models 20 and 21 that are red.

    С помощью скобок, даже если они не требуются, можно улучшить читаемость запросов и уменьшить вероятность совершения незаметной ошибки из-за приоритета операторов. Using parentheses, even when they are not required, can improve the readability of queries, and reduce the chance of making a subtle mistake because of operator precedence. Использование скобок практически не влияет на производительность. There is no significant performance penalty in using parentheses. Следующий пример более понятен, чем исходный, хотя синтаксически они равноправны. The following example is more readable than the original example, although they are syntactically the same.

    Оптимизация инструкций SELECT Optimizing SELECT statements

    Инструкция SELECT является непроцедурной. Она не определяет точные шаги, которые сервер базы данных должен предпринять для получения запрошенных данных. A SELECT statement is non-procedural; it does not state the exact steps that the database server should use to retrieve the requested data. Это означает, что сервер базы данных должен проанализировать инструкцию для определения самого эффективного способа извлечения запрошенных данных. This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. Это упоминается как оптимизация инструкции SELECT . This is referred to as optimizing the SELECT statement. Компонент, который выполняет эти действия, называется оптимизатором запросов. The component that does this is called the Query Optimizer. Входные данные оптимизатора запросов включают сам запрос, схему базы данных (определения таблиц и индексов) и статистику базы данных. The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. Выходные данные оптимизатора запросов — это план выполнения запроса, который иногда называется планом запроса или просто планом. The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan or just a plan. Содержимое плана запроса описывается более подробно далее в этом разделе. The contents of a query plan are described in more detail later in this topic.

    Входные и выходные данные оптимизатора запросов при оптимизации одиночной инструкции SELECT показаны на следующей схеме. The inputs and outputs of the Query Optimizer during optimization of a single SELECT statement are illustrated in the following diagram:

    Инструкция SELECT определяет только следующее. A SELECT statement defines only the following:

    • Формат результирующего набора. The format of the result set. Он указан, главным образом, в списке выбора. This is specified mostly in the select list. Однако другие предложения, например ORDER BY и GROUP BY , также затрагивают конечную форму результирующего набора. However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
    • Таблицы, которые содержат исходные данные. The tables that contain the source data. Они указываются в предложении FROM . This is specified in the FROM clause.
    • Логическую связь между таблицами для инструкции SELECT . How the tables are logically related for the purposes of the SELECT statement. Это определяется в спецификациях соединения, которые могут появляться в предложении WHERE или в предложении ON , следующем за предложением FROM . This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM .
    • Условия, которым строки в исходных таблицах должны соответствовать для выбора их инструкцией SELECT . The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. Они указываются в предложениях WHERE и HAVING . These are specified in the WHERE and HAVING clauses.

    План выполнения запроса представляет собой определение следующего. A query execution plan is a definition of the following:

    Последовательности, в которой происходит обращение к исходным таблицам. The sequence in which the source tables are accessed.
    Как правило, существует много последовательностей, в которых сервер базы данных может обращаться к базовым таблицам для построения результирующего набора. Typically, there are many sequences in which the database server can access the base tables to build the result set. Например, если инструкция SELECT ссылается на три таблицы, сервер базы данных сначала может обратиться к TableA , использовать данные из TableA для извлечения соответствующих строк из TableB , а затем использовать данные из TableB для извлечения данных из TableC . For example, if the SELECT statement references three tables, the database server could first access TableA , use the data from TableA to extract matching rows from TableB , and then use the data from TableB to extract data from TableC . Другие последовательности, в которых сервер базы данных может обращаться к таблицам: The other sequences in which the database server could access the tables are:
    TableC , TableB , TableA или TableC , TableB , TableA , or
    TableB , TableA , TableC или TableB , TableA , TableC , or
    TableB , TableC , TableA или TableB , TableC , TableA , or
    TableC , TableA , TableB TableC , TableA , TableB

    Методы, используемые для извлечения данных из каждой таблицы. The methods used to extract data from each table.
    Есть различные методы для обращения к данным в каждой таблице. Generally, there are different methods for accessing the data in each table. Если необходимы только несколько строк с определенными ключевыми значениями, то сервер базы данных может использовать индекс. If only a few rows with specific key values are required, the database server can use an index. Если необходимы все строки в таблице, то сервер базы данных может пропустить индексы и выполнить просмотр таблицы. If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. Если необходимы все строки в таблице, но есть индекс, ключевые столбцы которого находятся в ORDER BY , то просмотр индекса вместо просмотра таблицы позволит избежать отдельный сортировки результирующего набора. If all the rows in a table are required but there is an index whose key columns are in an ORDER BY , performing an index scan instead of a table scan may save a separate sort of the result set. Если таблица является очень маленькой, то просмотры таблицы могут быть самым эффективным методом для практически всех обращений к таблице. If a table is very small, table scans may be the most efficient method for almost all access to the table.

    Процесс выбора одного плана выполнения из множества потенциально возможных планов называется оптимизацией. The process of selecting one execution plan from potentially many possible plans is referred to as optimization. Оптимизатор запросов является одним из самых важных компонентов системы базы данных SQL. The Query Optimizer is one of the most important components of a SQL database system. Хотя для анализа запроса и выбора плана оптимизатору запросов требуются некоторые накладные расходы, эти накладные расходы обычно многократно окупаются, когда оптимизатор запроса выбирает эффективный план выполнения. While some overhead is used by the Query Optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the Query Optimizer picks an efficient execution plan. Например, двум строительным компаниям могут быть предоставлены идентичные проекты дома. For example, two construction companies can be given identical blueprints for a house. Если одна компания потратит сначала несколько дней на планирование того, как она будет строить дом, а другая компания начнет строить без планирования, то компания, которая потратит время на планирование проекта, вероятно, закончит первой. If one company spends a few days at the beginning to plan how they will build the house, and the other company begins building without planning, the company that takes the time to plan their project will probably finish first.

    Оптимизатор запросов SQL Server SQL Server основан на оценке стоимости. The SQL Server SQL Server Query Optimizer is a cost-based Query Optimizer. Каждому возможному плану выполнения соответствует некоторая стоимость, определенная в терминах объема использованных вычислительных ресурсов. Each possible execution plan has an associated cost in terms of the amount of computing resources used. Оптимизатор запросов должен проанализировать возможные планы и выбрать один файл с самой низкой предполагаемой стоимостью. The Query Optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Для некоторых сложных инструкций SELECT есть тысячи возможных планов выполнения. Some complex SELECT statements have thousands of possible execution plans. В этих случаях оптимизатор запросов не анализирует все возможные комбинации. In these cases, the Query Optimizer does not analyze all possible combinations. Вместо этого он использует сложные алгоритмы поиска плана выполнения, имеющего стоимость, близкую к минимальной возможной стоимости. Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

    Оптимизатор запросов SQL Server SQL Server не выбирает план выполнения только на основе самой низкой стоимости ресурсов. Он выбирает такой план, который возвращает результаты пользователю при разумной стоимости ресурсов и делает это быстрее по сравнению с другими планами. The SQL Server SQL Server Query Optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. Например, параллельная обработка запроса обычно использует больше ресурсов, чем его последовательная обработка, но завершает выполнение запроса быстрее. For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. Оптимизатор запросов SQL Server SQL Server будет использовать план параллельного выполнения для возврата результатов, если это не окажет неблагоприятного влияния на загрузку сервера. The SQL Server SQL Server Query Optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.

    Оптимизатор запросов SQL Server SQL Server полагается на статистику распределения при оценке затрат на ресурсы для различных методов извлечения сведений из таблицы или индекса. The SQL Server SQL Server Query Optimizer relies on distribution statistics when it estimates the resource costs of different methods for extracting information from a table or index. Статистика распределения хранится для столбцов и индексов и содержит сведения о плотности 1 базовых данных. Distribution statistics are kept for columns and indexes, and hold information on the density 1 of the underlying data. Она указывает избирательность значений в определенном индексе или столбце. This is used to indicate the selectivity of the values in a particular index or column. Например, в таблице, представляющей автомобили, много автомобилей имеют одного производителя, но каждый автомобиль имеет уникальный идентификационный номер транспортного средства (VIN). For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). Индекс по VIN является более избирательным, чем индекс по производителям, так как VIN имеет меньшую плотность, чем производитель. An index on the VIN is more selective than an index on the manufacturer, because VIN has lower density then manufacturer. Если статистика индекса не является текущей, оптимизатор запросов, возможно, не сделает лучший выбор для текущего состояния таблицы. If the index statistics are not current, the Query Optimizer may not make the best choice for the current state of the table. Дополнительные сведения о плотности см. в разделе Статистика. For more information about densities, see Statistics.

    1 Плотность определяет распределение уникальных значений в данных или среднее количество повторяющихся значений для данного столбца. 1 Density defines the distribution of unique values that exist in the data, or the average number of duplicate values for a given column. По мере повышения плотности избирательность значения повышается. As density decreases, selectivity of a value increases.

    Оптимизатор запросов SQL Server SQL Server очень важен, так как позволяет серверу базы данных динамически изменять конфигурацию в ответ на меняющиеся условия в базе данных без участия программиста или администратора базы данных. The SQL Server SQL Server Query Optimizer is important because it enables the database server to adjust dynamically to changing conditions in the database without requiring input from a programmer or database administrator. Это дает возможность программистам сосредоточиться на описании конечного результата запроса. This enables programmers to focus on describing the final result of the query. Они могут положиться на то, что каждый раз при выполнении инструкции оптимизатор запросов SQL Server SQL Server будет создавать эффективный план выполнения с учетом состояния базы данных. They can trust that the SQL Server SQL Server Query Optimizer will build an efficient execution plan for the state of the database every time the statement is run.

    Обработка инструкции SELECT Processing a SELECT Statement

    Основные шаги, используемые SQL Server SQL Server для обработки одиночной инструкции SELECT, включают следующее: The basic steps that SQL Server SQL Server uses to process a single SELECT statement include the following:

    1. Средство анализа просматривает инструкцию SELECT и разбивает ее на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы. The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
    2. Строится дерево запроса, иногда называемое деревом последовательности, с описанием логических шагов, необходимых для преобразования исходных данных в формат, требуемый результирующему набору. A query tree, sometimes referred to as a sequence tree, is built describing the logical steps needed to transform the source data into the format required by the result set.
    3. Оптимизатор запросов анализирует различные способы, с помощью которых можно обратиться к исходным таблицам. The Query Optimizer analyzes different ways the source tables can be accessed. Затем он выбирает ряд шагов, которые возвращают результаты быстрее всего и используют меньше ресурсов. It then selects the series of steps that returns the results fastest while using fewer resources. Дерево запроса обновляется для записи этого точного ряда шагов. The query tree is updated to record this exact series of steps. Конечную, оптимизированную версию дерева запроса называют планом выполнения. The final, optimized version of the query tree is called the execution plan.
    4. Реляционный механизм начинает реализовывать план выполнения. The relational engine starts executing the execution plan. В ходе обработки шагов, требующих данных из базовых таблиц, реляционный механизм запрашивает у подсистемы хранилища передачу данных из набора строк, указанных реляционным механизмом. As the steps that require data from the base tables are processed, the relational engine requests that the storage engine pass up data from the rowsets requested from the relational engine.
    5. Реляционный механизм преобразует данные, возвращенные подсистемой хранилища, в заданный для результирующего набора формат и возвращает результирующий набор клиенту. The relational engine processes the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

    Свертывание констант и вычисление выражений Constant Folding and Expression Evaluation

    SQL Server SQL Server предварительно вычисляет некоторые постоянные выражения для улучшения производительности запросов. evaluates some constant expressions early to improve query performance. Это называет сверткой констант. This is referred to as constant folding. Константа является литералом Transact-SQL Transact-SQL , например 3, «ABC», «2005-12-31», 1.0e3 или 0x12345678. A constant is a Transact-SQL Transact-SQL literal, such as 3, ‘ABC’, ‘2005-12-31’, 1.0e3, or 0x12345678.

    Свертываемые выражения Foldable Expressions

    SQL Server SQL Server использует свертку констант со следующими типами выражений. uses constant folding with the following types of expressions:

    • Арифметические выражения, такие как 1+1, 5/3*2, которые содержат только константы. Arithmetic expressions, such as 1+1, 5/3*2, that contain only constants.
    • Логические выражения, такие как 1=1, 1>2 и 3>4, которые содержат только константы. Logical expressions, such as 1=1 and 1>2 AND 3>4, that contain only constants.
    • Встроенные функции, которые считаются сворачиваемыми SQL Server SQL Server , включая CAST и CONVERT . Built-in functions that are cons >SQL Server SQL Server , including CAST and CONVERT . Обычно внутренняя функция является свертываемой, если это функция только своих входных данных, а не контекстуальных данных, таких как параметры SET, настройки языка, параметры базы данных, ключи шифрования. Generally, an intrinsic function is foldable if it is a function of its inputs only and not other contextual information, such as SET options, language settings, database options, and encryption keys. Недетерминированные функции не являются свертываемыми. Nondeterministic functions are not foldable. Детерминированные встроенные функции являются свертываемыми за некоторыми исключениями. Deterministic built-in functions are foldable, with some exceptions.

    Исключение делается для типов больших объектов. An exception is made for large object types. Если в процессе свертки для выходных данных используется тип больших объектов (text, image, nvarchar(max), varchar(max) или varbinary(max)), то SQL Server SQL Server не свертывает такое выражение. If the output type of the folding process is a large object type (text, image, nvarchar(max), varchar(max), or varbinary(max)), then SQL Server SQL Server does not fold the expression.

    Несворачиваемые выражения Nonfoldable Expressions

    Все остальные типы выражения являются несвертываемыми. All other expression types are not foldable. В частности, несвертываемыми являются следующие типы выражений. In particular, the following types of expressions are not foldable:

    • Неконстантные выражения, такие как выражение, результат которого зависит от значения столбца. Nonconstant expressions such as an expression whose result depends on the value of a column.
    • Выражения, результат которых зависит от локальной переменной или параметра, такие как @x. Expressions whose results depend on a local variable or parameter, such as @x.
    • Недетерминированные функции. Nondeterministic functions.
    • Пользовательские функции (и языка Transact-SQL Transact-SQL , и среды CLR). User-defined functions (both Transact-SQL Transact-SQL and CLR).
    • Выражения, результат которых зависит от языковых настроек. Expressions whose results depend on language settings.
    • Выражения, результат которых зависит от параметров SET. Expressions whose results depend on SET options.
    • Выражения, результат которых зависит от параметров конфигурации сервера. Expressions whose results depend on server configuration options.

    Примеры свертываемых и несвертываемых постоянных выражений Examples of Foldable and Nonfoldable Constant Expressions

    Обратите внимание на следующий запрос: Consider the following query:

    Если для параметра базы данных PARAMETERIZATION не установлено значение FORCED для этого запроса, выражение 117.00 + 1000.00 вычисляется и заменяется его результатом ( 1117.00 ) перед компиляцией запроса. If the PARAMETERIZATION database option is not set to FORCED for this query, then the expression 117.00 + 1000.00 is evaluated and replaced by its result, 1117.00 , before the query is compiled. Такая свертка констант имеет следующие преимущества. Benefits of this constant folding include the following:

    • Нет необходимости вычислять выражение несколько раз во время выполнения. The expression does not have to be evaluated repeatedly at run time.
    • Значение выражения после его вычисления используется оптимизатором запросов для оценки размера результирующего набора части запроса TotalDue > 117.00 + 1000.00 . The value of the expression after it is evaluated is used by the Query Optimizer to estimate the size of the result set of the portion of the query TotalDue > 117.00 + 1000.00 .

    С другой стороны, если dbo.f является скалярной, определяемой пользователем функцией, выражение dbo.f(100) не свертывается, так как SQL Server SQL Server не свертывает выражения, которые включают определяемые пользователем функции, даже если они детерминированы. On the other hand, if dbo.f is a scalar user-defined function, the expression dbo.f(100) is not folded, because SQL Server SQL Server does not fold expressions that involve user-defined functions, even if they are deterministic. Дополнительные сведения о параметризации см. в разделе Принудительная параметризация далее в этой статье. For more information on parameterization, see Forced Parameterization later in this article.

    Вычисление выражений Expression Evaluation

    В дополнение некоторые выражения, которые не свертываются, но аргументы которых известны во время компиляции, где аргументы являются параметрами или постоянными, вычисляются механизмом оценки размера (количества элементов) набора результатов, который является частью оптимизатора во время оптимизации. In addition, some expressions that are not constant folded but whose arguments are known at compile time, whether the arguments are parameters or constants, are evaluated by the result-set size (cardinality) estimator that is part of the optimizer during optimization.

    Во время компиляции вычисляются следующие встроенные функции и специальные операторы (если их входные данные известны): UPPER , LOWER , RTRIM , DATEPART( YY only ) , GETDATE , CAST и CONVERT . Specifically, the following built-in functions and special operators are evaluated at compile time if all their inputs are known: UPPER , LOWER , RTRIM , DATEPART( YY only ) , GETDATE , CAST , and CONVERT . Следующие операторы также вычисляются во время компиляции, если все входные данные известны: The following operators are also evaluated at compile time if all their inputs are known:

    • Арифметические операторы: +, –, *, / и unary — Arithmetic operators: +, -, *, /, unary —
    • Логические операторы: AND , OR и NOT Logical Operators: AND , OR , NOT
    • Операторы сравнения: , =, <>, LIKE , IS NULL и IS NOT NULL Comparison operators: , =, <>, LIKE , IS NULL , IS NOT NULL

    Остальные функции или операторы не вычисляются оптимизатором запросов во время оценки кратности. No other functions or operators are evaluated by the Query Optimizer during cardinality estimation.

    Примеры вычисления выражений во время компиляции Examples of Compile-Time Expression Evaluation

    Рассмотрим следующую хранимую процедуру: Consider this stored procedure:

    Во время оптимизации инструкции SELECT в процедуре оптимизатор запросов пытается вычислить ожидаемую кратность результирующего набора для условия OrderDate > @d+1 . During optimization of the SELECT statement in the procedure, the Query Optimizer tries to evaluate the expected cardinality of the result set for the condition OrderDate > @d+1 . Выражение @d+1 не свертывается, так как @d является параметром. The expression @d+1 is not constant-folded, because @d is a parameter. Однако во время оптимизации значение этого параметра известно. However, at optimization time, the value of the parameter is known. Это дает возможность оптимизатору запросов точно оценить размер результирующего набора, что поможет выбрать наилучший план запроса. This allows the Query Optimizer to accurately estimate the size of the result set, which helps it select a good query plan.

    Теперь рассмотрим пример, похожий на предыдущий, за исключением того, что локальная переменная @d2 заменена в запросе выражением @d+1 и это выражение вычисляется в инструкции SET вместо вычисления в запросе. Now consider an example similar to the previous one, except that a local variable @d2 replaces @d+1 in the query and the expression is evaluated in a SET statement instead of in the query.

    Когда инструкция SELECT в MyProc2 оптимизируется в SQL Server SQL Server , значение переменной @d2 неизвестно. When the SELECT statement in MyProc2 is optimized in SQL Server SQL Server , the value of @d2 is not known. Поэтому в оптимизаторе запросов используется оценка по умолчанию для избирательности значений OrderDate > @d2 (в данном случае 30 %). Therefore, the Query Optimizer uses a default estimate for the selectivity of OrderDate > @d2 , (in this case 30 percent).

    Обработка других инструкций Processing Other Statements

    Основные шаги, описанные для обработки инструкции SELECT , применимы к другим инструкциям Transact-SQL Transact-SQL , таким как INSERT , UPDATE и DELETE . The basic steps described for processing a SELECT statement apply to other Transact-SQL Transact-SQL statements such as INSERT , UPDATE , and DELETE . Инструкции UPDATE и DELETE предназначены для набора строк, которые будут изменены или удалены. UPDATE and DELETE statements both have to target the set of rows to be modified or deleted. Идентификация этих строк выполняется так же, как и идентификация исходных строк, определяющих результирующий набор инструкции SELECT . The process of identifying these rows is the same process used to identify the source rows that contribute to the result set of a SELECT statement. Обе инструкции ( UPDATE и INSERT ) могут содержать встроенные инструкции SELECT , предоставляющие значения данных, которые будут обновлены или вставлены. The UPDATE and INSERT statements may both contain embedded SELECT statements that provide the data values to be updated or inserted.

    Даже инструкции языка описания данных (DDL), такие как CREATE PROCEDURE или ALTER TABLE , в конечном счете приводятся к ряду реляционных операций с таблицами системного каталога, а иногда (например, ALTER TABLE ADD COLUMN ) с таблицами данных. Even Data Definition Language (DDL) statements, such as CREATE PROCEDURE or ALTER TABLE , are ultimately resolved to a series of relational operations on the system catalog tables and sometimes (such as ALTER TABLE ADD COLUMN ) against the data tables.

    Рабочие таблицы Worktables

    Чтобы выполнить логические операции, указанные в инструкции Transact-SQL Transact-SQL , реляционному механизму может потребоваться создание рабочей таблицы. The relational engine may need to build a worktable to perform a logical operation specified in an Transact-SQL Transact-SQL statement. Рабочие таблицы — это внутренние таблицы, предназначенные для хранения промежуточных результатов. Worktables are internal tables that are used to hold intermediate results. Они создаются для некоторых запросов GROUP BY , ORDER BY или UNION . Worktables are generated for certain GROUP BY , ORDER BY , or UNION queries. Например, если предложение ORDER BY ссылается на столбцы, не включенные в индексы, реляционному модулю может потребоваться создать рабочую таблицу, чтобы отсортировать результирующий набор в необходимом порядке. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Рабочие таблицы также иногда применяются для временного хранения результатов выполнения части плана запроса. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Рабочие таблицы строятся в базе данных tempdb и после того, как они больше не нужны, автоматически удаляются. Worktables are built in tempdb and are dropped automatically when they are no longer needed.

    Разрешение представлений View Resolution

    Обработчик запросов SQL Server SQL Server обращается с индексированными и неиндексированными представлениями по-разному: The SQL Server SQL Server query processor treats indexed and nonindexed views differently:

    • Строки индексированного представления хранятся в базе данных в том же формате, что и таблица. The rows of an indexed view are stored in the database in the same format as a table. Если оптимизатор запросов решает использовать индексированное представление в плане запроса, оно обрабатывается так же, как базовая таблица. If the Query Optimizer decides to use an indexed view in a query plan, the indexed view is treated the same way as a base table.
    • В случае неиндексированного представления хранится только его определение, но не строки. Only the definition of a nonindexed view is stored, not the rows of the view. Оптимизатор запросов интегрирует логику из определения представления в план выполнения, который создается для инструкции Transact-SQL Transact-SQL , ссылающейся на неиндексированное представление. The Query Optimizer incorporates the logic from the view definition into the execution plan it builds for the Transact-SQL Transact-SQL statement that references the nonindexed view.

    Решение об использовании индексированного представления принимается оптимизатором запросов SQL Server SQL Server на основе тех же принципов, что и решение об использовании индекса таблицы. The logic used by the SQL Server SQL Server Query Optimizer to decide when to use an indexed view is similar to the logic used to decide when to use an index on a table. Если данные индексированного представления охватывают всю инструкцию Transact-SQL Transact-SQL или ее часть, и оптимизатор запросов определит, что использовать индекс представления выгодно с точки зрения стоимости, он выберет индекс независимо от того, имеется ли в запросе ссылка на представление по имени. If the data in the indexed view covers all or part of the Transact-SQL Transact-SQL statement, and the Query Optimizer determines that an index on the view is the low-cost access path, the Query Optimizer will choose the index regardless of whether the view is referenced by name in the query.

    Если инструкция Transact-SQL Transact-SQL ссылается на неиндексированное представление, средство синтаксического анализа и оптимизатор запросов анализируют исходный код инструкции Transact-SQL Transact-SQL и представления, разрешая их в один план выполнения. When an Transact-SQL Transact-SQL statement references a nonindexed view, the parser and Query Optimizer analyze the source of both the Transact-SQL Transact-SQL statement and the view and then resolve them into a single execution plan. Отдельных планов для инструкции Transact-SQL Transact-SQL и представления нет. There is not one plan for the Transact-SQL Transact-SQL statement and a separate plan for the view.

    Рассмотрим следующее представление: For example, consider the following view:

    Обе следующих инструкции Transact-SQL Transact-SQL , основанных на данном представлении, выполняют одни и те же операции над базовой таблицей, формируя одинаковый результат: Based on this view, both of these Transact-SQL Transact-SQL statements perform the same operations on the base tables and produce the same results:

    Функция Showplan SQL Server SQL Server Management Studio показывает, что реляционное ядро создает один и тот же план выполнения для обеих инструкций SELECT . The SQL Server SQL Server Management Studio Showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements.

    Использование указаний с представлениями Using Hints with Views

    Указания, связываемые с представлениями в запросах, могут конфликтовать с другими указаниями, которые обнаруживаются при расширении представления для доступа к его базовым таблицам. Hints that are placed on views in a query may conflict with other hints that are discovered when the view is expanded to access its base tables. Когда это происходит, запрос возвращает ошибку. When this occurs, the query returns an error. Взгляните, например, на следующее представление, в определение которого входит табличное указание: For example, consider the following view that contains a table hint in its definition:

    Предположим, что вводится следующий запрос: Now suppose you enter this query:

    Он завершится ошибкой, так как указание SERIALIZABLE , примененное в запросе к представлению Person.AddrState , при расширении представления распространится как на таблицу Person.Address , так и на таблицу Person.StateProvince . The query fails, because the hint SERIALIZABLE that is applied on view Person.AddrState in the query is propagated to both tables Person.Address and Person.StateProvince in the view when it is expanded. Однако при расширении представления будет также обнаружено указание NOLOCK , связанное с таблицей Person.Address . However, expanding the view also reveals the NOLOCK hint on Person.Address . Из-за конфликта указаний SERIALIZABLE и NOLOCK результирующий запрос окажется неправильным. Because the SERIALIZABLE and NOLOCK hints conflict, the resulting query is incorrect.

    Табличные указания PAGLOCK , NOLOCK , ROWLOCK , TABLOCK и TABLOCKX , а также HOLDLOCK , NOLOCK , READCOMMITTED , REPEATABLEREAD и SERIALIZABLE конфликтуют друг с другом. The PAGLOCK , NOLOCK , ROWLOCK , TABLOCK , or TABLOCKX table hints conflict with each other, as do the HOLDLOCK , NOLOCK , READCOMMITTED , REPEATABLEREAD , SERIALIZABLE table hints.

    Указания могут распространяться через уровни вложенных представлений. Hints can propagate through levels of nested views. Предположим, что в запросе указание HOLDLOCK применяется к представлению v1 . For example, suppose a query applies the HOLDLOCK hint on a view v1 . При расширении представления v1 выясняется, что представление v2 является частью его определения. When v1 is expanded, we find that view v2 is part of its definition. Определение v2 включает в себя связанное с одной из его базовых таблиц указание NOLOCK . v2 ‘s definition includes a NOLOCK hint on one of its base tables. Однако эта таблица также наследует представленное в запросе указание HOLDLOCK , примененное к представлению v1 . But this table also inherits the HOLDLOCK hint from the query on view v1 . Из-за конфликта указаний NOLOCK и HOLDLOCK запрос завершится ошибкой. Because the NOLOCK and HOLDLOCK hints conflict, the query fails.

    Если в запросе, включающем представление, используется указание FORCE ORDER , порядок соединения таблиц в представлении определяется по позиции представления в конструкции упорядочения. When the FORCE ORDER hint is used in a query that contains a view, the join order of the tables within the view is determined by the position of the view in the ordered construct. Например, приведенный ниже запрос выбирает данные из трех таблиц и представления: For example, the following query selects from three tables and a view:

    Допустим, что представление View1 определено следующим образом: And View1 is defined as shown in the following:

    В этом случае порядок соединения таблиц в плане запроса будет таким: Table1 , Table2 , TableA , TableB , Table3 . The join order in the query plan is Table1 , Table2 , TableA , TableB , Table3 .

    Разрешение индексов для представлений Resolving Indexes on Views

    Как и при работе с любым индексом, SQL Server SQL Server использует индексированное представление в плане запроса только в том случае, если оптимизатор запросов определит, что это целесообразно. As with any index, SQL Server SQL Server chooses to use an indexed view in its query plan only if the Query Optimizer determines it is beneficial to do so.

    Индексированные представления можно создавать в любом выпуске SQL Server SQL Server . Indexed views can be created in any edition of SQL Server SQL Server . В некоторых версиях SQL Server SQL Server оптимизатор запросов автоматически учитывает индексированные представления. In some editions of some versions of SQL Server SQL Server , the Query Optimizer automatically considers the indexed view. В некоторых версиях SQL Server SQL Server для использования индексированного представления необходимо применить табличное указание NOEXPAND . In some editions of some versions of SQL Server SQL Server , to use an indexed view, the NOEXPAND table hint must be used. Пояснения см. в документации для каждой версии. For clarification, see the documentation for each version.

    Оптимизатор запросов SQL Server SQL Server использует индексированные представления при соблюдении следующих условий. The SQL Server SQL Server Query Optimizer uses an indexed view when the following conditions are met:

    • Для следующих параметров сеанса установлено значение ON : These session options are set to ON :
      • ANSI_NULLS
      • ANSI_PADDING
      • ANSI_WARNINGS
      • ARITHABORT
      • CONCAT_NULL_YIELDS_NULL
      • QUOTED_IDENTIFIER
      • Для параметра сеанса NUMERIC_ROUNDABORT установлено значение OFF. The NUMERIC_ROUNDABORT session option is set to OFF.
    • Оптимизатор запросов находит соответствие между столбцами индексированного представления и элементами запроса, например: The Query Optimizer finds a match between the view index columns and elements in the query, such as the following:
      • предикатами условия поиска в предложении WHERE; Search condition predicates in the WHERE clause
      • операциями соединения; Join operations
      • Агрегатные функции Aggregate functions
      • Предложения GROUP BY GROUP BY clauses
      • ссылками на таблицы. Table references
    • Предполагаемые затраты на использование индекса имеют меньшую стоимость по сравнению с любыми механизмами доступа, имеющимися в распоряжении оптимизатора запросов. The estimated cost for using the index has the lowest cost of any access mechanisms considered by the Query Optimizer.
    • Каждая таблица, на которую ссылается запрос (либо прямо, либо при расширении представления для доступа к его базовым таблицам), соответствующая табличной ссылке в индексированном представлении, должна иметь в запросе точно такой же набор указаний. Every table referenced in the query (either directly, or by expanding a view to access its underlying tables) that corresponds to a table reference in the indexed view must have the same set of hints applied on it in the query.

    Указания READCOMMITTED и READCOMMITTEDLOCK в данном контексте всегда рассматриваются как разные, независимо от уровня изоляции текущей транзакции. The READCOMMITTED and READCOMMITTEDLOCK hints are always considered different hints in this context, regardless of the current transaction isolation level.

    За исключением требований к параметрам SET и табличным указаниям, это те же самые правила, по которым оптимизатор запросов выясняет, подходит ли индекс таблицы для выполнения запроса. Other than the requirements for the SET options and table hints, these are the same rules that the Query Optimizer uses to determine whether a table index covers a query. Для использования индексированного представления в запросе больше ничего указывать не нужно. Nothing else has to be specified in the query for an indexed view to be used.

    Запрос не обязательно должен ссылаться в предложении FROM на индексированное представление, чтобы оптимизатор запросов его использовал. A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. Если запрос ссылается на столбцы в базовой таблице, которые также присутствуют в индексированном представлении, и оптимизатор запросов определяет, что индексированное представление будет иметь самую низкую стоимость механизма доступа, он применит индексированное представление точно так же, как он применяет индекс базовой таблицы, если на него отсутствуют прямые ссылки в запросе. If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access mechanism, the Query Optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query. Оптимизатор запросов может применить представление и в том случае, если оно содержит столбцы, на которые не ссылается запрос, если это представление обеспечивает самую низкую стоимость доступа к одному или нескольким столбцам, указанным в запросе. The Query Optimizer may choose the view when it contains columns that are not referenced by the query, as long as the view offers the lowest cost option for covering one or more of the columns specified in the query.

    Индексированное представление, указанное в предложении FROM , оптимизатор запросов рассматривает как стандартное представление. The Query Optimizer treats an indexed view referenced in the FROM clause as a standard view. В начале процесса оптимизации оптимизатор запросов расширяет определение представления в запрос, The Query Optimizer expands the definition of the view into the query at the start of the optimization process. а затем выполняет в индексированном представлении поиск соответствий. Then, indexed view matching is performed. В окончательном плане выполнения, выбранном оптимизатором запросов, может быть использовано индексированное представление, или план может материализовать необходимые данные за счет доступа к базовым таблицам этого представления. The indexed view may be used in the final execution plan selected by the Query Optimizer, or instead, the plan may materialize necessary data from the view by accessing the base tables referenced by the view. Оптимизатор запросов выбирает вариант с наименьшей стоимостью. The Query Optimizer chooses the lowest-cost alternative.

    Использование указаний с индексированными представлениями Using Hints with Indexed Views

    Чтобы индексы представления не использовались в запросе, можно задать указание запроса EXPAND VIEWS или табличное указание NOEXPAND , чтобы принудительно задействовать индекс для индексированного представления запроса в предложении FROM . You can prevent view indexes from being used for a query by using the EXPAND VIEWS query hint, or you can use the NOEXPAND table hint to force the use of an index for an indexed view specified in the FROM clause of a query. Однако оптимизатору запросов следует разрешить динамически определять лучший метод доступа для каждого из запросов. However, you should let the Query Optimizer dynamically determine the best access methods to use for each query. Ограничьте применение указаний EXPAND и NOEXPAND только теми случаями, когда очевидно, что они значительно повысят производительность. Limit your use of EXPAND and NOEXPAND to specific cases where testing has shown that they improve performance significantly.

    Параметр EXPAND VIEWS указывает, что оптимизатор запросов не будет использовать индексы представления для всего запроса. The EXPAND VIEWS option specifies that the Query Optimizer not use any view indexes for the whole query.

    Если для представления задано указание NOEXPAND , оптимизатор запросов предполагает использование всех индексов, определенных в представлении. When NOEXPAND is specified for a view, the Query Optimizer considers using any indexes defined on the view. NOEXPAND может иметь необязательное предложение INDEX() , которое активирует принудительное применение указанных индексов в оптимизаторе запросов. NOEXPAND specified with the optional INDEX() clause forces the Query Optimizer to use the specified indexes. NOEXPAND может быть указано только для индексированного представления и не применяется для представлений без индексов. NOEXPAND can be specified only for an indexed view and cannot be specified for a view not indexed.


    Если в запросе, содержащем представление, не заданы ни NOEXPAND , ни EXPAND VIEWS , это представление расширяется для доступа к базовым таблицам. When neither NOEXPAND nor EXPAND VIEWS is specified in a query that contains a view, the view is expanded to access underlying tables. Если запрос представления содержит какие-либо табличные указания, они распространяются на базовые таблицы. If the query that makes up the view contains any table hints, these hints are propagated to the underlying tables. (Этот процесс подробно описан в разделе «Разрешение представлений».) Пока указания, имеющиеся в базовых таблицах представления, идентичны, для запроса может устанавливаться соответствие с индексированным представлением. (This process is explained in more detail in View Resolution.) As long as the set of hints that exists on the underlying tables of the view are identical to each other, the query is eligible to be matched with an indexed view. Чаще всего эти указания соответствуют друг другу, поскольку они наследуются непосредственно из представления. Most of the time, these hints will match each other, because they are being inherited directly from the view. Однако если запрос ссылается на таблицы, а не на представления, и применяемые к этим таблицам указания неидентичны, то для такого запроса соответствие с индексированным представлением устанавливаться не может. However, if the query references tables instead of views, and the hints applied directly on these tables are not identical, then such a query is not eligible for matching with an indexed view. Если указания INDEX , PAGLOCK , ROWLOCK , TABLOCKX , UPDLOCK или XLOCK применяются к таблицам, на которые запрос ссылается после расширения представления, для этого запроса не может быть установлено соответствие с индексированным представлением. If the INDEX , PAGLOCK , ROWLOCK , TABLOCKX , UPDLOCK , or XLOCK hints apply to the tables referenced in the query after view expansion, the query is not eligible for indexed view matching.

    Если табличное указание вида INDEX (index_val[ . n] ) ссылается на представление в запросе, а указание NOEXPAND не задано, указание индекса не обрабатывается. If a table hint in the form of INDEX (index_val[ . n] ) references a view in a query and you do not also specify the NOEXPAND hint, the index hint is ignored. Для указания конкретного индекса используйте NOEXPAND . To specify use of a particular index, use NOEXPAND .

    Обычно, если оптимизатор запросов устанавливает соответствие индексированного представления запросу, все заданные в таблицах или представлениях запроса указания применяются непосредственно к индексированному представлению. Generally, when the Query Optimizer matches an indexed view to a query, any hints specified on the tables or views in the query are applied directly to the indexed view. Если оптимизатор запросов решил не использовать индексированное представление, все указания распространяются непосредственно на таблицы, на которые ссылается это представление. If the Query Optimizer chooses not to use an indexed view, any hints are propagated directly to the tables referenced in the view. Дополнительные сведения см. в разделе «Разрешение представлений». For more information, see View Resolution. Это не относится к указаниям соединения. This propagation does not apply to join hints. Они применяются только в той исходной позиции запроса, где они указаны. They are applied only in their original position in the query. Указания в соединении оптимизатором запросов при установке соответствия запроса индексированным представлениям не рассматриваются. Join hints are not considered by the Query Optimizer when matching queries to indexed views. Если план запроса использует индексированное представление, которое совпадает с частью запроса, содержащей указание соединения, последний в данном плане не используется. If a query plan uses an indexed view that matches part of a query that contains a join hint, the join hint is not used in the plan.

    В определении индексированных представлений указания не допускаются. Hints are not allowed in the definitions of indexed views. В режимах совместимости 80 и выше SQL Server SQL Server пропускает указания при работе с определениями индексированных представлений и при выполнении содержащих их запросов. In compatibility mode 80 and higher, SQL Server SQL Server ignores hints inside indexed view definitions when maintaining them, or when executing queries that use indexed views. В режиме совместимости 80 использование указаний в определениях индексированных представлений не вызывает ошибок синтаксиса — они просто пропускаются. Although using hints in indexed view definitions will not produce a syntax error in 80 compatibility mode, they are ignored.

    Разрешение распределенных секционированных представлений Resolving Distributed Partitioned Views

    Обработчик запросов SQL Server SQL Server оптимизирует производительность распределенных секционированных представлений. The SQL Server SQL Server query processor optimizes the performance of distributed partitioned views. При оптимизации распределенных секционированных представлений важно минимизировать количество данных, передаваемых между серверами. The most important aspect of distributed partitioned view performance is minimizing the amount of data transferred between member servers.

    SQL Server SQL Server интеллектуальные динамические планы, которые позволяют эффективно использовать распределенные запросы для доступа к данным в таблицах удаленных серверов. builds intelligent, dynamic plans that make efficient use of distributed queries to access data from remote member tables:

    • Обработчик запросов сначала использует OLE DB для получения определений ограничений CHECK для каждой таблицы-элемента. The Query Processor first uses OLE DB to retrieve the check constraint definitions from each member table. Это позволяет ему определить распределение ключевых значений между таблицами серверов. This allows the query processor to map the distribution of key values across the member tables.
    • Обработчик запросов сравнивает диапазоны ключей, заданные в инструкции Transact-SQL Transact-SQL WHERE , со схемой распределения строк между таблицами-элементами. The Query Processor compares the key ranges specified in an Transact-SQL Transact-SQL statement WHERE clause to the map that shows how the rows are distributed in the member tables. Затем обработчик запросов строит план выполнения, который использует распределенные запросы для получения только тех удаленных строк, которые требуются для завершения инструкции Transact-SQL Transact-SQL . The query processor then builds a query execution plan that uses distributed queries to retrieve only those remote rows that are required to complete the Transact-SQL Transact-SQL statement. Кроме того, план выполнения строится таким образом, чтобы обращение к удаленным данным или метаданным выполнялось только в тот момент, когда они требуются. The execution plan is also built in such a way that any access to remote member tables, for either data or metadata, are delayed until the information is required.

    Например, рассмотрим систему, в которой пользовательская таблица разделена на три секции на серверах Server1 ( CustomerID от 1 до 3299999), Server2 ( CustomerID от 3300000 до 6599999) и Server3 ( CustomerID от 6600000 до 9999999). For example, consider a system where a customers table is partitioned across Server1 ( CustomerID from 1 through 3299999), Server2 ( CustomerID from 3300000 through 6599999), and Server3 ( CustomerID from 6600000 through 9999999).

    Допустим, план выполнения, созданный для этого запроса, выполняется на сервере Server1: Consider the execution plan built for this query executed on Server1:

    План выполнения этого запроса извлекает строки со значениями ключей CustomerID от 3200000 до 3299999 из локальной таблицы-элемента и вызывает распределенный запрос для получения строк со значениями ключей от 3300000 до 3400000 с сервера Server2. The execution plan for this query extracts the rows with CustomerID key values from 3200000 through 3299999 from the local member table, and issues a distributed query to retrieve the rows with key values from 3300000 through 3400000 from Server2.

    Обработчик запросов SQL Server SQL Server также может встраивать динамическую логику в планы выполнения запросов для инструкций Transact-SQL Transact-SQL в тех случаях, когда значения ключей во время создания плана неизвестны. The SQL Server SQL Server Query Processor can also build dynamic logic into query execution plans for Transact-SQL Transact-SQL statements in which the key values are not known when the plan must be built. Рассмотрим следующую хранимую процедуру: For example, consider this stored procedure:

    SQL Server SQL Server не может предсказать, какое значение ключа будет выдавать параметр @CustomerIDParameter каждый раз при выполнении процедуры. cannot predict what key value will be supplied by the @CustomerIDParameter parameter every time the procedure is executed. Поскольку значение ключа предсказать нельзя, обработчик запросов не может заранее определить, к какой таблице потребуется доступ. Because the key value cannot be predicted, the query processor also cannot predict which member table will have to be accessed. В этом случае SQL Server SQL Server встраивает в план выполнения условную логику, называемую динамическими фильтрами, для управления доступом к удаленным таблицам на основе значения входного параметра. To handle this case, SQL Server SQL Server builds an execution plan that has conditional logic, referred to as dynamic filters, to control which member table is accessed, based on the input parameter value. Если предположить, что хранимая процедура GetCustomer выполнена на сервере Server1, логику плана выполнения можно представить следующим образом: Assuming the GetCustomer stored procedure was executed on Server1, the execution plan logic can be represented as shown in the following:

    Иногда SQL Server SQL Server строит динамические планы даже для непараметризованных запросов. SQL Server SQL Server sometimes builds these types of dynamic execution plans even for queries that are not parameterized. Оптимизатор запросов может параметризовать запрос так, чтобы план выполнения можно было использовать повторно. The Query Optimizer may parameterize a query so that the execution plan can be reused. Если оптимизатор запросов параметризует запрос, ссылающийся на секционированное представление, он не будет знать, находятся ли нужные строки в заданной базовой таблице. If the Query Optimizer parameterizes a query referencing a partitioned view, the Query Optimizer can no longer assume the required rows will come from a specified base table. В дальнейшем ему придется использовать динамические фильтры в планах выполнения. It will then have to use dynamic filters in the execution plan.

    Выполнение хранимых процедур и триггеров Stored Procedure and Trigger Execution

    SQL Server SQL Server хранит только исходный код хранимых процедур и триггеров. stores only the source for stored procedures and triggers. При выполнении хранимой процедуры или триггера в первый раз исходный код компилируется в план выполнения. When a stored procedure or trigger is first executed, the source is compiled into an execution plan. Если очередной вызов хранимой процедуры или триггера будет инициирован до устаревания плана выполнения, реляционный механизм обнаружит существующий план и использует его повторно. If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. Если план устарел и был удален из памяти, будет создан новый план. If the plan has aged out of memory, a new plan is built. Этот процесс похож на то, как SQL Server SQL Server обрабатывает все инструкции Transact-SQL Transact-SQL . This process is similar to the process SQL Server SQL Server follows for all Transact-SQL Transact-SQL statements. Основное преимущество хранимых процедур и триггеров SQL Server SQL Server над пакетами динамического кода Transact-SQL Transact-SQL в плане быстродействия заключается в том, что их инструкции Transact-SQL Transact-SQL всегда остаются постоянными. The main performance advantage that stored procedures and triggers have in SQL Server SQL Server compared with batches of dynamic Transact-SQL Transact-SQL is that their Transact-SQL Transact-SQL statements are always the same. Благодаря этому реляционный механизм может с легкостью сопоставлять их с любыми существующими планами выполнения. Therefore, the relational engine easily matches them with any existing execution plans. Это облегчает повторное использование планов хранимых процедур и триггеров. Stored procedure and trigger plans are easily reused.

    Планы выполнения хранимых процедур и триггеров обрабатываются отдельно от плана выполнения пакета, вызвавшего хранимую процедуру или приведшего к срабатыванию триггера. The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. Это способствует повторному использованию планов выполнения хранимых процедур и триггеров. This allows for greater reuse of the stored procedure and trigger execution plans.

    Кэширование и повторное использование плана выполнения Execution Plan Caching and Reuse

    В SQL Server SQL Server есть пул памяти, предназначенный для хранения планов выполнения и буферов данных. SQL Server SQL Server has a pool of memory that is used to store both execution plans and data buffers. Процентное соотношение размера пула, выделенного для планов выполнения и буферов данных, динамически изменяется в зависимости от состояния системы. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. Часть пула памяти, используемого для хранения планов выполнения, называется кэшем планов. The part of the memory pool that is used to store execution plans is referred to as the plan cache.

    В SQL Server SQL Server планы выполнения состоят из следующих основных компонентов. SQL Server SQL Server execution plans have the following main components:

    • План выполнения запросаQuery Execution Plan
      Тело плана выполнения является реентерабельной структурой данных только для чтения, которая предназначена для использования любым числом пользователей. The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. Оно называется планом запроса. This is referred to as the query plan. План запроса не содержит контекста пользователя. No user context is stored in the query plan. В памяти содержится одна или две копии плана запроса (но не более): одна — для всех последовательных выполнений, а другая — для всех параллельных выполнений. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. Одна параллельная копия обслуживает все параллельные выполнения независимо от степени параллелизма. The parallel copy covers all parallel executions, regardless of their degree of parallelism.
    • Контекст выполненияExecution Context
      Для каждого пользователя, который в настоящий момент выполняет запрос, имеется структура данных, которая содержит данные, относящиеся к данному выполнению, например значения параметров. Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. Эта структура данных называется контекстом выполнения. This data structure is referred to as the execution context. Структуры данных контекста выполнения являются повторно используемыми. The execution context data structures are reused. Если пользователь выполняет запрос и одна из структур не используется, она повторно инициализируется контекстом нового пользователя. If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.

    При выполнении любой инструкции Transact-SQL Transact-SQL в SQL Server SQL Server реляционное ядро сначала просматривает кэш планов, проверяя, нет ли в нем плана выполнения для такой же инструкции Transact-SQL Transact-SQL . When any Transact-SQL Transact-SQL statement is executed in SQL Server SQL Server , the Relational Engine first looks through the plan cache to verify that an existing execution plan for the same Transact-SQL Transact-SQL statement exists. Инструкция Transact-SQL Transact-SQL считается существующей, если она точно соответствует выполнявшейся ранее инструкции Transact-SQL Transact-SQL с кэшированным планом, символ за символом. The Transact-SQL Transact-SQL statement qualifies as existing if it literally matches a previously executed Transact-SQL Transact-SQL statement with a cached plan, character per character. SQL Server SQL Server повторно использует все найденные планы, что позволяет избежать перекомпиляции инструкций Transact-SQL Transact-SQL . reuses any existing plan it finds, saving the overhead of recompiling the Transact-SQL Transact-SQL statement. Если не найдено ни одного существующего плана, SQL Server SQL Server формирует для этого запроса новый план. If no existing execution plan exists, SQL Server SQL Server generates a new execution plan for the query.

    Некоторые инструкции Transact-SQL Transact-SQL не кэшируются, к ним относятся инструкции массовых операций, работающие в rowstore, а также инструкции, содержащие строковые литералы размером более 8 КБ. Some Transact-SQL Transact-SQL statements are not cached, such as bulk operation statements running on rowstore or statements containing string literals larger than 8 KB in size.

    SQL Server SQL Server реализует эффективный алгоритм поиска существующих планов выполнения для любой инструкции Transact-SQL Transact-SQL . has an efficient algorithm to find any existing execution plans for any specific Transact-SQL Transact-SQL statement. В большинстве систем ресурсы, затрачиваемые на поиск готового плана, всегда меньше ресурсов, затрачиваемых на повторную компиляцию каждой инструкции Transact-SQL Transact-SQL . In most systems, the minimal resources that are used by this scan are less than the resources that are saved by being able to reuse existing plans instead of compiling every Transact-SQL Transact-SQL statement.

    Алгоритмы поиска соответствия инструкции Transact-SQL Transact-SQL существующему неиспользуемому плану выполнения в кэше требуют, чтобы все ссылки на объекты были полными. The algorithms to match new Transact-SQL Transact-SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. Например, предположим, что Person является схемой по умолчанию для пользователя, выполняющего инструкции SELECT ниже. For example, assume that Person is the default schema for the user executing the below SELECT statements. Хотя в этом примере для выполнения не обязательно, чтобы таблица Person была полной, это означает, что вторая инструкция не соответствует существующему плану, однако третья инструкция соответствует: While in this example it is not required that the Person table is fully qualified to execute, it means that the second statement is not matched with an existing plan, but the third is matched:

    Удаление планов выполнения из кэша планов Removing Execution Plans from the Plan Cache

    Планы выполнения остаются в кэше планов до тех пор, пока для их хранения остается достаточно памяти. Execution plans remain in the plan cache as long as there is enough memory to store them. При нехватке памяти Компонент SQL Server Database Engine SQL Server Database Engine определяет планы выполнения, которые нужно удалить из кэша процедур, на основе стоимости планов. When memory pressure exists, the Компонент SQL Server Database Engine SQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. Для принятия основанного на стоимости решения компонент Компонент SQL Server Database Engine SQL Server Database Engine увеличивает и уменьшает переменную текущей стоимости для каждого плана выполнения, руководствуясь следующими факторами. To make a cost-based decision, the Компонент SQL Server Database Engine SQL Server Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

    Когда пользовательский процесс добавляет в кэш план выполнения, он устанавливает текущую стоимость равной стоимости компиляции исходного запроса. Для нерегламентированных планов выполнения пользовательский процесс устанавливает значение текущей стоимости равным нулю. When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to zero. После этого каждый раз, когда пользовательский процесс ссылается на план выполнения, он сбрасывает текущую стоимость, делая ее равной исходной стоимости компиляции. Для нерегламентированных планов выполнения пользовательский процесс повышает значение текущей стоимости. Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. Для всех планов максимальное значение текущей стоимости равно исходной стоимости компиляции. For all plans, the maximum value for the current cost is the original compile cost.

    При нехватке памяти Компонент SQL Server Database Engine SQL Server Database Engine удаляет планы выполнения из кэша планов. When memory pressure exists, the Компонент SQL Server Database Engine SQL Server Database Engine responds by removing execution plans from the plan cache. Чтобы определить, какие планы следует удалить, компонент Компонент SQL Server Database Engine SQL Server Database Engine многократно проверяет состояние каждого плана выполнения и удаляет те из них, для которых текущая стоимость равна нулю. To determine which plans to remove, the Компонент SQL Server Database Engine SQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. План выполнения с нулевой текущей стоимостью не удаляется автоматически при возникновении нехватки памяти; он удаляется только после проверки компонентом Компонент SQL Server Database Engine SQL Server Database Engine , если его текущая стоимость равна нулю. An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Компонент SQL Server Database Engine SQL Server Database Engine examines the plan and the current cost is zero. При проверке плана выполнения компонент Компонент SQL Server Database Engine SQL Server Database Engine приближает текущую стоимость к нулю, уменьшая ее в случае, если запрос в данный момент не использует план. When examining an execution plan, the Компонент SQL Server Database Engine SQL Server Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

    Компонент Компонент SQL Server Database Engine SQL Server Database Engine многократно проверяет планы выполнения, пока не удалит достаточно, чтобы удовлетворить требования к памяти. The Компонент SQL Server Database Engine SQL Server Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. При нехватке памяти стоимость плана выполнения может увеличиться и уменьшиться несколько раз. While memory pressure exists, an execution plan may have its cost increased and decreased more than once. При восстановлении достаточного объема памяти Компонент SQL Server Database Engine SQL Server Database Engine прекращает уменьшать текущую стоимость неиспользуемых планов выполнения, и все планы выполнения остаются в кэше планов, даже если их стоимость равна нулю. When memory pressure no longer exists, the Компонент SQL Server Database Engine SQL Server Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the plan cache, even if their cost is zero.

    Компонент SQL Server Database Engine SQL Server Database Engine использует монитор ресурсов и пользовательские рабочие потоки для освобождения памяти, занимаемой кэшем планов, при нехватке памяти. The Компонент SQL Server Database Engine SQL Server Database Engine uses the resource monitor and user worker threads to free memory from the plan cache in response to memory pressure. Монитор ресурсов и пользовательские рабочие потоки могут проверять параллельно выполняющиеся планы, что позволяет уменьшать текущую стоимость для каждого неиспользуемого плана выполнения. The resource monitor and user worker threads can examine plans run concurrently to decrease the current cost for each unused execution plan. Монитор ресурсов удаляет планы выполнения из кэша планов при глобальной нехватке памяти. The resource monitor removes execution plans from the plan cache when global memory pressure exists. Он освобождает память для принудительного выполнения политик для системной памяти, памяти процессов, памяти пула ресурсов и максимального размера всех кэшей. It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

    Максимальный размер всех кэшей является функцией от размера буферного пула и не может превышать максимальный объем памяти сервера. The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. Дополнительные сведения о настройке максимального объема памяти сервера см. в описании параметра max server memory в статье об sp_configure . For more information on configuring the maximum server memory, see the max server memory setting in sp_configure .

    Пользовательские рабочие потоки удаляют планы выполнения из кэша планов при нехватке памяти в одиночном кэше. The user worker threads remove execution plans from the plan cache when single cache memory pressure exists. Они обеспечивают выполнение политик для максимального размера кэша и максимума записей одиночного кэша. They enforce policies for maximum single cache size and maximum single cache entries.

    В следующих примерах показано, какие планы выполнения удаляются из кэша планов. The following examples illustrate which execution plans get removed from the plan cache:

    • План выполнения часто используется, поэтому его стоимость никогда не принимает значение ноль. An execution plan is frequently referenced so that its cost never goes to zero. Этот план остается в кэше планов и не удаляется, пока имеется достаточный объем памяти, а его текущая стоимость не равна нулю. The plan remains in the plan cache and is not removed unless there is memory pressure and the current cost is zero.
    • Нерегламентированный план выполнения вставляется и не используется до возникновения нехватки памяти. An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. Так как нерегламентированные планы выполнения инициализируются с текущей стоимостью, равной нулю, то Компонент SQL Server Database Engine SQL Server Database Engine при проверке планов выполнения обнаруживает план выполнения с нулевой стоимостью и удаляет его из кэша планов. Since ad-hoc plans are initialized with a current cost of zero, when the Компонент SQL Server Database Engine SQL Server Database Engine examines the execution plan, it will see the zero current cost and remove the plan from the plan cache. Нерегламентированный план выполнения с нулевой текущей стоимостью остается в кэше планов при наличии достаточного объема памяти. The ad-hoc execution plan remains in the plan cache with a zero current cost when memory pressure does not exist.

    Чтобы вручную удалить отдельный план выполнения или все планы, используйте команду DBCC FREEPROCCACHE. To manually remove a single plan or all plans from the cache, use DBCC FREEPROCCACHE. Начиная с версии SQL Server 2020 (13.x) SQL Server 2020 (13.x) , для очистки кэша процедур (планов) для базы данных в области действия служит инструкция ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE . Starting with SQL Server 2020 (13.x) SQL Server 2020 (13.x) , the ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE to clear the procedure (plan) cache for the database in scope.

    Перекомпиляция планов выполнения Recompiling Execution Plans

    Некоторые изменения в базе данных могут привести к тому, что план выполнения станет неэффективным или неправильным в зависимости от нового состояния базы данных. Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server SQL Server обнаруживает изменения, которые делают план выполнения недействительным, и помечает такой план как недействительный. detects the changes that invalidate an execution plan and marks the plan as not valid. При следующем выполнении данного запроса план должен быть перекомпилирован. A new plan must then be recompiled for the next connection that executes the query. План может стать недействительным в следующих случаях. The conditions that invalidate a plan include the following:

    • Изменены таблица или представления, на которые ссылается запрос ( ALTER TABLE или ALTER VIEW ). Changes made to a table or view referenced by the query ( ALTER TABLE and ALTER VIEW ).
    • Изменена одна процедура, которая удалит все планы для этой процедуры из кэша ( ALTER PROCEDURE ). Changes made to a single procedure, which would drop all plans for that procedure from the cache ( ALTER PROCEDURE ).
    • Изменены индексы, используемые планом выполнения. Changes to any indexes used by the execution plan.
    • Обновлена статистика, которая используется планом выполнения и сформирована либо явным образом по UPDATE STATISTICS , либо автоматически. Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS , or generated automatically.
    • Удалены индексы, используемые планом выполнения. Dropping an index used by the execution plan.
    • Явный вызов sp_recompile . An explicit call to sp_recompile .
    • Частое изменение ключей (инструкциями INSERT или DELETE от пользователей, изменяющих таблицу, на которую ссылается запрос). Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
    • Для таблиц с триггерами: значительный рост числа строк в таблицах inserted и deleted. For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
    • Выполнение хранимой процедуры с помощью параметра WITH RECOMPILE . Executing a stored procedure using the WITH RECOMPILE option.

    Большинство перекомпиляций необходимы либо для обеспечения правильности работы инструкции, либо для потенциального ускорения работы плана выполнения. Most recompilations are required either for statement correctness or to obtain potentially faster query execution plans.

    В SQL Server SQL Server 2000 каждый раз, когда одна из инструкций пакета вызывает перекомпиляцию, компилируется весь пакет, независимо от того, был ли он отправлен через хранимую процедуру, триггер, специализированный пакет или подготовленную инструкцию. In SQL Server SQL Server 2000, whenever a statement within a batch causes recompilation, the whole batch, whether submitted through a stored procedure, trigger, ad-hoc batch, or prepared statement, is recompiled. В SQL Server 2005 (9.x) SQL Server 2005 (9.x) и более поздних версиях перекомпилируется только та инструкция пакета, которая вызвала перекомпиляцию. Starting with SQL Server 2005 (9.x) SQL Server 2005 (9.x) , only the statement inside the batch that causes recompilation is recompiled. Из-за этого различия счетчики перекомпиляции в SQL Server SQL Server 2000 и более поздних версиях не совместимы между собой. Because of this difference, recompilation counts in SQL Server SQL Server 2000 and later releases are not comparable. Кроме этого, в SQL Server 2005 (9.x) SQL Server 2005 (9.x) и более поздних версиях больше типов перекомпиляции, что вызвано расширением набора возможностей. Also, there are more types of recompilations in SQL Server 2005 (9.x) SQL Server 2005 (9.x) and later because of its expanded feature set.

    Перекомпиляция на уровне инструкции дает выигрыш в производительности, поскольку в большинстве случаев перекомпиляция небольшого числа инструкций и связанных с этим потерь занимает меньше ресурсов в плане использования времени ЦП и затрат на блокировки. Statement-level recompilation benefits performance because, in most cases, a small number of statements causes recompilations and their associated penalties, in terms of CPU time and locks. Этих потерь, таким образом, удается избежать для остальных инструкций пакета, которые в перекомпиляции не нуждаются. These penalties are therefore avoided for the other statements in the batch that do not have to be recompiled.

    Расширенное событие sql_statement_recompile (xEvent) выводит сведения о перекомпиляции на уровне инструкций. The sql_statement_recompile extended event (xEvent) reports statement-level recompilations. Это событие xEvent возникает при перекомпиляции инструкций уровня для любого типа пакета. This xEvent occurs when a statement-level recompilation is required by any kind of batch. К таким пакетам относятся хранимые процедуры, триггеры, нерегламентированные пакеты и запросы. This includes stored procedures, triggers, ad hoc batches and queries. Пакеты можно отправлять с помощью различных интерфейсов, включая sp_executesql, динамический язык SQL, методы Prepare и Execute. Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods. Столбец recompile_cause для событий xEvent sql_statement_recompile содержит код причины перекомпиляции в виде целого числа. The recompile_cause column of sql_statement_recompile xEvent contains an integer code that indicates the reason for the recompilation. В следующей таблице приведены возможные причины. The following table contains the possible reasons:

    Изменение схемы Schema changed Изменение статистики Statistics changed
    Отложенная компиляция Deferred compile Изменение параметра SET SET option changed
    Изменение временной таблицы Temporary table changed Изменение удаленного набора строк Remote rowset changed
    Изменение разрешения FOR BROWSE FOR BROWSE permission changed Изменение среды уведомлений о запросах Query notification environment changed
    Изменение секционированного представления Partitioned view changed Изменение параметров курсора Cursor options changed
    OPTION (RECOMPILE) запрошено OPTION (RECOMPILE) requested Очистка параметризованного плана Parameterized plan flushed
    Изменение версии базы данных, влияющее на план Plan affecting database version changed Изменение политики форсирования плана для хранилища запросов Query Store plan forcing policy changed
    Сбой форсирования плана для хранилища запросов Query Store plan forcing failed Отсутствие плана для хранилища запросов Query Store missing the plan

    В выпусках SQL Server SQL Server , в которых xEvents недоступны, для тех же целей (отслеживание перекомпиляции уровня инструкций) можно использовать событие трассировки SQL Server SQL Server Profiler SP:Recompile. In SQL Server SQL Server versions where xEvents are not available, then the SQL Server SQL Server Profiler SP:Recompile trace event can be used for the same purpose of reporting statement-level recompilations. Событие трассировки SQL: StmtRecompile также сообщает о перекомпиляции уровня инструкций, и это событие трассировки также может использоваться для трассировки и отладки перекомпиляции. The trace event SQL:StmtRecompile also reports statement-level recompilations, and this trace event can also be used to track and debug recompilations. Событие SP:Recompile создается только для хранимых процедур и триггеров, а SQL:StmtRecompile — для хранимых процедур, триггеров, нерегламентированных пакетов, пакетов, которые выполняются с помощью sp_executesql , подготовленных запросов и динамического SQL. Whereas SP:Recompile generates only for stored procedures and triggers, SQL:StmtRecompile generates for stored procedures, triggers, ad-hoc batches, batches that are executed by using sp_executesql , prepared queries, and dynamic SQL. Столбец EventSubClass для событий SP:Recompile и SQL:StmtRecompile содержит код в виде целого числа, обозначающий причину перекомпиляции. The EventSubClass column of SP:Recompile and SQL:StmtRecompile contains an integer code that indicates the reason for the recompilation. Коды описаны здесь. The codes are described here.

    Если для параметра базы данных AUTO_UPDATE_STATISTICS установлено значение ON , то запросы перекомпилируются при условии, что они указывают на целевые таблицы или индексированные представления, для которых со времени последнего выполнения была изменена статистика или в значительной степени была изменена кратность. When the AUTO_UPDATE_STATISTICS database option is set to ON , queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution. Это относится к стандартным пользовательским таблицам, временным таблицам, а также таблицам inserted и deleted, созданным триггерами DML. This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. Если на производительность запроса оказывают влияние излишние перекомпиляции, измените значение этого параметра на OFF . If query performance is affected by excessive recompilations, consider changing this setting to OFF . Если для параметра базы данных AUTO_UPDATE_STATISTICS установлено значение OFF , перекомпиляция по причине изменения статистики или кратности не выполняется, за исключением вставляемых и удаляемых таблиц, созданных триггерами DML INSTEAD OF . When the AUTO_UPDATE_STATISTICS database option is set to OFF , no recompilations occur based on statistics or cardinality changes, with the exception of the inserted and deleted tables that are created by DML INSTEAD OF triggers. Так как данные таблицы создаются в базе данных tempdb, перекомпиляция запросов, которые обращаются к этим таблицам, зависит от значения параметра AUTO_UPDATE_STATISTICS в базе данных tempdb. Because these tables are created in tempdb, the recompilation of queries that access them depends on the setting of AUTO_UPDATE_STATISTICS in tempdb. Обратите внимание, что в SQL Server SQL Server 2000 запросы по-прежнему будут перекомпилироваться при изменении кратности в таблицах, вставляемых и удаляемых триггерами DML, даже если этот параметр имеет значение OFF . Note that in SQL Server SQL Server 2000, queries continue to recompile based on cardinality changes to the DML trigger inserted and deleted tables, even when this setting is OFF .

    Параметры и повторное использование планов выполнения Parameters and Execution Plan Reuse

    Использование параметров, включая маркеры параметров в приложениях ADO, OLE DB и ODBC, может повысить уровень использования планов выполнения. The use of parameters, including parameter markers in ADO, OLE DB, and ODBC applications, can increase the reuse of execution plans.

    Использование параметров и маркеров параметров для хранения введенных конечными пользователями значений безопаснее, чем сцепление значений в строку, которая затем выполняется с помощью метода API доступа к данным, инструкции EXECUTE или хранимой процедуры sp_executesql . Using parameters or parameter markers to hold values that are typed by end users is more secure than concatenating the values into a string that is then executed by using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

    Единственная разница между следующими двумя инструкциями SELECT — в значениях, сравниваемых в предложении WHERE : The only difference between the following two SELECT statements is the values that are compared in the WHERE clause:

    Единственная разница между планами выполнения для этих запросов — в значении, хранимом для сравнения со столбцом ProductSubcategoryID . The only difference between the execution plans for these queries is the value stored for the comparison against the ProductSubcategoryID column. В то время как выявление факта формирования инструкциями одного и того же плана и повторного его использования является основной задачей SQL Server SQL Server , SQL Server SQL Server не всегда может это обнаружить в сложных инструкциях Transact-SQL Transact-SQL . While the goal is for SQL Server SQL Server to always recognize that the statements generate essentially the same plan and reuse the plans, SQL Server SQL Server sometimes does not detect this in complex Transact-SQL Transact-SQL statements.

    Отделение констант от инструкции Transact-SQL Transact-SQL с помощью параметров помогает реляционному механизму распознавать дубликаты планов. Separating constants from the Transact-SQL Transact-SQL statement by using parameters helps the relational engine recognize duplicate plans. Параметры можно использовать следующими способами. You can use parameters in the following ways:

    В Transact-SQL Transact-SQL используется атрибут sp_executesql : In Transact-SQL Transact-SQL , use sp_executesql :

    Этот метод рекомендуется использовать для скриптов языка Transact-SQL Transact-SQL , хранимых процедур и триггеров, динамически формирующих инструкции SQL. This method is recommended for Transact-SQL Transact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

    В технологиях ADO, OLE DB и ODBC используются маркеры параметров. ADO, OLE DB, and ODBC use parameter markers. Маркеры параметров представляют собой знаки вопроса (?), заменяющие константу в инструкции SQL и привязываемые к программной переменной. Parameter markers are question marks (?) that replace a constant in an SQL statement and are bound to a program variable. Например, в приложении ODBC можно сделать следующее: For example, you would do the following in an ODBC application:

    • использовать параметр SQLBindParameter для привязки целочисленной переменной к первому маркеру параметра в инструкции SQL; Use SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.
    • поместить целочисленное значение в переменную; Put the integer value in the variable.
    • выполнить инструкцию, указав маркер параметра (?): Execute the statement, specifying the parameter marker (?):

    Если в приложениях используются маркеры параметров, то поставщик OLE DB для собственного клиента SQL Server SQL Server и драйвер ODBC для собственного клиента SQL Server SQL Server , включенные в состав SQL Server SQL Server , используют для отправки инструкций в SQL Server SQL Server процедуру sp_executesql . The SQL Server SQL Server Native Client OLE DB Prov >SQL Server SQL Server Native Client ODBC driver included with SQL Server SQL Server use sp_executesql to send statements to SQL Server SQL Server when parameter markers are used in applications.

    Чтобы проектировать хранимые процедуры, использующие указанные разработчиком параметры. To design stored procedures, which use parameters by design.

    Если структура приложения не предусматривает явного создания параметров, можно воспользоваться оптимизатором запросов SQL Server SQL Server для автоматической параметризации некоторых запросов с использованием установленного по умолчанию поведения простой параметризации. If you do not explicitly build parameters into the design of your applications, you can also rely on the SQL Server SQL Server Query Optimizer to automatically parameterize certain queries by using the default behavior of simple parameterization. В качестве альтернативы можно настроить принудительный учет параметризации всех запросов к базе данных в оптимизаторе запросов, установив для параметра PARAMETERIZATION инструкции ALTER DATABASE значение FORCED . Alternatively, you can force the Query Optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCED .

    При включенной принудительной параметризации может также иметь место и простая параметризация. When forced parameterization is enabled, simple parameterization can still occur. Например, в соответствии с правилами принудительной параметризации следующий запрос не может быть параметризован. For example, the following query cannot be parameterized according to the rules of forced parameterization:

    Однако он может быть параметризован согласно правилам простой параметризации. However, it can be parameterized according to simple parameterization rules. В случае неуспешной попытки принудительной параметризации впоследствии производятся попытки использования простой параметризации. When forced parameterization is tried but fails, simple parameterization is still subsequently tried.

    Простая параметризация Simple Parameterization

    В SQL Server SQL Server использование параметров или маркеров параметров в инструкциях Transact-SQL позволяет реляционному ядру более эффективно применять существующие скомпилированные планы выполнения для новых инструкций Transact-SQL Transact-SQL . In SQL Server SQL Server , using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new Transact-SQL Transact-SQL statements with existing, previously-compiled execution plans.

    Использование параметров и маркеров параметров для хранения введенных конечными пользователями значений безопаснее, чем сцепление значений в строку, которая затем выполняется с помощью метода API доступа к данным, инструкции EXECUTE или хранимой процедуры sp_executesql . Using parameters or parameter markers to hold values typed by end users is more secure than concatenating the values into a string that is then executed using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

    Если инструкция Transact-SQL Transact-SQL выполняется без параметров, SQL Server SQL Server неявно параметризует инструкцию, чтобы увеличить возможность ее противопоставления существующему плану выполнения. If a Transact-SQL Transact-SQL statement is executed without parameters, SQL Server SQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. Данный процесс называется простой параметризацией. This process is called simple parameterization. В SQL Server SQL Server 2000 этот процесс назывался автоматической параметризацией. In SQL Server SQL Server 2000, the process was referred to as auto-parameterization.

    Рассмотрим следующую инструкцию. Consider this statement:

    Значение 1 в конце инструкции может быть указано в виде параметра. The value 1 at the end of the statement can be specified as a parameter. Реляционный механизм строит план выполнения для данного пакета, как если бы параметр был указан на месте значения 1. The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. При помощи этой простой параметризации SQL Server SQL Server распознает, что следующие две инструкции формируют, по сути, одинаковый план выполнения, и повторно использует первый план для второй инструкции: Because of this simple parameterization, SQL Server SQL Server recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:

    В процессе обработки сложных инструкций Transact-SQL Transact-SQL реляционный механизм может с трудом определять, какие выражения могут быть параметризованы. When processing complex Transact-SQL Transact-SQL statements, the relational engine may have difficulty determining which expressions can be parameterized. Чтобы увеличить возможность реляционного модуля находить соответствующие существующие неиспользованные планы выполнения для сложных инструкций Transact-SQL Transact-SQL , необходимо явно указать параметры с помощью процедуры sp_executesql или маркеров параметров. To increase the ability of the relational engine to match complex Transact-SQL Transact-SQL statements to existing, unused execution plans, explicitly specify the parameters using either sp_executesql or parameter markers.

    При использовании арифметических операторов (+, -, *, / или %) для явного или неявного преобразования постоянных значений типов данных int, smallint, tinyint или bigint в типы данных float, real, decimal или numeric SQL Server SQL Server применяет специальные правила для определения типа и точности результатов выражения. When the +, -, *, /, or % arithmetic operators are used to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, SQL Server SQL Server applies specific rules to calculate the type and precision of the expression results. Однако эти правила различаются в зависимости от того, параметризован запрос или нет. However, these rules differ, depending on whether the query is parameterized or not. Таким образом, одинаковые выражения в запросах могут в некоторых случаях давать отличающиеся результаты. Therefore, similar expressions in queries can, in some cases, produce differing results.

    При проведении простой параметризации SQL Server SQL Server по умолчанию параметризует сравнительно небольшой класс запросов. Under the default behavior of simple parameterization, SQL Server SQL Server parameterizes a relatively small class of queries. Однако можно указать, чтобы все запросы в базе данных были параметризованы в соответствии с определенными ограничениями, настроив параметр PARAMETERIZATION команды ALTER DATABASE на FORCED . However, you can specify that all queries in a database be parameterized, subject to certain limitations, by setting the PARAMETERIZATION option of the ALTER DATABASE command to FORCED . Уменьшая частоту компиляции запросов, эти действия улучшат производительность баз данных, которые испытывают большие объемы параллельных запросов. Doing so may improve the performance of databases that experience high volumes of concurrent queries by reducing the frequency of query compilations.

    Иначе можно указать параметризацию одного запроса и других, синтаксически равных, но отличающихся значениями параметра, запросов. Alternatively, you can specify that a single query, and any others that are syntactically equivalent but differ only in their parameter values, be parameterized.

    Принудительная параметризация Forced Parameterization

    Можно переопределить простую параметризацию, используемую в SQL Server SQL Server по умолчанию, указав, что все инструкции SELECT , INSERT , UPDATE и DELETE в базе данных должны быть параметризованы (с учетом некоторых ограничений). You can overr >SQL Server SQL Server by specifying that all SELECT , INSERT , UPDATE , and DELETE statements in a database be parameterized, subject to certain limitations. Принудительная параметризация активируется путем установки для параметра PARAMETERIZATION значения FORCED в инструкции ALTER DATABASE . Forced parameterization is enabled by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement. Принудительная параметризация может улучшить производительность некоторых баз данных, сократив частоту выполнения компиляции и перекомпиляции запросов. Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. Базы данных, которым может пойти на пользу принудительная параметризация, — это, как правило, те, которым приходится выполнять большое количество параллельных запросов из источников наподобие приложений торговых точек. Databases that may benefit from forced parameterization are generally those that experience high volumes of concurrent queries from sources such as point-of-sale applications.

    Если параметру PARAMETERIZATION присвоено значение FORCED , любое литеральное значение, представленное в инструкции SELECT , INSERT , UPDATE или DELETE , заявленной в любой форме, преобразуется в аргумент в процессе компиляции запроса. When the PARAMETERIZATION option is set to FORCED , any literal value that appears in a SELECT , INSERT , UPDATE , or DELETE statement, submitted in any form, is converted to a parameter during query compilation. Исключениями являются литералы, представленные в следующих конструкциях запроса. The exceptions are literals that appear in the following query constructs:

    • Инструкции INSERT. EXECUTE . INSERT. EXECUTE statements.
    • Инструкции в теле хранимых процедур, триггеров или определяемых пользователем функций. Statements inside the bodies of stored procedures, triggers, or user-defined functions. В SQL Server SQL Server планы запросов для этих подпрограмм уже используются повторно. SQL Server SQL Server already reuses query plans for these routines.
    • Подготовленные инструкции, которые уже были параметризованы приложением на стороне клиента. Prepared statements that have already been parameterized on the client-side application.
    • Инструкции, содержащие вызовы метода XQuery, где метод представлен в контексте, в котором его аргументы обычно параметризуются, например в предложении WHERE . Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. Если метод представлен в контексте, где его аргументы не параметризуются, остальная часть инструкции будет параметризована. If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
    • Инструкции внутри курсора Transact-SQL Transact-SQL . Statements ins >Transact-SQL Transact-SQL cursor. (Инструкции SELECT внутри курсоров API-интерфейса параметризуются.) ( SELECT statements inside API cursors are parameterized.)
    • Устаревшие конструкции запроса. Deprecated query constructs.
    • Любая инструкция, выполняемая в контексте ANSI_PADDING или ANSI_NULLS со значением OFF . Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF .
    • Инструкции, содержащие более 2 097 литералов, пригодных для параметризации. Statements that contain more than 2,097 literals that are eligible for parameterization.
    • Инструкции, ссылающиеся на переменные, такие как WHERE T.col2 >= @bb . Statements that reference variables, such as WHERE T.col2 >= @bb .
    • Инструкции, содержащие указание запроса RECOMPILE . Statements that contain the RECOMPILE query hint.
    • Инструкции, содержащие предложение COMPUTE . Statements that contain a COMPUTE clause.
    • Инструкции, содержащие предложение WHERE CURRENT OF . Statements that contain a WHERE CURRENT OF clause.

    Кроме того, в запросах не параметризуются следующие предложения Additionally, the following query clauses are not parameterized. (следует иметь в виду, что не параметризуются только предложения; Note that in these cases, only the clauses are not parameterized. другие предложения внутри того же запроса могут оказаться пригодными для принудительной параметризации). Other clauses within the same query may be eligible for forced parameterization.

    • Список любой инструкции SELECT . The of any SELECT statement. Сюда входят списки SELECT во вложенных запросах и списки SELECT внутри инструкций INSERT . This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.
    • Инструкции SELECT во вложенных запросах, представленные внутри инструкции IF . Subquery SELECT statements that appear inside an IF statement.
    • Предложения запроса TOP , TABLESAMPLE , HAVING , GROUP BY , ORDER BY , OUTPUT. INTO или FOR XM L. The TOP , TABLESAMPLE , HAVING , GROUP BY , ORDER BY , OUTPUT. INTO , or FOR XM L clauses of a query.
    • Аргументы, прямые или в качестве подвыражений, для OPENROWSET , OPENQUERY , OPENDATASOURCE , OPENXML или для любого оператора FULLTEXT . Arguments, either direct or as subexpressions, to OPENROWSET , OPENQUERY , OPENDATASOURCE , OPENXML , or any FULLTEXT operator.
    • Аргументы pattern и escape_character предложения LIKE . The pattern and escape_character arguments of a LIKE clause.
    • Аргумент style предложения CONVERT . The style argument of a CONVERT clause.
    • Целочисленные константы внутри предложения IDENTITY . Integer constants inside an IDENTITY clause.
    • Константы, указанные использованием синтаксиса расширения ODBC. Constants specified by using ODBC extension syntax.
    • Свертываемые константные выражения, являющиеся аргументами операторов +, -, *, / и %. Constant-foldable expressions that are arguments of the +, -, *, /, and % operators. При определении пригодности для принудительной параметризации SQL Server SQL Server рассматривает выражение как свертываемое, если верно хотя бы одно из следующих условий. When cons >SQL Server SQL Server considers an expression to be constant-foldable when either of the following conditions is true:
      • В выражении не представлены столбцы, переменные или вложенные запросы. No columns, variables, or subqueries appear in the expression.
      • Выражение содержит предложение CASE . The expression contains a CASE clause.
    • Аргументы для предложений указаний запросов. Arguments to query hint clauses. Сюда входит аргумент number_of_rows указания запроса FAST , аргумент number_of_processors указания запроса MAXDOP и числовой аргумент указания запроса MAXRECURSION . These include the number_of_rows argument of the FAST query hint, the number_of_processors argument of the MAXDOP query hint, and the number argument of the MAXRECURSION query hint.

    Параметризация происходит на уровне отдельных инструкций Transact-SQL Transact-SQL . Parameterization occurs at the level of indiv >Transact-SQL Transact-SQL statements. Иными словами, параметризуются отдельные инструкции в пакете. In other words, individual statements in a batch are parameterized. После компиляции параметризированный запрос выполняется в контексте пакета, в котором он был изначально заявлен. After compiling, a parameterized query is executed in the context of the batch in which it was originally submitted. Если план выполнения для запроса кэширован, можно определить, был ли параметризован запрос, обратившись к столбцу sql в динамическом административном представлении sys.syscacheobjects. If an execution plan for a query is cached, you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects dynamic management view. Если запрос параметризован, имена и типы данных аргументов располагаются перед текстом заявленного пакета в этом столбце, например (@1 tinyint). If a query is parameterized, the names and data types of parameters come before the text of the submitted batch in this column, such as (@1 tinyint).

    Имена аргументов произвольны. Parameter names are arbitrary. Пользователи или приложения не должны опираться на какой-либо конкретный порядок именования. Users or applications should not rely on a particular naming order. Кроме того, в зависимости от версии SQL Server SQL Server и пакетов обновления могут меняться имена параметров, выбор литералов, подлежащих параметризации, и разбивка параметризованного текста. Also, the following can change between versions of SQL Server SQL Server and Service Pack upgrades: Parameter names, the choice of literals that are parameterized, and the spacing in the parameterized text.

    Типы данных аргументов Data Types of Parameters

    Когда SQL Server SQL Server параметризует литералы, аргументы преобразовываются в следующие типы данных. When SQL Server SQL Server parameterizes literals, the parameters are converted to the following data types:

    • Целочисленные литералы, размер которых в ином случае соответствовал бы типу данных int, параметризуются в int. Большие целочисленные литералы, являющиеся частью предикатов, которые включают в себя любой оператор сравнения (в том числе , >=, ! , <>, ALL , ANY , SOME , BETWEEN и IN ), параметризуются в numeric(38,0). Integer literals whose size would otherwise fit within the int data type parameterize to int. Larger integer literals that are parts of predicates that involve any comparison operator (includes , >=, , ! , <>, ALL , ANY , SOME , BETWEEN , and IN ) parameterize to numeric(38,0). Большие литералы, не являющиеся частью предикатов, которые включают в себя операторы сравнения, параметризуются в числовой тип с точностью достаточно большой, чтобы поддержать их размер, и с масштабом 0. Larger literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision is just large enough to support its size and whose scale is 0.
    • Числовые литералы с фиксированной запятой, являющиеся частью предикатов, которые включают в себя операторы сравнения, параметризуются в числовой тип с точностью 38 и масштабом достаточно большим, чтобы поддержать их размер. Fixed-point numeric literals that are parts of predicates that involve comparison operators parameterize to numeric whose precision is 38 and whose scale is just large enough to support its size. Числовые литералы с фиксированной запятой, не являющиеся частью предикатов, которые включают в себя операторы сравнения, параметризуются в числовой тип с точностью и масштабом достаточно большими, чтобы поддержать их размер. Fixed-point numeric literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision and scale are just large enough to support its size.
    • Числовые литералы с плавающей запятой параметризуются в float(53). Floating point numeric literals parameterize to float(53).
    • Строковые литералы не в формате Юникод параметризуются в varchar(8000), если размер литерала не превышает 8000 символов, и в varchar(max), если он больше 8000 символов. Non-Unicode string literals parameterize to varchar(8000) if the literal fits within 8,000 characters, and to varchar(max) if it is larger than 8,000 characters.
    • Строковые литералы в формате Юникод параметризуются в nvarchar(4000), если размер литерала не превышает 4000 символов Юникода, и в nvarchar(max), если он больше 4000 символов. Unicode string literals parameterize to nvarchar(4000) if the literal fits within 4,000 Unicode characters, and to nvarchar(max) if the literal is larger than 4,000 characters.
    • Двоичные литералы параметризуются в varbinary(8000), если размер литерала не превышает 8000 байт. Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. Если он больше 8000 байт, он преобразуется в varbinary(max). If it is larger than 8,000 bytes, it is converted to varbinary(max).
    • Денежные литералы параметризуются в тип money. Money type literals parameterize to money.

    Рекомендации по использованию принудительной параметризации Guidelines for Using Forced Parameterization

    Устанавливая для параметра PARAMETERIZATION значение FORCED, примите во внимание следующие сведения. Consider the following when you set the PARAMETERIZATION option to FORCED:

    • Принудительная параметризация, в сущности, преобразует литеральные константы в запросе в параметры при компиляции запроса. Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. Следовательно, оптимизатор запросов может выбирать не самые оптимальные планы для запросов. Therefore, the Query Optimizer might choose suboptimal plans for queries. В частности, уменьшается вероятность того, что оптимизатор запросов сопоставит запрос с индексированным представлением или индексом по вычисляемому столбцу. In particular, the Query Optimizer is less likely to match the query to an indexed view or an index on a computed column. Он может также выбирать не самые оптимальные планы для запросов, ориентированных на секционированные таблицы или распределенные секционированные представления. It may also choose suboptimal plans for queries posed on partitioned tables and distributed partitioned views. Принудительная параметризация не должна использоваться в средах, в значительной степени опирающихся на индексированные представления и индексы по вычисляемым столбцам. Forced parameterization should not be used for environments that rely heavily on indexed views and indexes on computed columns. Параметр PARAMETERIZATION FORCED должны использовать только опытные администраторы баз данных и лишь после того, как они определят, что такое использование не повредит производительности. Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance.
    • Распределенные запросы, ссылающиеся на более чем одну базу данных, пригодны для принудительной параметризации, если для параметра PARAMETERIZATION задано значение FORCED в базе данных, в контексте которой выполняется запрос. Distributed queries that reference more than one database are eligible for forced parameterization as long as the PARAMETERIZATION option is set to FORCED in the database whose context the query is running.
    • Установка для параметра PARAMETERIZATION на значения FORCED производит очистку всех планов запросов из кэша планов в базе данных за исключением тех, которые компилируются, перекомпилируются или выполняются в настоящий момент. Setting the PARAMETERIZATION option to FORCED flushes all query plans from the plan cache of a database, except those that currently are compiling, recompiling, or running. Планы для запросов, которые компилируются или выполняются в момент изменения настроек, параметризуются при следующем выполнении запроса. Plans for queries that are compiling or running during the setting change are parameterized the next time the query is executed.
    • Настройка параметра PARAMETERIZATION выполняется в режиме в сети и не требует монопольных блокировок на уровне базы данных. Setting the PARAMETERIZATION option is an online operation that it requires no database-level exclusive locks.
    • Текущая настройка параметра PARAMETERIZATION сохраняется при повторном присоединении или восстановлении базы данных. The current setting of the PARAMETERIZATION option is preserved when reattaching or restoring a database.

    Можно перекрывать поведение принудительной параметризации, предписав выполнение попытки простой параметризации для отдельного запроса, а также всех остальных запросов с таким же синтаксисом, отличающихся только значениями аргументов. You can override the behavior of forced parameterization by specifying that simple parameterization be attempted on a single query, and any others that are syntactically equivalent but differ only in their parameter values. Справедливо и обратное: можно потребовать выполнения попытки принудительной параметризации для отдельного набора синтаксически эквивалентных запросов, даже если принудительная параметризация в базе данных отключена. Conversely, you can specify that forced parameterization be attempted on only a set of syntactically equivalent queries, even if forced parameterization is disabled in the database. В этих целях используютсяструктуры планов . Plan guides are used for this purpose.

    Если для параметра PARAMETERIZATION задано значение FORCED , то отчеты об ошибках могут отличаться от отчетов в случае, когда для параметра PARAMETERIZATION задано значение SIMPLE : при принудительной параметризации число сообщений об ошибках в некоторых случаях больше, чем при простой параметризации, а номера строк, в которых возникают ошибки, могут указываться неверно. When the PARAMETERIZATION option is set to FORCED , the reporting of error messages may differ from when the PARAMETERIZATION option is set to SIMPLE : multiple error messages may be reported under forced parameterization, where fewer messages would be reported under simple parameterization, and the line numbers in which errors occur may be reported incorrectly.


    Подготовка инструкций SQL Preparing SQL Statements

    В реляционном механизме SQL Server SQL Server введена полная поддержка подготовки инструкций Transact-SQL Transact-SQL перед их выполнением. The SQL Server SQL Server relational engine introduces full support for preparing Transact-SQL Transact-SQL statements before they are executed. Если приложению требуется выполнить инструкцию Transact-SQL Transact-SQL несколько раз, то оно может использовать API базы данных следующим образом. If an application has to execute an Transact-SQL Transact-SQL statement several times, it can use the database API to do the following:

    • Однократная подготовка инструкции. Prepare the statement once. Инструкция Transact-SQL Transact-SQL компилируется в план выполнения. This compiles the Transact-SQL Transact-SQL statement into an execution plan.
    • Ранее скомпилированный план выполнения выполняется каждый раз при необходимости использовать эту инструкцию. Execute the precompiled execution plan every time it has to execute the statement. Это избавляет от необходимости повторно компилировать инструкцию Transact-SQL Transact-SQL при каждом последующем выполнении. This prevents having to recompile the Transact-SQL Transact-SQL statement on each execution after the first time.
      Подготовка и выполнение инструкций контролируется функциями и методами API. Preparing and executing statements is controlled by API functions and methods. Они не имеют отношения к языку Transact-SQL Transact-SQL . It is not part of the Transact-SQL Transact-SQL language. Модель подготовки и выполнения инструкций Transact-SQL Transact-SQL поддерживается поставщиком OLE DB для собственного клиента SQL Server SQL Server , а также драйвером ODBC для собственного клиента SQL Server SQL Server . The prepare/execute model of executing Transact-SQL Transact-SQL statements is supported by the SQL Server SQL Server Native Client OLE DB Prov >SQL Server SQL Server Native Client ODBC driver. При запросе на подготовку поставщик или драйвер отправляет в SQL Server SQL Server инструкцию с запросом на подготовку инструкции. On a prepare request, either the prov >SQL Server SQL Server with a request to prepare the statement. SQL Server SQL Server компилирует план выполнения и возвращает его дескриптор поставщику или драйверу. compiles an execution plan and returns a handle for that plan to the provider or driver. При запросе на выполнение поставщик или драйвер отправляет на сервер запрос на выполнение плана, связанного с этим дескриптором. On an execute request, either the provider or the driver sends the server a request to execute the plan that is associated with the handle.

    В SQL Server SQL Server подготовленные инструкции нельзя применять для создания временных объектов. Prepared statements cannot be used to create temporary objects on SQL Server SQL Server . Подготовленные инструкции не могут содержать ссылки на системные хранимые процедуры, создающие временные объекты, такие как временные таблицы. Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. Эти процедуры следует выполнять напрямую. These procedures must be executed directly.

    Злоупотребление моделью подготовки и выполнения может отрицательно сказаться на производительности. Excess use of the prepare/execute model can degrade performance. Если инструкция выполняется только один раз, то для прямого выполнения потребуется только один цикл приема-передачи с сервером. If a statement is executed only once, a direct execution requires only one network round-trip to the server. Для подготовки и выполнения инструкции Transact-SQL Transact-SQL , которая выполняется только один раз, потребуется два таких цикла: один для подготовки и один для выполнения. Preparing and executing an Transact-SQL Transact-SQL statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it.

    Подготовка инструкции более эффективна, если используются маркеры параметров. Preparing a statement is more effective if parameter markers are used. Предположим, что приложение случайно запросило сведения о продукте из образца базы данных AdventureWorks . For example, assume that an application is occasionally asked to retrieve product information from the AdventureWorks sample database. Эти сведения можно извлечь двумя способами. There are two ways the application can do this.

    Приложение может выполнять отдельный запрос по каждому необходимому продукту: Using the first way, the application can execute a separate query for each product requested:

    Второй способ заключается в следующем. Using the second way, the application does the following:

    1. Приложение подготавливает инструкцию, содержащую маркер параметра (?): Prepares a statement that contains a parameter marker (?):
    2. Затем оно связывает переменную программы с этим маркером. Binds a program variable to the parameter marker.
    3. Каждый раз, когда требуются сведения о продукте, приложение присваивает связанной переменной ключевое значение и выполняет инструкцию. Each time product information is needed, fills the bound variable with the key value and executes the statement.

    Второй способ более эффективен, если инструкция выполняется более трех раз. The second way is more efficient when the statement is executed more than three times.

    В SQL Server SQL Server модель подготовки и выполнения не дает существенного прироста производительности по сравнению с непосредственным выполнением из-за того, как SQL Server SQL Server повторно использует планы выполнения. In SQL Server SQL Server , the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL Server SQL Server reuses execution plans. В SQL Server SQL Server предусмотрены эффективные алгоритмы для сопоставления текущих инструкций Transact-SQL Transact-SQL и планов выполнения, созданных для предыдущих случаев выполнения той же инструкции Transact-SQL Transact-SQL . SQL Server SQL Server has efficient algorithms for matching current Transact-SQL Transact-SQL statements with execution plans that are generated for prior executions of the same Transact-SQL Transact-SQL statement. Если приложение несколько раз выполняет инструкцию Transact-SQL Transact-SQL с маркерами параметров, то со второго выполнения SQL Server SQL Server будет использовать готовый план выполнения (если этот план не будет удален из кэша планов). If an application executes a Transact-SQL Transact-SQL statement with parameter markers multiple times, SQL Server SQL Server will reuse the execution plan from the first execution for the second and subsequent executions (unless the plan ages from the plan cache). Впрочем, у модели подготовки и выполнения есть следующие достоинства: The prepare/execute model still has these benefits:

    • поиск плана производится путем идентификации дескриптора, что эффективнее алгоритмов, которые применяются для сопоставления инструкции Transact-SQL Transact-SQL и существующих планов выполнения; Finding an execution plan by an >Transact-SQL Transact-SQL statement to existing execution plans.
    • приложение может управлять временем создания и повторного использования плана выполнения; The application can control when the execution plan is created and when it is reused.
    • Модель подготовки и выполнения можно переносить в другие базы данных, включая более ранние версии SQL Server SQL Server . The prepare/execute model is portable to other databases, including earlier versions of SQL Server SQL Server .

    Сканирование параметров Parameter Sniffing

    Сканирование параметров — это процесс, посредством которого SQL Server SQL Server «сканирует» текущие значения параметров во время компиляции или перекомпиляции и передает их оптимизатору запросов для создания более эффективных планов запросов. «Parameter sniffing» refers to a process whereby SQL Server SQL Server «sniffs» the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.

    Значения параметров сканируются во время компиляции или перекомпиляции для следующих типов пакетов: Parameter values are sniffed during compilation or recompilation for the following types of batches:

    • Хранимые процедуры Stored procedures
    • Запросы, отправленные через sp_executesql Queries submitted via sp_executesql
    • Подготовленные запросы Prepared queries

    Дополнительные сведения об устранении неполадок с выявлением неверных параметров см. в разделе Устранение неполадок при выполнении запросов с проблемами в плане выполнения запроса, зависящими от параметров. For more information on troubleshooting bad parameter sniffing issues, see Troubleshoot queries with parameter-sensitive query execution plan issues.

    Для запросов, в которых используется указание RECOMPILE , сканируются как значения параметров, так и текущие значения локальных переменных. For queries using the RECOMPILE hint, both parameter values and current values of local variables are sniffed. Сканируемые значения (параметров и локальных переменных) — это значения, которые имеются в пакете, прямо перед выполнением указания RECOMPILE . The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the RECOMPILE hint. В частности для параметров значения, которые поставляются вместе с вызовом пакета, не сканируются. In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

    Параллельная обработка запросов Parallel Query Processing

    SQL Server SQL Server обеспечивает параллельную обработку запросов, оптимизирующую выполнение запросов и операции с индексами на компьютерах, где установлено несколько микропроцессоров (ЦП). provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Благодаря параллельной обработке запросов и параллельному выполнению операций с индексами с помощью нескольких рабочих потоков операционной системы SQL Server SQL Server выполняет эти операции быстрее и эффективнее. Because SQL Server SQL Server can perform a query or index operation in parallel by using several operating system worker threads, the operation can be completed quickly and efficiently.

    Во время оптимизации запроса SQL Server SQL Server пытается обнаружить запросы и операции с индексами, которые можно ускорить за счет параллельного выполнения. During query optimization, SQL Server SQL Server looks for queries or index operations that might benefit from parallel execution. Для таких запросов SQL Server SQL Server вставляет в план выполнения операторы обмена, чтобы подготовить запрос к параллельной обработке. For these queries, SQL Server SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. Операторы обмена служат для управления процессом, перераспределения данных и управления потоком. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. К ним относятся логические операторы Distribute Streams , Repartition Streams и Gather Streams (в качестве подтипов), один или несколько из которых появляются в выводе инструкции Showplan плана запроса для параллельного запроса. The exchange operator includes the Distribute Streams , Repartition Streams , and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.

    Определенные конструкции блокируют для SQL Server SQL Server возможность использования параллелизма для всего плана выполнения или его частей. Certain constructs inhibit SQL Server SQL Server ‘s ability to leverage parallelism on the entire execution plan, or parts or the execution plan.

    Конструкции, которые блокируют параллелизм, включают перечисленные ниже. Constructs that inhibit parallelism include:

    • Определяемые пользователем скалярные функцииScalar UDFs
      Дополнительные сведения об определяемых пользователем скалярных функциях см. в разделе Создание определяемых пользователем функций. For more information on scalar user-defined functions, see Create User-defined Functions. Начиная с SQL Server 2020 (15.x) SQL Server 2020 (15.x) Компонент SQL Server Database Engine SQL Server Database Engine включает возможность встраивать эти функции и разблокировать использование параллелизма во время обработки запросов. Starting with SQL Server 2020 (15.x) SQL Server 2020 (15.x) , the Компонент SQL Server Database Engine SQL Server Database Engine has the ability to inline these functions, and unlock use of parallelism during query processing. Дополнительные сведения о встраивании скалярных пользовательских функций см. в разделе Интеллектуальная обработка запросов в базах данных SQL. For more information on scalar UDF inlining, see Intelligent query processing in SQL databases.
    • Remote QueryRemote Query
      Дополнительные сведения о Remote Query см. в разделе Справочник по логическим и физическим операторам Showplan. For more information on Remote Query, see Showplan Logical and Physical Operators Reference.
    • Динамические курсорыDynamic cursors
      Дополнительные сведения о курсорах см. в описании DECLARE CURSOR. For more information on cursors, see DECLARE CURSOR.
    • Рекурсивные запросыRecursive queries
      Дополнительные сведения о рекурсии см. в разделах Рекомендации по созданию и использованию рекурсивных обобщенных табличных выражений и Рекурсия в T-SQL. For more information on recursion, see Guidelines for Defining and Using Recursive Common Table Expressions and Recursion in T-SQL.
    • Функции с табличным значением (TVF)Table Valued Functions (TVFs)
      Дополнительные сведения о функциях TVF см. в разделе Создание определяемых пользователем функций (ядро СУБД). For more information on TVFs, see Create User-defined Functions (Database Engine).
    • Ключевое слово TOPTOP keyword
      Дополнительные сведения см. в разделе TOP (Transact-SQL). For more information, see TOP (Transact-SQL).

    После вставки операторов обмена получается план параллельного выполнения запроса. After exchange operators are inserted, the result is a parallel-query execution plan. План параллельного выполнения запроса может использовать несколько рабочих потоков. A parallel-query execution plan can use more than one worker thread. План последовательного выполнения, который используется для обработки непараллельных запросов, использует только один рабочий поток. A serial execution plan, used by a nonparallel query, uses only one worker thread for its execution. Фактическое количество рабочих потоков для параллельного выполнения запроса определяется при инициализации плана выполнения запроса и зависит от сложности и степени параллелизма плана. The actual number of worker threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism. Степень параллелизма определяет максимальное количество используемых ЦП, а не количество используемых рабочих потоков. Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of worker threads that are being used. Степень параллелизма устанавливается на уровне сервера и изменяется системной хранимой процедурой sp_configure. The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. Это значение можно переопределить для отдельных инструкций запроса или индекса при помощи указания запроса MAXDOP или параметра индекса MAXDOP . You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

    Оптимизатор запросов SQL Server SQL Server не использует план параллельного выполнения для запроса, если выполняется любое из следующих условий. The SQL Server SQL Server Query Optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

    • Затраты на последовательное выполнение запроса не настолько высоки, чтобы альтернативой ему считался план параллельного выполнения. The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
    • План последовательного выполнения признан более быстрым, чем любой другой возможный план параллельного выполнения данного запроса. A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
    • Запрос содержит скалярные или реляционные операторы, параллельное выполнение которых невозможно. The query contains scalar or relational operators that cannot be run in parallel. Определенные операторы могут привести к выполнению участка запроса или всего плана целиком в последовательном режиме. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

    Степень параллелизма Degree of Parallelism

    SQL Server SQL Server автоматически обнаруживает высшую степень параллелизма для каждого экземпляра параллельного выполнения запроса или индекс операции языка DDL. automatically detects the best degree of parallelism for each instance of a parallel query execution or index data definition language (DDL) operation. Это осуществляется на основе следующих критериев. It does this based on the following criteria:

    Работает ли SQL Server SQL Server на компьютере, имеющем более одного микропроцессора или ЦП (таком как симметричный многопроцессорный компьютер (SMP)). Whether SQL Server SQL Server is running on a computer that has more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP).
    Использовать параллельные запросы могут только компьютеры, имеющие более одного ЦП. Only computers that have more than one CPU can use parallel queries.

    Достаточно ли доступных рабочих потоков. Whether sufficient worker threads are available.
    Каждый запрос или операция с индексами требуют определенного числа рабочих потоков. Each query or index operation requires a certain number of worker threads to execute. Для выполнения параллельного плана требуется больше рабочих потоков, чем для выполнения последовательного плана, и по мере увеличения степени параллелизма число необходимых рабочих потоков возрастает. Executing a parallel plan requires more worker threads than a serial plan, and the number of required worker threads increases with the degree of parallelism. Если требования к рабочим потокам для параллельного плана с определенной степенью параллелизма не могут быть удовлетворены, Компонент SQL Server Database Engine SQL Server Database Engine автоматически уменьшает степень параллелизма или полностью отказывается от параллельного плана в указанном контексте рабочей нагрузки. When the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the Компонент SQL Server Database Engine SQL Server Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. В этом случае он выполняет последовательный план (с одним рабочим потоком). It then executes the serial plan (one worker thread).

    Тип выполняемого запроса или операции с индексами. The type of query or index operation executed.
    Операции с индексами, которые создают или перестраивают индекс или удаляют кластеризованный индекс и запросы, интенсивно использующие циклы ЦП, являются лучшими кандидатами для параллельного плана. Index operations that create or rebuild an index, or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. Например, хорошими кандидатами являются соединения больших таблиц, больших статистических выражений и сортировка больших результирующих наборов. For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates. Простые запросы, часто находящиеся в приложениях обработки транзакций, находят дополнительную координацию, запрашиваемую для выполнения запроса в параллельном перевешивании возможного повышения производительности. Simple queries, frequently found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. Чтобы различать запросы, которые выигрывают от параллелизма, и запросы, которые не выигрывают от параллелизма, Компонент SQL Server Database Engine SQL Server Database Engine сравнивает предполагаемую стоимость выполняемого запроса или операции индекса со значением ценового ограничения для параллелизма. To distinguish between queries that benefit from parallelism and those that do not benefit, the Компонент SQL Server Database Engine SQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. Пользователи могут изменить значение по умолчанию 5 при помощи sp_configure, если при надлежащем тестировании найдено другое значение, которое больше подходит для выполнения рабочей нагрузки. Users can change the default value of 5 using sp_configure if proper testing found that a different value is better suited for the running workload.

    Достаточно ли число строк, подлежащих обработке. Whether there are a sufficient number of rows to process.
    Если оптимизатор запросов устанавливает, что число строк слишком мало, то для распространения строк он не вставляет операторы преобразования валюты. If the Query Optimizer determines that the number of rows is too low, it does not introduce exchange operators to distribute the rows. Следовательно, операторы обрабатываются последовательно. Consequently, the operators are executed serially. Обработка операторов в последовательном плане позволяет избежать сценариев, когда стоимость запуска, распределения и координации превышает преимущества, достигнутые параллельной обработкой оператора. Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination costs exceed the gains achieved by parallel operator execution.

    Доступна ли статистика распределения. Whether current distribution statistics are available.
    Если наивысшая степень параллелизма невозможна, более низкие степени рассматриваются до того, как отвергается параллельный план. If the highest degree of parallelism is not possible, lower degrees are considered before the parallel plan is abandoned.
    Например, статистика распределения не может вычисляться при создании кластеризованного индекса на представлении, потому что кластеризованный индекс еще не существует. For example, when you create a clustered index on a view, distribution statistics cannot be evaluated, because the clustered index does not yet exist. В таком случае компонент Компонент SQL Server Database Engine SQL Server Database Engine не может предоставить наивысшую степень параллелизма для операции с индексами. In this case, the Компонент SQL Server Database Engine SQL Server Database Engine cannot provide the highest degree of parallelism for the index operation. Однако некоторые операторы, такие как сортировка и сканирование, по-прежнему могут выигрывать от параллельной обработки. However, some operators, such as sorting and scanning, can still benefit from parallel execution.

    Параллельные операции с индексами доступны только в выпусках SQL Server SQL Server Developer Edition, Evaluation Edition и Enterprise Edition. Parallel index operations are only available in SQL Server SQL Server Enterprise, Developer, and Evaluation editions.

    Во время выполнения компонент Компонент SQL Server Database Engine SQL Server Database Engine устанавливает, разрешены ли описанные ранее текущая рабочая нагрузка системы и конфигурация для параллельного выполнения. At execution time, the Компонент SQL Server Database Engine SQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. Если параллельное выполнение гарантировано, Компонент SQL Server Database Engine SQL Server Database Engine определяет оптимальное число рабочих потоков и распределяет выполнение параллельного плана по этим рабочим потокам. If parallel execution is warranted, the Компонент SQL Server Database Engine SQL Server Database Engine determines the optimal number of worker threads and spreads the execution of the parallel plan across those worker threads. Если запрос или операция с индексами начинает параллельно выполняться в нескольких рабочих потоках, это же число рабочих потоков используется до тех пор, пока операция не будет завершена. When a query or index operation starts executing on multiple worker threads for parallel execution, the same number of worker threads is used until the operation is completed. Компонент SQL Server Database Engine SQL Server Database Engine повторно определяет оптимальное число рабочих потоков каждый раз при получении плана выполнения из кэша планов. The Компонент SQL Server Database Engine SQL Server Database Engine re-examines the optimal number of worker thread decisions every time an execution plan is retrieved from the plan cache. Например, при первом выполнении запроса может использоваться последовательный план, при повторном выполнении того же запроса — параллельный план с тремя рабочими потоками, при третьем выполнении — параллельный план с четырьмя рабочими потоками. For example, one execution of a query can result in the use of a serial plan, a later execution of the same query can result in a parallel plan using three worker threads, and a third execution can result in a parallel plan using four worker threads.

    В плане параллельного выполнения запроса операторы вставки, обновления и удаления обрабатываются последовательно. In a parallel query execution plan, the insert, update, and delete operators are executed serially. Однако предложение WHERE инструкции UPDATE или DELETE или часть SELECT инструкции INSERT могут обрабатываться параллельно. However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. В таком случае изменения фактических данных последовательно применяются к базе данных. The actual data changes are then serially applied to the database.

    Статические курсоры и курсоры, управляемые набором ключей, могут быть заполнены параллельными планами выполнения. Static and keyset-driven cursors can be populated by parallel execution plans. Однако поведение динамических курсоров может поддерживаться только последовательным выполнением. However, the behavior of dynamic cursors can be provided only by serial execution. Оптимизатор запросов всегда формирует последовательный план выполнения для запроса, являющегося частью динамического курсора. The Query Optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

    Переопределение степеней параллелизма Overriding Degrees of Parallelism

    Чтобы ограничить число процессоров для выполнения параллельного плана, можно использовать параметр конфигурации сервера максимальная степень параллелизма (MAXDOP) (ALTER DATABASE SCOPED CONFIGURATION в База данных SQL SQL Database ). You can use the max degree of parallelism (MAXDOP) server configuration option (ALTER DATABASE SCOPED CONFIGURATION on База данных SQL SQL Database ) to limit the number of processors to use in parallel plan execution. Для отдельного запроса и инструкций операции с индексами параметр max degree of parallelism можно переопределить, задав указание запроса MAXDOP или параметр индекса MAXDOP. The max degree of parallelism option can be overridden for individual query and index operation statements by specifying the MAXDOP query hint or MAXDOP index option. Параметр MAXDOP предоставляет улучшенное управление через отдельные запросы и операции с индексами. MAXDOP provides more control over individual queries and index operations. Например, с помощью параметра MAXDOP можно увеличить или уменьшить число процессоров, выделенных для операций с индексами в сети. For example, you can use the MAXDOP option to control, by increasing or reducing, the number of processors dedicated to an online index operation. Таким образом, можно сбалансировать ресурсы, используемые операцией с индексами с теми текущими пользователями. In this way, you can balance the resources used by an index operation with those of the concurrent users.

    Если указать для параметра «максимальная степень параллелизма» значение 0 (по умолчанию), SQL Server SQL Server сможет использовать все доступные процессоры (до 64) при выполнении параллельного плана. Setting the max degree of parallelism option to 0 (default) enables SQL Server SQL Server to use all available processors up to a maximum of 64 processors in a parallel plan execution. Хотя при использовании значения 0 параметра MAXDOP целевое количество процессоров в SQL Server SQL Server составляет 64 логических процессора, при необходимости можно указать другое значение вручную. Although SQL Server SQL Server sets a runtime target of 64 logical processors when MAXDOP option is set to 0, a different value can be manually set if needed. При использовании значения 0 параметра MAXDOP для запросов и индексов SQL Server SQL Server использует все доступные процессоры (максимально допустимое количество процессоров равно 64) для данных запросов и индексов при выполнении параллельного плана. Setting MAXDOP to 0 for queries and indexes allows SQL Server SQL Server to use all available processors up to a maximum of 64 processors for the given queries or indexes in a parallel plan execution. Значение MAXDOP не применяется принудительно для всех параллельных запросов. Оно представляет собой условный целевой показатель для всех запросов, которые можно выполнять параллельно. MAXDOP is not an enforced value for all parallel queries, but rather a tentative target for all queries eligible for parallelism. Это означает, что если во время выполнения доступно недостаточное количество рабочих потоков, запрос может выполняться со степенью параллелизма, более низкой по сравнению с той, которая задана в параметре MAXDOP. This means that if not enough worker threads are available at runtime, a query may execute with a lower degree of parallelism than the MAXDOP server configuration option.

    Рекомендации по настройке MAXDOP см. в этой статье технической поддержки Майкрософт. Refer to this Microsoft Support Article for best practices on configuring MAXDOP.

    Пример параллельного запроса Parallel Query Example

    В нижеследующем запросе подсчитывается количество заказов, размещенных в течение указанного квартала, начиная с 1 апреля 2000, в которых хотя бы один элемент из списка заказанных товаров был получен заказчиком позже фиксированной даты. The following query counts the number of orders placed in a specific quarter, starting on April 1, 2000, and in which at least one line item of the order was received by the customer later than the committed date. В этом запросе представлен подсчет таких заказов, сгруппированных в соответствии со срочностью каждого заказа и отсортированных в возрастающем порядке. This query lists the count of such orders grouped by each order priority and sorted in ascending priority order.

    В этом примере используются теоретические имена таблицы и столбцов. This example uses theoretical table and column names.

    Предположим, что в таблицах lineitem и orders определены следующие индексы: Assume the following indexes are defined on the lineitem and orders tables:

    Вот один из возможных параллельных планов, созданный для запроса, показанного выше: Here is one possible parallel plan generated for the query previously shown:

    На рисунке показан план запросов, который выполняется со степенью параллелизма, равной 4, и включает соединение двух таблиц. The illustration below shows a query plan executed with a degree of parallelism equal to 4 and involving a two-table join.

    Параллельный план содержит три оператора параллелизма. The parallel plan contains three parallelism operators. Оба оператора, Index Seek для индекса o_datkey_ptr и Index Scan для индекса l_order_dates_idx , выполняются параллельно. Both the Index Seek operator of the o_datkey_ptr index and the Index Scan operator of the l_order_dates_idx index are performed in parallel. В результате образуется несколько исключающих потоков. This produces several exclusive streams. Это можно определить по ближайшим операторам параллелизма над операторами Index Scan и Index Seek соответственно. This can be determined from the nearest Parallelism operators above the Index Scan and Index Seek operators, respectively. Оба перераспределяют тип обмена. Both are repartitioning the type of exchange. То есть они всего лишь перегруппируют данные между потоками и выдают в результате столько же потоков на выходе, сколько их было на входе. That is, they are just reshuffling data among the streams and producing the same number of streams on their output as they have on their input. Количество потоков равно степени параллелизма. This number of streams is equal to the degree of parallelism.

    Оператор параллелизма над оператором Index Seek l_order_dates_idx перераспределяет свои входные потоки с использованием значения L_ORDERKEY в качестве ключа. The parallelism operator above the l_order_dates_idx Index Scan operator is repartitioning its input streams using the value of L_ORDERKEY as a key. В этом случае те же значения L_ORDERKEY выдаются в том же выходном потоке. In this way, the same values of L_ORDERKEY end up in the same output stream. Одновременно в выходных потоках сохраняется порядок в столбце L_ORDERKEY для соответствия требованиям оператора Merge Join к входным данным. At the same time, output streams maintain the order on the L_ORDERKEY column to meet the input requirement of the Merge Join operator.

    Оператор параллелизма над оператором Index Seek перераспределяет свои входные потоки с использованием значения O_ORDERKEY . The parallelism operator above the Index Seek operator is repartitioning its input streams using the value of O_ORDERKEY . Так как его входные данные не сортируются по значениям столбца O_ORDERKEY , а он является столбцом соединения в операторе Merge Join , то оператор Sort между операторами параллелизма и Merge Join обеспечивает сортировку входных данных для оператора Merge Join по столбцам соединения. Because its input is not sorted on the O_ORDERKEY column values and this is the join column in the Merge Join operator, the Sort operator between the parallelism and Merge Join operators make sure that the input is sorted for the Merge Join operator on the join columns. Оператор Sort , как и оператор Merge Join, выполняется параллельно. The Sort operator, like the Merge Join operator, is performed in parallel.

    Первый оператор параллелизма объединяет результаты из нескольких потоков в один. The topmost parallelism operator gathers results from several streams into a single stream. Результаты частичной статистической обработки, выполняемой оператором Stream Aggregate под оператором параллелизма, затем собираются в единое значение SUM для каждого отдельного значения O_ORDERPRIORITY в операторе Stream Aggregate над оператором параллелизма. Partial aggregations performed by the Stream Aggregate operator below the parallelism operator are then accumulated into a single SUM value for each different value of the O_ORDERPRIORITY in the Stream Aggregate operator above the parallelism operator. Так как этот план состоит из двух сегментов обмена со степенью параллелизма, равной 4, в этом плане используется восемь рабочих потоков. Because this plan has two exchange segments, with degree of parallelism equal to 4, it uses eight worker threads.

    Дополнительные сведения об операторах, используемых в этом примере, см. в справочнике по логическим и физическим операторам Showplan. For more information on the operators used in this example, refer to the Showplan Logical and Physical Operators Reference.

    Параллельные операции с индексами Parallel Index Operations

    Планы запросов, созданные для операций создания или перестроения индекса либо удаления кластеризованного индекса, поддерживают возможность параллельной обработки в нескольких рабочих потоках на многопроцессорных компьютерах. The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-worker threaded operations on computers that have multiple microprocessors.

    Параллельные операции с индексами доступны, начиная с выпуска SQL Server 2008 SQL Server 2008 . Parallel index operations are only available in Enterprise Edition, starting with SQL Server 2008 SQL Server 2008 .

    В SQL Server SQL Server используются одни и те же алгоритмы определения степени параллелизма (общего числа отдельных рабочих потоков, которые будут запущены) как для операций с индексами, так и для других запросов. SQL Server SQL Server uses the same algorithms to determine the degree of parallelism (the total number of separate worker threads to run) for index operations as it does for other queries. Максимальная степень параллелизма для операции с индексом определяется параметром конфигурации сервера max degree of parallelism . The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. Значение max degree of parallelism можно переопределять для отдельных операций с индексами путем настройки параметра индекса MAXDOP в инструкциях CREATE INDEX, ALTER INDEX, DROP INDEX и ALTER TABLE. You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.

    Когда Компонент SQL Server Database Engine SQL Server Database Engine создает план выполнения индекса, количество параллельных операций устанавливается в самое низкое из следующих значений. When the Компонент SQL Server Database Engine SQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

    • Число микропроцессоров (ЦП) в компьютере. The number of microprocessors, or CPUs in the computer.
    • Число, указанное в качестве параметра конфигурации сервера max degree of parallelism. The number specified in the max degree of parallelism server configuration option.
    • Число ЦП, которые не превышают порог загруженности для рабочих потоков SQL Server SQL Server . The number of CPUs not already over a threshold of work performed for SQL Server SQL Server worker threads.

    Например, на компьютере с восемью ЦП, на котором максимальная степень параллелизма равна 6, для операций с индексами создается не более шести параллельных рабочих потоков. For example, on a computer that has eight CPUs, but where max degree of parallelism is set to 6, no more than six parallel worker threads are generated for an index operation. Если на пяти ЦП компьютера будет превышено ограничение количества рабочих потоков SQL Server SQL Server , когда строится план выполнения индексации, в плане выполнения будет указано только три параллельных рабочих потока. If five of the CPUs in the computer exceed the threshold of SQL Server SQL Server work when an index execution plan is built, the execution plan specifies only three parallel worker threads.

    Главные фазы параллельных операций с индексами таковы. The main phases of a parallel index operation include the following:

    • Координирующий рабочий поток быстро и случайным образом просматривает таблицу для оценки распределения ключей индекса. A coordinating worker thread quickly and randomly scans the table to estimate the distribution of the index keys. Координирующий рабочий поток устанавливает ключевые границы, образующие число диапазонов ключей, равное степени параллелизма. Каждый диапазон должен покрывать примерно одинаковое число строк. The coordinating worker thread establishes the key boundaries that will create a number of key ranges equal to the degree of parallel operations, where each key range is estimated to cover similar numbers of rows. Например, если в таблице четыре миллиона строк, а степень параллелизма равна 4, то координирующий рабочий поток определит ключевые значения, которые разделят все строки на четыре набора строк по одному миллиону строк в каждом. For example, if there are four million rows in the table and the degree of parallelism is 4, the coordinating worker thread will determine the key values that delimit four sets of rows with 1 million rows in each set. Если для использования всех ЦП невозможно установить достаточное число диапазонов ключей, степень параллелизма соответствующим образом снижается. If enough key ranges cannot be established to use all CPUs, the degree of parallelism is reduced accordingly.
    • Координирующий рабочий поток запускает рабочие потоки, количество которых равно степени параллелизма операций, и ожидает завершения этих потоков. The coordinating worker thread dispatches a number of worker threads equal to the degree of parallel operations and waits for these worker threads to complete their work. Каждый из рабочих потоков просматривает базовую таблицу с использованием фильтра, который отделяет только строки со значениями ключей в диапазоне, назначенном этому рабочему потоку. Each worker thread scans the base table using a filter that retrieves only rows with key values within the range assigned to the worker thread. Каждый рабочий поток создает структуру индекса для строк в своем диапазоне ключей. Each worker thread builds an index structure for the rows in its key range. В случае секционированного индекса каждый из рабочих потоков создает заданное число секций. In the case of a partitioned index, each worker thread builds a specified number of partitions. Одни и те же секции не разделяются между несколькими рабочими потоками. Partitions are not shared among worker threads.
    • После завершения работы всех параллельных рабочих потоков координирующий рабочих поток связывает компоненты индекса в единый индекс. After all the parallel worker threads have completed, the coordinating worker thread connects the index subunits into a single index. Эта фаза применяется только для операций с индексами в сети. This phase applies only to offline index operations.

    В отдельных инструкциях CREATE TABLE или ALTER TABLE могут содержаться несколько ограничений, требующих создания индекса. Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require that an index be created. Такие множественные операции по созданию индекса выполняются последовательно, хотя каждая из них может быть параллельной операцией на многопроцессорном компьютере. These multiple index creation operations are performed in series, although each individual index creation operation may be a parallel operation on a computer that has multiple CPUs.

    Архитектура распределенных запросов Distributed Query Architecture

    Microsoft SQL Server SQL Server поддерживает два метода обращения к разнородным источникам данных OLE DB в инструкциях Transact-SQL Transact-SQL . Microsoft SQL Server SQL Server supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQL Transact-SQL statements:

    Имена связанных серверов Linked server names
    Системные хранимые процедуры sp_addlinkedserver и sp_addlinkedsrvlogin используются для задания серверного имени источнику данных OLE DB. The system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to give a server name to an OLE DB data source. К объектам на этих связанных серверах можно обращаться в инструкциях языка Transact-SQL Transact-SQL по четырехкомпонентным именам. Objects in these linked servers can be referenced in Transact-SQL Transact-SQL statements using four-part names. Например, если имя связанного сервера DeptSQLSrvr определено для другого экземпляра SQL Server SQL Server , для обращения к таблице на таком сервере используется следующая инструкция. For example, if a linked server name of DeptSQLSrvr is defined against another instance of SQL Server SQL Server , the following statement references a table on that server:

    Имя связанного сервера можно также указать в инструкции OPENQUERY для открытия набора строк из источника данных OLE DB. The linked server name can also be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. К этому набору строк можно обращаться в инструкциях языка Transact-SQL Transact-SQL так же, как и к таблице: This rowset can then be referenced like a table in Transact-SQL Transact-SQL statements.

    Имена нерегламентированных соединителей Ad hoc connector names
    Для нечастых обращений к источнику данных используются функции OPENROWSET или OPENDATASOURCE , которым задаются данные, необходимые для подключения к связанному серверу. For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the linked server. Затем можно обращаться к набору строк в инструкциях языка Transact-SQL Transact-SQL тем же путем, что и к таблице: The rowset can then be referenced the same way a table is referenced in Transact-SQL Transact-SQL statements:

    В SQL Server SQL Server для коммуникации между реляционным механизмом и подсистемой хранилища используется технология OLE DB. SQL Server SQL Server uses OLE DB to communicate between the relational engine and the storage engine. Реляционный механизм разбивает каждую инструкцию языка Transact-SQL Transact-SQL на последовательные операции над простыми наборами строк OLE DB, открываемые подсистемой хранилища из базовых таблиц. The relational engine breaks down each Transact-SQL Transact-SQL statement into a series of operations on simple OLE DB rowsets opened by the storage engine from the base tables. Это означает, что реляционный механизм может также открывать простые наборы строк OLE DB на любом источнике данных OLE DB. This means the relational engine can also open simple OLE DB rowsets on any OLE DB data source.

    В реляционном механизме используется прикладной программный интерфейс (API) OLE DB для открытия наборов строк на связанных серверах, выборки строк и управления транзакциями. The relational engine uses the OLE DB application programming interface (API) to open the rowsets on linked servers, fetch the rows, and manage transactions.

    Для каждого источника данных OLE DB, доступ к которому осуществляется как к связанному серверу, на сервере с запущенной службой SQL Server SQL Server должен быть поставщик OLE DB. For each OLE DB data source accessed as a linked server, an OLE DB prov >SQL Server SQL Server . Набор операций языка Transact-SQL Transact-SQL , которые можно использовать с конкретным источником данных OLE DB, зависит от возможностей поставщика OLE DB. The set of Transact-SQL Transact-SQL operations that can be used against a specific OLE DB data source depends on the capabilities of the OLE DB provider.

    Для каждого экземпляра SQL Server SQL Server участники предопределенной роли сервера sysadmin могут включать или отключать использование нерегламентированных имен соединителей для поставщика OLE DB с помощью свойства SQL Server SQL Server DisallowAdhocAccess . For each instance of SQL Server SQL Server , members of the sysadmin fixed server role can enable or disable the use of ad-hoc connector names for an OLE DB prov >SQL Server SQL Server DisallowAdhocAccess property. Если нерегламентированный доступ включен, любой пользователь, зарегистрированный на данном экземпляре, может выполнять инструкции Transact-SQL Transact-SQL , содержащие имена нерегламентированных соединителей, обращающиеся к любым источникам данных в сети, доступ к которым возможен посредством данного поставщика OLE DB. When ad-hoc access is enabled, any user logged on to that instance can execute Transact-SQL Transact-SQL statements containing ad-hoc connector names, referencing any data source on the network that can be accessed using that OLE DB provider. Для управления доступом к источникам данных члены роли sysadmin могут отключить нерегламентированный доступ к определенному поставщику OLE DB, ограничивая таким образом пользователям доступ лишь к тем источникам данных, обращение к которым производится по именам связанных серверов, определенным администраторами. To control access to data sources, members of the sysadmin role can disable ad-hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. По умолчанию нерегламентированный доступ включен для поставщика OLE DB SQL Server SQL Server и отключен для всех остальных поставщиков OLE DB. By default, ad-hoc access is enabled for the SQL Server SQL Server OLE DB provider, and disabled for all other OLE DB providers.

    С помощью распределенных запросов пользователи могут обращаться к другим источникам данных (например, файлам, нереляционным источникам данных, таким как Active Directory и др.) с помощью контекста безопасности учетной записи Microsoft Windows, от имени которой запущена служба SQL Server SQL Server . Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory, and so on) using the security context of the Microsoft Windows account under which the SQL Server SQL Server service is running. SQL Server SQL Server может олицетворять имена для входа в Windows, но в случае имен для входа SQL Server SQL Server это невозможно. impersonates the login appropriately for Windows logins; however, that is not possible for SQL Server SQL Server logins. Это потенциально может открыть пользователю распределенного запроса доступ к другому источнику данных, для которого у него нет разрешения, но у учетной записи, под которой запущена служба SQL Server SQL Server , такое разрешение есть. This can potentially allow a distributed query user to access another data source for which they do not have permissions, but the account under which the SQL Server SQL Server service is running does have permissions. Для указания конкретных имен входа, которым будет разрешен доступ к соответствующему связанному серверу, используется процедура sp_addlinkedsrvlogin . Use sp_addlinkedsrvlogin to define the specific logins that are authorized to access the corresponding linked server. Для нерегламентированных имен такой контроль недоступен, поэтому следует проявлять осторожность при включении нерегламентированного доступа к поставщику OLE DB. This control is not available for ad-hoc names, so use caution in enabling an OLE DB provider for ad-hoc access.

    По возможности SQL Server SQL Server принудительно отправляет реляционные операции (соединения, ограничения, проекции, сортировки и группировки по операциям) к источнику данных OLE DB. When possible, SQL Server SQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB data source. По умолчанию SQL Server SQL Server не просматривает базовую таблицу в SQL Server SQL Server и не выполняет реляционных операций самостоятельно. SQL Server SQL Server does not default to scanning the base table into SQL Server SQL Server and performing the relational operations itself. SQL Server SQL Server запрашивает у поставщика OLE DB уровень поддерживаемой им грамматики SQL и на основе этих данных направляет поставщику максимально возможное число реляционных операций. queries the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, pushes as many relational operations as possible to the provider.

    SQL Server SQL Server указывает поставщику OLE DB механизм возвращения статистики распределения ключевых значений в пределах источника данных OLE DB. specifies a mechanism for an OLE DB provider to return statistics indicating how key values are distributed within the OLE DB data source. Это позволяет оптимизатору запросов SQL Server SQL Server лучше проанализировать шаблон данных в источнике данных на соответствие требованиям для каждой инструкции Transact-SQL Transact-SQL , что позволяет более эффективно создавать оптимальные планы выполнения. This lets the SQL Server SQL Server Query Optimizer better analyze the pattern of data in the data source against the requirements of each Transact-SQL Transact-SQL statement, increasing the ability of the Query Optimizer to generate optimal execution plans.

    Улучшенные возможности обработки запросов для секционированных таблиц и индексов Query Processing Enhancements on Partitioned Tables and Indexes

    В SQL Server 2008 SQL Server 2008 повышена эффективность обработки запросов к секционированным таблицам для множества параллельных планов, изменен способ представления параллельных и последовательных планов и улучшены сведения о секционировании, содержащиеся в планах выполнения времени компиляции и времени выполнения. SQL Server 2008 SQL Server 2008 improved query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhanced the partitioning information provided in both compile-time and run-time execution plans. В этом разделе описываются названные улучшения, содержится справочник об интерпретации планов выполнения запросов таблиц секционирования и индексов и дополнительные сведения об улучшении производительности запросов к секционированным объектам. This topic describes these improvements, provides guidance on how to interpret the query execution plans of partitioned tables and indexes, and provides best practices for improving query performance on partitioned objects.

    Секционированные таблицы и индексы поддерживаются только в выпусках SQL Server SQL Server Enterprise Edition, Developer Edition и Evaluation Edition. Partitioned tables and indexes are supported only in the SQL Server SQL Server Enterprise, Developer, and Evaluation editions.

    Новая операция поиска, учитывающая секционирование New Partition-Aware Seek Operation

    В SQL Server SQL Server внутреннее представление секционированной таблицы изменено таким образом, что таблица представляется обработчику запросов как индекс по нескольким столбцам с PartitionID в качестве начального столбца. In SQL Server SQL Server , the internal representation of a partitioned table is changed so that the table appears to the query processor to be a multicolumn index with PartitionID as the leading column. PartitionID представляет собой скрытый внутренний вычисляемый столбец для представления ID секции, содержащей определенную строку. PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row. Например, предположим, что таблица T, определенная как T(a, b, c) , секционирована по столбцу a и содержит кластеризованный индекс по столбцу b. For example, assume the table T, defined as T(a, b, c) , is partitioned on column a, and has a clustered index on column b. В SQL Server SQL Server эта секционированная таблица обрабатывается как несекционированная таблица со схемой T(PartitionID, a, b, c) и кластеризованным индексом по составному ключу (PartitionID, b) . In SQL Server SQL Server , this partitioned table is treated internally as a nonpartitioned table with the schema T(PartitionID, a, b, c) and a clustered index on the composite key (PartitionID, b) . Это позволяет оптимизатору запросов выполнять операции поиска на основе PartitionID по любой секционированной таблице или индексу. This allows the Query Optimizer to perform seek operations based on PartitionID on any partitioned table or index.

    Устранение секций теперь осуществляется в этой операции поиска. Partition elimination is now done in this seek operation.

    In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (в качестве логического начального столбца) и, возможно, по другим ключевым столбцам индекса, а затем может быть выполнен поиск второго уровня с другим условием по одному дополнительному столбцу или более для каждого уникального значения, удовлетворяющего операции поиска первого уровня. In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (as the logical leading column) and possibly other index key columns, and then a second-level seek, with a different condition, can be done on one or more additional columns, for each distinct value that meets the qualification for the first-level seek operation. Операция, называемая «просмотр с пропуском», позволяет оптимизатору запросов выполнять операцию поиска или просмотра по одному условию для определения секций, к которым будет осуществляться доступ, и операцию поиска индекса второго уровня с помощью этого оператора для выборки строк из этих секций, удовлетворяющих другому условию. That is, this operation, called a skip scan, allows the Query Optimizer to perform a seek or scan operation based on one condition to determine the partitions to be accessed and a second-level index seek operation within that operator to return rows from these partitions that meet a different condition. Например, рассмотрим следующий запрос. For example, consider the following query.

    В данном примере, предположим, таблица T, определенная как T(a, b, c) , секционирована по столбцу a и содержит кластеризованный индекс по столбцу b. For this example, assume that table T, defined as T(a, b, c) , is partitioned on column a, and has a clustered index on column b. Границы секции для таблицы T определены следующей функцией секционирования: The partition boundaries for table T are defined by the following partition function:

    Для разрешения запроса обработчик запросов выполняет операцию поиска первого уровня для нахождения каждой секции, содержащей строки, удовлетворяющие условию T.a . To solve the query, the query processor performs a first-level seek operation to find every partition that contains rows that meet the condition T.a . Это позволяет выявить секции, к которым необходимо получить доступ. This identifies the partitions to be accessed. В каждой выявленной секции обработчик выполняет поиск второго уровня по кластеризованному индексу по столбцу b для нахождения строк, удовлетворяющих условию T.b = 2 и T.a . Within each partition identified, the processor then performs a second-level seek into the clustered index on column b to find the rows that meet the condition T.b = 2 and T.a .

    На следующем рисунке изображено логическое представление операции просмотра с пропуском. The following illustration is a logical representation of the skip scan operation. На нем изображена таблица T с данными в столбцах a и b . It shows table T with data in columns a and b . Секции пронумерованы от 1 до 4, а границы секций показаны вертикальными штриховыми линиями. The partitions are numbered 1 through 4 with the partition boundaries shown by dashed vertical lines. Операция поиска первого уровня для секций (на иллюстрации не показана) определила, что секции 1, 2 и 3 удовлетворяют условию поиска, предполагаемого секционированием, определенным для таблицы и предиката по столбцу a . A first-level seek operation to the partitions (not shown in the illustration) has determined that partitions 1, 2, and 3 meet the seek condition implied by the partitioning defined for the table and the predicate on column a . то есть T.a . That is, T.a . Путь, пройденный частью операции просмотра с пропуском, поиском второго уровня, изображен изогнутой линией. The path traversed by the second-level seek portion of the skip scan operation is illustrated by the curved line. Фактически операция просмотра с пропуском выполняет поиск строк, удовлетворяющих условию b = 2 в каждой их этих секций. Essentially, the skip scan operation seeks into each of these partitions for rows that meet the condition b = 2 . Общие затраты на выполнение операции просмотра с пропуском соответствуют трем отдельным поискам по индексу. The total cost of the skip scan operation is the same as that of three separate index seeks.

    Отображение сведений о секционировании в планах выполнения запросов Displaying Partitioning Information in Query Execution Plans

    Планы выполнения запросов в секционированных таблицах и индексах можно изучить с помощью инструкций SET SHOWPLAN_XML или SET STATISTICS XML языка Transact-SQL Transact-SQL SET или с помощью графического представления в среде SQL Server SQL Server Management Studio. The execution plans of queries on partitioned tables and indexes can be examined by using the Transact-SQL Transact-SQL SET statements SET SHOWPLAN_XML or SET STATISTICS XML , or by using the graphical execution plan output in SQL Server SQL Server Management Studio. Например, план выполнения времени компиляции можно отобразить, щелкнув Показать предполагаемый план выполнения на панели инструментов редактора запросов, а план времени выполнения — щелкнув Включить действительный план выполнения. For example, you can display the compile-time execution plan by clicking Display Estimated Execution Plan on the Query Editor toolbar and the run-time plan by clicking Include Actual Execution Plan.

    С помощью этих средств можно получить следующую информацию: Using these tools, you can ascertain the following information:

    • операции, такие как scans , seeks , inserts , updates , merges и deletes , которые осуществляют доступ к таблицам и индексам; The operations such as scans , seeks , inserts , updates , merges , and deletes that access partitioned tables or indexes.
    • секции, к которым запрос получает доступ — The partitions accessed by the query. например, в планах времени выполнения приведено общее число секций, к которым получен доступ, и диапазоны смежных секций, к которым получен доступ; For example, the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans.
    • когда операция просмотра с пропуском используется в операции поиска или просмотра для получения данных из одной секции или более. When the skip scan operation is used in a seek or scan operation to retrieve data from one or more partitions.

    Улучшенные возможности информации о секции Partition Information Enhancements

    SQL Server SQL Server содержит расширенные сведения о секционировании как для планов времени компиляции, так и для планов времени выполнения. provides enhanced partitioning information for both compile-time and run-time execution plans. Планы выполнения теперь содержат следующую информацию. Execution plans now provide the following information:


    • Дополнительный атрибут Partitioned указывает, что оператор, например seek , scan , insert , update , merge или delete , выполняется в отношении секционированной таблицы. An optional Partitioned attribute that indicates that an operator, such as a seek , scan , insert , update , merge , or delete , is performed on a partitioned table.
    • Новый элемент SeekPredicateNew с вложенным элементом SeekKeys , содержащим PartitionID в качестве начального ключевого столбца индекса и условия фильтра, определяющие операции поиска по диапазону в PartitionID . A new SeekPredicateNew element with a SeekKeys subelement that includes PartitionID as the leading index key column and filter conditions that specify range seeks on PartitionID . Наличие двух вложенных элементов SeekKeys указывает на то, что в отношении PartitionID используется операция просмотра с пропуском. The presence of two SeekKeys subelements indicates that a skip scan operation on PartitionID is used.
    • Сводные данные об общем числе секций, к которым получен доступ. Summary information that provides a total count of the partitions accessed. Эта информация доступна только в планах времени выполнения. This information is available only in run-time plans.

    Для демонстрации отображения этой информации как в графическом плане выполнения, так и в отчете инструкции XML Showplan рассмотрим следующий запрос по секционированной таблице fact_sales . To demonstrate how this information is displayed in both the graphical execution plan output and the XML Showplan output, consider the following query on the partitioned table fact_sales . Этот запрос обновляет данные в двух секциях. This query updates data in two partitions.

    На следующем рисунке показаны свойства оператора Clustered Index Seek в плане выполнения времени компиляции для этого запроса. The following illustration shows the properties of the Clustered Index Seek operator in the compile-time execution plan for this query. Определение таблицы fact_sales и определение секции см. в подразделе «Пример» в этом разделе. To view the definition of the fact_sales table and the partition definition, see «Example» in this topic.

    Атрибут Partitioned Partitioned Attribute

    Когда оператор, такой как Index Seek , выполняется по секционированной таблице или индексу, в планах времени компиляции и времени выполнения появляется атрибут Partitioned со значением True (1). When an operator such as an Index Seek is executed on a partitioned table or index, the Partitioned attribute appears in the compile-time and run-time plan and is set to True (1). Этот атрибут не отображается, если его значение установлено как False (0). The attribute does not display when it is set to False (0).

    Атрибут Partitioned может встречаться в следующих физических и логических операторах: The Partitioned attribute can appear in the following physical and logical operators:

    • Table Scan
    • Index Scan
    • Index Seek
    • Insert
    • Update
    • Delete
    • Merge

    Как показано на предыдущей иллюстрации, этот атрибут отображается в свойствах оператора, в котором он определен. As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. В отчете инструкции XML Showplan этот атрибут появляется как Partitioned=»1″ в узле RelOp оператора, в котором он определен. In the XML Showplan output, this attribute appears as Partitioned=»1″ in the RelOp node of the operator in which it is defined.

    Предикат New Seek New Seek Predicate

    В выводе инструкции XML Showplan элемент SeekPredicateNew появляется в операторе, в котором он определен. In XML Showplan output, the SeekPredicateNew element appears in the operator in which it is defined. Он может содержать до двух экземпляров вложенного элемента SeekKeys . It can contain up to two occurrences of the SeekKeys sub-element. Первый элемент SeekKeys определяет операцию поиска первого уровня на уровне идентификатора секции логического индекса. The first SeekKeys item specifies the first-level seek operation at the partition ID level of the logical index. То есть эта операция поиска определяет секции, к которым должен быть осуществлен доступ для удовлетворения условий запроса. That is, this seek determines the partitions that must be accessed to satisfy the conditions of the query. Второй элемент SeekKeys определяет часть операции просмотра с пропуском, поиск второго уровня, который производится в каждой секции, определенной поиском первого уровня. The second SeekKeys item specifies the second-level seek portion of the skip scan operation that occurs within each partition identified in the first-level seek.

    Сводные данные по секциям Partition Summary Information

    В планах времени выполнения сводка по секциям содержит данные о числе секций, к которым осуществлен доступ, и фактический перечень секций, к которым осуществлен доступ. In run-time execution plans, partition summary information provides a count of the partitions accessed and the identity of the actual partitions accessed. С помощью этих данных можно проверить, к правильным ли секциям обращается запрос и исключены ли из рассмотрения остальные секции. You can use this information to verify that the correct partitions are accessed in the query and that all other partitions are eliminated from consideration.

    Предоставляется следующая информация: Actual Partition Count и Partitions Accessed . The following information is provided: Actual Partition Count , and Partitions Accessed .

    Actual Partition Count — это общее число секций, к которым запрос получает доступ. Actual Partition Count is the total number of partitions accessed by the query.

    Partitions Accessed в выводе инструкции XML Showplan — это сводные данные по секциям, которые появляются в новом элементе RuntimePartitionSummary в узле RelOp оператора, в котором он определен. Partitions Accessed , in XML Showplan output, is the partition summary information that appears in the new RuntimePartitionSummary element in RelOp node of the operator in which it is defined. В следующем примере показано содержимое элемента RuntimePartitionSummary , указывающее, что получен доступ только к двум секциям (секции 2 и 3). The following example shows the contents of the RuntimePartitionSummary element, indicating that two total partitions are accessed (partitions 2 and 3).

    Отображение сведений о секционировании с помощью других методов Showplan Displaying Partition Information by Using Other Showplan Methods

    Методы Showplan SHOWPLAN_ALL , SHOWPLAN_TEXT и STATISTICS PROFILE не формируют сведения о секционировании, описанные в этом разделе, за следующим исключением. The Showplan methods SHOWPLAN_ALL , SHOWPLAN_TEXT , and STATISTICS PROFILE do not report the partition information described in this topic, with the following exception. Как часть предиката SEEK , секции, к которым необходимо получить доступ, обозначаются предикатом по диапазону в вычисляемом столбце, представляющем идентификатор секций. As part of the SEEK predicate, the partitions to be accessed are identified by a range predicate on the computed column representing the partition ID. В следующем примере показан предикат SEEK для оператора Clustered Index Seek . The following example shows the SEEK predicate for a Clustered Index Seek operator. К секциям 2 и 3 происходит обращение, и оператор поиска производит фильтрацию по строкам, удовлетворяющим условию date_id BETWEEN 20080802 AND 20080902 . Partitions 2 and 3 are accessed, and the seek operator filters on the rows that meet the condition date_id BETWEEN 20080802 AND 20080902 .

    Интерпретация планов выполнения для секционированной кучи Interpreting Execution Plans for Partitioned Heaps

    Секционированная куча обрабатывается как логический индекс по идентификатору секции. A partitioned heap is treated as a logical index on the partition ID. Устранение секций на секционированной куче представлено в плане выполнения в виде оператора Table Scan с предикатом SEEK по идентификатору секции. Partition elimination on a partitioned heap is represented in an execution plan as a Table Scan operator with a SEEK predicate on partition ID. Следующий пример отображает сведения Showplan: The following example shows the Showplan information provided:

    Интерпретация планов выполнения для выровненных соединений Interpreting Execution Plans for Collocated Joins

    Выравнивание соединений может возникать, когда две таблицы секционированы с использованием одной и той же функции или эквивалентных функций секционирования и столбцы секционирования из обеих сторон соединения указываются в условии соединения запроса. Join collocation can occur when two tables are partitioned using the same or equivalent partitioning function and the partitioning columns from both sides of the join are specified in the join condition of the query. Оптимизатор запросов может сформировать план, в котором секции каждой таблицы, имеющие равные идентификаторы, соединяются отдельно. The Query Optimizer can generate a plan where the partitions of each table that have equal partition IDs are joined separately. Выровненные соединения могут выполняться быстрее, чем невыровненные, поскольку требуют меньшего объема памяти и времени обработки. Collocated joins can be faster than non-collocated joins because they can require less memory and processing time. Оптимизатор выбирает невыровненный план или выровненный план исходя из расчета затрат. The Query Optimizer chooses a non-collocated plan or a collocated plan based on cost estimates.

    В выровненных планах соединение Nested Loops считывает одну или более секций для соединяемых таблиц или индексов с внутренней стороны. In a collocated plan, the Nested Loops join reads one or more joined table or index partitions from the inner side. Цифры в операторах Constant Scan представляют собой номера секций. The numbers within the Constant Scan operators represent the partition numbers.

    Если для секционированных таблиц или индексов формируются параллельные планы для выровненных соединений, то между операторами соединения Constant Scan и Nested Loops появляется оператор Parallelism. When parallel plans for collocated joins are generated for partitioned tables or indexes, a Parallelism operator appears between the Constant Scan and the Nested Loops join operators. В этом случае каждый из нескольких рабочих потоков на внешней стороне соединения считывает разные секции и работает с разными секциями. In this case, multiple worker threads on the outer side of the join each read and work on a different partition.

    Следующий рисунок демонстрирует план параллельных запросов для выровненных соединений. The following illustration demonstrates a parallel query plan for a collocated join.

    Стратегия выполнения параллельных запросов для секционированных объектов Parallel Query Execution Strategy for Partitioned Objects

    Обработчик запросов использует стратегию параллельного выполнения для запросов, производящих выборку из секционированных объектов. The query processor uses a parallel execution strategy for queries that select from partitioned objects. В рамках стратегии выполнения обработчик запросов определяет секции таблицы, необходимые для запроса, и долю рабочих потоков, которую следует выделить для каждой секции. As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of worker threads to allocate to each partition. В большинстве случаев обработчик запросов выделяет равное или почти равное количество рабочих потоков для каждой секции, а затем выполняет запрос параллельно на всех секциях. In most cases, the query processor allocates an equal or almost equal number of worker threads to each partition, and then executes the query in parallel across the partitions. Выделение рабочих потоков более подробно описано ниже. The following paragraphs explain worker thread allocation in greater detail.

    Если число рабочих потоков меньше числа секций, обработчик запросов назначает по одному рабочему потоку каждой отдельной секции, оставляя несколько секций без рабочих потоков. If the number of worker threads is less than the number of partitions, the query processor assigns each worker thread to a different partition, initially leaving one or more partitions without an assigned worker thread. Когда рабочий поток завершает работу с секцией, обработчик запросов назначает этот поток следующей секции. Это продолжается до тех пор, пока у каждой секции не будет по одному рабочему потоку. When a worker thread finishes executing on a partition, the query processor assigns it to the next partition until each partition has been assigned a single worker thread. Это единственный случай, когда обработчик запросов перераспределяет рабочие потоки к другим секциям. This is the only case in which the query processor reallocates worker threads to other partitions.
    Отображает рабочий поток, повторно назначенный после завершения. Shows worker thread reassigned after it finishes. Если число рабочих потоков равно числу секций, обработчик запросов назначает каждой секции по одному рабочему потоку. If the number of worker threads is equal to the number of partitions, the query processor assigns one worker thread to each partition. После того как рабочий поток заканчивает работу, он не назначается другой секции. When a worker thread finishes, it is not reallocated to another partition.

    Если число рабочих потоков больше числа секций, обработчик запросов назначает каждой секции одинаковое число рабочих потоков. If the number of worker threads is greater than the number of partitions, the query processor allocates an equal number of worker threads to each partition. Если число рабочих потоков не кратно числу секций, обработчик запросов выделяет по одному дополнительному рабочему потоку для некоторых секций, чтобы были использованы все доступные рабочие потоки. If the number of worker threads is not an exact multiple of the number of partitions, the query processor allocates one additional worker thread to some partitions in order to use all of the available worker threads. Обратите внимание, что если секция всего одна, ей будут назначены все потоки. Note that if there is only one partition, all worker threads will be assigned to that partition. На приведенном ниже рисунке показаны четыре секции и 14 рабочих потоков. In the diagram below, there are four partitions and 14 worker threads. Каждой секции назначено по 3 рабочих потока, у двух секций есть дополнительные рабочие потоки; всего назначено 14 рабочих потоков. Each partition has 3 worker threads assigned, and two partitions have an additional worker thread, for a total of 14 worker thread assignments. После того как рабочий поток заканчивает работу, он не назначается другой секции. When a worker thread finishes, it is not reassigned to another partition.

    В приведенных выше примерах демонстрируется достаточно прямолинейный способ распределения рабочих потоков. Реальная стратегия более сложна; она учитывает другие факторы, которые возникают при выполнении запроса. Although the above examples suggest a straightforward way to allocate worker threads, the actual strategy is more complex and accounts for other variables that occur during query execution. Например, если таблица секционирована и имеет кластеризованный индекс для столбца А, а в запросе используется предложение предиката WHERE A IN (13, 17, 25) , то обработчик запросов выделит один рабочий поток или несколько каждому из трех искомых значений из значений поиска (A=13, A=17 и A=25), а не каждой секции таблицы. For example, if the table is partitioned and has a clustered index on column A and a query has the predicate clause WHERE A IN (13, 17, 25) , the query processor will allocate one or more worker threads to each of these three seek values (A=13, A=17, and A=25) instead of each table partition. Запрос необходимо выполнить только в секциях, содержащих эти значения; если все предикаты поиска будут расположены в одной секции таблицы, все рабочие потоки будут назначены этой секции. It is only necessary to execute the query in the partitions that contain these values, and if all of these seek predicates happen to be in the same table partition, all of the worker threads will be assigned to the same table partition.

    Другой пример: предположим, что таблица имеет четыре секции для столбца A с граничными точками (10, 20, 30), индекс на столбце B, а в запросе содержится предикат WHERE B IN (50, 100, 150) . To take another example, suppose that the table has four partitions on column A with boundary points (10, 20, 30), an index on column B, and the query has a predicate clause WHERE B IN (50, 100, 150) . Так как секции таблицы основаны на значениях A, значения столбца B могут появляться во всех секциях таблицы. Because the table partitions are based on the values of A, the values of B can occur in any of the table partitions. Поэтому обработчик запросов будет искать каждое из этих трех значений столбца B (50, 100, 150) в каждой из четырех секций таблицы. Thus, the query processor will seek for each of the three values of B (50, 100, 150) in each of the four table partitions. Обработчик запросов распределит рабочие потоки пропорционально, чтобы эти 12 операций сканирования запроса могли выполняться параллельно. The query processor will assign worker threads proportionately so that it can execute each of these 12 query scans in parallel.

    Секции таблицы основаны на столбце А Table partitions based on column A Операции поиска для столбца B в каждой секции таблицы Seeks for column B in each table partition
    Секция таблицы 1: A Table Partition 1: A B = 50, B = 100, B = 150 B=50, B=100, B=150
    Секция таблицы 2: A >= 10 AND A Table Partition 2: A >= 10 AND A B = 50, B = 100, B = 150 B=50, B=100, B=150
    Секция таблицы 3: A >= 20 И A Table Partition 3: A >= 20 AND A B = 50, B = 100, B = 150 B=50, B=100, B=150
    Секция таблицы 4: A >= 30 Table Partition 4: A >= 30 B = 50, B = 100, B = 150 B=50, B=100, B=150

    Рекомендации Best Practices

    Для увеличения производительности запросов, обращающихся к большому количеству данных из больших секционированных таблиц и индексов, предлагаются следующие рекомендации. To improve the performance of queries that access a large amount of data from large partitioned tables and indexes, we recommend the following best practices:

    • Распределяйте каждую секцию по нескольким дискам. Stripe each partition across many disks. Это особенно актуально при использовании шпиндельных жестких дисков. This is especially relevant when using spinning disks.
    • Чтобы снизить затраты на ввод-вывод, по возможности используйте сервер с достаточным объемом основной памяти, вмещающей секции, требующие частого доступа, или все секции. When possible, use a server with enough main memory to fit frequently accessed partitions or all partitions in memory to reduce I/O cost.
    • Если данные, по которым выполняется запрос, не помещаются в памяти, рекомендуется выполнить сжатие таблиц и индексов. If the data you query will not fit in memory, compress the tables and indexes. Это позволит снизить затраты на ввод-вывод. This will reduce I/O cost.
    • Чтобы в полной мере реализовать возможности параллельной обработки запросов, используйте сервер с быстрыми процессорами и как можно большим числом процессорных ядер. Use a server with fast processors and as many processor cores as you can afford, to take advantage of parallel query processing capability.
    • Обеспечьте достаточную пропускную способность контроллера ввода-вывода для сервера. Ensure the server has sufficient I/O controller bandwidth.
    • Чтобы в полной мере реализовать возможности оптимизированного просмотра сбалансированного дерева, создайте кластеризованный индекс по каждой большой секционированной таблице. Create a clustered index on every large partitioned table to take advantage of B-tree scanning optimizations.
    • При массовой загрузке данных в секционированные таблицы следуйте рекомендациям, приведенным в техническом документе The Data Loading Performance Guide (Руководство по эффективной загрузке данных). Follow the best practice recommendations in the white paper, The Data Loading Performance Guide, when bulk loading data into partitioned tables.

    Пример Example

    В следующем примере показано создание тестовой базы данных, состоящей из одной таблицы с семью секциями. The following example creates a test database containing a single table with seven partitions. Чтобы при выполнении запросов в этом примере просматривать сведения о секционировании в планах времени компиляции и времени выполнения, следует пользоваться инструментами, описанными ранее. Use the tools described previously when executing the queries in this example to view partitioning information for both compile-time and run-time plans.

    В данном примере в таблицу вставляется более 1 миллиона строк. This example inserts more than 1 million rows into the table. В зависимости от имеющегося оборудования выполнение данного примера может занять несколько минут. Running this example may take several minutes depending on your hardware. Перед выполнением этого примера следует убедиться, что на диске 1,5 ГБ свободного места. Before executing this example, verify that you have more than 1.5 GB of disk space available.

    Sqlглава 5 о предложениях определения данных и оптимизации запросов

    Оптимизация запросов в SQL Server 2005, статистика баз данных SQL Server 2005, CREATE STATISTICS, UPDATE STATISTICS, SET NOCOUNT ON, планы выполнения запросов, количество логических чтений (logical reads), хинты оптимизатора (optimizer hints), MAXDOP, OPTIMIZE FOR, руководства по планам выполнения (plan guides), sp_create_plan_guide

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

    Отношение к оптимизации запросов у многих специалистов неоднозначное. С одной стороны, работа программного модуля Query Optimizer , который генерирует планы выполнения запросов, вызывает множество справедливых нареканий и в SQL Server 2000, и в SQL Server 2005. Query Optimizer часто выбирает не самые оптимальные планы выполнения запросов и в некоторых ситуациях проигрывает аналогичным модулям из Oracle и Informix . С другой стороны, ручная оптимизация запросов — процесс чрезвычайно трудоемкий. Вы можете потратить много времени на такую оптимизацию и, в конце концов, выяснить, что ничего оптимизировать не удалось: план, предложенный Query Optimizer изначально, оказался наиболее оптимальным (так бывает в большинстве случаев). Кроме того, может случиться так, что созданный вами вручную план выполнения запросов через какое-то время (после добавления новой информации в базу данных) окажется неоптимальным и будет снижать производительность при выполнении запросов.

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

    Статистика — это специальная служебная информация о распределении данных в столбцах таблиц. Представим, например, что выполняется запрос, который должен вернуть всех Ивановых, проживающих в городе Санкт-Петербурге. Предположим, что у 90% записей в этой таблице одно и то же значение в столбце Город — «Санкт-Петербург» . Конечно, с точки зрения выполнения запроса вначале выгоднее выбрать в таблице всех Ивановых (их явно будет не 90%), а затем уже проверять значение столбца Город для каждой отобранной записи. Однако для того, чтобы узнать, как распределяются значения в столбце, нужно вначале выполнить запрос. Поэтому SQL Server самостоятельно инициирует выполнение таких запросов, а потом сохраняет информацию о распределении данных (которая и называется статистикой) в служебных таблицах базы данных.

    Для баз данных SQL Server 2005 по умолчанию устанавливаются параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS . При этом статистика для столбцов баз данных будет создаваться и обновляться автоматически. Для самых больших и важных баз данных может получиться так, что операции по созданию и обновлению статистики могут мешать текущей работе пользователей. Поэтому для таких баз данных иногда эти параметры отключают, а операции по созданию и обновлению статистики выполняют вручную в ночное время. Для этого используются команды CREATE STATISTICS и UPDATE STATISTICS .

    Теперь поговорим об оптимизации запросов.

    Первое, что необходимо сделать, — найти те запросы, которые в первую очередь подлежат оптимизации. Проще всего это сделать при помощи профилировщика, установив фильтр на время выполнения запроса (фильтр Duration в окне Edit Filter (Редактировать фильтр), которое можно открыть при помощи кнопки Column Filters на вкладке Events Selection окна свойств сеанса трассировки). Например, в число кандидатов на оптимизацию могут попасть запросы, время выполнения которых составило более 5 секунд. Кроме того, можно использовать информацию о запросах, которая предоставляется Database Tuning Advisor .

    Затем нужно проверить, устанавлен ли для ваших соединений, хранимых процедур и функций параметр NOCOUNT . Установить его можно при помощи команды SET NOCOUNT ON . При установке этого параметра, во-первых, отключается возврат с сервера и вывод информации о количестве строк в результатах запроса (т. е. не отображается строка «N row(s) affected» на вкладке Messages ( C ообщения) окна работы с кодом при выполнении запроса в Management Studio ). Во-вторых, отключается передача специального серверного сообщения DONE_IN_PROC , которое по умолчанию возвращается для каждого этапа хранимой процедуры. При вызове большинства хранимых процедур нужен только результат их выполнения, а количество обработанных строк для каждого этапа никого не интересует. Поэтому установка параметра NOCOUNT для хранимых процедур может серьезно повысить их производительность. Повышается скорость выполнения и обычных запросов, но в меньшей степени (до 10%).

    После этого можно приступать к работе с планами выполнения запросов.

    План выполнения запроса проще всего просмотреть из SQL Server Management Studio . Для того чтобы получить информацию об ожидаемом плане выполнения запроса, можно в меню Query (Запрос) выбрать команду Display Estimated Execution Plan (Отобразить ожидаемый план выполнения). Если вы хотите узнать реальный план выполнения запроса, можно перед его выполнением установить в том же меню параметр Include Actual Execution Plan (Включить реальный план выполнения). В этом случае после выполнения запроса в окне результатов в SQL Server Management Studio появится еще одна вкладка Execution Plan (План выполнения), на которой будет представлен реальный план выполнения запроса. При наведении указателя мыши на любой из этапов можно получить о нем дополнительную информацию (рис. 11.15).

    Рис. 11.15. План выполнения запроса в SQL Server Management Studio

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

    q вначале в окне Management Studio выполните команду SET STATISTICS IO ON . В результате после каждого выполнения запроса будет выводиться дополнительная информация. В ней нас интересует значение только одного параметра — Logical Reads . Этот параметр означает количество логических чтений при выполнении запросов, т. е. сколько операций чтения пришлось провести при выполнении данного запроса без учета влияния кэша (количество чтений и из кэша, и с диска). Это наиболее важный параметр. Количество физических чтений (чтений только с диска) — информация не очень представительная, поскольку зависит от того, были ли перед этим обращения к данным таблицам или нет. Статистика по времени также является величиной переменной и зависит от других операций, которые выполняет в это время сервер. А вот количество логических чтений — наиболее объективный показатель, на который в наименьшей степени влияют дополнительные факторы;

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

    Хинтов оптимизатора в SQL Server 2005 предусмотрено много. Прочитать информацию о них можно в Books Online (в списке на вкладке Index (Индекс) нужно выбрать Query Hints [ SQL Server ] (Хинты запросов [ SQL Server ]), Join Hints (Хинты джойнов) или Table Hints [ SQL Server ] (Табличные хинты [ SQL Server ])). Чаще всего используются следующие хинты:

    q NOLOCK , ROWLOCK , PAGLOCK , TABLOCK , HOLDLOCK , READCOMMITTEDLOCK , UPDLOCK , XLOCK — эти хинты используются для управления блокировками (см. разд. 11.5.7);

    q FAST количество_строк — будет выбран такой план выполнения запроса, при котором максимально быстро будет выведено указанное количество строк (первых с начала набора записей). Если пользователю нужны именно первые записи (например, последние заказы), то для их максимально быстрой загрузки в окно приложения можно использовать этот хинт;

    q FORCE ORDER — объединение таблиц при выполнении запроса будет выполнено точно в том порядке, в котором эти таблицы перечислены в запросе;

    q MAXDOP (от Maximum Degree of Parallelism — максимальная степень распараллеливания запроса) — при помощи этого хинта указывается максимальное количество процессоров, которые можно будет использовать для выполнения запроса. Обычно этот хинт используется в двух ситуациях:

    · когда из-за переключения между процессорами ( context switching ) скорость выполнения запроса сильно снижается. Такое поведение было характерно для SQL Server 2000 на многопроцессорных системах;

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

    q OPTIMIZE FOR — этот хинт позволяет указать, что запрос оптимизируется под конкретное значение передаваемого ему параметра (например, под значение фильтра для WHERE );

    q USE PLAN — это самая мощная возможность. При помощи такого хинта можно явно определить план выполнения запроса, передав план в виде строкового значения в формате XML . Хинт USE PLAN появился только в SQL Server 2005 (в предыдущих версиях была возможность явно определять планы выполнения запросов, но для этого использовались другие средства). План в формате XML можно написать вручную, а можно сгенерировать автоматически (например, щелкнув правой кнопкой мыши по графическому экрану с планом выполнения, представленному на рис. 11.15, и выбрав в контекстном меню команду Save Execution Plan As (Сохранить план выполнения как)).

    В SQL Server 2005 появилась новая важная возможность, которая позволяет вручную менять план выполнения запроса без необходимости вмешиваться в текст запроса. Очень часто бывает так, что код запроса нельзя изменить: он жестко «прошит» в коде откомпилированного приложения. Чтобы справиться с этой проблемой, в SQL Server 2005 появилась хранимая процедура sp_create_plan_guide . Она позволяет создавать так называемые руководства по планам выполнения ( plan guides ), которые будут автоматически применяться к соответствующим запросам.

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

    q насколько часто в планах выполнения запроса встречается операция Table Scan (Полное сканирование таблицы). Вполне может оказаться, что обращение к таблице при помощи индексов будет эффективнее;

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

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

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

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

    q если приложение передает на сервер команды EXECUTE , то имеет смысл подумать о том, чтобы заменить их на вызов хранимой процедуры sp_executesql . Она обладает преимуществами в производительности по сравнению с обычной командой EXECUTE ;

    q повышения производительности иногда можно добиться, устранив необходимость повторной компиляции хранимых процедур и построения новых планов выполнения запросов. Нужно обратить внимание на применение параметров, постараться не смешивать в коде хранимой процедуры команды DML и DDL и следить за тем, чтобы параметры подключения SET ANSI_DEFAULTS , SET ANSI_NULLS , SET ANSI_PADDING , SET ANSI_WARNINGS и SET CONCAT_NULL_YIELDS_NULL не изменялись между запросами (любое изменение таких параметров приводит к тому, что старые планы выполнения считаются недействительными). Обычно проблема может возникнуть тогда, когда эти параметры устанавливаются на уровне отдельного запроса или в коде хранимой процедуры.

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

    5.2. Оптимизация SELECT и других запросов
    5.2.4. Как MySQL оптимизирует выражения WHERE

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

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

    Ниже перечислены некоторые из оптимизации, выполняемых MySQL:

    Удаляются ненужные скобки:

    Константы заменяются значениями:

    Удаляются условия для констант (требуется при замене констант значением):

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

    Для таблиц HEAP и MyISAM функция COUNT(*) , которая вызывается для одной таблицы и не содержит предложения WHERE , берется непосредственно из табличной информации. Это делается также для любого выражения NOT NULL , в котором используется только одна таблица.

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

    Выполняется слияние выражения HAVING с WHERE , если не используется предложение GROUP BY или групповые функции ( COUNT(), MIN(). ).

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

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

    Пустая таблица или таблица с 1 строкой.

    Таблица, которая используется с выражением WHERE для индекса UNIQUE , или PRIMARY KEY , где все части индекса используются с константными выражениями и части индекса определены как NOT NULL .

    Все эти таблицы используются как константные таблицы:

    Лучшая комбинацию связывания для связывания таблиц находится путем испытания всех возможных вариантов. Если все столбцы в предложениях ORDER BY и GROUP BY принадлежат одной таблице, эта таблица рассматривается первой при связывании.

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

    Если используется SQL_SMALL_RESULT , MySQL будет применять временную таблицу, которую разместит в памяти.

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

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

    Перед выводом каждой записи пропускаются те, которые не соответствуют выражению HAVING .

    Вот некоторые примеры очень быстрых запросов:

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

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

    Назад Начало Главы Начало Раздела Вперед
    Пред. Глава След. Глава
    Глава 4. Администрирование баз данных Начало Книги Глава 6. Справочник по языку MySQL

    Если Вы не нашли что искали, то рекомендую воспользоваться поиском по сайту:

    Скахин Алексей / pihel

    Личный блог. Заметки о программировании и не только

    Страницы

    суббота, 1 декабря 2012 г.

    Советы по оптимизации SQL запросов

    Поделюсь опытом, который получил за несколько лет оптимизации sql запросов. Большая часть советов касается субд ORACLE.
    Если кому статья покажется слишком очевидной, то считайте это заметкой чисто для себя, чтобы не забыть.

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

    2. Выбор IN или EXISTS ?
    На самом деле это сложный выбор и правильное решение можно получить только опытным путем.
    Я дам только несколько советов:
    * Если в основной выборке много строк, а в подзапросе мало, то ваш выбор IN. Т.к. в этом случае запрос в in выполнится один раз и сразу ограничит большую основную таблицу.
    * Если в подзапросе сложный запрос, а в основной выборке относительно мало строк, то ваш выбор EXISTS. В этом случае сложный запрос выполнится не так часто.
    * Если и там и там сложно, то это повод изменить логику на джойны.

    3. Не забывайте про индексы
    Совет для совсем новичков: вешайте индексы на столбцы по которым джойните таблицы.

    4. По возможности не используйте OR.
    Проведите тесты, возможно UNION выглядит не так элегантно, за то запрос может выполнится значительно быстрей. Причина в том, что в случае OR индексы почти не используются в join.

    5. По возможности не используйте WITH в oracle.
    Значительно облегчает жизнь, если запрос в with необходимо использовать несколько раз ( с хинтом materialize ) в основной выборке или если число строк в подзапросе не значительно.
    Во всех других случаях необходимо использовать прямые подзапросы в from или взаранее подготовленную таблицу с нужными индексами и данными из WITH.
    Причина плохой работы WITH в том, что при его джойне не используются ни какие индексы и если данных в нем много, то все встанет. Вторая причина в том, что оптимизатору сложно определить сколько данных нам вернет with и оптимизатор не может построить правильный план запроса.
    В большинстве случаев WITH без +materialize все равно будет развернут в основной запрос.

    6. Не делайте километровых запросов
    Часто в web обратная проблема — это много мелких запросов в цикле и их советуют объединить в один большой. Но тут есть свои ограничения, если у вас запрос множество раз обернутый в from, то внутреннюю(ие) части надо вынести в отдельную выборку, заполнить временную таблицу, навесить индексы, а потом использовать ее в основной выборке. Скорость работы будет значительно выше (в первую очередь из-за сложности построения оптимального плана на большом числе сочетаний таблиц)

    7. Используйте KEEP взамен корреляционных подзапросов.
    В ORACLE есть очень полезные аналитические функции, которые упростят ваши запросы. Один из них — это KEEP.
    KEEP позволит сделать вам сортировку или группировку основной выборки без дополнительно запроса.
    Пример: отобрать контрагента для номенклатуры, который раньше остальных был к ней подвязан. У одной номенклатуры может быть несколько поставщиков.
    При обычном бы подходе пришлось бы делать корреляционный подзапрос для каждой номенклатуры с выбором минимальной даты.
    Но не злоупотребляйте большим числом аналитических функций, особенно если они имеют разные сортировки. Каждая разная сортировка — это новое сканирование окна.

    8. Гуляние по выборке вверх-вниз
    Менее популярная функция, но не менее полезная. Позволяет смещать текущую строку выборки на N элементов вверх или вниз. Бывает полезно, если необходимо сравнить показатели рядом стоящих строк.
    Следующий пример отбирает продажи департаментов отсортированных по дате. К основной выборке добавляются столбцы со следующим и предыдущим значением выручки. Второй параметр — это на сколько строк сместиться, третьи — параметр по-умолчанию, если данные соседа не нашлись. При обычном подходе бы пришлось это делать через логику приложения.

    9. Direct Path Read
    Установка этой настройки (настройкой или параллельным запросом) — чтение данных напрямую в PGA, минуя буферный кэш. Что укоряет последующие этапы запроса, т.к. не используется UNDO и защелки совместного доступа.

    10. Direct IO
    Использование прямой записи/чтения с диска без использования буфера файловой системы (файловая система конкретно для СУБД).
    * В случае чтения преимущество в использовании буферного кэша БД, замен кэша ФС (кэш бд лучше заточен на работу с sql)
    * В случае записи, прямая запись гарантирует, что данные не потеряются в буфере ФС в случае выключения электричества (для redolog всегда использует fsync, в не зависимости от типа ФС)

    Илон Маск рекомендует:  Java приложение новое в java 1 1
  • Понравилась статья? Поделиться с друзьями:
    Кодинг, CSS и SQL