SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Методика, которая гарантирует в SQL Server 2000 выбор определенного плана исполнения запроса

ПУБЛИКАЦИИ  

По материалам статьи Ken Henderson: A technique for ensuring plan stability in SQL Server 2000
Перевод Ирины Наумовой

Одной из важнейших особенностей SQL Server является процессор оптимизации запросов. В Sybase версии SQL Server была реализована передовая технология оптимизации запросов "на лету", и, даже по настоящее время, встроенная оптимизация запроса - одна из самых полезных особенностей этого продукта. Таким образом, автоматически оптимизируя запросы конечных пользователей, SQL Server продолжает эту традицию отделения логической обработки запросов от физических операций, чем обеспечивает повторное использование планов запросов и лучшую гибкости их обработки в большинстве практических применений.
Однако, имеют место случаи когда некоторые из особенностей оптимизатора могут вызвать проблемы. Иногда он просто чересчур сильно вмешивается. Один из таких случаев - когда оптимизатор запросов автоматически использует значения параметра, переданного в хранимую процедуру для определения плана исполнения запроса. Т.н. прослушивание параметров полезно в большинстве случаев и такой подход позволяет оптимизатору принимать во внимание реальные значения параметров хранимой процедуры, поскольку планы выполнения запросов формируется в пределах процедуры, которая их использует. Другими словами, если Вы передаете значение "bar" для параметра, названного @foo, а затем используете @foo в предложении WHERE запроса внутри процедуры, оптимизатор достаточно эффективно оценит сколько строк в возвращаемом наборе будет соответствовать значению "bar" в фильтре WHERE, поскольку он создает план выполнения на основании самого запроса.
В то время как прослушивание параметров конечно достаточно мощная особенность SQL Server, но как раз он и может стать проблемой, в случае если план процедуры был удален из кэша процедур (или никогда не был помещен в кэш) до запуска процедуры с нетипичными значениями параметра. Это может привести к тому, что будет построен план, адаптированный к нетипичному использованию, является неоптимальным (или даже медленным) при выполнении процедуры с типичными значениями параметра. Единожды кэшируемый, план запроса может многократно использоваться для значений параметра, которые изменяются в достаточно большом диапазоне, идеальная ситуация - это когда имеется план в кэше, который охватывает использование процедуры с типичными значениями параметра в максимально возможной степени. Если план попал в кэш, который ориентирован на использование нетипичных значений параметра, это может привести к потере производительности.
Думаю, на примере это будет понятней. Предположим, что у нас есть хранимая процедура, которая возвращает данные о продажах по каждой стране. В нашем примере, три четверти всех продаж происходят в США. Процедура берет единственный параметр, @country, указывая страну, чтобы возвратить информацию о продажах. Этот параметр используется для того, чтобы отфильтровать данные, полученные с помощью простого SELECT, который возвращает информацию обо всех продажах.
Как правило, оптимизатор выбрал бы метод сканирования кластерного индекса при выполнении этого запроса, потому что (принимая то, что чаще всего для @country будет передаваться значение: "США") так или иначе, будет просмотрено много строк таблицы и сканирование кластерного индекса будет требовать меньшего количества операций ввода - вывода, и будет выполняться быстрее чем повторное сканирование не кластерного индекса. Однако, что случится, если план будет удален из кэша (скажем, из-за автоматического обновления статистики) до того как пользователь вызывал процедуру с параметром, скажем, "BEL" (Бельгия), где почти нет никаких продаж? Если предположить, что подходящий индекс существует, оптимизатор может решить использовать не кластерный индекс при создании нового плана исполнения запроса. Последующее выполнение процедуры будет многократно использовать этот план, даже если в качестве значения параметра @country будет передано "США". В результате это может привести к более медленному исполнению запроса, чем при использовании плана на основе сканирования таблицы.
Есть множество причин, по которым план выполнения запроса может быть удален из кэша, и эти причины не имеют ничего общего с деятельностью конечных пользователей. Это, вместе упомянутым выше прослушиванием параметров, может привести к непредсказуемой производительности выполнения хранимых процедур, параметры которых при вызове могут варьироваться в достаточно широком диапазоне.
В SQL Server 2005 уже имеются средства борьбы с этим, но в предыдущих версиях нет таких встроенных возможностей. Однако, есть довольно простая уловка, которую Вы можете использовать, чтобы управлять тем, какое значение параметра будет оценивать оптимизатор при создании плана выполнения запроса. Эта уловка работает на любой версии SQL Server, начиная с версии 7.0. Определение значений непосредственно в процедуре вводит в заблуждение оптимизатор и фактически отключает прослушивание параметров. Давайте рассмотрим следующий код:

CREATE PROC prSalesByCountry @country char(3), @template_country char(3)="USA" AS SET @template_country = @country SELECT *
FROM
sales
WHERE
country = @template_country GO

Эта методика действительно очень проста. Мы отфильтровываем результаты запроса по параметру @template_country, который мы в действительности никогда не передаем в процедуру. Мы всегда передаем в качестве значения фильтра @country, и в качестве значения по умолчанию для фильтра используем @template_country. Оптимизатор обращает внимание, что мы фильтруем запрос по таблице продаж, используя @template_country, и осуществляет оценку по значению этого параметра (которое всегда будет "США" во время компиляции, потому что мы его не меняем и "США" - его значение по умолчанию) при создании плана запроса.
Обратите внимание на повторное присваивание значения параметра @country, значению @template_country. Поскольку эта инструкция не выполняется, пока план не создан, оптимизатор не принимает это во внимание во время компиляции плана. Это - ключевой момент для понимания. Эта методика работает, потому что оптимизатор не может учесть повторное присваивание @country значения @template_country в процессе компиляции. Для оптимизатора, во время планирования инструкции SELECT, @template_country будет всегда иметь значение "США".
Таким образом, этот метод можно эффективно использовать для предотвращения прослушивания параметров и гарантии лучшей стабильности, по сравнению с причудами метода подбора параметра независимо от того, случается ли что-либо с кэшируемым планом через какой-то промежуток времени.
Конечно, этот метод хорош подходит для случаев с предсказуемыми значениями параметров, но что, если Вы хотите каждый раз получать разные планы при исполнении процедур с использованием нетипичных параметров? Для этого у Вас в распоряжении имеется несколько вариантов. Если компиляция Вашей процедуры не слишком дорогостоящая операция, Вы могли бы создать или выполнять процедуру, используя опцию WITH RECOMPILE. Также можно разбить типичные и нетипичные случаи на две различных процедуры так, чтобы они гарантированно получили свои собственные планы исполнения. Для реализации этого возможны несколько вариантов. Методика, приведенная выше, действительно предназначается для случаев, когда в чаще всего используется наиболее типичный параметр, где слишком часто происходит прослушивание параметров с повторным использованием плана, в комбинации с тем, что нельзя предсказать исполнение процедуры.
Этот подход был описан в моей последней книге The Guru's Guide to SQL Server Architecture and Internals . Для получения более подробной информации обратитесь к разделу " Parameter Sniffing" главы Query Processor этой книги.

[В начало]

Перевод: Ирины Наумовой  2005г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013