November 18, 2015 at 8:31 pm
Hello!
Counter "SQLServer:Plan Cache - Cache Pages - _Total" shows real value?
Example:
SQL Server 2014 Enterprise
RAM: 128 Gb
Maximum server memory set to 102 Gb.
select
counter_value = cast(round(t.cntr_value * 8 / 1024.0, 2) as decimal(19, 2))
from
sys.dm_os_performance_counters as t
where
t.object_name = 'SQLServer:Plan Cache' and
t.counter_name = 'Cache Pages' and
t.instance_name = '_Total'
Result is about 8000
But if check this:
select
count(*) as count_t,
cast(round(sum(cast(decp.size_in_bytes as bigint))/1024.0/1024.0, 2) as decimal(19, 2)) as size_in_mb
from
sys.dm_exec_cached_plans as decp
Result is about 2000 Mb and 10 000 of plans.
After SQL Server restarted in sys.dm_exec_cached_plans I see 8000 Mb and 60 000 of plans. But in next days this values goes down.
What is it?
If it result of memory pressure why sys.dm_os_performance_counters doesn't change value?
November 19, 2015 at 12:11 am
You might need to see what is being allocated to the different plans. adhoc plans, procedures etc.
Baseline the results and see where they fluctuate. You can then see what is changing in the cache
-- http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
SELECT objtype AS [CacheType],
COUNT_BIG(*) AS [Total Plans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
AVG(usecounts) AS [Avg Use Count],
SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
ELSE 0
END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
SUM(CASE WHEN usecounts = 1 THEN 1
ELSE 0
END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
GO
November 19, 2015 at 5:02 am
OK.
Result is:
select
counter_value = cast(round(t.cntr_value * 8 / 1024.0, 2) as decimal(19, 2))
from
sys.dm_os_performance_counters as t
where
t.object_name = 'SQLServer:Plan Cache' and
t.counter_name = 'Cache Pages' and
t.instance_name = '_Total'
7720.59
SELECT objtype AS [CacheType],
COUNT_BIG(*) AS [Total Plans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
AVG(usecounts) AS [Avg Use Count],
SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
ELSE 0
END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
SUM(CASE WHEN usecounts = 1 THEN 1
ELSE 0
END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
GO
CacheTypeTotal PlansTotal MBsAvg Use CountTotal MBs – USE Count 1Total Plans – USE Count 1
Adhoc598824434.04208332366.88082848915
Prepared6470930.62500038414.3437503170
Proc1056879.67968734335.015625356
Trigger248131.859375920.86718755
View19927.406250121.03125016
Check150.43750010.39843713
UsrTab411.02343790.26562511
Sum of Total MBs is 6405.07
7720.59 in dm_os_performance_counters,
6405.07 in sys.dm_exec_cached_plans
Why such a difference?
After few days it will bee
7720.59 in dm_os_performance_counters,
2000 in sys.dm_exec_cached_plans
November 19, 2015 at 5:28 am
You have a lot of the cache allocated to ad hoc statements?
Have you identified these and suggested making them stored proc's at least?
I think the number will fluctuate as long as there are ad hoc queries running against your server.
November 19, 2015 at 6:47 am
Justin Manning SA (11/19/2015)
You have a lot of the cache allocated to ad hoc statements?Have you identified these and suggested making them stored proc's at least?
Yes. This is some kind of normal 🙂
Justin Manning SA (11/19/2015)
I think the number will fluctuate as long as there are ad hoc queries running against your server.
That is not the question. When no activity on server same diference between counter and cache.
And few month ago, with same activity, there was no problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply