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?