WebClub - Всероссийский Клуб Веб-разработчиков
WebClub.RU » Советы » Повышение эффективности за счёт использования подсказок оптимизатору

Повышение эффективности за счёт использования подсказок оптимизатору


Дата публикации: 27-11-2010

Перевод:

Александра Гладченко

 

Во время анализа возможностей повышения эффективности исполнения запросов и хранимых процедур Вы можете обнаружить, что план их исполнения, выбранный SQL Server, не является наиболее лучшим планом. Часто, небольшие изменения в структуре базы данных и/или запроса исправляют эту проблему. Но бывает, когда такие Ваши действия не приводят к тому, что оптимизатор SQL Server будет выбирать лучший путь исполнения вашего кода. Это тот самый случай, когда подсказки (Hints или хинты) могут помочь повысить эффективность исполнения запроса. Хинты сообщают SQL серверу, чтобы он исполнял некоторые части Вашего SQL-запроса тем путём, которым Вы считаете лучшим.
Есть много вещей, которые влияют на то, как SQL Server выполняет SQL-запросы. Среди них: первичные ключи (или их отсутствие), индексы, объединения, число строк в таблицах, версия SQL Server, и аппаратные средства (один или более процессоров). Любое изменение у SQL Server может влиять на исполнение запроса или хранимой процедуры. Хинт, который Вы используете сегодня, из-за которого запрос выполняется пол секунды, может через несколько месяцев на том же самом запросе привести к его исполнению дольше 10 секунд. Это происходит потому, что Ваш SQL Server постоянно изменяется.
Как только Вы включаете в запрос хинты, SQL Server будет использовать их, даже если причина, из-за которой он выбирал худший план исполнения, исчезнет. В этой статье автор описывает использование некоторых хинтов, которые он считает полезными. Однако, он предостерегает Вас от не обдуманного использования хинтов, надеясь, что Вы будете использовать их только в случае крайней необходимости. Автор работал с SQL Server в течение приблизительно 3 лет, и использовал хинты только в трёх или четырёх хранимых процедурах из приблизительно 600, находящихся в промышленной эксплуатации.

Использование хинта Index

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

USE pubs
SELECT *
FROM authors WITH (INDEX(0))

Использование id равного 0, вынудит SQL Server использовать сканирование кластерного индекса, если он существует. Если кластерного индекса нет, тогда будет выполнено простое сканирование таблицы. Ниже представлен пример запроса, в котором вместо id используется имя индекса:

USE pubs
SELECT *
FROM authors WITH (INDEX(aunmind))

Хинт порядка объединения

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

Запрос №1:

USE pubs
SELECT *
FROM titles t
INNER JOIN roysched r ON t.title_id = r.title_id
INNER JOIN titleauthor ta ON t.title_id = ta.title_id
OPTION (FORCE ORDER)

Теперь выполните второй запрос (который является тем же самым запросом, но без использования хинта).

Запрос №2:

USE pubs
SELECT *
FROM titles t
INNER JOIN roysched r ON t.title_id = r.title_id
INNER JOIN titleauthor ta ON t.title_id = ta.title_id

Когда хинт удалён из запроса, объединение таблиц будет выполнено в другом порядке. Порядок, который использует SQL Server без хинта, окажется лучше. Он начинается с таблицы titleauthor, которая объединяется с таблицей titles. После этого следует объединение с таблицей roysched с результатом объединения двух других таблиц.
Быстрым способом увидеть то, какой из представленных выше запросов работает быстрее, является исполнение их обоих в одном окне Query Analyzer, и совместное рассмотрение их планов исполнения. Так Вы увидите, почему второй запрос работает быстрее, чем первый.

Как Вы видите, в первом запросе join таблиц titles и roysched даёт в результате 86 записей. Эти 86 записей используются для следующего вложенного цикла с join. Тут мы имеем 123 записи, которые составляют результат, представленный ссылкой выше. Во втором запросе, первый join таблиц titleauthor и titles даёт уже в результате 25 записей. Кроме того, отсутствуют промежуточные шаги перед объединением с третьей таблицей из 25 записей, после чего получается результирующий набор из 123 записей.
Второй запрос выполнился быстрее, потому что он имеет дело с меньшим количеством строк. Число строк, которые были задействованы, сильно зависит от порядка объединения таблиц между собой. Это демонстрирует важность порядка объединения таблиц. SQL Server рассматривает довольно много влияющих на объединение факторов, перед тем, как определить, какой порядок оптимально использовать при объединении таблиц. Это обычно: число строк в таблице, первичные ключи, индексы, число процессоров и текущую загрузку сервера, а иногда только некоторые из этих факторов. Если SQL Server вдруг разместит таблицы для join в неправильном порядке, попробуйте исправить эту проблему, определив более оптимальный порядок, используя хинт порядка объединения.

Хинт nolock для таблиц

Хинт Nolock является единственным хинтом, который автор использует почти для каждой таблицы, используемой в запросах с SELECT в хранимых процедурах, которую он использует. Этот хинт позволяет запросу исполняться даже в то время, когда осуществляется модификация таблицы, по которой идёт выборка. Это позволяет делать то, что называется "грязным чтением" данных (Dirty Read). Этот термин используется когда говорят, что получаемые данные ещё не прошли фазу фиксации и могут быть возвращены в предыдущее состояние. Автор использует эту подсказку, чтобы избежать блокировок на его сайте. Одна из причин, по которой это является для него приемлемым, это то, что большинство модификаций, выполняемых на сайте, затрагивает только одну запись, так что количество незафиксированных просматриваемых данных бывает очень маленьким. Ниже представлен пример использования хинта Nolock:


USE pubs
SELECT *
FROM titles t WITH (NOLOCK)
INNER JOIN roysched r WITH (NOLOCK) ON t.title_id = r.title_id

Предложение WITH (Nolock) всегда помещается сразу после имени таблицы или её псевдонима.
Представленный ниже запрос демонстрирует, как Вы можете выбирать данные без хинта nolock и просматривать эти данных до того, как они будут зафиксированы при использовании хинта Nolock. Выполните следующий запрос в отдельном окне Query Analyzer:


USE pubs
BEGIN TRANSACTION
UPDATE titles SET title = 'UNCOMMITTED' + title

Этот запрос установит исключительную блокировку на таблицу titles из-за чего невозможно будет выполнять в ней изменения, пока Вы не выполняете команду COMMIT TRANSACTION. Теперь в другом окне Query Analyzer, подключившись к тому же серверу, выполните следующий запрос:


USE pubs
SELECT *
FROM titles

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


USE pubs
SELECT *
FROM titles WITH (NOLOCK)

Теперь Вы сможете увидеть всю titles с добавленным словом UNCOMMITTED вначале поля. Далее нужно перейти в первое окно, и напечатать ROLLBACK TRANSACTION, а затем исполнить только эту строку. После этого возвратимся в другое окно, и выполним запрос без хинта Nolock. Вы увидите поля без слова UNCOMMITTED. Если использование грязного чтения для Вас допустимо, тогда автор рекомендует использовать хинт Nolock, всюду где только можно. Это сократит число блокировок. Если грязное чтение недопустимо, тогда Вы никогда не должны использовать этот хинт.

Заключение

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

Интересный сайт кулинарных лайфхаков для нее на планшете.
Домен продается

Популярное

Не так давно в сети появился новый сервис, под названием Dead Man Zero. Этот сервис сделал...
Рынок социальных площадок уже давно стал стабильным. Несмотря на то, что время от времени...
Artisteer 4 – единственный в своем роде продукт, позволяющий автоматизировать работу над созданием...
Октябрь 2018 (14)
Февраль 2017 (3)
Январь 2017 (1)
Август 2016 (1)
Май 2016 (2)
Ноябрь 2015 (1)

Карта сайта: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41

Друзья сайта



Случайная цитата

Неизвестный автор:

"Случайное красивым не бывает."

Опрос

Какими социальными сетями Вы пользуетесь?

Vkontakte.ru
Одноклассники
Мой Мир - mail.ru
Google Plus
Facebook
ЖЖ
Другие
Не пользуюсь