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!
Awesome stuff, Greg! I can't wait to start promoting it. =^)
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.
Hi Gatej,
When you say that it's not the same, I'm not following what you're referring to exactly.
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
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
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
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!
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')
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
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
Thank you Greg looking forward to it 🙂
Thanks Greg. Look forward to the response 🙂
Hi Greg,
Anything back from the engine team?
Thanks
Still chasing – sent to another member today.
Thanks for chasing Greg
No update yet Greg?
Thanks
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.