Execution Plans
With the release of SQL Server 7.0, Microsoft changed the way execution plans were used and maintained in memory. With SQL Server 6.5 and earlier, the chances of an execution plan being reused were pretty slim. This caused some extra overhead because SQL Server would have to reevaluate the stored procedure every time it was executed. With SQL Server 7.0 and beyond, execution plans have a much higher chance of being reused.
As with previous versions of SQL Server, after the memory for system data structures is allocated from the overall amount of memory, the remaining memory is divided into two portions:
A percentage of the memory is allocated for use as procedure cache, which is used for storing execution plans.
The other portion ...
Get Writing Stored Procedures for Microsoft SQL Server 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.
