Memory-mapped files and SQL Server

Continuing the discussion from yesterday about memory-mapped files, how do they affect SQL Server?  Every DLL referenced by the server (Windows libs, Net Libraries, C RTL libs, etc.), every DLL they reference, and so forth, requires virtual memory address space.  That’s also true of every xproc DLL you call, every in-proc OLE-DB provider you use, and every COM object you load in-process with the server.  This address space is lost to other uses no matter how little the DLL is used.  While the number of pages copied into physical memory may not be huge if the DLL’s code isn’t used much, its virtual memory footprint remains constant until it is unloaded.  If the DLL was loaded implicitly because the binary imports a function it exports (rather than explicitly using something like LoadLibraryEx()), it’s not unloaded until the process ends.

Why does this matter?  Windows binaries map to predetermined address ranges in virtual memory.  Given that it’s advantageous from a performance standpoint to have them map to unique ranges (when a DLL specifies a map address that is already in use, Windows must “rebase” it – it must map it elsewhere and adjust its internal address references accordingly), it’s not uncommon to have DLLs that map all over the user mode portion of the virtual address space.  Why is this an issue?  Because it can fragment the virtual address space to the point of making it difficult to satisfy large allocation requests. 

Let’s look at an example.  If you’ve loaded numerous xprocs, OLE-DB providers, etc., into the SQL Server address space, and then try to run a query that requires an extremely large query plan (one measured in multi-megabytes, for example), you may be in for an unpleasant surprise.  The query processor may return an out of memory error – not because the process is truly out of memory or even because there isn’t enough total memory available to satisfy the request, but because there is no single contiguous block of memory large enough to satisfy it.  Windows doesn’t do any behind-the-scenes sub-allocating to satisfy large virtual memory reservation requests.  As you are no doubt aware, SQL Server reserves and commits allocations larger than 8KB directly from virtual memory rather than from its buffer pool.  In order to allocate the memory, SQL Server must first reserve it.  This reservation will fail if a sufficiently large contiguous block of memory is not available.

The most common consequence of this condition within SQL Server is a “failed to reserve” warning message being written to the error log, along these lines:

2005-11-27 19:01:07.03 spid61 WARNING: Failed to reserve contiguous memory of Size= 65536.

Here, we’re trying to reserve a 64KB buffer and failing.  This message is followed by the equivalent of DBCC MEMORYSTATUS() output to help you troubleshoot the problem.

The moral of the story is this:  in addition to keeping the explicit memory allocations you make within the server as low as possible, be careful not to deplete the virtual memory address space, as well.  Especially on 32-bit Windows, virtual address space is a precious commodity not to be wasted.  Be mindful of overuse as well as fragmentation – either can deprive SQL Server of the virtual memory it needs and possibly break applications you depend on.