SQL Server 2005 offers two options that help you optimize database performance:
the ability to choose forced parameterization at a database level or specify
within an sp_create_plan_guide stored procedure a plan guide that lets you pick
and choose which values will be parameterized.
If you're not familiar with the concept of auto-parameterization and how SQL
Server 2000 reuses query plans, you can find plenty of information on the Internet
and in SQL Server 2000 Books Online (BOL); you should at least read the
BOL topic "Auto-Parameterization," or the Microsoft article "Auto-Parameterization"
at http://
msdn.microsoft.com/library/default.asp?url =/library/en-us/architec/8_ar_sa_9r8u.asp
for a quick overview. In short, the query optimizer uses the auto-parameterization
tool to reuse plans for ad hoc SQL code that isn't submitted as a stored procedure.
The auto-parameterization process that Microsoft released in SQL Server 2000 can provide database performance boosts— especially on high-volume online transaction processing (OLTP) systems—by reducing the time and resource overhead associated with generating an execution plan.When the optimizer does a good job of selecting a query plan, it saves SQL Server the trouble of compiling a plan from scratch. However, it's dangerous for the optimizer to be too aggressive in making an "educated guess" about whether a query would benefit from an existing plan.When the optimizer guesses correctly, it saves the cost of compilation. With an incorrect guess, the query might be executed with an inefficient query plan.
With SQL Server 2005, you should be careful when you're using the default decision-processes
to auto-parameterize a query. SQL Server 2005 uses a new term—simple parameterization—to
refer to the feature that SQL Server 2000 users think of as autoparameterization.
However, SQL Server's ability to reuse plans in SQL Server 2005 is more robust
than in SQL Server 2000. This process is similar to the one SQL Server 2000
uses to auto-parameterize a query.You can override the simple-parameterization
process by changing the PARAMETERIZATION option to forced—simple is the
default—in the ALTER DATABASE TSQL statement. You can read the SQL Server
2005 BOL topic "ALTER DATABASE (Transact-SQL)"for more information. During
query compilation, changing the PARAMETERIZATION setting causes SQL Server to
convert to a parameter any literal value in a SELECT, INSERT, UPDATE,or DELETE
statement submitted in any form.The BOL topic "Forced Parameterization"at
http://msdn2.microsoft.com/
en-us/library/ms175037.aspx includes a list of exceptions to this rule.
I encourage you to review these exceptions.
Compared to simple parameterization, forced parameterization causes SQL Server
to be more aggressive when choosing how and when to create and reuse a plan,
but the risks associated with both types of parameterization are the same. Performance
is improved if SQL Server reuses plans that resulted from a previous query—assuming
the existing plan is an efficient execution strategy. However, using forced
parameterization and executing an inefficient query plan could result in degraded
database performance instead of an improvement.
Fortunately, SQL Server 2005 provides a tuning weapon—the sp_create_plan_guide
stored procedure—that lets you use a template plan guide for queries that
match certain-patterns. Using plan guides generally optimizes query performance.
In some cases, a DBA might know that a particular statement in a stored procedure
could cause SQL Server to choose a less than optimal plan. In such an instance,
the DBA can modify the statement to make the query plan run more efficiently.
For example, you can modify a statement in the sp_create_plan_guide stored procedure
to override SQL Server 2005's specified default behavior for the PARAMETERIZATION
option in the ALTER DATABASE statement. Thus, even when the PARAMETERIZATION
option is set to forced, you can specify that for a certain class of queries,
only simple parameterization is attempted, not forced parameterization.
You might be saying,"Wow, using the sp_create_plan_guide stored procedure seems
like a good idea, so show me some examples." You can find examples and more
detailed explanations in the following SQL Server 2005 BOL topics:"Simple
Parameterization," "Forced Parameterization," "Parameters and Execution Plan
Reuse," "Understanding Plan Guides," "Designing and Implementing Plan Guides,"
" Specifying Query Parameterization Behavior by Using Plan Guides," "Designing
Plan Guides for Parameterized Queries,""How SQL Server Matches Plan Guides to
Queries," and "Using SQL Server Profiler to Create and Test Plan Guides."
End of Article
