November 5, 2012 at 6:50 pm
In SQL 2012, I found out that it seems flush from memory very soon make it unable to use the cache data, results in slow to getting results.
1) How long will it cache the data?
2) Any setting can be set for the interval of caching?
3)Any hot fix /patches in SQL2012 to solve this issue?
November 5, 2012 at 8:28 pm
angel.wong 72408 (11/5/2012)
In SQL 2012, I found out that it seems flush from memory very soon make it unable to use the cache data, results in slow to getting results.
Could you be more specific please?
1) How long will it cache the data?
Depends on how often those pages are used and if there's memory pressure. For frequently used pages, often they'll remain permanently in cache. If there's enough free memory, then pages will remain cached a very long time.
2) Any setting can be set for the interval of caching?
There's no defined interval for data pages to be cached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2012 at 9:24 pm
Feel that the cache query in SQL 2012 very fast gone. I also suspect is the memory pressure issue. Where to check the cache memory pressure limit and how to set it? We need to cache it for a large query.
November 5, 2012 at 10:03 pm
Query results are never cached.
What SQL caches are data pages (to avoid having to read from disk) and execution plans (to avoid having to recompile). in both of those cases caching duration is automatic and depends on usage and available memory and there are no settings you can use to adjust that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2012 at 10:46 pm
Ok. After do some testing on it, the caching in SQL 2012 is very fast gone if compare to SQL 2008 R2. Can you explain why? Is it the Memory Configuration change design caused it or other issue? Below is the comparison between SQL2008 R2 & SQL 2012 Memory Manager.
SQL Server 2012 enhancements to the Resource Governor which reflect a growing need for centrally managed database services to support isolated workloads in multitenant environments.
November 6, 2012 at 9:04 am
Huh?
The data caching is much the same between 2008 and 2012. Data pages from the data file on disk are cached, query results are not and never have been cached.
How did you measure that the caching is 'very fast gone'? What exactly are you looking at?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2012 at 8:02 am
I suggest looking at "SQLServer:Buffer Manager" in PerfMon to see how the buffer is doing. If there are buffers being flushed out you'll see it there. You'll also see the current amount of data pages in the buffer cache
And have a look at sys.dm_os_memory_cache_clock_hands to see if and where there was memory pressure
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply