SQL Server switched to use pagefile exclusively instead of RAM and effectively froze the world

Question

I was hammering my SQL Server instance yesterday for about 3 hours running an intensive calculation which was consuming all allocated physical RAM _{(I have it limited to 2GB due to my limited workstation hardware)}, and suddenly it (and many other things?) released all their memory in an apparent switch to exclusively using pagefile.

My computer was lagging so incredibly after that I was forced to restart (which took 10 minutes just to access the power options).

I was able to get a screencap of task manager before restarting (which I leave running in the background with a low update speed). With the window resized to show the maximum history, this is what it looked like:

This surprising behavior caused downtime. Is this a documented feature?

I am running Windows Server 2008 R2 with an Enterprise 2014 database engine.

Thanks.

Answer 1

_{Community wiki answer:}

Windows paged you, that's how the Virtual Memory Manager works. If you use lock pages in memory, it wouldn't be able to... though you may crash your system instead of it being sluggish. Properly setting max server memory is also a good start.

If the data set you're working with is much larger than 8GB, it might be time to look at hosting it on a server with more appropriate hardware. SQL Server doesn't work with pages on disk (they have to get read into memory), and queries may ask for memory for certain operators (sorts, hashes). SQL Server can use memory beyond max server memory for those memory grants. See: Did You Give SQL Server 2012 Standard Edition Enough Memory?

If and when you upgrade to SQL Server 2012 or later, note that significant changes were made to memory management. See Changes to Memory Management starting with SQL Server 2012 (11.x). In particular, SQL Server may then use memory (e.g. for sorts and hashing) beyond the limit set by max server memory (especially for columnstore operations) but it will still try to get back within that limit as soon as possible.

Answer 2

This surprising behavior caused downtime. Is this a documented feature?

Yes this is, its documented in RAM, virtual memory, pagefile, and memory management in Windows. Quoting from this suport article

RAM is a limited resource, whereas for most practical purposes, virtual memory is unlimited. There can be many processes, and each process has its own 2 GB of private virtual address space. When the memory being used by all the existing processes exceeds the available RAM, the operating system moves pages (4-KB pieces) of one or more virtual address spaces to the computer’s hard disk. This frees that RAM frame for other uses. In Windows systems, these “paged out” pages are stored in one or more files (Pagefile.sys files) in the root of a partition. There can be one such file in each disk partition. The location and size of the page file is configured in System Properties (click Advanced, click Performance, and then click the Settings button).

The reason why it froze because disk speed is much much slower than speed in memory. After paging portion of disk was behaving like RAM.

To find out how much memory was paged at given time you can use DMV sys.dm_os_process_memory. Look at difference of column physical_memory_in_use_kb and virtual_address_space_committed_kb. The difference of these two columns would give you SQL Server using paged memory.

select (virtual_address_space_committed_kb/1024) as Total_Memory_Used_MB, --Ram+page file
(physical_memory_in_use_kb/1024) as Total_physical_memory_used_MB ,--PhysicalMemory Used
((virtual_address_space_committed_kb-physical_memory_in_use_kb)/1024) as Page_file_Utilization_MB
from sys.dm_os_process_memory.

You can to larger extent avoid this by giving SQL Server service account Locked Pages in Memory Privilege. This will not allow SQL Server memory to be paged BUT still windows can ask SQL Server to trim its memory utilization uptill min server memory after that if there is still a severe memory pressure OS process will be paged and this can even lead to unexpected OS shutdown so be careful. Better solution is to add more RAM, in this case specifically.

EDIT: From your comment

I already limit the entire SQL Server instance to 2GB of memory

Ohh!! that is really too small amount of memory for SQL Server to work and that too when you are doing some severe testing