SQLServerPedia

First things first – depending on the version of SQL Server that you’re using, SQL Server could be creating this problem for itself. See these articles if you’re running SQL Server 2005 pre-SP2:

• SQL Server becomes sluggish or appears to stall on 64 bit installations
• A significant part of sql server process memory has been paged out. This may result in performance degradation
• How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
• The SQL Server Working Set Message

So what’s going on? First we need to address two concepts: Virtual Address Space and Working Set. Windows uses virtual address space (VAS) to allocate memory to applications (that’s as deep as I’m going here). A program’s working set is the collection of VAS pages that have been recently referenced. When a process is created, it is assigned a minimum and maximum working set size. As the working set size increases, thereby increasing memory demand, the virtual memory manager will attempt to keep up to the maximum working set size (memory) resident without exceeding the maximum threshold.

One common misconception is that there is as much virtual memory available to a process as there is physical memory (RAM) on a machine. Not so (or I wouldn’t be going there); you can get much more depth by reading Slava OKs’s blog on VAS. One very important point from a SQL Server perspective, however, is that when you’re configuring min and max server memory, you have to take into account the MemToLeave area. MemToLeave is VAS that’s left unused when SQL Server starts so that external components have address space to consume when they’re loaded. Typically, the majority of memory allocations from MemToLeave are from non-SQL Server memory consumers like COM objects, extended stored procedures, and linked servers. You can use DBCC MEMORYSTATUS to see these, but bear in mind that what’s reported will only reflect allocations made by SQL Server directly (SQL Server has no knowledge of outside memory requests).

So, when troubleshooting out of memory errors, do your homework.

• Check the list above to see whether your SQL Server is suffering from a known SQL Server bug, and understand (at least a little bit about) VAS and memory allocations.
• You can use Performance Monitor (Task Manager won’t help here) to get an idea of how much of VAS is currently consumed inside of your process by looking at the virtual bytes counter.
• If you believe that a 3rd party application is loading DLLs into SQL Server’s VAS, or you want to see if your own CLR or external procedures are doing so use SQL Server’s DMV sys.dm_os_loaded_modules to see what’s been loaded into SQL Server’s address space.

Oh, and by the way, for anyone who’s used to configuring the working set size parameter with sp_configure, you need to be aware that it’s been deactivated in SQL Server 2005.

Hope that helps.