Suppose I have a 32 GB machine, now I have allocated 12GB memory to the maximum server memory for my database application to perform. Now I am running some load simulation which is going to consume the entire 12GB. Now SQL Server is run out of memory when it required more memory, OS is going to assign more memory:
Now here is my thought, the scheduler will check the priority of the task if the task has got the highest priority then, how the memory is going to allocate? if so how much % of the rest available memory will allocate to SQL Server. Is it entire or it will assign periodically and how much memory will allocate?
If the above condition is false then the alternate concept is (note this is what I think), if the task has got the lowest priority then it will wait for its priority to come till the time application will throw out memory exception.
Is the maximum server memory parameter play a role in memory allocation in the SQL server? How important it is for memory allocation and troubleshooting?
Best Answer
By OS I believe you mean Windows OS, if so, no windows OS does not do memory management of SQL Server it is taken care by SQLOS (Specific OS built for SQL Server) primarily which has component memory manager(MM). This MM takes care of memory allocations and interacts with Windows OS.
For the other part if you are running something which is going to consume entire 12 GB yes you may get OOM error, but its rare to see such process.
AFAIK SQL Server does not allocates memory as per task priority if you boost priority the task would get higher "CPU time" not memory.. The memory allocation would be done normally as it should be, subject to condition how much free memory is available.
Yes very much, Max server memory controls the SQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Basically max server memory control how much maximum memory SQL Server can utilize.
NOTE: Their are some direct memory allocations which are not tracked by clerks and are done directly to windows, if such request comes this can further increase SQL Server memory ( more than specified in max server memory value).
PS: The topic is very deep it may not be completely possible to make you understand with simple answer, so I would suggest you to read SQL Server Memory Architecture Guide which I have added as a link.