SQL2012 caching behaviour

Question

SQL2012 caching behaviour

angel.wong 72408

Right there with Babe

Points: 741
More actions
November 5, 2012 at 6:50 pm

#264475

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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

angel.wong 72408 Right there with Babe Points: 741 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

angel.wong 72408 Right there with Babe Points: 741 More actions · Answer 4

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.

http://sqlblog.com/blogs/sqlos_team/default.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

w8buch 88800 Mr or Mrs. 500 Points: 540 More actions · Answer 6

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