Query Processing & The Buffer Pool

Question

When SQL Server 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?

The buffer pool does not come into play until the query is actually being executed to retrieve/manipulate data, correct?

Scenario: (server was not built on configured by me)

SQL Server 2008r2 SP1 - yes it needs to be patched, that is on the radar 96 GB of RAM MAX Memory set to 92 GB Buffer Pool sitting just below 80 GB used PLE over 4 hours Seeing resource_semaphore WAITS

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?

Thanks!

• Hannah Vernon: I definitely drop the max server memory from where it is now. You may want to look at this question and the answers
• pshore73: Normally I would use the formula Jonathan Keyhias blogs about, but that sets MAX Memory higher than what is being used. The post is really a 2 fold question, to confirm where in memory query processing happens and to obtain some thoughts on a new MAX memory setting.
• Hannah Vernon: Buffer pool is used only for caching data; however the max 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."
• Hannah Vernon: Do you have a lot of concurrent queries? With 92GB of max server memory, I'd expect a longer PLE than 4 hours - how big is the total size of data being served?
• Hannah Vernon: a PLE of 4 hours with that amount of memory equates (by my estimate) to around 380MB+ per minute of IO, on average.

Answer 1

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

Answer 2

resource_semaphore is used when SQL need to generate plan for huge query. when SQL understand that the amount of memory needed to compile a query is high sql server prefer to use restricted amount of memory for compilation since the other way is to drop part of the buffer pool which obviously sql server does not want to do. so, high rate of compilation while compilation need a lot of memory cause the resource_semaphore wait type even when the machine is monster (in terms of hardware)

I hope this answers the question. (I'm new here, not enough reputation to add comment yet)

• pshore73: Thank you. Your thoughts seem to be in agreement with mine.
• Hannah Vernon: welcome @Yochanan - you may want to take a look at the tour to get yourself acquainted with the site.