Принудительная параметризация
Можно переопределить простую параметризацию, используемую по умолчанию в 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.
Инструкции, содержащие предложение 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.
Дополнительные сведения о свертываемых выражениях см. в разделе Диагностика низкой производительности запросов. Cвертка констант и механизм вычисления выражений во время оценки мощности.
Аргументы для предложений подсказок в запросах. Сюда входит аргумент number_of_rows подсказки FAST, аргумент number_of_processors подсказки MAXDOP и аргумент number подсказки MAXRECURSION.
Параметризация происходит на уровне отдельных инструкций Transact-SQL. Иными словами, параметризуются отдельные инструкции в пакете. После компиляции параметризованный запрос выполняется в контексте пакета, в котором он был изначально заявлен. Если план выполнения для запроса кэширован, можно определить, был ли параметризован запрос, обратившись к столбцу sql в динамическом административном представлении sys.syscacheobjects. Если запрос параметризован, имена и типы данных аргументов располагаются перед текстом заявленного пакета в этом столбце, например (@1 tinyint). Дополнительные сведения о кэшировании плана запроса см. в разделе Кэширование и повторное использование плана выполнения.
.gif "Примечание") Примечание |
|---|
Имена аргументов произвольны. Пользователи или приложения не должны опираться на какой-либо конкретный порядок именования. Кроме того, в зависимости от версии 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 установлен в 80 или если база данных на экземпляре более ранней версии присоединена к экземпляру SQL Server 2005 или более поздней версии.
Текущая настройка параметра PARAMETERIZATION сохраняется при повторном присоединении или восстановлении базы данных.
Можно перекрывать поведение принудительной параметризации, предписав выполнение попытки простой параметризации для отдельного запроса, а также всех остальных запросов с таким же синтаксисом, отличающихся только значениями аргументов. Справедливо и обратное: можно потребовать выполнения попытки принудительной параметризации для отдельного набора синтаксически эквивалентных запросов, даже если принудительная параметризация в базе данных отключена. В этих целях используются структуры планов. Дополнительные сведения см. в разделе Указание механизма параметризации запросов с помощью структур плана.
| Примечание |
|---|
Если параметр PARAMETERIZATION имеет значение FORCED, то отчеты об ошибках могут отличаться от отчетов, формируемых при простой параметризации: число сообщений об ошибках в некоторых случаях больше, чем при простой параметризации, а номера строк ошибок могут быть выданы неверно. |