THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

http://msdn.microsoft.com/en-us/library/dn148262.aspx

Enjoy!

 

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

Comments

 

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:

Hello,

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.

Thanks.

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:

Hello,

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:

DBCC FREEPROCCACHE;

GO

exec uspGetManagerEmployees 1

go

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...)

Thanks

June 15, 2013 10:34 AM
 

Gatej Alexandru said:

Hello,

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

Thanks,

Alex

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.

Regards,

Greg

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
 

Jacob said:

In this paper you suggest that cost is calculated as follows:

------------------------------------------------------------------------------

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.

------------------------------------------------------------------------------

When i join sys.dm_exec_cached_plans to sys.dm_os_memory_cache_entries on memory_object_address to view these plans it is hard to see how that 31 cost corresponds to the original cost value.

Given that the original cost value is a 32 bit signed integer, is there some kind of mapping between the cost calculated above (0 to 31 note 32 bits) and the integer original cost recorded?

The costs (stored) always seem to be powers of 2.

I have attempted to reverse engineer this, but plans with the same cost have different IO, CPU and memory page costs; it isn't a simple 2^n relationship.

Also I see plans with the maximum negative signed integer as well, what do they mean?

Query for Reference:

SELECT

db_name(st.dbid) [database_name]

   ,object_name(st.objectid, st.dbid) [OBJECT_NAME], objtype, refcounts, usecounts, cast((size_in_bytes/power(2.0,20)) as decimal(10,2)) [size_in_mb],

   disk_ios_count, context_switches_count,

   pages_kb as MemoryKB, original_cost, current_cost,

case when original_cost > 0 then log(original_cost,2)

else original_cost end as [lg_cost]

   --DOCUMENTED COST CALCULATION

,case when disk_ios_count > 19 then 19 else disk_ios_count end [disk_io_cost]

,case when context_switches_count > 8 then 8 else context_switches_count end [cpu_cost]

,case when (pages_kb/(8*16)) > 4 then 4 else (pages_kb/(8*16)) end [pages_cost]

, (case when disk_ios_count > 19 then 19 else disk_ios_count end )

  + (case when context_switches_count > 8 then 8 else context_switches_count end)

  + (case when (pages_kb/(8*16)) > 4 then 4 else (pages_kb/(8*16)) end) [documented_cost_calculation]

FROM sys.dm_exec_cached_plans p

CROSS APPLY sys.dm_exec_sql_text(plan_handle) st

JOIN sys.dm_os_memory_cache_entries e

ON p.memory_object_address = e.memory_object_address

WHERE cacheobjtype = 'Compiled Plan'

AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')

September 10, 2015 11:06 PM
 

Jacob said:

Edit my comment above was mistaken

Your whitepaper talks about the plan cost being calculated as follows:

------------------------------------------------------------------------------

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.

------------------------------------------------------------------------------

When I join sys.dm_exec_cached_plans to sys.dm_os_memory_cache_entries on memory_object_address to view the cost of my plans, I can see this maps to the documented cost n as 2^n for some plans, but not all.

I see plans with the maximum negative signed integer (-2,147,483,648) as well, what do they mean?

Is there any further information on the discrepancies?

Query for Reference:

SELECT

db_name(st.dbid) [database_name]

   ,object_name(st.objectid, st.dbid) [OBJECT_NAME], objtype, refcounts, usecounts, cast((size_in_bytes/power(2.0,20)) as decimal(10,2)) [size_in_mb],

   disk_ios_count, context_switches_count,

   pages_kb as MemoryKB, original_cost, current_cost,

case when original_cost > 0 then log(original_cost,2)

else original_cost end as [lg_cost]

   --DOCUMENTED COST CALCULATION

,case when disk_ios_count/2 > 19 then 19 else disk_ios_count/2 end [disk_io_cost]

,case when context_switches_count/2 > 8 then 8 else context_switches_count/2 end [cpu_cost]

,case when (pages_kb/(8*16)) > 4 then 4 else (pages_kb/(8*16)) end [pages_cost]

, (case when disk_ios_count/2 > 19 then 19 else disk_ios_count/2 end )

  + (case when context_switches_count/2 > 8 then 8 else context_switches_count/2 end)

  + (case when (pages_kb/(8*16)) > 4 then 4 else (pages_kb/(8*16)) end) [documented_cost_calculation]

FROM sys.dm_exec_cached_plans p

CROSS APPLY sys.dm_exec_sql_text(plan_handle) st

JOIN sys.dm_os_memory_cache_entries e

ON p.memory_object_address = e.memory_object_address

WHERE cacheobjtype = 'Compiled Plan'

AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')

and objtype = 'proc'

order by original_cost

September 10, 2015 11:48 PM
 

Greg Low said:

Hi Jacob, I'm chasing the guys from that part of the engine team to find out. I'll report back when I can find something from them.

Regards,

Greg

September 11, 2015 10:02 PM
 

Jacob said:

Thank you Greg looking forward to it :)

September 14, 2015 9:29 AM
 

Jacob said:

Thanks Greg. Look forward to the response :)

September 15, 2015 5:42 PM
 

Jacob said:

Hi Greg,

Anything back from the engine team?

Thanks

September 22, 2015 10:32 AM
 

Greg Low said:

Still chasing - sent to another member today.

September 22, 2015 7:07 PM
 

Jacob said:

Thanks for chasing Greg

September 24, 2015 5:52 PM
 

Jacob said:

No update yet Greg?

Thanks

October 19, 2015 5:42 PM
 

Greg Low said:

Two issues:

Apparently the cost exposed by the DMVs is now not the same as used for cache replacement policy.

It also changes over versions.

After discussing it with team members, haven't found a way to see the values used via the DMVs. Will update if I find out more. Team members that supplied initial values have now moved on.

October 20, 2015 11:14 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement