SQL SERVER – Plan Caching in SQL Server 2008 by Greg Low

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 (http://blog.sqlauthority.com)

About these ads

3 thoughts on “SQL SERVER – Plan Caching in SQL Server 2008 by Greg Low

  1. Pingback: SQL SERVER – Plan Caching and Schema Change – An Interesting Observation Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER – Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s