SQL Server switched to use pagefile exclusively instead of RAM and effectively froze the world 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.
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