I’m a big advocate of trying to get as much query plan reuse as possible. By plan reuse, I’m talking about SQL Server’s ability to reuse an already existing plan in the procedure cache to run a newly executed query that’s similar in nature. Reusing an already compiled plan instead of compiling a completely new plan can save a considerable amount of time and resources.
The queries that most often don’t reuse an existing plan are those generated dynamically by the client or the middle tier of an application. These types of queries are referred to as ad hoc queries or ad hoc batches. Many of them don’t attempt to use parameters in any way and essentially hard-code all of the queries’ literal values. But the most common offense is not using parameters in the search arguments (SARGs) of WHERE clauses.
Fortunately, two SQL Server options can help alleviate some of the pains associated with lack of plan reuse caused by applications that weren’t written with plan reuse in mind. Before I tell you about these options, though, it’s helpful to know the basics about the procedure cache process.
Procedure Cache Basics
Each time SQL Server gets a request to run an ad hoc query, it must first look through the existing plans in the procedure cache to see if one already exists. After using a hash routine to narrow down the list, SQL Server ends up doing a character-by-character, case- and space-sensitive comparison of the new batch against existing batches. A single character or literal value difference will result in a mismatch. If SQL Server finds an exact match, it uses the existing plan to execute the new batch. If it doesn’t find an exact match, SQL Server parses and compiles a new plan and places that plan in the cache before it uses the new plan to execute the query. For more details on how this process works, see the excellent white paper “Plan Caching in SQL Server 2008”.
If there are lots of similar plans already in the procedure cache, the query lookup process can be quite resource intensive. So is compiling and optimizing a new plan for each batch that doesn’t have an exact match. This also results in yet another query plan placed into the cache, which uses up vital memory and potentially makes the next query lookup process even more resource intensive.
Option 1
By default, SQL Server tries to find a way to make ad hoc queries reuse previous plans, which is referred to as auto-parameterization. When a batch has been auto-parameterized, some or all of the statements’ literal values get replaced with a parameter. So, it essentially takes an ad hoc batch that looks like
SELECT * FROM Customers WHERE CustID = 652837;
SELECT * FROM Customers
WHERE CustID = 843762;
and turns it into
DECLARE @p1 INT; SET @p1 = 652837;
SELECT * FROM Customers
WHERE CustID = @p1;
DECLARE @p1 INT;
SET @p1 = 843762;
SELECT * FROM Customers
WHERE CustID = @p1;
(Note that if you were to run this ad hoc batch and view the auto-parameterized statements in SQL Server Profiler, they won’t be the same as shown here. I added the parameter assignments to better get the point across.) Now when SQL Server attempts to find a similar query plan in the cache, it looks at the parameterized version of the query (minus the parameter assignments). It’ll find a match each time a subsequent similar parameterized batch is called and reuse the previously existing plan. The result is a far more efficient use of resources and a better performing system overall.