When SQL Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps. It is doing all of that in memory OUTSIDE of the buffer pool, correct?
No the memory is from inside the buffer pool. Generally memory required for such process would 'not be much' and this could be easily satisfied by buffer pool. Also note SQL Server might not grant all memory requested by the query in one go. If query requests memory which is of considerable amount SQL Server would grant minimum
just to make sure query starts running.
The buffer pool does not come into play until the query is actually being executed to retrieve/manipulate data, correct?
Buffer pool is always there starting from when plan is created to when query executes. Buffer pool in SQL Server 2008 R2
is sole one
allocating memory for any request <= 8KB of memory page
.
If my understanding that the memory grant to process the query does not come out of the buffer pool, but the remaining available memory, reducing the MAX Memory should help re-mediate the WAIT type. My thought is set MAX Memory at 72 GB. Your thoughts?
No, reducing max server memory would be of help here. I would suggest you to look at query and the 'statistics
optimizer is looking when it is preparing the query plan'. You have enough amount of RAM. Sometimes when you run a query with outdated stats it requests large amount of memory and ofcourse SQL Server is not going to grant all of it. It would provide minimum amount so that query at least starts running.
When SQL server creates a compiled plan, it calculates two memory grant parameters called "required memory" and "additional memory". The required memory is minimum memory SQL Server can give for sorting and hash operation and additional is what can be required by query to store temporary data which comes with huge sorts or spills
. If Query start with minimum memory and requests more but SQL Server is not able to provide it all in on go query waits with wait type 'resource semaphore'. This does not means memory is less it means the statistics was outdated which forced SQL Server to create bad plan thus requesting more memory.
I suggest you read Understanding Query Memory Grants
max server memory
from where it is now. You may want to look at this question and the answersmax server memory
setting is not necessarily only buffer pool. I'd certainly drop the max server memory setting, probably to 80GB or 84GB, while watching both the PLE and wait stats. Just in case you don't know the definition for semaphore_wait it is "Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts."