In sql server max memory settings is 200GB but my physical OS memory is 8 GB.Will SQL will use beyond 8 GB in any case.Could somebody explain me or refer me the link.

• Moved by Jos de Bruijn [MSFT] Monday, February 23, 2015 4:50 PM

In sql server max memory settings is 200GB but my physical OS memory is 8 GB.Will SQL will use beyond 8 GB in any case.

??? How could SQL Server or any other application use more memory then the physical existing memory ... beside page swapping ??? No, the max Memory is limited by physical mem, of course.

OS can push part of sql server's virtual memory into a page file. It is the best practice to manually set the max server memory according to physical mem and needs of other apps on the machine. With giving LPIM (lock pages in memory) privilege to sql engine account one can enable sql to use only physical memory. Of course, there are considerations that should be taken care of. It is hard to describe all in an short answer.

• Marked as answer by Brindhat Friday, February 20, 2015 7:03 PM

No way SQl server cannot use more than available physical memory.

Ideal configuration suggests block 80-85% of your physical server memory for SQL Server process rest will be used by OS and other process.I assume that on server there is SQL Server installed alone as a major software.

Of course sql CAN use more than entire physical memory. As any other windows process, sqlservr.exe can allocate virtual address space which consist of physical mem + page file.

Of course sql CAN use more than entire physical memory. As any other windows process, sqlservr.exe can allocate virtual address space which consist of physical mem + page file.

Vedran,

Before reaching this either SQL Server will be paged or OS will be paged or OS might terminate. If a bug in SQL Server forces it to use all memory with page file as well OS is bound to crash much before that. I guess you mean to say memory used by sqlserver.exe +page file CAN be in some circumstances more than available RAM

As per you, you are saying that

virtual_address_space_committed_kb, -- Total SQL Server memory...This is not actually total memory utilized by SQL Server
physical_memory_in_use_kb, -- RAM....This is the total memory utilized by SQL Server which includes Working set and memory allocated via AWE API

Hi Shanky,

It is easy to confuse the terms here, since broad term "memory" might mean something different to you or me. For me:

RAM = physical memory, chip that will be erased when the power is gone

Virtual memory = memory allocated from virtual address space (RAM+page file).

You can see the memory allocated by SQL Server with this query:

-- SQL 2008+
SELECT
	m.virtual_address_space_committed_kb, -- Total SQL Server memory
	m.physical_memory_in_use_kb, -- RAM
	m.locked_page_allocations_kb -- locked RAM
FROM sys.dm_os_process_memory m

Total sql server memory consists of RAM + paged memory (pushed into page file). Part of memory allocated in RAM can be locked so it can never be pushed to page file (if LPIM=Locked Pages In Memoy windows system privilege is granted to sql server service account).

Normally, SQL server uses VitualAlloc() to allocate memory from virtual address space - that memory can be paged if OS decides so, e.g. when some other app need RAM, OS can push that memory from RAM into page file. With LPIM privilege is granted, SQL can use AWE API's funtion AllocateUserPhysicalPages() to allocate RAM that cannot be pushed-out to page file (non-pageable memory). Memory allocated with that function is not counted into "working set", so you can't see it in e.g. task manager or Sysinternal's ProcessExplorer when looking at "working set" column of sqlservr.exe process.

OS and SQL do communicate, and sql can release memory if OS signals that the memory is low (external pressure), so total memory allocated by sql should not grow indefinitely. OS can decide to flush part of the (pageable) sql server's memory to the page file. E.g. on memory pressure, Lazy writer will release buffer pool memory (dirty pages must be written to data files first, but clean pages are just released - they do not have to be written to disk), but that takes some time, and OS can be faster and push those into the page file before lazy writer releases them.

You can find more details here, here, here and here.

Best regards,

Vedran Kesegic

Hi Shanky,

I think the "physical_memory_in_use_kb" is just what is says it is: the physical memory (RAM) used by sqlservr.exe process.

Physical memory (RAM) used by sql = working set + locked RAM + large page allocations

But not all allocated memory is physical (RAM), as some (or even a majority if LPIM is not enabled) allocations are done through VirtualAlloc() function. That means allocation in virtual address space, which can be partially pushed by OS into page file. So allocated memory is not entirely in RAM, and that is dynamic (e.g. in one moment almost all is in RAM, and in the other moment it is in page file if OS decides to make a room in RAM for other apps to run).

Physical memory is only a part of the memory sql allocates (hopefully the bigger part - if not, that sql server has problems!). In my opinion, the complete allocated memory (=virtual, that is physical+pushed into page file) is in virtual_address_space_committed_kb column. It should be at least slightly higher than physical memory, because part of the sql's memory is paged into page file. Substract those two to see exactly how much sql server's memory has been pushed into page file. Hopefully, not much.

Please, read about virtual memory allocation here. In fact, it is the best to read the whole chapter about memory management, it has really useful explanations.

Thanks,

Vedran

Vedran,

I agree to all above points mentioned but here is what I know

When a process wants to read or write something into memory it first references memory in its VAS region which it can see( lets say 8 TB for 64 bit system) then VMM(virtual memory manger) looks for free memory(physical) and then maps this address to physical memory now this memory becomes committed. Now suppose due to pressure this process is paged out in that case VMM will make the VAS address referenced by process invalid and physical memory it was mapped to may be mapped to VAS of other process so the process which is paged out  does not have physical memory backing it.

This will come under virtual memory but wont have physical memory backing it. So again when asked how much memory SQL Server is using I would say value returned by physical_memory_in_use_kb would be the answer or will it be value returned by virtual_address_space_committed_kb ?

Hi Shanky,

I prefer using virtual_address_space_committed_kb, because that is the total amount of mem that sql server process has allocated. If we count only physical memory, we might left part of allocated memory out of the picture. But the best is to know both, so one is aware of exactly how much sql memory is paged-out to a page file.

sql memory paged into page file = virtual_address_space_committed_kb - physical_memory_in_use_kb

Quote from already posted link about VAS:

"The virtual address space of each process can be smaller or larger than the total physical memory available on the computer. The subset of the virtual address space of a process that resides in physical memory is known as the working set. If the threads of a process attempt to use more physical memory than is currently available, the system pages some of the memory contents to disk. The total amount of virtual address space available to a process is limited by physical memory and the free space on disk available for the paging file.

...

To maximize its flexibility in managing memory, the system can move pages of physical memory to and from a paging file on disk. When a page is moved in physical memory, the system updates the page maps of the affected processes. When the system needs space in physical memory, it moves the least recently used pages of physical memory to the paging file. Manipulation of physical memory by the system is completely transparent to applications, which operate only in their virtual address spaces."