WITH (I NDEX (О)). Эта подсказка должна находиться сразу же за псевдонимом таблицы в разделе FROM. Она заставляет SQL Server обращаться к этому псевдониму таблицы при помощи полного сканирования.

WITH (NOLOCK). Эта подсказка должна находиться сразу же за псевдонимом таблицы в разделе FROM. Она заставляет SQL Server считывать данные из таблицы с указанным псевдонимом, не применяя блокировок или каких-либо других мер для обеспечения непротиворечивости. Блокировки считывания в SQL Server могут создать узкое место , если их применять во время активного обновления таблицы. Эта подсказка предотвращает появление такой проблемы, возможно, за счет стоимости непротиворечивого представления данных на какой-то конкретный момент времени.

LOOP и HASH. Две этих подсказки могут находиться сразу же перед ключевым словом JOIN в разделе FROM. Они принуждают SQL Server выполнить указанное соединение указанным методом. Для того чтобы применять эти подсказки, необходимо использовать синтаксис соединения в новом стиле, с ключевым словом JOIN в разделе FROM. Если указана хотя бы одна подсказка этого типа, то все соединения будут выполнены в том же порядке, в каком псевдонимы перечислены в FROM.

OPTIONCLOOP JOIN). Эта подсказка находится в конце запроса и приказывает выполнять все соединения методом вложенных циклов.

OPTIONCFORCE ORDER). Эта подсказка находится в конце запроса и приказывает выполнять все соединения в том порядке, в котором в разделе FROM перечислены псевдонимы таблиц.

OPTION(FAST 1). Эта подсказка просто принуждает SQL Server попытаться выдать первые строки как можно быстрее, что обычно приводит к выбору плана выполнения с вложенными циклами. Она работает почти как подсказка ОРТ ION (LOOP JOIN), хотя теоретически SQL Server может понять, что никакой план выполнения не сможет быстро вернуть первые строки, если в запросе явно присутствует ORDER BY, сводящий на нет эффект OPTION (FAST 1). Подсказка OPTION (FAST 1) эквивалентна подсказке FIRST ROWS в Oracle и подсказке OPTIMIZE FOR 1 ROW в DB2.

Подсказки вполне можно комбинировать. Например, в одну фразу WITH можно вставить несколько подсказок, разделяя их запятыми: WITH (I NDEX (Empl oyee Fi rst Name). NOLOCK). Несколько подсказок во фразе OPTION также разделяются запятыми, например, OPTION(LOOP JOIN. FORCE ORDER). Вместе эти подсказки обеспечивают полный контроль над порядком соединения, методами соединения и методами доступа к таблицам.

Примеры подсказок

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

SELECT E.First Name, Е.Lastjame. Е.Salary. LE. Descri pti on. M.Fi rstjame. M. Last Jame. LM. Descri pti on

FROM Employees Е WITH (INDEX(Enployee Last Narae))

INNER JOIN Locations LE WITH (INDEX(Location PKey))

ON E.Location ID-LE.Location ID INNER JOIN Employees M WITH (INDEX(Employee PKey))

ON E.Manager ID-M.Employee ID INNER JOIN Locations LM WITH (INDEX(Location PKey)) ON M.Location ID-LM.Location ID WHERE E.Lastjame - Johnson AND LE.Description-Dallas OPTION(LOOP JOIN. FORCE ORDER)

SET SHOWPLANJEXT ON (as described in Chapter 3) generates the following results when you

run this query from SQL Server Query Analyzer:

StmtText

I--Bookmark Lookup(...(...[Locations] AS [LM])) I--Nested Loopsdnner Join)

-Bookmark Lookup(...(...[Employees] AS [M])) I--Nested Loopsdnner Join)

-Fi 1 ter(WHERE; ([LE]. [Descri pti on]- Dal 1 as))

I-Bookmark Lookup(...(...[Locations] AS [LE])) I--Nested Loopsdnner Join)

I--Bookmark LookupC...(...[Employees] (wrapped line) AS [E]))

I I I l-Index Seek(...(...

(wrapped line) [Employees].[EmployeejastJame] (wrapped line) AS [E]). SEEK:([E].[LastJame]-Johnson) ORDERED)

I I I-Index

(wrapped 1ine) Seek(...(,..[Locations],[Location PKey] (wrapped line) AS [LE]), SEEK:([LE].[Location ID]-[E].[Location ID]) (wrapped line) ORDERED)

I I --Index Seek(...(... [Enployees]. [EmployeeJKey]

(wrapped line) AS [M]). SEEK:([M].[Employee ID]-[E].[Manager ID]) ORDERED)

I--Index Seek(..(...[Locations].[Location PKey] (wrapped line) AS [LM]). SEEK:([LM].[Location ID]=[M].[Location ID]) (wrapped line) ORDERED)

(12 row(s) affected)

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

SELECT Е. First Jame, Е. Last Jame. Е. Salary. LE. Description,

M. First Jame. M. Lastjame. LM. Description FROM Employees E WITH (INDEX(Employee LastJame))

INNER HASH JOIN Locations LE WITH (INDEX(Locatlon Description))

ON E.Locat1on ID=LE,Location ID INNER LOOP JOIN Employees M WITH (INDEX(EmployeeJKey))

ON E.Manager ID-M.Employee ID INNER LOOP JOIN [.ocations LM WITH dNDEX(Lccation PKey)) ON M.Location ID-LM.Location ID WHERE E.Last Name = Johnson AND LE.Description-Dalias

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

при помощи SET SHOWPLANJEXT ON:

StmtText

I--Bookmark Lookup(...(...[Locations] AS [LM])) -Nested Loopsdnner Join)

--Bookmark Lookup(...(...[Employees] AS [M])) i--Nested LoopsCInner Join) I--Hash Matchdnner Join... (wrapped line) ([Е].[Location ID])-([LE].[Location ID])...)

I I i Bookmark LookupC...(.., [Enployees] AS [E]))

I i i I-Index

(wrapped line) Seek(..,(...[Employees].[Employee Last Name] (wrapped line) AS [E]). SEEK:([Е].[Last Name]-Johnson) ORDERED)

I I -Bookmark Lookup(...(... [Locations] AS [LE])) i I--Index

(wrapped line) Seek(...(...[Locations].[Location Description] (wrapped line) AS [LE]). SEEK:([LE].[Description]-Danas) ORDERED)

I I--Index Seek(...(...[Employees].[Employee PKey]

(wrapped line) AS [M]). SEEK:([М].[Employee ID]-[E].[Manager ID]) ORDERED)

I -Index Seek(...(... [Locations]. [Location PKey] (wrapped line) AS [LM]). SEEK:([LM].[Location ID]-[M].[Location ID]) (wrapped line) ORDERED)

(11 row(s) affected)

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

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

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

Если вы уверены в выбранном плане вьшолнения (а вы должны быть уверены, если применили все методы, которые я опишу далее в этой книге), то не имеет смысла полностью явно указывать план. Я пока что не сталкивался со случаем, когда хорошо выбранный надежный план вьшолнения требуется изменить, чтобы обработать данные с новым распределением или использовать новые возможности базы данных. С другой стороны, SQL, частично ограниченный набором подсказок, может выполняться неправильно, особенно если для некоторых таблиц или индексов статистика утеряна. Когда SQL Server делает неправильный выбор, то ошибка, которая заставила сделать его этот выбор, вероятнее всего портит весь план вьшолнения. Однако подсказка OPTION (FAST 1) - это инструкция, которая может оказаться полезной, даже если у SQL Server уже есть прекрасная информация. Она всего лишь указывает, что время получения первой строки намного важнее времени получения последней строки.