Приветствую Вас на сайте Info-Comp.ru! В данном материале мы с Вами поговорим о том, что же такое хинты в языке T-SQL (Microsoft SQL Server), Вы узнаете, какие типы хинтов бывают, для чего и в каких случаях мы их можем использовать.
В предыдущих материалах мы с Вами рассмотрели архитектуру выполнения запроса в Microsoft SQL Server, план выполнения запроса, поговорили об операторах плана запроса, сегодня же мы поговорим о том, как мы можем влиять на процесс формирования плана выполнения запроса, а влиять на него мы можем как раз с помощью хинтов.
Полезные статьи:
Хинты в T-SQL
Хинт (hint) – это указание оптимизатору запросов, которое переопределяет его поведение по умолчанию на время выполнения SQL инструкции.
Иными словами, с помощью хинта мы можем сказать оптимизатору запросов, как именно поступить в той или иной ситуации в процессе построения плана запроса.
Например, мы можем сказать, какой конкретно индекс использовать, какой конкретно алгоритм физического соединения таблиц применить, или, допустим, на время выполнения запроса разрешить «грязное чтение».
Обычно оптимизатор запросов SQL Server выбирает самый оптимальный план выполнения запроса и крайне редко ошибается, поэтому использовать подсказки, т.е. хинты, без острой на то необходимости не рекомендуется.
Однако оптимизатор запросов все же может ошибиться или построить план, который нас будет не устраивать, и в этом случае мы можем подсказать оптимизатору с помощью хинтов, как лучше было бы поступить в той или иной ситуации.
Обязательно стоит отметить, при использовании хинтов Вы должны четко понимать, как изменится план выполнения запроса и как запрос с хинтом будет отрабатывать в случае изменения каких-либо факторов, которые влияют на процесс формирования плана. Например, что будет, если у нас объем данных в таблицах, участвующих в запросе, увеличится в несколько раз, т.е. в данном случае Вы должны понимать, не просядет ли производительность с нашим хинтом в случае увеличения объема данных, иными словами, наш хинт так же эффективно будет работать, как и с текущим объемом данных.
Примечание! Факторов, влияющих на построение плана выполнения запроса, на самом деле много, поэтому использование хинтов рекомендуется только опытным разработчикам и администраторам баз данных, да и то в самом крайнем случае, когда другого решения нет.
Типы хинтов в T-SQL
В Microsoft SQL Server хинты мы можем использовать в разных секциях запроса, и на основе этого можно выделить следующие типы хинтов.
Заметка! Как посмотреть план выполнения запроса в Microsoft SQL Server.
Join Hints
С помощью именно этого типа хинтов мы можем влиять на выбор алгоритма физического соединения таблиц.
Существуют следующие хинты:
- LOOP – указывает, что соединение будет выполнено с помощью алгоритма Nested Loops (вложенные циклы);
- HASH – указывает, что соединение будет выполнено с помощью алгоритма Hash Match (хэш-соединение);
- MERGE – указывает, что соединение будет выполнено с помощью алгоритма Merge (соединение слиянием);
- REMOTE – указывает, что соединение будет выполнено на стороне правой таблицы, если она расположена удаленно. Обычно это используется, когда таблица слева является локальной, а таблица справа удаленной и в ней гораздо больше строк, чем в левой таблице.
- Если правая таблица является локальной, соединение выполняется локально;
- Если обе таблицы являются удаленными, но из разных источников данных, REMOTE вызывает выполнение соединения на стороне правой таблицы;
- Если обе таблицы являются удаленными таблицами из одного источника данных, REMOTE не требуется;
- Аргумент REMOTE может быть использован только при операциях INNER JOIN.
Пример.
SELECT p.ProductID, p.Name, pr.Comments FROM Production.Product AS p INNER HASH JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID ORDER BY ProductReviewID DESC;
В этом примере с помощью хинта Hash мы принудительно задали тип физического соединения таблиц Hash Match.
Более подробно о типах физического соединения таблиц мы говорили в отдельном материале.
Заметка! Типы физического соединения таблиц в Microsoft SQL Server.
Query Hints
Хинты на уровне запросов распространяются на весь запрос. Иными словами, они влияют на все операторы в SQL инструкции.
Определяются такие хинты с помощью ключевого слова OPTION. В SQL запросе может быть определено только одно предложение OPTION, при этом в нем мы можем указать несколько хинтов.
Существует много различных хинтов, которые мы можем использовать на уровне запросов, например, у OPTION существуют следующие параметры (представлена лишь небольшая часть):
- FORCE ORDER – параметр указывает, что при оптимизации запроса сохраняется порядок соединения, заданный синтаксисом запроса;
- MAXDOP – переопределяет параметр конфигурации, задающий максимальный уровень параллелизма;
- MAXRECURSION – указывает максимальное число рекурсий, допустимых для рекурсивного запроса (по умолчанию сервером используется значение 100);
- OPTIMIZE FOR – указывает оптимизатору запросов, что при компиляции и оптимизации запросов нужно использовать конкретное значение для локальной переменной. Значение используется только в процессе оптимизации запроса, но не в процессе выполнения;
- RECOMPILE – параметр указывает SQL Server, что необходимо создать временный план для запроса, который будет удален сразу же после выполнения этого запроса. Без указания подсказки RECOMPILE SQL Server кэширует планы запросов и использует их повторно, иными словами, с параметром RECOMPILE план выполнения запроса будет строиться каждый раз при запуске запроса;
- QUERYTRACEON – данный параметр позволяет включить флаг трассировки, влияющий на план, только во время компиляции с одним запросом;
- TABLE HINT – применяет табличный хинт к таблице или представлению (табличные указания рекомендуется использовать в качестве подсказок в запросах только в контексте структуры плана);
- USE HINT – указывает дополнительные подсказки оптимизатору запросов.
Пример.
SELECT p.ProductID, p.Name, pr.Comments FROM Production.Product AS p INNER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID ORDER BY ProductReviewID DESC OPTION (RECOMPILE);
В этом запросе мы использовали хинт RECOMPILE.
Примечание!
- Если Вы укажете параметр, из-за которого оптимизатор запросов не сможет сформировать допустимый план запроса, возникает ошибка 8622.
- Если в основном запросе используется операция UNION, только последний запрос может содержать предложение OPTION.
Table Hints
Табличные хинты относятся только к таблицам и представлениям, для которых они указаны. Такие хинты предназначены для определения способа блокировки, указания конкретного индекса, способа обработки данных в таблицах, например, сканирование или поиск.
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
Вот несколько популярных табличных хинтов:
- INDEX – указывает индекс, который будет принудительно использован оптимизатором запросов при обработке инструкции;
- FORCESEEK – указывает, что в качестве пути доступа к данным таблиц или представлений оптимизатор запросов использует только операцию поиска в индексе;
- FORCESCAN – указывает, что в качестве пути доступа к данным или представлений оптимизатор запросов использует только операцию сканирования в индексе;
- ROWLOCK – указывает, что вместо блокировки страниц или таблиц применяются блокировки строк;
- HOLDLOCK – накладывает дополнительные ограничения на совмещаемую блокировку, в частности удерживает ее до завершения транзакции вместо снятия блокировки сразу после того, как таблица или страница данных больше не требуется. Иными словами, поведение становится как при уровне изоляции SERIALIZABLE. Хинт HOLDLOCK эквивалентен хинту SERIALIZABLE;
- NOLOCK – разрешает «грязное чтение». Поведение становится похоже на уровень изоляции READ UNCOMMITTED. Хинт NOLOCK равнозначен хинту READUNCOMMITTED;
- NOEXPAND – указывает, что при обработке запроса индексированное представление не расширяется для доступа к базовым таблицам. Оптимизатор запросов обрабатывает представление так же, как и таблицу с кластеризованным индексом. Данный хинт применяется только для индексированных представлений.
Табличные хинты задаются в предложении FROM с помощью ключевого слова WITH, которое мы указываем после названия таблицы или представления.
Пример.
SELECT p.ProductID, p.Name, pr.Comments FROM Production.Product AS p WITH (INDEX = [PK_Product_ProductID]) INNER JOIN Production.ProductReview AS pr WITH (NOLOCK) ON p.ProductID = pr.ProductID ORDER BY ProductReviewID DESC;
В данном примере для таблицы Production.Product мы указали хинт INDEX, с помощью которого задали конкретный индекс, а для таблицы Production.ProductReview мы указали хинт NOLOCK, который позволяет обрабатывать данную таблицу в режиме READ UNCOMMITTED, т.е. мы можем читать неподтверждённые данные (грязное чтение).
Заметка! Популярные хинты в T-SQL. Подсказки оптимизатору, которыми часто пользуются. ТОП 10
На сегодня это все, надеюсь, материал был Вам полезен, пока!
