|
|
По материалам статьи Robert Marda: Performance Adding Hints
Во время анализа возможностей повышения эффективности
исполнения запросов и хранимых процедур Вы можете обнаружить,
что план их исполнения, выбранный SQL Server, не является
наиболее лучшим планом. Часто, небольшие изменения в структуре
базы данных и/или запроса исправляют эту проблему. Но бывает,
когда такие Ваши действия не приводят к тому, что оптимизатор
SQL Server будет выбирать лучший путь исполнения вашего кода.
Это тот самый случай, когда подсказки (Hints или хинты) могут
помочь повысить эффективность исполнения запроса. Хинты
сообщают SQL серверу, чтобы он исполнял некоторые части Вашего
SQL-запроса тем путём, которым Вы считаете лучшим.
Подсказка оптимизатору использовать конкретный индекс полезна, когда необходимо вынудить 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 с результатом
объединения двух других таблиц.
Хинт 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) всегда помещается сразу после
имени таблицы или её псевдонима. 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. Однако, использование большинства хинтов должно быть ограничено, за исключением разве что хинта, который описан в третьем примере. Пытаться повысить эффективность с помощью хинта можно только тогда, когда не может быть найдено другое решение. |
Перевод: Александра Гладченко 2002г. |
