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."
