Принудительная параметризация

Изменения: 5 декабря 2005 г.

Можно переопределить простую параметризацию, используемую по умолчанию в SQL Server, указав, что все инструкции SELECT, INSERT, UPDATE и DELETE в базе данных должны быть параметризованы (с учетом некоторых ограничений). Принудительная параметризация активируется путем установки для параметра PARAMETERIZATION значения FORCED в инструкции ALTER DATABASE. Принудительная параметризация может улучшить производительность некоторых баз данных, сократив частоту выполнения компиляции и перекомпиляции запросов. Базы данных, которым может пойти на пользу принудительная параметризация, — это, как правило, те, которым приходится выполнять большое количество параллельных запросов из источников наподобие приложений торговых точек.

Если параметру PARAMETERIZATION присвоено значение FORCED, любое литеральное значение, представленное в инструкции SELECT, INSERT, UPDATE или DELETE, заявленной в любой форме, преобразуется в аргумент в процессе компиляции запроса. Исключениями являются литералы, представленные в следующих конструкциях запроса.

• Инструкции INSERT...EXECUTE.
• Инструкции в теле хранимых процедур, триггеров или пользовательских функций. SQL Server уже использует повторно планы запросов для этих подпрограмм.
• Подготовленные инструкции, которые уже были параметризованы приложением на стороне клиента.
• Инструкции, содержащие вызовы метода XQuery, где метод представлен в контексте, где его аргументы обычно параметризуются, например в предложении WHERE. Если метод представлен в контексте, где его аргументы не параметризуются, остальная часть инструкции будет параметризована.
• Инструкции внутри курсора Transact-SQL. (Инструкции SELECT внутри курсоров API-интерфейса параметризуются.)
• Устаревшие конструкции запроса.
• Любая инструкция, выполняемая в контексте ANSI_PADDING или ANSI_NULLS со значением OFF.
• Инструкции, содержащие более 2 097 литералов, пригодных для параметризации.
• Инструкции, ссылающиеся на переменные, такие как WHERE T.col2 >= @bb.
• Инструкции в запросе, содержащем подсказки RECOMPILE или OPTIMIZE FOR.
• Инструкции, содержащие предложение COMPUTE.
• Инструкции, содержащие предложение WHERE CURRENT OF.

Кроме того, в запросах не параметризуются следующие предложения (следует иметь в виду, что не параметризуются только предложения; другие предложения внутри того же запроса могут оказаться пригодными для принудительной параметризации).

• <select_list> в любой инструкции SELECT. Сюда входят списки SELECT во вложенных запросах и списки SELECT внутри инструкций INSERT.
• Инструкции SELECT во вложенных запросах, представленные внутри инструкции IF.
• Предложения запроса TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO или FOR XML.
• Аргументы, прямые или в качестве подвыражений, для OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML или для любого оператора FULLTEXT.
• Аргументы pattern и escape_character предложения LIKE.
• Аргумент style предложения CONVERT.
• Целочисленные константы внутри предложения IDENTITY.
• Константы, указанные использованием синтаксиса расширения ODBC.
• Свертываемые выражения, являющиеся аргументами операторов +, -, *, / и %. При определении пригодности для принудительной параметризации SQL Server рассматривает выражение как свертываемое, если верно хотя бы одно из следующих условий.
  • В выражении не представлены столбцы, переменные или подзапросы.
  • Выражение содержит предложение CASE.
    Дополнительные сведения о свертываемых выражениях см. в разделе Диагностика низкой производительности запросов: свертка констант и механизм вычисления выражений во время оценки мощности.
• Аргументы для предложений подсказок в запросах. Сюда входит аргумент number_of_rows подсказки FAST, аргумент number_of_processors подсказки MAXDOP и аргумент number подсказки MAXRECURSION.

Параметризация происходит на уровне отдельных инструкций Transact-SQL. Иными словами, параметризуются отдельные инструкции в пакете. После компиляции параметризованный запрос выполняется в контексте пакета, в котором он был изначально заявлен. Если план выполнения для запроса кэширован, можно определить, был ли параметризован запрос, обратившись к столбцу sql в динамическом административном представлении sys.syscacheobjects. Если запрос параметризован, имена и типы данных аргументов располагаются перед текстом заявленного пакета в этом столбце, например (@1 tinyint). Дополнительные сведения о кэшировании плана запроса см. в разделе Кэширование и повторное использование плана выполнения.

Примечание.
Имена аргументов произвольны. Пользователи или приложения не должны опираться на какой-либо конкретный порядок именования. Кроме того, в зависимости от версии SQL Server и пакетов обновления могут меняться имена параметров, выбор литералов, подлежащих параметризации, и разбивка параметризованного текста.

Типы данных аргументов

Когда SQL Server параметризует литералы, аргументы преобразовываются в следующие типы данных.

• Целочисленные литералы, размер которых в ином случае соответствовал бы типу данных int, параметризуются в int. Большие целочисленные литералы, являющиеся частью предикатов, которые включают в себя любой оператор сравнения (в том числе <, <=, =, !=, >, >=, !<, !>, <>, ALL, ANY, SOME, BETWEEN и IN), параметризуются в numeric(38,0). Большие литералы, не являющиеся частью предикатов, которые включают в себя операторы сравнения, параметризуются в numeric с точностью достаточно большой, чтобы поддержать их размер, и с масштабом 0.
• Числовые литералы с фиксированной запятой, являющиеся частью предикатов, которые включают в себя операторы сравнения, параметризуются в numeric с точностью 38 и масштабом достаточно большим, чтобы поддержать их размер. Числовые литералы с фиксированной запятой, не являющиеся частью предикатов, которые включают в себя операторы сравнения, параметризуются в numeric с точностью и масштабом достаточно большими, чтобы поддержать их размер.
• Числовые литералы с плавающей запятой параметризуются в float(53).
• Строковые литералы не в формате Юникод параметризуются в varchar(8000), если размер литерала не превышает 8 000 символов, и в varchar(max), если он больше 8 000 символов.
• Строковые литералы в формате Юникод параметризуются в nvarchar(4000), если размер литерала не превышает 4 000 символов, и в nvarchar(max), если литерал больше 4 000 символов.
• Двоичные литералы параметризуются в varbinary(8000), если размер литерала не превышает 8 000 байт. Если он больше 8 000 байт, он преобразуется в varbinary(max).
• Денежные литералы параметризуются в money.

Рекомендации по использованию принудительной параметризации

Устанавливая для параметра PARAMETERIZATION значение FORCED, примите во внимание следующие сведения.

• Принудительная параметризация, в сущности, преобразует литеральные константы в запросе в параметры при компиляции запроса. Следовательно, оптимизатор запросов может выбирать не самые оптимальные планы для запросов. В частности, уменьшается вероятность того, что оптимизатор запросов сопоставит запрос с индексированным представлением или индексом по вычисляемому столбцу. Он может также выбирать не самые оптимальные планы для запросов, ориентированных на секционированные таблицы или распределенные секционированные представления. Принудительная параметризация не должна использоваться в средах, в значительной степени опирающихся на индексированные представления и индексы по вычисляемым столбцам. Параметр PARAMETERIZATION FORCED должен использоваться только опытными администраторами баз данных и лишь после того, как будет определено, что такое использование не повредит производительности.
• Распределенные запросы, ссылающиеся на более чем одну базу данных, пригодны для принудительной параметризации, если параметр PARAMETERIZATION установлен на FORCED в базе данных, в контексте которой выполняется запрос.
• Установка параметра PARAMETERIZATION на FORCED производит очистку всех планов запросов из кэша планов в базе данных за исключением тех, которые компилируются, перекомпилируются или выполняются в настоящий момент. Планы для запросов, которые компилируются или выполняются в момент изменения настроек, параметризуются при следующем выполнении запроса.
• Настройка параметра PARAMETERIZATION выполняется в оперативном режиме и не требует монопольных блокировок на уровне базы данных.
• Принудительная параметризация отключена (установлена на SIMPLE), если уровень совместимости базы данных SQL Server 2005 установлен на 80, или если база данных на сервере более ранней версии присоединена к серверу SQL Server 2005.
• Текущая настройка параметра PARAMETERIZATION сохраняется при повторном присоединении или восстановлении базы данных.

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

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

См. также

Справочник

SQL Server, объект SQL Statistics

Основные понятия

Простая параметризация

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений