SQL server memory management – foundations

Everyone knows that it is important to understand how SQL server memory is managed. We’ve all heard about things like object cache, procedure cache etc. So naturally when I was asked to give an overview on the subject I thought that there was really not much for me to do and a quick internet search would turn up enough information. I was in for a surprise. It turns out that many blog articles or even BOL explanations either concentrate on some small detail or present confusing information altogether, or because of various changes in SQL server memory management, are outdated and no longer relevant to newer versions.There is a lot to talk about and many interesting sidetracks, but I will try to keep non-sql server related discussion to the minimum while siting related sources for those interested in learning more. Also, please let me know if you’d like to see more on a given topic and I will try to accommodate. So without further ado, let’s try to get to the bottom of this memory mystery.

First things first. There is more than one type of memory out there. It is common to talk about a memory hierarchy.

memory hierarchy. Curteosy of http://cse1.net/recaps/4-memory.html

There are many versions of these pyramids, This one is relatively more detailed and inclusive, but already confusion starts to creep in. Note that physical RAM and virtual memory are placed side by side. Is that justifiable? Well, yes and no. Strictly speaking no, because Virtual Memory management is going to involve the hard disk in some fashion or form. But it will also use the physical RAM, so it isn’t entirely out of place here. Microsoft has an excellent article on why do we need the Virtual Memory in the first place. Which explains why we need Virtual addresses, and finally brings us to the concept of VAS – Virtual Address Space. In Windows, various memory functions can be used to interact with physical and virtual memory. This MSDN article has good information on reading and writing memory. This is where all you favorite memory related acronyms come into play:

I won’t describe them here just provide links for the interested. For me they are as interesting as a paleontology museum. I still remember the days when 64 bit servers were controversial, and many people still run 32 bit systems (I spoke to someone the other day that still has SQL 2000 in production), but really that was long time ago and I don’t mind not having to modify my boot.ini. To be fair, AWE is still relevant in the 64 bit work and can still do some damage, but it is certainly not as important as it used to be.

Last, but not least, no discussion about server memory would be complete without mentioning Non-uniform Memory Access a.k.a. NUMA. It introduces the notion of memory nodes. Not that we’re going to interact with it in any meaningful way. After all, SQL server interacts with Windows memory manager which allows us to access VAS and does the virtual to physical memory address mapping behind the scenes. It is important to remember that actual physical memory is several times abstracted from a SQL server user. SQL server user requests are managed by SQLOS which in turn interacts with the Windows Memory Manager which uses VAS to abstract the physical memory from the user:

Windows Memory Access. Curteosy of http://henry416.wordpress.com/microsoft-windows/windows-memory-management/

Also, NUMA is a common source of confusion because just like SQLOS it has memory nodes. These concepts are related but very different from each other. We will explore SQLOS memory usage in the next post See you soon.



This entry was posted in database, SQL server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *