TempDB spills and Paging file

Question

Hi,
This is a general question to understand when the paging file is used and when the tempdb spills occur.

As per what i know, when SQL is short on memory during query processing, it creates more space in memory by swapping out contents to paging file. if Paging file is used during memory shortages, how is this different from tempDb spills.

Please can someone explain on this forum explain the 2 concepts: use of Paging file, tempdb spills.

thanks

Answer 1

If you don't set the "maximum server memory" for SQL Server, and do not reserve memory for the operating system, SQL Server will try to grab all RAM to data cache and avoid using storage subsystem, if the OS does not have enough memory because SQL Server is targeting more memory than available, then pagination will occur and it will affect the performance of the whole server. The OS will provide storage space as memory and storage has poor performance compared to memory. You can use performance counter "page file expectancy" to monitor how much your server is paging. All servers do some paging, monitor de counter for excessive paging.

Tempdb is not RAM memory, is all about storage subsystem (disk). Temporary tables, table variables created by users/applications are stored on tempDB. Temp tables are also used internally by SQL Server, When you have a spill from a query, SQL Server will spill that data on to temporary tables. If you are using read committed snapshot isolation level that allows users to read data while other users write data, tempdb is going to hold the version history of that data. If you are using AlwaysOn AG, and you are on a readable secondary, SQL Server is going to creates statistics and store them on tempdb when you query that readable secondary.

A query spill data to TempDB when it was not granted enough memoryto finish the operation and spilled over into the TempDB to complete the operation. Spill to tempDB (disk) hurts performance so you want to avoid query spills to TempDB

Answer 2

Hi @Biju Mathew ,

Microsoft SQL Server performs dynamic memory management based on the memory requirements of the current load and activities on the system. On Windows, SQL Server can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API. Based on this information from the QueryMemoryResourceNotification Windows API or from the memory calculation, SQL Server responds to the current memory situation on a specific system. This provides the following benefits:

• The system does not page out the working set of the SQL Server process.
• The necessary database pages are available in memory to reduce physical I/O needs.

Refer to MS document How to reduce paging of buffer pool memory in SQL Server.

When SQL Server poorly (incorrectly) estimates the number of rows that will be returned from any operator, it requests an incorrect amount of memory grant from the SQL Server engine, which leads to an inefficient execution plan. If the inefficient plan has a relatively small memory grant, SQL Server will need additional space on the disk to do the necessary work (like join, order by). When SQL Server uses TempDB when any query does not have enough memory to do its operation, it is called TempDB Spill.

Refer to the blog What is TempDB Spill in SQL Server?

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 3

As per what i know, when SQL is short on memory during query processing, it creates more space in memory by swapping out contents to paging file.

Just to add to the other posts: SQL Server never swaps itself to the page file. SQL Server simply do not want to be in the page file. But the OS may get the idea to swap out virtual memory SQL Server to the page file, just like it can swap out any other process. As noted by Alberto and Cathy, SQL Server will trim its buffer cache in case of memory pressure, but it may not be fast enough, and therefore swapping can occur.

You can prevent swapping from occurring by giving the service account for SQL Server the permission "Lock pages in memory".

Answer 4

Just to expand on the other posts.

The "pagefile" is a WINDOWS RAM concept, not a SQL Server concept. Windows swaps RAM to the page file when WINDOWS decides it needs more RAM. SQL Server has no control or knowledge of the Windows page file.

In addition, you will see significant performance issues if your SQL Server is causing Windows to use the page file. You need to set the "Maximum Server Memory" to a value which allows all of SQL Server to remain in RAM.