June 20, 2012 at 5:51 am
Wondering if anyone can help me with this query.
I have a SQL 2008 Enterprise (64 bit) version 10.50.1617.0, 8GB Ram , 2 Quad Core cpu.
I have been reading a number of topics on here with regards to SQL Server Memory usage and specifically in relation to Procedure Plan cache bloating. Can anyone tell me why I am seeing a significant difference between the output of the following two queries (Both of which were provided by other contributors on this site)
1.
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
Adhoc20876982.004158210281.7307204830
Prepared2502250.335937122462.953125588
Proc23363.406250454611.95312521
Check370.9609373730.2343758
Trigger225.16406229600.0000000
View46243.507812740.0000000
UsrTab70.343750450.0000000
June 20, 2012 at 6:00 am
Apologies but accidentally submitted before i got finished;
to reiterate when comparing the output of;
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
Adhoc20876982.004158210281.7307204830
Prepared2502250.335937122462.953125588
Proc23363.406250454611.95312521
Check370.9609373730.2343758
Trigger225.16406229600.0000000
View46243.507812740.0000000
UsrTab70.343750450.0000000
vs
select name, SUM(single_pages_kb + multi_pages_kb)/1024.0 MBUsed,SUM(single_pages_kb)/1024.0 MBUsed
from sys.dm_os_memory_clerks
group by name
ORder by 2 desc
SQL Plans 2547.6796872380.507812
Lock Manager : Node 0274.421875274.421875
Default 86.59375027.101562
Object Plans 68.64843764.671875
Why is the SQL Plans showing (2547.679687) 2.5 Gb usage where the first query above showing Adhoc usage is only showing 982.004158 + 250.335937 for Prepared.
How do i find out what is using the other 1.3 Gb of procedure cache
June 20, 2012 at 6:35 am
Every query goes into the cache. Not just stored procedures or prepared queries (parameterized queries). All the ad hoc ones go there too (except when you've enabled 'Optimize for Ad hoc' in which case, you get plan stubs the first time an ad hoc is called, then a plan subsequently).
I've never compared output from these before, and based on how everything else works in sQL Server, I'd be surprised if they exactly matched, but the disparity you're viewing is because of the ad hoc queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2012 at 7:23 am
Thank you for your update Grant, appreciate the quick response.
Im probably misunderstanding something here but ;
Can I just add that 'Optimize for ad hoc Workload' is set to true.
I am not using 'forced' Parameterization, it is set to default 'simple'
In general on this instance I am happy enough with the amount of memory being consumed and used by the 'adhoc' queries i.e. 982 mb. The host OS has 8gb Ram and a max of 5 has been allocated to SQL Server. My concern was that
2.5 gb is being used by Procedure cache which is quite alot and if the adhoc queries are using only 982 mb, what is using the balance?
From the first query I can see that the adhoc is using 982, Prepared 250, proc 63 mb...This however in total only accounts for 1.2 GB of the procedure cache. Im trying to find out what is using the other 1.3 Gb approx of procedure cache that the second query is telling me is being used.
June 20, 2012 at 8:18 am
Procedure cache comes & goes to a degree. Sounds like you're fine. Personally, I'd turn on the ad hoc optimization. It doesn't hurt anything, but it can help a little.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply