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 2008 for applications to use stored procedures to achieve ...
Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
