Plan caching and reuse

As we have now established, the process of optimizing a query can consume a large quantity of resources and take a significant amount of time, so it makes sense to avoid that effort if possible whenever a query is executed. SQL Server caches nearly every plan that is created so that it can be reused when the same query is executed again. But not all execution plans are eligible for caching. For example, no DDL statements are cached, such as CREATE TABLE. As for DML statements, most simple forms that only have one possible execution plan are also not cached, such as INSERT INTO … VALUES.

There are several different methods for plan caching. The method we will use is typically based on how the query is called from the client. The different methods of plan caching that will be covered in this section are the following:

• Stored procedures
• Ad hoc plan caching
• Parameterization (Simple and Forced)
• The sp_executesql procedure
• Prepared statements