What is controlling SQL Server's page file usage? SQL Server or Windows?

Question

If Lock Pages In Memory is not enabled and SQL Server data is paged out to the page file does SQL Server decide what data gets paged out? Or is this handled by the OS?

If Windows is handling this does it tell SQL Server which data has been paged out? It is my understanding that SQL Server knows how much data is in RAM and how much is in the page file. But does SQL Server know what data pages are put where and does it have any influence on whether a data page is put in one or the other?

Here's some Background:

I am not really trying to solve a problem as such.

My question is more about understanding what's going on inside SQL Server...

I had an issue with an application that had a long queue of jobs/queries that timed out waiting for a SQL Server instance to handle the requests. I noticed that two SELECT queries on the database in question were briefly blocking an UPDATE and a DELETE query. After a short while two new SELECTs were blocking two new UPDATE and DELETE queries. This continued.

SQL Server worked as expected - except it was too slow for the application that experienced timeouts.

The SQL Server instance is located on a active/passive cluster with 14 other SQL Server instances.

I worked on this with the application developers. We discussed allocating more RAM to the instance and while not really thinking it would help I raised the max server memory (MB) setting from 40GB to 55GB. Within a few seconds all the jobs waiting in line at the application server where executed - the developers were watching them being removed from the queue one by one very quickly.

The server hosting the SQL Server was not at the time set to allow Lock Pages In Memory for the service account, so I was thinking that if the data needed to handle the requests mentioned were placed in the page file and when the extra RAM was allocated the data was moved from the page file to the RAM maybe that could explain why all the jobs finished so fast immediately after the extra RAM was allocated.

Answer 1

Firstly, the most important thing you really need to know is that it is critical to configure memory usage by SQL Server and other applications on the system to ensure that it never starts paging to disk. RAM access is measured in nanoseconds, and disk access is measured in milliseconds, so whatever gets paged out is going to take hundreds, if not thousands, of times longer to access. It will also create a massive load of I/O on the system to do the swapping, contending for memory, CPU, and disk access. So even if you have SSDs, it is still going to hurt if it starts swapping to disk.

When the max memory setting is changed, SQL Server discards everything in the buffer and starts over. So if it was swapping the buffer to disk, yes, this may have caused the disk swapping to stop and performance somewhat restored. However, if it was swapping to disk with a 40 GB setting, it will be more likely to swap in the future now that it's going to use even more memory.

However, the change you made could have just been a coincidence--do you know for sure that there were no long-running queries blocking those jobs? There are dozens of possible causes, and unless you have data from monitoring mechanisms, there's no way to be sure at this point.

Memory management to avoid swapping to disk is one of the dangers of running multiple instances of SQL Server on one system. If you have 128 GB of RAM, for example, the max memory setting of all instances should only total around 110 GB or so, perhaps a few GB more after monitoring available memory for a while

If SQL Server cannot lock pages in memory, then it has no control over what memory will be swapped to disk. A process has an address space of virtual memory, and when the process reads the memory, the OS will retrieve it from the physical location (memory, or disk). The OS keeps track of when memory was last accessed and it tries to page out the memory that is least active to reduce the impact.

The process does not know about the physical location of each offset of its virtual memory--this could create quite a lot of pointless overhead in tracking it. SQL Server does, however, have a way of knowing how much of its memory is swapped to disk, as it will log a message as seen in How to reduce paging of buffer pool memory in SQL Server when it reaches a certain threshold.

Highly recommended reading: RAM, virtual memory, pagefile, and memory management in Windows, particularly to understand that monitoring Memory, Pages/Sec is not a valid measure of memory being swapped to disk. Many other articles on the Internet incorrectly advise this counter to be used.

Memory, Pages/Sec: This counter is one of the most misunderstood measures.

A high value for this counter does not necessarily imply that your performance bottleneck stems from a shortage of RAM. The operating system uses the paging system for purposes other than swapping pages because of memory over-commitment.

You can easily verify this by simply having an application write a large amount of data to disk, and you'll see high numbers for Memory, Pages/Sec. This is because the data that's being written to disk first has to be in memory, and then it is "paged" to disk. The terminology around "paging" is misunderstood by many system administrators and it is often defined as "swapping process working set to disk," but "paging" is a much broader term than covers every read and write to disk.

Answer 2

After reading your question multiple times I have the impression that you might be mixing one or other memory management issue. Be it from the OS perspective or the SQL Server Memory Management perspective.

Let's approach this step by step and then roll it up from the back again.

Paging Issues and SQL Server

If SQL Server is really encountering issues with SQL Server memory being paged out to disk, then you will find the following error message in the ERRORLOG file of your SQL Server instance related to error 17890:

A significant part of sql server process memory has been paged out. 
This may result in a performance degradation. Duration: #### seconds. 
Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

^{Reference: Memory Management Architecture Guide (Microsoft | SQL Docs)
Reference: Server Memory Configuration Options (Microsoft | SQL Docs)}

This issue can be related to the Lock Pages in Memory setting and is documented in the Microsoft article Memory Management Architecture Guide (Microsoft | SQL Docs). The solution would be to turn on the option Lock Pages in Memory, but ...

Only used when necessary, namely if there are signs that sqlservr process is being paged out.

...which would be mentioned in the ERRORLOG. (scroll back up for the error message)

Max Server Memory

To see what is consuming SQL Server buffer cache on your instance, run the following command:

Buffer Statement Grouped

SELECT
    (CASE WHEN ([database_id] = 32767)
        THEN N'Resource Database'
        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
    COUNT (*) * 8 / 1024 AS [MBUsed],
    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];
GO

The statement will ...

Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.

^{Reference: sys.dm_os_buffer_descriptors (Transact-SQL) (Microsoft | SQL Docs)}

Before I ran the Buffer Statement Grouped query against an instance hosting the AdventureWorks2012 and AdventureWorks2012DW database, I made sure that my instance had some data stored in the buffer cache by issuing the following command:

select 'SELECT * FROM ' + ss.name + '.' + sao.name + ' where 1=1' from sys.all_objects sao join sys.schemas as ss on ss.schema_id = sao.schema_id
where sao.type = 'U' and sao.is_ms_shipped = 0

...and then running the output against the AdventureWorks2012 and AdventureWorks2012DW database. I then queried the sys.dm_os_buffer_descriptors management view with the script and received the following results:

      DatabaseName     | MBUsed | MBEmpty 
 ----------------------|--------|--------- 
  AdventureWorksDW2012 |    103 |       6 
  AdventureWorks2012   |     95 |       9 

SQL Server had stored some of the data I had queried in the SQL Server buffer. Question is would it change if I increased the max server memory (MB) setting? My current setting was 1024 MB. I then increased the value to 2048 MB:

sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'max server memory (MB)', 2048
go
reconfigure

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.  
Configuration option 'max server memory (MB)' changed from 1024 to 2048. Run the RECONFIGURE statement to install.

...and queried the sys.dm_os_buffer_descriptors management view again:

      DatabaseName     | MBUsed | MBEmpty 
 ----------------------|--------|--------- 
  AdventureWorksDW2012 |      2 |       0 
  AdventureWorks2012   |      3 |       0 

As you can see, altering the max server memory (MB) setting will flush out the SQL Server buffer cache (data) and other, which can be observed in the ERRORLOG file:

2020-01-30 08:33:17.17 spid55      Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2020-01-30 08:35:23.74 spid55      Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2020-01-30 08:35:23.75 spid55      Configuration option 'max server memory (MB)' changed from 1024 to 2048. Run the RECONFIGURE statement to install.
2020-01-30 08:35:23.75 spid55      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2020-01-30 08:35:23.75 spid55      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2020-01-30 08:35:23.75 spid55      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Because the SQL Server cache has been flushed, any queries that now have to be run against the database(s), will have enough memory to:

• create fresh execution plans
• store data in memory

You're instance is able to keep up with the user request up until the point where normal user requests might hinder the queue you are talking about, because SQL Server will optimize the available memory based on the queries hitting the database engine.

Paging and SQL Server

Microsoft writes:

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.
For more information, see the "Dynamic memory management" topic and the "Server memory options" topic in SQL Server Books Online.

^{Reference: How to reduce paging of buffer pool memory in SQL Server (Microsoft Support)}

Important

Caution
There is a Workaround and a How to troubleshoot this problem section in the above article. Please read the How to troubleshoot this problem first!!

If you are encountering issues of SQL Server paging out the working set data, then this will be commented in the ERRORLOG. You can then determine if you need to troubleshoot the SQL server and possibly configure Lock Pages in Memory.

However, if your max server memory (MB) setting is too low, then you might encounter the ERRORLOG message, but can relieve the issue by increasing the max server memory (MB) setting without having to set the Lock Pages in Memory privilege.

I reproduced this issue on my SQL Server instance by:

• decreasing the max server memory (MB)
• performing huge selects
• checking the ERRORLOG
• increasing the max server memory (MB) setting again

I observed that paging was in fact an issue when I didn't have sufficient memory:

2020-01-30 09:13:30.69 spid23s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 177204, committed (KB): 347808, memory utilization: 50%.
2020-01-30 09:13:33.69 spid23s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 164596, committed (KB): 334896, memory utilization: 49%.

As can be seen looking at the time stamps, these tests were performed in sequence on my SQL Server instance

Increasing the memory again, reduced paging.

Solution

1. If you have memory to spend, increase the max server memory (MB) parameter. This can reduce paging (as shown in my case).
2. If you don't have memory to spend, follow the How to troubleshoot this problem in the article I referenced.
3. If all else fails, consider setting the Lock Pages in Memory configuration.

Answering Your Question

What is controlling SQL Server's page file usage? SQL Server or Windows?

I'll quote again from the Memory Management Architecture document I referenced and emphasise some points:

The default memory management behavior of the SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. The SQL Server Database Engine does this by using the Memory Notification APIs in Microsoft Windows.

When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If less memory is free, SQL Server releases memory to the OS. If more memory is free, SQL Server may allocate more memory. SQL Server adds memory only when its workload requires more memory; a server at rest does not increase the size of its virtual address space.

SQL Server release memory to the Operating System based on the Operating System's requirements/needs. They work hand-in-hand.