Controlling Parameterization

Now I'll change my AdventureWorks database to use FORCED parameterization. To do so, change the PARAMETERIZATION option for AdventureWorks by running the following code, then rerun the code in Web Listing 1, followed by the code in Listing 4 to examine sys.syscacheobjects.

ALTER DATABASE AdventureWorks
  SET PARAMETERIZATION FORCED
  GO

You really don't need the DBCC FREEPROCCACHE command included at the beginning of Web Listing 1 this time, because the ALTER DATABASE command clears out all the plans for the database that has been altered. In the data from sys.syscacheobjects, you should see the autoparameterized form of the JOIN query and should also note that the usecounts value shows the plan was used twice, as Web Table 2 shows.

In this case, autoparameterization is a good thing, but for other queries it might not be. Leaving your database set to PARAMETERIZATION FORCED, rerun the code in Listing 1 to execute the queries referencing the ProductID column, which has the nonunique, nonclustered index. This time, even without the INDEX hint, the second query returns 3,007 rows and takes 9,226 reads. It's using the plan for the previous query that returned only 44 rows. Using the nonclustered index is a good plan for the query that returns 44 rows but not for the query that returns 3,007. When you specify PARAMETERIZATION FORCED, SQL Server will use the same plan for both queries.

Selective Parameterization
So what can you do if you have some queries that will benefit from forcing autoparameterization and others that need the more restrictive simple autoparameterization? SQL Server provides a new optimizer hint that lets you control a query's parameterization value, but you can use this hint only as part of a TEMPLATE plan guide. The procedure to create the plan guide takes a "template" as a parameter. Fortunately, SQL Server provides another procedure to take an ad hoc query with constants and create a template from it, so that all queries matching the template can have the same hint applied. The plan guides created from a template can use only the simple or forced parameterization hints. If your database is set to PARAMETERIZATION SIMPLE, you can create a template plan guide to use forced parameterization. If your database is set to PARAMETERIZATION FORCED, you can create a template plan guide to use simple parameterization.

The script in Web Listing 2 calls the procedure sp_get_query_template and passes in two variables as output parameters. One parameter will hold the parameterized version of the query; the other will hold the parameter list and the parameter data types. The code in Web Listing 2 then selects these two output parameters so that you can see their contents. (Of course, you can remove this SELECT from your own code.) Finally, Web Listing 2 calls the sp_create_plan_guide procedure, which instructs the SQL Server optimizer to use PARAMETERIZATION FORCED whenever it sees a query that matches this template. In other words, whenever SQL SERVER tries to optimize a query that parameterizes to the same form as the supplied query, it will use the same plan already cached.

After you've run the code in Web Listing 2 and created the plan guide, run the same statements as in Web Listing 1, and set the AdventureWorks database back to PARAMETERIZATION SIMPLE:

ALTER DATABASE AdventureWorks
  SET PARAMETERIZATION SIMPLE;
  GO

Note that the value of the @sample_statement parameter looks much like the parameterized query you saw in the sql column of syscacheobjects. You can also examine the plan cache again by running the code in Listing 4; you'll see that the parameterized form of the JOIN query has been run twice.

If you want to rerun these tests, you might want to drop the plan guide. You can do so by using the following statement:

EXEC sp_control_plan_guide
  N'drop', N'Template_Plan'

Making a Choice
SQL Server can autoparameterize queries when it determines that the constant values in the queries could be considered parameters. Autoparameterization means that queries that differ only in the constant values used in the WHERE clause will automatically reuse the same query plan. In some cases, this behavior is helpful; in other cases, it isn't. You can assess whether automatically reusing a query plan is beneficial by using the SET STATISTICS options and syscacheobjects view to observe the performance of autoparameterized queries.

You can use a hint in the TEMPLATE plan guide in SQL Server 2005 to override a database's PARAMETERIZATION setting. This hint allows SQL Server to autoparameterize individual classes of queries if the database is set to PARAMETERIZATION SIMPLE or alternatively, to make sure individual classes of queries aren't autoparameterized if the database is set to PARAMETERIZATION FORCED.

SQL Server is quite conservative in choosing which queries to autoparameterize, and in some cases, you might decide that it's being too conservative. Simple parameterization uses a very conservative strategy when deciding to parameterize your queries; forced parameterization uses a much less restrictive approach. So, by setting your database to PARAMETERIZATION FORCED, you can direct SQL Server to be aggressive about autoparameterizing queries. The more you know about query tuning and the usefulness of the different kinds of indexes, the better you'll be able to determine whether you should consider enabling autoparameterization.