March 14, 2014 at 1:17 am
Physical Memory - 64 GB, SQL2K8R2 64 bit, and Max Memory setting 12GB at instance side.
Determining the plan cache pressure Limit by MS. does it max memory should be set 6.4GB as per below calculation?
75% of visible traget memory from 0-4GB
10% of visible traget memory from 4GB-64GB.
5% of visible target memory > 64GB
I have checked Local Memory pressure by this script but buckets_count result is CACHESTORE_SQLCP = 84227 more than default maximum count , does it less then default count CACHESTORE_SQLCP=40009 always? but 'CACHESTORE_OBJCP' values is very less number 234. so I concluded application behaviour lots Ad Hoc query using.
SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP');
SELECT type, count(*) total_entries
FROM sys.dm_os_memory_cache_entries
WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
GROUP BY type;
thanks
March 14, 2014 at 9:34 am
On a server with 64 GB of Memory, How did you come up with 12GB Max Memory ? Do you have multiple instances running ?
--
SQLBuddy
March 14, 2014 at 10:29 pm
There is only one Instance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply