Query Processing - Forced Parameterization

From SQLServerPedia

Jump to: navigation, search
QPFP Query Processing - Forced Parameterization n STOC50 See Also: Main_Page - Monitoring & Tuning - Query Processing


SQL Server can auto-parameterize some queries to aid with execution plan reuse. This process is called simple parameterization. However, with simple parameterization SQL Server can only parameterize a small subset of queries. You can use the ALTER DATABASE command to set parameterization option to FORCED and thereby advise SQL Server to parameterize all SELECT, INSERT, UPDATE and DELETE statements. For example, the following statement changes parameterization option to forced in the AdventureWorksDW database:

ALTER DATABASE AdventureWorksDW SET  PARAMETERIZATION FORCED


Alternatively you can set this option from the database properties dialog.



The performance of transactional applications with heavy volumes of data changes can benefit from forced parameterization by the virtue of increased reuse of query execution plans.



Parameterization takes place at individual statement level, so each batch can contain multiple parameterized statements. Forced parameterization causes every single literal passed to a query to be translated into a parameter during compilation. The exceptions to this rule include:

  • INSERT statements populating tables by executing stored procedures.
     
  • Any statements inside stored procedures, triggers and user-defined functions. Such statements don't need to be parameterized. The execution plans are already being reused.
     
  • Prepared statements will already be parameterized from the application.
     
  • Statements within server side cursors.
     
  • All statements when ANSI_PADDING or ANSI_NULLS is turned off.
     
  • SELECT statements that contain COMPUTE, RECOMPILE hint or OPTIMIZE FOR hint.
     
  • Statements that contain WHERE CURRENT OF clause.
     
  • Statements that reference over 2097 literals that qualify for parameterization.
     
  • Statements that reference variables.


You should also note that not all clauses of the parameterized statement can be parameterized. In particular the following portions of SQL statements CANNOT be parameterized:

  • SELECT list for any statement including sub-queries and the statement producing rows to be added using INSERT statement.
     
  • Sub-query statements within an IF statement.
     
  • TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, FOR XML.
     
  • OPENROWSET, OPENQUERY, OPENXML, OPENDATASOURCE or full-text operators.
     
  • Pattern characters such as '%' or '_' within LIKE.
     
  • The style argument of CONVERT function denoting the style of date and time value.
     
  • Identity seed and increment values.
     
  • Arguments to query hints, such as MAXDOP 1.


Forced parameterization can speed up some queries because it increases the likelihood of execution plan re-use. On the other hand, it can hinder performance of other queries because the query optimizer might not utilize the index on an indexed view or computed column since literals are translated into parameters. Furthermore, when you set the parameterization option to "forced", all execution plans that aren't currently compiling / recompiling / referenced are flushed out of the procedure cache. It is recommended to test the application thoroughly with forced parameterization turned on prior to using this option in production environment.

Retrieved from "http://sqlserverpedia.com/wiki/Query_Processing_-_Forced_Parameterization"