patternsqlModerate
What is controlling SQL Server's page file usage? SQL Server or Windows?
Viewed 0 times
filewhatsqlusagecontrollingpagewindowsserver
Problem
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
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
Solution
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.
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.
Context
StackExchange Database Administrators Q#258420, answer score: 12
Revisions (0)
No revisions yet.