Inside SQL Server: SQL Server 7.0 Plan Caching

SQL Server's ability to reuse compiled plans for stored procedures has been one of its most heavily marketed features since its first release. However, from a performance standpoint, reuse of compiled plans wasn't the most important reason for using stored procedures. In fact, the time saved by not needing to compile a new plan was often only a tiny percentage of the total processing time, especially for decision-support queries involving joins of large tables and multiple search conditions. SQL Server 7.0 changes that situation dramatically. The query optimizer has been completely rewritten and enhanced to provide dozens of new processing techniques. With more possibilities for the optimizer to consider, producing a query plan can take a large percentage of the overall processing time. In fact, for some queries, the time needed to compile and optimize the query can exceed the time needed to execute the query and return the results. Therefore, reusing a precompiled plan can produce big performance gains.

In all earlier releases of SQL Server, you could reuse only the plan for stored procedures. SQL Server 7.0 expands that capability by using four mechanisms—ad hoc caching, autoparameterization, the sp_executesql procedure, and the prepare and execute method—to save and reuse individual query plans.

Ad Hoc Caching
SQL Server caches plans from ad hoc queries, and if a subsequent query matches a previous one exactly, SQL Server uses the cached plan. This caching feature requires no extra work, but it is limited to exact textual matches. For example, if you submit the following three queries, the first and third use the same plan, but the second one generates a new plan.

SELECT count(*) FROM
	Northwind..products WHERE
	categoryID BETWEEN 1 and 9
go
SELECT count(*) FROM
	Northwind..products WHERE
	categoryID < 10
go
SELECT count(*) FROM
	Northwind..products WHERE
	categoryID BETWEEN 1 and 9
go

The simplest way to discover whether recompilation is taking place is to use the option SET STATISTICS TIME ON while you run the Query Analyzer to test queries. (For more information on optimizing queries, see Kalen Delaney, "SQL Server Statistics: A Useful Query Optimizer Tool," August 1999.) Before any SQL statement runs, this option tells you the time SQL Server needed to parse and compile the query. Keep in mind that query optimization is the most time-consuming part of compilation.

When I ran the above three queries after setting STATISTICS TIME ON, I got the results for parse-and-compile time that you see in Figure 1. The queries all return the same results, and the same rows satisfy all the queries. However, because the second query uses the less-than operator instead of BETWEEN, it doesn't match the other queries' text and the plan isn't reused. The second query had to recompile; you can see the value of 10 milliseconds for compile time. The third query is identical to the first and reuses the plan from the first query. You can see that its compile time is zero. (Make sure you run the three statements as separate batches, separated by go. If you submit the three statements to SQL Server as a single batch, they are all parsed together, and you don't get separate parse-and-compile data for each one.)

Note that if you want to run these tests a second time, you need to remove the plans from the cache, so you can start from scratch. You can clear all compiled plans from SQL Server's cache by executing the command DBCC FREEPROCCACHE. This command is undocumented and unsupported, and Microsoft doesn't guarantee its availability in future versions of the product. However, it's available in SQL Server 7.0. If you don't use this command, the only way to clear all plans from the cache is to stop and start your SQL Server.

Although the reuse of ad hoc plans can be a good thing, SQL Server might not always reuse the plans that you intend it to use, so I don't recommend that you plan your applications to take advantage of it. Keep in mind that for SQL Server to reuse an ad hoc plan, the query's text must be identical to the original's. You can't change the spacing, indentation, or line breaks. You can't substitute the double dot (..) for the database owner (DBO). Case differences, even on a case-insensitive server, mean that the query processor doesn't see an exact textual match.