Microsoft SQL Server 2008 R2 Unleashed- P132:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1254 CHAPTER 35 Understanding Query Optimization Query Plan Caching SQL Server 2008 has a pool of memory used to store both execution plans and data. The amount of memory allocated to execution plans or data changes dynamically depending on the needs of the system. The portion of memory used to store execution plans is often referred to as the plan cache. The first time a cacheable query is submitted to SQL Server the query plan is compiled and put into the plan cache. Query plans are read-only re-entrant structures shared by multiple users. At most there are two instances of a query plan at any time in the plan cache a serial execution plan and parallel query execution plan. The same parallel execution plan is used for all parallel executions regardless of the degree of parallelism. When you execute subsequent SQL statements the Database Engine first checks to see whether an existing execution plan for the same SQL statement already resides in the plan cache. If it finds one SQL Server attempts to reuse the matching execution plan thereby saving the overhead of having to recompile an execution plan for each ad hoc SQL statement issued. If no matching execution plan is found SQL Server is forced to generate a new execution plan for the query. The ability to reuse query plans for ad hoc queries in addition to caching query plans for stored procedures can help improve the performance for complex queries that are executed frequently because SQL Server can avoid having to compile a query plan every time it s executed if a matching query plan is found in memory first. Query Plan Reuse Query plan reuse for stored procedures is pretty straightforward. The whole idea behind stored procedures is to promote plan reuse. For stored procedures and triggers plan reuse is simply based on the procedure or trigger name. The first time a stored procedure is executed the query plan is generated based on the initial parameters. On subsequent executions SQL Server checks the plan cache
