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)
1 Comment. Leave new
Hello,
I read the article and Is very interesting.
I don’t understand what is the relation between the formula:
Cost = I/O cost + context switch cost (a measure of CPU cost) + memory cost
The individual parts of the cost are calculated as follows.
• Two I/Os cost 1 tick, with a maximum of 19 ticks.
• Two context switches cost 1 tick, with a maximum of 8 ticks.
• Sixteen memory pages (128 KB) cost 1 tick, with a maximum of 4 ticks.
With the value of original_cost from sys.dm_os_memory_cache_entries?
It seems like this value are not correlated