Plan Caching and Recompilation in SQL Server 2012 Whitepaper has been my favorite paper for a long time. Plan caching and recompilation are two of the best concepts which are explained in depth by my favorite author Greg Low. I have met Greg several times and I have been a big fan of his writing and ability to make complex very easy. The same white paper was earlier available for SQL Server 2005 and 2008.
This paper explains how SQL Server 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. The white paper explains SQL Server’s statement-level recompilation feature and many tools and utilities that are useful as observation tools in the processes of query compilation, query recompilation, plan caching, and plan reuse. (Abstract from whitepaper).
I strongly encourage everybody to read this whitepaper if you are interested in the performance tuning as well interested in learning about the essentials of the query optimization.
Download the Plan Caching and Recompilation in SQL Server 2012 Whitepaper.
Reference: Pinal Dave (https://blog.sqlauthority.com)
