Controlling Parameterization

SQLServer 2005 provides a feature that lets the optimizer choose to treat constants in queries as parameters and reuse the same plan—that is, autoparameterize queries. Although SQL Server is generally quite conservative in its use of this feature, it provides tuning options that you can use to control the degree of autoparameterization that SQL Server uses in your queries. Let's look more closely at these PARAMETERIZATION hints and how you can determine when using autoparameterization will or won't benefit a query's performance.

Parameterization Limitation

These hints, as well as a related database setting to control parameterization, control how aggressively SQL Server will autoparameterize your queries. By default, SQL Server is conservative about deciding to treat constants in your queries as parameters and reusing the same plan. In general, being conservative is a good idea because the extra execution time needed to run an inappropriate plan is usually far more costly than the small amount of extra time needed to recompile a query.

Let's look at an example of how to work with the simple parameterization hint, using the Sales.SalesOrderDetail table in the AdventureWorks database. In the table, there's a nonclustered index on ProductID. As you know, a nonclustered index is useful when searching for a small number of rows, but (in general) not useful when searching for a large number. (Of course, what constitutes a "large number" depends on many factors, which are beyond the scope of this column.) Run the code in Listing 1 to execute two queries searching for different ProductID values, and examine the query plans and the number of logical reads required. But before you do so, make sure your AdventureWorks database is set to simple parameterization (I'll tell you why a bit later):

ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE;

After you run Listing 1, notice that the first SELECT used the nonclustered index on ProductID for a seek operation; on my server, the SELECT required 145 reads to return 44 rows. The second SELECT performed a clustered index scan (which is basically a table scan) and on my server required 1,238 reads to return 3,007 rows. If you doubted the choice of plan for the second SELECT and wondered why the optimizer didn't use the nonclustered index, you could try forcing the index by running the code in Listing 2.

You should notice much worse performance when forcing the index. For the same 3,007 rows, SQL Server now requires 9,226 reads. Because the appropriateness of a nonclustered index on nonunique values depends heavily on the actual values being referenced, SQL Server won't autoparameterize queries such as those in Listing 1. Each time you run a query with a different actual value, SQL Server compiles and optimizes a new plan.

Parameterization by Default

However, for some queries, the execution plan might continue to be useful even as the actual values change. To demonstrate how SQL Server might choose to autoparameterize by default, run the queries in Listing 3. These queries search for two different pairs of values for SalesOrderID and SalesOrderDetailID in the Sales.Sales OrderDetail table.

In this case, because there's a unique index on (SalesOrderID, SalesOrderDetailID), at most one row will be returned for each query, and SQL Server will use the same plan for each and perform the same number of logical reads. Because the same plan can be used no matter what the actual values are, SQL Server will choose to autoparameterize by default. You can verify this by running the code in Listing 4 to examine the sys .syscacheobjects view. You should see a value in the sql column similar to the output in Web Table 1. The parameter markers @0 and @1 indicate that the statement was autoparameterized.

Thus, SQL Server will autoparameterize by default when it can determine up front that the search will return no more than one row. SQL server won't autoparameterize when there's a chance of multiple rows being returned and only a nonclustered index is available for the search operation.

A number of other query elements preclude autoparameterization; that is, SQL Server will autoparameterize only the simplest queries. For a list of these elements, see "Query Elements That Preclude Autoparameterization."

Simple or Forced?

In SQL Server 2005, we refer to the default autoparameterization mechanism as simple parameterization. Although we certainly don't want SQL Server to reuse an inappropriate plan, you might have an application that uses constructs in the preceding list that could benefit from autoparameterization. For example, if I took the autoparameterized query in Listing 3 and expanded it to join the Sales.SalesOrderDetail table to the Sales.SalesOrderHeader table to obtain the order's OrderDate, only one plan would still be possible. However, since JOIN queries aren't autoparameterized, SQL Server won't autoparameterize the plan using simple parameterization.

Run the code in Web Listing 1, which will first clear your procedure cache (use the DBCC FREEPROCCACHE command on a test server only). The two queries each return a single row after joining Sales.SalesOrderDetail and Sales
.SalesOrderHeader. If you now rerun the query in Listing 4 to select from sys.syscacheobjects, you should notice that no queries are autoparameterized.

One way to enable queries such as the one in Web Listing 1 to be autoparameterized is to change the value of the PARAMETERIZATION database option, which is new in SQL Server 2005. You can set the PARAMETERIZATION option to either SIMPLE (the default) or FORCED. When the option is set to FORCED, any constant value that appears in a Data Manipulation Language (DML) statement (SELECT, INSERT, UPDATE, or DELETE) is converted to a parameter during compilation. There are a very few exceptions; to find a list of them, look up "forced parameterization" in SQL Server Books Online (BOL).

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.