I know that SQL Server 2005 lets me choose forced parameterization and use plan guides to change individual query behavior. How do I decide when to use these features?
Customers and colleagues who know me well can predict that my initial answer to almost any performance-tuning question is,"It depends," and this question is no different. I can't give you a comprehensive list of when, where, and how to override the query optimizer. But here are some examples of when to use forced parameterization for an entire database or force the optimizer to choose a particular query plan.
I'll start by saying that the optimizer is almost always smarter than you or me. Occasionally, the optimizer makes a poor choice. But in most cases, the optimizer will choose a great plan; trying to outguess the optimizer for most queries is futile.
But sometimes an application is written in a way that's simply inefficient—and you might not be able to change the application code, especially when working with an ISV application. For example, an ISV might write an application for multiple database back ends but might not fully leverage SQL Server stored procedures. Recently, I was helping a SQL Server 2000 customer to uncover the cause of poor database performance. I determined that part of the problem was that the application was issuing a large number of ad hoc SQL statements that couldn't be auto-parameterized effectively with SQL Server 2000 rules. The queries would have benefited from plan reuse because the large number of ad hoc SQL statements was causing the number of entries in syscacheobjects to increase dramatically.The excessive number of ad hoc statements degraded compilation times across the board.
It's hard to get ISVs to rewrite applications, and having the ISV rewrite the ad hoc SQL statements as stored-procedure calls wasn't an option for my client. In this scenario, SQL Server 2005 forced parameterization with plan guides would have let me tune the application by forcing the ad hoc queries to reuse an efficient plan.Will using the forced parameterization setting be a panacea for all applications that don't use stored procedures? Of course not. Some query workloads simply don't lend themselves to aggressively reusing a plan. But in cases such as this one, in which excessive compilations resulted when stored procedures weren't used, performance problems are often related to a relatively small number of distinct types of queries.You might not want to use forced parameterization for all queries, but using plan templates in a targeted manner can be beneficial.
I suspect that forced parameterization with plan guides will be most commonly used in scenarios similar to the previous example. However, sometimes a well-designed application has a few examples of queries for which the optimizer simply doesn't generate a great plan. Using plan guides selectively might be a powerful tuning tool in such cases, although this strategy is best pursued by DBAs and developers who have significant experience in performance tuning. Like I said, more often than not, the optimizer is smarter than we are.
