Blogging from the hospital

I'm in the hospital once again with kidney stones.  They have a saying in the emergency room here that the severity of the pain one typically experiences with kidney stones is similar to that suffered by women during childbirth.  If that's true, it makes me wonder how the race has survived.  I'm surprised that any woman would willingly go through childbirth more than once.  I have a feeling there would be far fewer kids being born if I were making that call -- that excruciating, knife-like pain is something I could go the rest of my life without experiencing again :-)

Thanks to the wonders of morphine, I slept most of the day today.  It's 4:00 a.m., and here I sit, wide awake.  I've commandeered a hospital phone line and am trying to do something more productive with my time than watch my IV slowly drip new chemicals into my bloodstream.

So, today's topic is SQL Server memory mgmt, specifically a question that came up during my recent trip to South Korea.  Question:  Can I use max server memory to shrink the BPool and thereby increase the space available for external consumers and thread stacks in the MemToLeave pool.  Answer:  No.  Changing max server memory does not affect the size of the MemToLeave pool.  Sound counterintuitive?  Let's walk through how all this works.

At start-up, SQL Server begins by reserving the MemToLeave region via a set of VirtualAlloc() calls.  It can make up to 16 VirtualAlloc() calls to reserve MTL.  How does it decide how large to make MTL?  The size of MTL is based on the -g server startup parameter and the max worker threads setting.  The default thread stack size for an executable built with VC++ is 1MB on 32-bit Windows.  However, SQL Server's worker threads don't need nearly that much stack space, so this has been adjusted downward to 512KB per thread.  The default -g setting is 256MB and the default max worker threads setting is 255, so the default MTL size is 256MB + (512KB * 255), or, about 384MB.

Here's a question for you:  Why would the server need up to 16 VirtualAlloc() calls to reserve MTL if this is done at start-up, before any other allocations have occurred?  If the idea behind making up to 16 reservations is to allow for possible virtual address space fragmentation, why are we even concerned about that given that the server process is just starting and there should be no fragmentation?  The main reason for this is that DLLs mapped into the virtual address space can fragment it.  You'll recall that a DLL has a load address, a region of memory within the virtual address space that it is mapped into when loaded.  SQL Server naturally has lots of DLLs it depends on in order to start, DLLs it implicitly loads by virtue of its own import table.  If those DLLs aren't present when the SQL Server process attempts to start, it will fail to load.  So, they are mapped into the process address space when the process first starts.  Because they can load anywhere they want within the user mode portion of the address space, they can very easily fragment that space.  Therefore, in order to ensure that it reserves the entirety of the computed MTL space, the server may make up to 16 tries, resulting in the MTL pool consisting of up to 16 separate regions rather than a single, contiguous block of virtual memory address space.

Here's another pertinent question:  Why reserve MTL at all?  Why does the server do this given that the region is primarily for consumers outside the database engine?  SQL Server reserves MTL prior to setting up the Buffer Pool (its main memory cache) in order to keep the MTL pool as contiguous as possible.  The more contiguous MTL is, the larger of an allocation a consumer of that region can make.  If, due to fragmentation, the largest contiguous MTL region is 16MB, no consumer of that space can allocate more than 16MB via a single VirtualAlloc() call, regardless of how much total free space there is.  As you'll see in a moment, once the BPool is set up, this MTL reservation is freed in order to make it available to consumers both inside and outside the database engine.  We reserve it ahead of setting up the BPool in order to keep that process from further fragmenting it, then we release it afterward.

Once MTL has been reserved, we then set up the BPool.  You'll recall that Windows supports reserving and committing memory as two separate operations.  We begin by reserving as large an area of the process address space as possible for the BPool.  The exact size computation is based on the physical memory in the machine.  Since no actual storage use is implied by this reservation, max server memory does not affect it.  That's right:  the size of the reservation is not controllable via max server memory.  As memory is needed by the various consumers within the server down the road, we commit these reserved pages.  We continue to do this up to the point that we hit either max server memory or our reservation size, whichever is less.  Because there is a correlation between the amount of virtual memory committed and the physical memory used by the server, it makes sense that max server memory should relate to the commitment, but not the reservation, of virtual memory.   Understand that, as consumers free memory they've allocated from the BPool, we don't call VirtualFree() to release it.  Memory that is released by one consumer is simply put on a free list so that the next consumer needing a virtual memory allocation can quickly receive a pointer to it rather than having to wait on a VirtualAlloc() call to complete.  This often fools people into believing that the server is leaking memory -- they watch the private process bytes counter in Perfmon for the sqlservr.exe process and notice that it continues to climb over time.  This is what it's supposed to do.  We are merely committing storage as we need it within the BPool reservation(s) that we made at startup.  We don't de-commit any of these until we detect that we're in danger of being paged out by the OS, as I describe below.

After the initial setup for the BPool has completed, we release the MTL reservation.  This makes the memory available to external consumers (e.g., xprocs, COM objects, NET Libraries, etc.) running within the server as well as to the database engine itself.  At that point, memory allocations within the database engine are serviced by the server's five memory manager objects and allocated from either the BPool or MTL based on size, as I've described elsewhere.  Allocations of 8KB or less come from the BPool; allocations larger than that come from MTL.  With the possible exception of xprocs, external (non-database engine) consumers get their memory from MTL.  Xprocs can have their memory allocations filled from either the BPool or MTL based on two factors:  whether they call srv_alloc() to perform the allocation and the size of the requested allocation.

The server regularly checks the available physical memory on the host machine and trims the BPool as necessary in order to prevent having the server paged out.  The exact amount of available physical memory needed to keep this from happening varies between OS and SQL Server versions.  Regardless, if the server determines that available physical memory has dropped too low, it begins calling VirtualFree() to de-commit BPool pages so that their corresponding physical memory -- however much that is -- can be freed and help keep Windows from paging the server to disk.

So, the bottom line here is that max server memory doesn't affect the size of the MTL because it's about committed pages in the BPool, not reserved pages.  If you want to adjust the size of MTL on 32-bit SQL Server, use the -g start-up parameter.  I should point out that this is not something that's often needed, and you should probably talk to Microsoft before manually tweaking the server's memory configuration in this way.

Comments

• Anonymous
  May 13, 2005
  The comment has been removed

• Anonymous
  May 14, 2005
  Been there, done that - you have my sympathies. I gave birth to a 2.8cm "boulder" last year, pain was unbelievable. Thank heaven for the miracle of Percoset. Oh yeah...good post! :-)

• Anonymous
  May 14, 2005
  I am pleased I do not have them, especially if it makes you be awake at all hours of the night and your thoughts turn to BPool and memory reserves. Or maybe its the drugs, what are you on?

• Anonymous
  May 14, 2005
  Ken,
  
  Hope you recover soon.. quick one....
  
  How would we believe what you wrote is... when you are not morphinised....
  
  Well no spellling mistakes so that should make us all belive ;)
  
  Best Regards
  TVN

• Anonymous
  May 19, 2005
  The comment has been removed

• Anonymous
  May 21, 2005
  The comment has been removed

• Anonymous
  May 22, 2005
  My sympathies, Ken. Been there and done that 3 times myself.
  
  You've probably been told all this but just in case you haven't.
  
  Cranberry juice. 2 or 3 times a week. I did this and haven't had any more problems for several years now. Slightly more water than coffee or tea also helps. Soft drinks are evil and to be avoided at all costs.
  
  Best wishes for a speedy recovery.

• Anonymous
  May 22, 2005
  Hi Ken
  I experienced this pain two or three years ago.
  And yes whaoo !!!
  It's the type of pain that one doesn't even wish to his worse ennemy.
  In my case, the pane vanished as suddenly as it appeared after a couple of hours.
  
  Hope you very prompt recovery.
  
  Best regards
  Med

• Anonymous
  September 02, 2005
  nice to be seen

• Anonymous
  March 21, 2006
  Your site is very informational for me. Nice work.

• Anonymous
  June 08, 2009
  PingBack from http://insomniacuresite.info/story.php?id=869

• Anonymous
  June 09, 2009
  PingBack from http://weakbladder.info/story.php?id=1491

• Anonymous
  June 16, 2009
  PingBack from http://workfromhomecareer.info/story.php?id=16407