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.
Parameterizationn 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 (http://www.windowsitpro.com, InstantDoc ID 96349). 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 the Web-exclusive sidebar "Query Elements That Preclude Autoparameterization,"
http://www.sqlmag.com, InstantDoc ID 96378.
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 (http://
www.sqlmag.com, InstantDoc ID 96349), 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).
Prev. page
[1]
2
next page 
