You are a database developer on an instance of SQL Server 2008. Your Prod database contains many parameterized queries, and you have set the PARAMETERIZATION option to FORCED for the Prod database.
You have several stored procedures that execute a specific query. You want to use simple parameterization for the query, instead of forced parameterization.
Which action should you take?
A. Call the sp_get_query_template stored procedure and use the result to create a template plan guide.
B. Set the PARAMETERIZATION database setting to SIMPLE.
C. Create a SQL plan guide for the query.
D. Modify the PARAMETERIZATION option for the query.
Correct Answer: A
Explanation/Reference:
When the PARAMETERIZATION database option is set to SIMPLE (by default), the SQL Server query optimizer may choose to parameterize the queries. This means that any literal values that are contained in a query are substituted with parameters. This process is referred to as simple parameterization. When SIMPLE parameterization is in effect, you cannot control which queries are parameterized and which queries are not. However, you can specify that all queries in a database be parameterized by setting the PARAMETERIZATION database option to FORCED. This process is referred to as forced parameterization.
You can override the parameterization behavior of a database by using plan guides in the following ways:
• When the PARAMETERIZATION database option is set to SIMPLE, you can specify that forced parameterization is attempted on a certain class of queries. You do this by creating a TEMPLATE plan guide on the parameterized form of the query, and specifying the PARAMETERIZATION FORCED query hint in the sp_create_plan_guide stored procedure. You can consider this kind of plan guide as a way to enable forced parameterization only on a certain class of queries, instead of all queries.
• When the PARAMETERIZATION database option is set to FORCED, you can specify that for a certain class of queries, only simple parameterization is attempted, not forced parameterization. You do this by creating a TEMPLATE plan guide on the force-parameterized form of the query, and specifying the PARAMETERIZATION SIMPLE query hint in sp_create_plan_guide.
In a database in which forced parameterization is already enabled, you can make sure that the sample query, and others that are syntactically equivalent, except for their constant literal values, are parameterized according to the rules of simple parameterization. To do this, complete the following steps:
1. Retrieve the parameterized form of the query. The only safe way to obtain this value for use in sp_create_plan_guide is by using the sp_get_query_template system stored procedure.
2. Create the plan guide on the parameterized form of the query, specifying the PARAMETERIZATION SIMPLE query hint.
DECLARE @stmt nvarchar(max), @params nvarchar(max);
EXEC sp_get_query_template
N’SELECT pi.ProductID, SUM(pi.Quantity) AS Total
FROM Production.ProductModel AS pm
INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
@stmt OUTPUT, @params OUTPUT;
EXEC sp_create_plan_guide
N’TemplateGuide1′,
@stmt,
N’TEMPLATE’,
NULL,
@params,
N’OPTION(PARAMETERIZATION SIMPLE)’;
You should not create a SQL plan guide for the query. SQL plan guides are used with stand-alone Transact-SQL statements and batches.
You should not set the PARAMETERIZATION database setting to SIMPLE. This would cause all queries in the Prod database to use simple parameterization.
You should not modify the PARAMETERIZATION option for the query. The PARAMETERIZATION setting is specified at the database level, not the query level.
