Difference Between Virtual memory and Page File in SQL Server?

Question

Difference Between Virtual memory and Page File in SQL Server?

Chirag Sachdeva 281

Hi there,

In On Prem SQL Server with 4gb server memory and page file size 4206 MB.

I ran DBCC memorystatus command and got below output Memory Status

Page file SS

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/dbcc-memorystatus-monitor-memory-usage

As per above link,

Available Virtual Memory is that This value shows the overall amount of free virtual memory for SQL Server process.

Available Paging File is that This value shows the free paging file space.

I had this understanding that page file acts as virtual memory for sql server, but values in above screenshot does not make sense as available virtual memory is showing values in TBs. Now I have this doubt if there is any difference between virtual memory and page file. Also why the size of virtual memory is in TBs?

2 answers

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

To be honest, I don't know that what Available virtual memory refers to. But it could be the total adressable memory space.

What I do know is that it is not particularly interesting. You only want SQL Server to use physical memory. The main usage for memory in SQL Server is the buffer cache. That is, it reads pages from disk and saves them into memory so that future access to these pages will be faster, when they don't have to be read from disk.

If the buffer cache spills to the page file, that complete negates the purpose of it, so you never want that to happen.

Chirag Sachdeva 281 Reputation points

2025-03-23T14:38:47.9433333+00:00

Hi, I agree with your comment and I know the concept of buffer usage. But recently I encountered below error in sql logs that. Investigating below error got me towards checking available virtual memory. That's why I was interested to know about the difference between Virtual memory and Page File.

"A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 12251 seconds. Working set (KB): 118892, committed (KB): 686564, memory utilization: 17%."
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-03-23T15:30:38.8566667+00:00

And that is exactly what you don't want to happen. This warning does not appear because of lack of virtual memory - that would lead to a full stop. This warning occurs because there was not physical memory enough.

When there is overall memory pressure in the machine, the operating system will inform SQL Server about the matter, and SQL Server will then trim its buffer cache and yield memory. But it may not yield fast enough.

On what sort of machine does this happen? If it is a true server, be that production or test, there is reason to investigate the overall workload on that machine. Maybe there are things running there, that should not be there. You may also want to consider to grant the service account for SQL Server the permission Lock pages in memory, which prevents spilling to disk. (But which can lead to graver issues, depending on what else that is running on this machine.)

On the other hand, if this is your own developer machine, there is less reason to be worried. On such a machine, we have so many other things running, that we may have to accept that SQL Server is swapped out every now and then. The only situation where you want to avoid is if you are running some performance testing, since you don't want these events to distort you measurements. The action to take in such case is to close programs you don't need running for the test.
Chirag Sachdeva 281 Reputation points

2025-03-23T18:35:29.5566667+00:00

Thanks for your response.
Memory pressure I have already investigated and shared details with respective team. Memory pressure was occurring because sql process was yielding memory for os processes. But now I just want to know the difference between the virtual memory and page file.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-03-24T04:32:20.4833333+00:00

The virtual memory is the entire address space, and to be able to address more memory than your physical memory, this is where the page file comes into play. The page file is what makes it possible.
Chirag Sachdeva 281 Reputation points

2025-03-24T07:59:10.3666667+00:00

As per your comment, virtual memory is physical memory (RAM) + page file, even I had the same understanding. But the screenshot I shared initially shows virtual memory in TBs even though RAM is 4 gb and page file size is 4gb.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-03-24T13:07:47.22+00:00

Available virtual memory = How much memory you can address in total. The value you see is the value for an ARM64 processor. It's completely theoretical and of no practical interest. It might have carried some interest in the days we ran SQL Server on 32-bit machines and there was special tricks to access more than 4GB of RAM.

Answer 2

Virtual memory and paging are what allows computers to run programs that use more memory than is physically in the machine. paging allows program memory to be written to disk, and to allow required memory pages to loaded in memory. the disk file is the paging file(s). So in effect the size of the paging file is max size of virtual memory.

So available memory is how much real memory the computer has and available virtual memory is basically the page file size. Virtual memory is shared with all processes, so a single process can not use all of it.

The working set size is how many in memory pages are allocated to a process (though if there is low memory demand, it can borrow above this size). Unless the process can borrow, to load a new page, another page of the process must be paged out.

SQLSever wants to manages its own memory and performs its own paging, so it wants the all of its pages in memory. if its cache is paged out, the point of the cache is lost. So sqlserver wants to use less than physical memory so it does not page. That is, its wants its working set size to be large enough to hold all of it data caches. The percent of committed memory in WS is a measurement of this. In your sample 88% of sqlserver process memory is in real memory.

note: there is also pinned memory, which are virtual pages not allowed to be paged, and thus reduce the available pages for working sets.

Share via

Difference Between Virtual memory and Page File in SQL Server?

2 answers

Your answer