What happens when there is no available physical memory left for SQL Server? While googling I found some conflicting information.

Some sites state that when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB (see: SQL Server: Demystifying TempDb and recommendations). 

But other sites state that, when there is not enough physical memory left, the operating system can use the PAGE FILE and move data from the physical memory to it (see Page File for SQL Server).

I wonder where SQL Server writes data when it runs out of physical memory? To tempdb or to the OS Page file? Or maybe both of them?
when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB


The article you linked to is misleading at best, and incorrect in some places.  I think the author was attempting to over-simplify some complicated things, and in doing so went a little too far.  

SQL Server doesn't move data from memory (the buffer pool) into tempdb in that way.  It uses a "least recently used" caching strategy (in general), so if there is memory pressure, and new data needs to be pulled into memory, SQL Server will kick out the LRU data from the buffer pool to accommodate the new data.  This behavior is often monitored by a perfmon counter called "Page Life Expectancy" (PLE):


  The definition of PLE is the expected time, in seconds, that a data file page read into the buffer pool (the in-memory cache of data files pages) will remain in memory before being pushed out of memory to make room for a different data file page. Another way to think of PLE is an instantaneous measure of the pressure on the buffer pool to make free space for pages being read from disk. For both of these definitions, a higher number is better.


During query execution, SQL Server can use tempdb for certain operations.  This is usually done if estimates are bad, but low available memory can influence this behavior.  

Some of the operations that can "spill" to tempdb in this way are hashing rows (for joins or aggregates, etc), sorting rows in memory, and buffering rows during parallel query execution.

User queries can also explicitly use tempdb (with global or local temporary tables), and implicitly use tempdb (with snapshot or read committed snapshot isolation levels).  

Neither of these situations really seem to fit the statement you quoted.


  when there is not enough physical memory left, operating system can use the PAGE FILE and move data from the physical memory to it


This can definitely happen, and is outside of SQL Server's control for the most part.  There is a knob you can turn to try to prevent some types of OS-level paging, namely turning on "Lock Pages in Memory" (LPIM):


  This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk.


So what can we prevent from being paged to disk?

Prior to SQL Server 2012, pages that were allocated through a component called the "Single Page Allocator" were locked in memory (could not be paged).  This included the buffer pool (database pages), procedure cache, and some other areas of memory.  

See Fun with Locked Pages, AWE, Task Manager, and the Working Set… for details, especially the section "4. Now I know that SQL Server on x64 can use “Locked Pages”, what exactly is locked?"  Additional related reading can be found here: Great SQL Server Debates: Lock Pages in Memory

In SQL Server 2012 and later, there is no "Single Page Allocator" (the single and multi-page allocators were merged, per An In-depth look at memory – SQL Server 2012/2014).  The details of what, exactly, can and cannot be paged is not documented in detail anywhere that I've seen.  You can use a query like this to see what is locked:

select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc &lt;&gt; 'ONLINE DAC'


Per the same MS Support article, you could also use DBCC MEMORYSTATUS to see how much memory is "locked."

As a side note, you can see evidence of SQL Server's working set being paged by the OS in the error log.  There will be messages that look like this:


  2019-09-02 10:19:27.29 spid11s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 329 seconds. Working set (KB): 68780, committed (KB): 244052, memory utilization: 28%.