16

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?

28

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 <> '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%.

0

Modern versions of SQL server have a really small chance of hanging up altogether. SQL Server loads .NET Framework into its address space and uses it under normal operation. If physical memory and page file both run out, Windows will try to grow the page file; however even if it can grow the page file, this is not an instantaneous operation, and memory allocations fail while the page file is growing. There's a bug in the .NET Asynchronous I/O handler where it allocates memory in response to an APC notification. If the call to new fails, it will throw OutOfMemoryException. This exception is caught in native code inside the task scheduler; however the asynchronous I/O will appear to never finish. The finalizer thread for FileStream will block waiting for the I/O to finish so it can unpin the buffer, thus hanging up the finalizer thread forever. This causes .NET Framework to gradually use up more and more memory until no more memory can be allocated, at which point SQL server will be unresponsive because winsock can't allocate anymore buffers so even the admin access connection is useless.

I have actually hit a task scheduler total hangup in a .NET application due to running out of memory. Thankfully the process finally died due to throwing OutOfMemoryException on some thread that didn't catch it after several failures so we could figure out what was actually hanging up the server.

Once I knew what I was looking for, finding the bug in static analysis was easy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.