Sdílet prostřednictvím

Be careful with xproc memory allocation

  • Článek

With the advent of CLR stored procedures in SQL Server 2005, the custom extended procedure era will hopefully be soon behind us.  Xprocs were a good idea and certainly added great functionality to earlier releases of the product, but they can also cause severe resource problems within the database engine or even be downright dangerous when not coded properly.  It’s simply too easy for an ill-behaved xproc to cause problems for other components in the SQL Server ecosystem.

Xprocs will still be used for certain product components in SQL Server 2005, but it’s my hope that the days of customers creating custom xprocs to extend the product are coming to an end.  CLR stored procedures, functions, and data types, are a far better way to go when that type of product extension is required.  Xprocs, while powerful, make lousing up the server too easy, even for good developers.

One area in which this can be painfully obvious is with memory allocation.  There are essentially two ways to allocate non-stack memory within an xproc:  srv_alloc() or a Win32 API call such as VirtualAlloc().  Using srv_alloc() allows an xproc to allocate memory from either SQL Server’s Buffer Pool or its “MemToLeave” pool, depending on the size of the allocation.  If the size of the allocation request is 8KB or less, srv_alloc() fills the request from the Buffer Pool; otherwise, it grabs memory from MemToLeave.  MemToLeave, you’ll recall, refers to the area(s) of its virtual address space that SQL Server leaves unallocated.  It’s for use by “external” consumers such as xprocs, COM objects, and OLEDB providers, and is also used for large (>8KB) allocations by the database engine.

Allocating from the Buffer Pool is problematic because an ill-behaved xproc can easily dominate the BPool, stealing memory from the database engine that would be better used for caching database or index pages, storing procedure plans, or meeting other engine needs.  Worse yet, these allocations can’t be aged out of the pool by the lazy writer process.  They’ll be there until the xproc frees them.  If the xproc never frees them, they won’t be released until the server is restarted.  People calling srv_alloc() for xproc memory allocations don’t often realize that going overboard can actually slow down the server dramatically because it impairs the server’s ability to cache data. There’s nothing magical about it:  the memory has to come from somewhere, and if you intentionally allocate from the SQL Server Buffer Pool, you’re competing for that resource with the consumers in the server engine itself.

Allocations through srv_alloc() are handled by the server’s General memory manager, which, among other things, means that requests larger than 8KB are automatically filled from the MemToLeave pool.  The General memory manager is one of the five memory managers within SQL Server 7.0 and 2000.  Each of these is an instance of a single class within the server code that takes on a different personality based on the type of memory it is being asked to manage.  All five share in common the ability to allocate from either the BPool or MemToLeave based on the size of the allocation request.  If you call srv_alloc() to request an allocation larger than 8KB, the General memory manager calls the Win32 VirtualAlloc() function to allocate it from the only memory pool not already reserved for use by the server – the MemToLeave pool.

Allocating large buffers from MemToLeave brings with it many of the same dangers that allocating from the BPool does:  doing so competes with other consumers – both inside and outside the server engine – and can easily use all available space.  In fact, with MemToLeave, using up all available space or fragmenting it to the point of making it unusable can be considerably easier than with the BPool because the MemToLeave pool is typically so much smaller and the allocations coming from it are usually much larger.

The default MemToLeave size on SQL Server 2000 is 384MB.  Of this, ~128MB is set aside for worker thread stacks (thread stacks in SQL Server 2000 running on 32-bit Windows are 512KB, and the default maximum number of worker threads is 255).  The size of the remainder – ~256MB – is controlled via the -g server command line parameter.

So, by default, there’s only 384MB of MemToLeave space to play in, and it has to be shared by all in-process OLEDB providers, large query plans (and other large allocations by the database engine), COM objects, xprocs, etc.  It’s not difficult to see how a single ill-behaved xproc can cause serious resource problems throughout the server.  One guy always ruins it for everyone else.

Also, as with the BPool, allocations by xprocs from the MemToLeave pool are not aged out by the lazy writer process or otherwise managed by the server.  An external allocation within this space has the same status as any taken out by the database engine itself, but is not managed in any way by the engine.  Xprocs allocating from MemToLeave must take care to efficiently manage the allocations they make and to free them as soon as possible.

Direct allocations via Win32 APIs have the same characteristics as large srv_alloc() allocations:  they come from outside the BPool and compete with other consumers for resources.  An xproc making direct allocations in this way does so via either VirtualAlloc() (or some derivative) or HeapAlloc().

A consumer using VirtualAlloc() to reserve memory must take care to do so in 64KB chunks, the allocation granularity size of 32-bit Windows.  What happens when you don’t reserve virtual memory in 64KB chunks on 32-bit Windows?  The remainder of the virtual address space between the end of the reservation and the next 64KB boundary is wasted – you can’t later commit it or reserve it via another VirtualAlloc() call.  An xproc doing this repeatedly can quickly exhaust the 2-3GB of address space allotted a 32-bit process, causing subsequent allocation requests to fail even though actual committed storage is quite low.

Using HeapAlloc() can also be problematic.  Allocating from the default process heap is problematic because the heap is sized at 1MB by default and may not have much room to automatically expand due to the reservation of the BPool and the consumption of MemToLeave by the engine and other consumers.  Using a custom heap can be problematic because access to it by multiple threads needs to be serialized – after all, SQL Server is a multi-threaded application, and multiple workers might need to access the heap simultaneously.  As long as the xproc is creating the custom heap itself, that’s not a problem because the heap can be created such that Windows takes care of this serialization (this is, in fact, the default behavior of custom heaps).  But it’s common for user code to turn off Window’s heap serialization facilities when creating heaps for single-threaded applications or when it’s synchronizing access to the heap itself.  Serializing access to a heap takes time, and, in the interest of speed, some apps opt to skip it or manage the synchronization themselves rather than allowing Windows to do it.  It’s not difficult to see how this could be a problem with an xproc.  What happens if you build an xproc that uses a third-party DLL that creates a non-serialized heap?  You may run into heap corruption as soon as multiple users run the xproc at the same time, likely resulting in an access violation that may crash SQL Server itself, depending on what thread the AV occurs on.

Another key consumer of the MemToLeave space is the server-side Net Library facility.  Net Libraries facilitiate connection to and communication with the server.  They are DLL-based and allocate any memory they need from the MemToLeave pool.  If MemToLeave is exhausted or fragmented to the point that it can’t be used to fulfill simple allocations, clients may not be able to connect to the server and may not be able to run queries and submit RPC requests once connected.

So, the moral of the story is this:  be careful with dynamic memory allocation within xprocs.  Going forward from SQL Server 2005, use CLR stored procedures, functions, and data types where you might have considered using an xproc.  Regardless of the SQL Server version, if you must use an xproc to extend the system in some way, be very careful, particularly with respect to allocating precious resources such as virtual memory.

Comments