Microsoft® SQL Server 2012 Unleashed

The SQL Server Plan Cache

SQL Server uses the same buffer area for storing data and index pages as it does for storing query execution plans. The portion of the buffer pool used to store execution plans is referred to as the plan cache. The percentage of the memory pool allocated to execution plans fluctuates dynamically, depending on the state of the system. SQL Server also can keep execution plans in cache for ad hoc queries. This means that even dynamic SQL queries might be able to reuse a cached execution plan and skip recompilation. The cache space is dynamically allocated as needed.

With the ability to keep query plans in memory for ad hoc queries, it is not as critical in SQL Server 2012 for applications to use stored procedures to achieve ...