Memory - Total/Target always using same value?
-
george sibbald (3/26/2012)
total and target memory the same = a good thing.is this a 32 or 64 bit system?
I m facing problem for Procedure cache Hit ratio = 70 to 75 %
Memory utilization - 80 % - always (Monitor OP manager Tools)
I have set max memory 3200 MB in dedicated SQL Server machine.
This is for 32 bit system
As you said good - for Target/Total memory always using same value in Memory object counter.
How to reduse Memory utilization - 80 % & increase cache hit ratio? not enable AWE because 4GB not possible. also not enable /3GB switch.. if enable /3GB switch can possible to increase cache hit ratio?
Thanks
ananda
-
enable /3gb you will get another gb for sql - buffer cache only though, wont affect proc cache though I dont think your problem is there anyway
---------------------------------------------------------------------
-
Do you have a lot of ad hoc queries running in your system?
Do you have a lot of procs that recompile each time they run?
From what I understand, this could cause your Procedure Cache Hit Ratio to go down percentage wise.
Although, since I am unsure if I am perpetrating more SQL Myths, I am going to call in some memory experts to check my advice.
-
ananda.murugesan (3/26/2012)
I m facing problem for Procedure cache Hit ratio = 70 to 75 %Then you need to start looking at your code, see what you're running and why it's not reusing cached plans. That's not a sign of memory pressure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Indirectly, a bloated proc cache from tons of ad hoc queries generating single-use plans can contribute to memory pressure since the proc cache can grow largely unchecked. Since you're on 2008 we have 'optimize for ad hoc workloads' to consider if that scenario is affecting you.
Can you please run this on your instance and post the results?
SELECT objtype AS [CacheType],COUNT_BIG(*) AS [Total Plans],
CAST(SUM(size_in_bytes) / 1024.0 / 1024.0 AS DECIMAL(18, 2)) AS [Total MBs],
AVG(usecounts) AS [Avg Use Count],
CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes
ELSE 0
END)) / 1024.0 / 1024.0 AS DECIMAL(18, 2)) 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
--Read more: http://sqlskills.com/BLOGS/KIMBERLY/post/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx%5B/code%5D
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I'm not the OP, but I decided to try it anyway. I think I'm scared of my result.
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Oh, well. I could use the debug practice.
-
If you run into the overflow error, like I did, try this:
SELECT objtype AS [CacheType],
COUNT_BIG(*) AS [Total Plans],
CAST(SUM(CONVERT(bigint,size_in_bytes)) / 1024.0 / 1024.0 AS DECIMAL(18, 2)) AS [Total MBs],
AVG(convert(bigint,usecounts)) AS [Avg Use Count],
CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes
ELSE 0
END)) / 1024.0 / 1024.0 AS DECIMAL(18, 2)) 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
--Read more: http://sqlskills.com/BLOGS/KIMBERLY/post/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx
Worked like a charm.
-
Nice one! You should email your fix to Kim. What were your results? Sounds like you're on an instance with a lot of memory.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
For those keeping track at home, here was the change:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I don't have Kimberly's contact information to email her the updated proc.
As far as my results go, they're pretty nasty.
CacheType Total Plans Total MBs Avg Use Count Total MBs - USE Count 1 Total Plans - USE Count 1
Adhoc810315816.92306091909.4837624
Prepared143641351.23128171588.954582
Proc2159665.0435277978.20167
Trigger6914.4841940890.461
UsrTab90.8220150.000
View1678192.051470.000
Check210.4143260.000
SQL 2008 SP2
Min Memory MB 12288
Max Memory MB 98304
AWE is on.
Definitely talking to my boss about this one. May be one of those "we have bigger battles" moments, though.
EDIT: Though, it occurs to me that at least half those ad hocs are probably us (the production support team) trying to track down problem on a day-to-day basis.
-
Cool, thanks for posting back.
37,624 single-use adhoc plans out of 81,031 taking 1.86GB, and prepared plans are not far behind as a percentage...an efficient waste of RAM 😀
I would explore enabling the adhoc workloads server configuration if it's not already on.
You could also break out the sledgehammer to reclaim the prepared and adhoc space once in a while: Plan cache, adhoc workloads and clearing the single-use plan cache bloat
I have a job run a derivative of the code in the post on one of my instances every hour. It reclaims ~2GB RAM every 2-3 hours during peak usage. My instance only has 16GB RAM though, so 2GB is far more significant on my instance than it may be on your instance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
PS http://www.sqlskills.com/AboutKimberlyLTripp.asp
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (3/26/2012)
Cool, thanks for posting back....
I would explore enabling the adhoc workloads server configuration if it's not already on.
Opc, you do realize I'm not the OP, right? I only posted because you asked, not because I needed advice in addition to what's already been posted on the thread or is in Kimberly's blog. But thanks, anyway.
-
Brandie Tarvin (3/26/2012)
opc.three (3/26/2012)
Cool, thanks for posting back....
I would explore enabling the adhoc workloads server configuration if it's not already on.
Opc, you do realize I'm not the OP, right? I only posted because you asked, not because I needed advice in addition to what's already been posted on the thread or is in Kimberly's blog. But thanks, anyway.
Understood...this is a discussion site, right? Just carrying on...sorry.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (3/26/2012)
Brandie Tarvin (3/26/2012)
opc.three (3/26/2012)
Cool, thanks for posting back....
I would explore enabling the adhoc workloads server configuration if it's not already on.
Opc, you do realize I'm not the OP, right? I only posted because you asked, not because I needed advice in addition to what's already been posted on the thread or is in Kimberly's blog. But thanks, anyway.
Understood...this is a discussion site, right? Just carrying on...sorry.
I just think she wanted to make sure there was no confusing her with OP.
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply