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