Share via

Q and A: Controlling size of a single cache in SQL 2005

  • Article

Q: I have teaching a class on Yukon for the Ascend program, and the class had a question I wanted to double check before answering. On an Itanium with 1 TB of RAM, the class’ curiosity asked, “If plan cache is very large “Then how does Plan Cache lookups work such that the plan lookup does not itself become a bottleneck? The reason for the question is because the RM thread would not necessarily decrement the tick count on plans if they have not reached 50% of BPool (or am I wrong on that point?). So if there is 450 GB used for Plan Cache, what makes Yukon plan cache lookups work smarter than Shiloh's?

 

A: In my post about SQLOS caching https://blogs.msdn.com/slavao/archive/category/9005.aspx I have talked about clock algorithm and two clock hands - external and internal. I have mentioned that internal clock hand controls a size of the single cache " ...

You can think of internal clock hand as a way to put a max cap on a single cache. If this mechanism didn't exists then it would be possible for a single cache to push the whole process into memory pressure or become a bottleneck on lookups. For example if you execute numerous ad hoc queries, they can be cached. Without having internal clock hand or some kind of cap they would force the whole SQL Server's process into memory pressure or would cause severe performance degradations. To avoid such situation internal clock hand will starts moving once framework predicts that procedure cache's max cap is reached …" However in that post I forgot to mention that internal clock hand will start moving not only when specific size is reached but also when a given number of entries are put into the cache. The number of entries depends on max server memory setting, width of cache's hash table as well as memory state on the box. Keep in mind that this behavior affect all caches not only procedure cache - every cache has its own internal clock hand. If required every operation on a cache such as look up will move its internal clock hand.

 

One could observe movement of internal clock hands by querying sys.dm_os_memory_cache_clock_hands. You can also find out the width of the hash table by querying sys.dm_os_memory_cache_hash_tables

 

I hope your question is answered. I will be glad to answer more :-)

Comments