SQL SERVER – Plan Caching in SQL Server 2008

Plan Caching in SQL Server 2008
SQL Server Technical Article
Writer:Greg Low, SolidQ Australia
Technical Reviewers From Solid Quality Mentors: Andrew Kelly, Eladio Rincón, Itzik Ben-Gan
Technical Reviewers From Microsoft: Adam Prout, Campbell Fraser, Xin Zhang
Published:  August 2009

There are several goals of this white paper. This paper explains how SQL Server 2005 and SQL Server 2008 allocate memory for plan caching, how query batches are cached and suggests best practices on maximizing reuse of cached plans. It also explains scenarios in which batches are recompiled, and gives best practices for reducing or eliminating unnecessary recompilations and for minimizing plan cache pollution. The white paper explains SQL Server’s “statement-level recompilation” feature (first introduced in SQL Server 2005) and many tools and utilities that are useful as observation tools in the processes of query compilation, query recompilation, plan caching, and plan reuse.

This paper targets these audiences:

• Users: Persons who use, maintain, and develop applications for SQL Server. Users who are new to SQL Server 2008 and those who are migrating from SQL Server 2005 will find useful information here.
• Developers: SQL Server developers will find useful background information here.

This paper explains how SQL Server 2008 allocates memory for plan caching, how query batches are cached and suggests best practices on maximizing reuse of cached plans. It also explains scenarios in which batches are recompiled, and gives best practices for reducing or eliminating unnecessary recompilations and for minimizing plan cache pollution.

Read Plan Caching in SQL Server 2008 Whitepaper

Abstract courtesy : Microsoft

Reference : Pinal Dave (https://blog.sqlauthority.com)