مشاركة عبر

  • مقالة

Forced Parameterization

You can override the default simple parameterization behavior of SQL Server by specifying that all SELECT, INSERT, UPDATE, and DELETE statements in a database be parameterized, subject to certain limitations. Forced parameterization is enabled by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement. Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. Databases that may benefit from forced parameterization are generally those that experience high volumes of concurrent queries from sources such as point-of-sale applications.

When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement, submitted in any form, is converted to a parameter during query compilation. The exceptions are literals that appear in the following query constructs:

  • INSERT...EXECUTE statements.

  • Statements inside the bodies of stored procedures, triggers, or user-defined functions. SQL Server already reuses query plans for these routines.

  • Prepared statements that have already been parameterized on the client-side application.

  • Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.

  • Statements inside a Transact-SQL cursor. (SELECT statements inside API cursors are parameterized.)

  • Deprecated query constructs.

  • Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF.

  • Statements that contain more than 2,097 literals that are eligible for parameterization.

  • Statements that reference variables, such as WHERE T.col2 >= @bb.

  • Statements that contain the RECOMPILE query hint.

  • Statements that contain a COMPUTE clause.

  • Statements that contain a WHERE CURRENT OF clause.

Additionally, the following query clauses are not parameterized. Note that in these cases, only the clauses are not parameterized. Other clauses within the same query may be eligible for forced parameterization.

  • The <select_list> of any SELECT statement. This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.

  • Subquery SELECT statements that appear inside an IF statement.

  • The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.

  • Arguments, either direct or as subexpressions, to OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, or any FULLTEXT operator.

  • The pattern and escape_character arguments of a LIKE clause.

  • The style argument of a CONVERT clause.

  • Integer constants inside an IDENTITY clause.

  • Constants specified by using ODBC extension syntax.

  • Constant-foldable expressions that are arguments of the +, -, *, /, and % operators. When considering eligibility for forced parameterization, SQL Server considers an expression to be constant-foldable when either of the following conditions is true:

    • No columns, variables, or subqueries appear in the expression.

    • The expression contains a CASE clause.

    For more information about constant-foldable expressions, see Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation.

  • Arguments to query hint clauses. These include the number_of_rows argument of the FAST query hint, the number_of_processors argument of the MAXDOP query hint, and the number argument of the MAXRECURSION query hint.

Parameterization occurs at the level of individual Transact-SQL statements. In other words, individual statements in a batch are parameterized. After compiling, a parameterized query is executed in the context of the batch in which it was originally submitted. If an execution plan for a query is cached, you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects dynamic management view. If a query is parameterized, the names and data types of parameters come before the text of the submitted batch in this column, such as (@1 tinyint). For information about query plan caching, see Execution Plan Caching and Reuse.

Note

Parameter names are arbitrary. Users or applications should not rely on a particular naming order. Also, the following can change between versions of SQL Server and service pack upgrades: Parameter names, the choice of literals that are parameterized, and the spacing in the parameterized text.

Data Types of Parameters

When SQL Server parameterizes literals, the parameters are converted to the following data types:

  • Integer literals whose size would otherwise fit within the int data type parameterize to int. Larger integer literals that are parts of predicates that involve any comparison operator (includes <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN, and IN) parameterize to numeric(38,0). Larger literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision is just large enough to support its size and whose scale is 0.

  • Fixed-point numeric literals that are parts of predicates that involve comparison operators parameterize to numeric whose precision is 38 and whose scale is just large enough to support its size. Fixed-point numeric literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision and scale are just large enough to support its size.

  • Floating point numeric literals parameterize to float(53).

  • Non-Unicode string literals parameterize to varchar(8000) if the literal fits within 8,000 characters, and to varchar(max) if it is larger than 8,000 characters.

  • Unicode string literals parameterize to nvarchar(4000) if the literal fits within 4,000 Unicode characters, and to nvarchar(max) if the literal is larger than 4,000 characters.

  • Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. If it is larger than 8,000 bytes, it is converted to varbinary(max).

  • Money type literals parameterize to money.

Guidelines for Using Forced Parameterization

Consider the following when you set the PARAMETERIZATION option to FORCED:

  • Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. Therefore, the query optimizer might choose suboptimal plans for queries. In particular, the query optimizer is less likely to match the query to an indexed view or an index on a computed column. It may also choose suboptimal plans for queries posed on partitioned tables and distributed partitioned views. Forced parameterization should not be used for environments that rely heavily on indexed views and indexes on computed columns. Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance.

  • Distributed queries that reference more than one database are eligible for forced parameterization as long as the PARAMETERIZATION option is set to FORCED in the database whose context the query is running.

  • Setting the PARAMETERIZATION option to FORCED flushes all query plans from the plan cache of a database, except those that currently are compiling, recompiling, or running. Plans for queries that are compiling or running during the setting change are parameterized the next time the query is executed.

  • Setting the PARAMETERIZATION option is an online operation that it requires no database-level exclusive locks.

  • Forced parameterization is disabled (set to SIMPLE) when the compatibility of a SQL Server database is set to 80, or a database on an earlier instance is attached to an instance of SQL Server 2005 or later.

  • The current setting of the PARAMETERIZATION option is preserved when reattaching or restoring a database.

You can override the behavior of forced parameterization by specifying that simple parameterization be attempted on a single query, and any others that are syntactically equivalent but differ only in their parameter values. Conversely, you can specify that forced parameterization be attempted on only a set of syntactically equivalent queries, even if forced parameterization is disabled in the database. Plan guides are used for this purpose. For more information, see Specifying Query Parameterization Behavior by Using Plan Guides.

Note

When the PARAMETERIZATION option is set to FORCED, the reporting of error messages may differ from that of simple parameterization: multiple error messages may be reported in cases where fewer message would be reported under simple parameterization, and the line numbers in which errors occur may be reported incorrectly.