THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Whitepaper: Plan Caching and Recompilations in SQL Server 2012

Hi Folks,

Over the last year, I've been working on an update to the whitepaper Plan Caching in SQL Server 2008 as a background task.

A great group of reviewers have been involved at various stages during the process. Thank you to Paul White, Andrew Kelly, Kalen Delaney, and Rubén Garrigós from the SQL community, along with Leigh Stewart and Jack Li from the SQL Server product team for sharing your knowledge and ideas.

Gail Erikson from the SQL documentation team let me know today that the latest version Plan Caching and Recompilation in SQL Server 2012 is now available:



Published Saturday, March 16, 2013 2:39 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



The Bit Bucket (Greg Low): IDisposable said:

Hi Folks, I posted a day or so back about the whitepaper on plan caching and recompilation being published.

March 19, 2013 2:13 AM

KKline said:

Awesome stuff, Greg! I can't wait to start promoting it. =^)

March 19, 2013 10:49 AM

Gatej Alexandru said:


I saw your article and is a very nice one. Congrats!

I have one question: This formula is Cost = I/O cost + context switch cost (a measure of CPU cost) + memory cost is not the same with the value of original_cost or current_cost of DMV sys.dm_os_memory_cache_entries. Can you please help me with the difference?

I have also an example I ran uspGetManagerEmployees from AdventureWorks db and is not the same.


April 7, 2013 10:32 AM

Greg Low said:

Hi Gatej,

When you say that it's not the same, I'm not following what you're referring to exactly.

April 25, 2013 9:11 PM

Gatej Alexandru said:


Sorry for coming so late but my comment was approaved very late, and also sorry for not providing an example.

Today I saw it.

Let me post my example:



exec uspGetManagerEmployees 1


Select top 1000 st.text, cp.objtype, cp.refcounts, cp.usecounts, cp.size_in_bytes,

ce.disk_ios_count, ce.context_switches_count,ce.pages_kb, --(or --ce.pages_allocated_count for above sql 2012) ,

ce.original_cost, ce.current_cost

from sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

join sys.dm_os_memory_cache_entries ce

on cp.memory_object_address = ce.memory_object_address

where cp.cacheobjtype = 'Compiled Plan'

--and (cp.objtype = 'Adhoc' or cp.objtype = 'Prepared')

order by cp.objtype desc, cp.usecounts desc

For the "select" I recieved this:

objtype refcounts usecounts size_in_bytes disk_ios_count context_switches_count pages_kb original_cost current_cost

Proc 2 1 131072 0 12 13 64 64

Adhoc 2 1 81920 0 0 4 0 0

I tried to understand how the original_cost is calculated based on the formula. I read the documentation for original cost and they say that is an approximation of the real cost. Is any dmv or something else to view exactly how the plan cost was calculated?

If you run the example again and you put the value 2 instead of 1 for @BusinessEntityID parameter you will have 256 instead of 64.(Because is generated another plan...)


June 15, 2013 10:34 AM

Gatej Alexandru said:


Someone point me an intersting example of auto-parametrization:

SELECT d.SalesOrderID, d.SalesOrderDetailID

FROM Sales.SalesOrderDetail d WITH(FORCESEEK)

WHERE d.ProductID = 800;

And you if you run a select on sys.dm_exec_sql_text you will see:

(@1 smallint)SELECT [d].[SalesOrderID],[d].[SalesOrderDetailID] FROM [Sales].[SalesOrderDetail] [d] WITH(forceseek)  WHERE [d].[ProductID]=@1  2013-06-13 08:49:39.967 1              

This is in contradiction of the exception in Appendix A: • When FROM clause has one of the following:o Table hints or index hints



June 15, 2013 10:54 AM

Greg Low said:

Hi Gatej, I don't believe the values that were used in the calculation are directly exposed anywhere. That's an interesting example with the auto-parameterization. I'll see if I can get someone in that part of the engine team to comment.



June 20, 2013 1:31 PM

Greg Low said:

Hi Gatej,

I've researched that and there is no longer a rule regarding index hints. Your example is correct. I've asked for that line in the whitepaper to be amended. Well researched!

August 2, 2013 10:58 PM

Leave a Comment


This Blog



No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement