February 8, 2024 at 6:06 pm
Hi,
we have a little bit of an interesting topic.
Sometimes, we get wait RESERVED_MEMORY_ALLOCATION_EXT, we have 12TB RAM , and not all are full but the primary server is totally slow, whoisactive is not possible run, other queries are waiting for this memory wait or also DTC or HADR SYNC, and we tried to find root cause lot of times but wasn't possible.
We found that during this issue is lot of times automatically updates statistics, but not sure if this slowness is also a consequence of this memory wait.
Does anybody have any idea what it can be?
Only failing over will help, because I guess all will be killed and on secondary node is working well then.
Thanks
February 9, 2024 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 9, 2024 at 8:56 pm
I did a quick google on that wait type as I had never seen it and was curious and it sounds like it is not exactly related to memory, but more related to CPU. SQL Skills says this about it:
This wait type is when a thread is switching to preemptive mode while allocating memory that has previously been reserved for the query (i.e. the query execution memory grant). The preemptive switch is so that the code being executed does not have to check for quantum exhaustion.
link - https://www.sqlskills.com/help/waits/reserved_memory_allocation_ext/
The way I read that the wait type will trigger whenever a thread is being switched from standby to active on the CPU. So the way I read that, the wait type is due to too many parallel threads on the server and not enough CPU cores/logical processors to handle the load.
So, what I'd check is how many of your queries are going parallel and of those, how many are using a lot of CPU cores. You may need to adjust your maxdop value or your cost threshold for parallelism.
Note - I have not had the problem you are experiencing and my advice MAY be wrong, but it is how I am reading the wait type and what I suggest should help the wait errors. BUT fixing the wait errors MAY not fix your issues (my opinion probably won't). With queries not running, my experience has been that it is sometimes a resource problem of some sort and sometimes is due to blocking. If your max memory is too low (under 1 GB on SQL 2008 R2, not sure on newer versions), I've had issues like what you are saying with queries never completing. If the OS doesn't have enough resources (CPU and memory) for SQL, I've hit that too. I wouldn't rule out blocking either though and you may want to look into what could be blocking. If you have a tool (like SQL Monitor), then you can use that to check. Otherwise, I tend to run exec sp_who2 (built in stored procedure and very lightweight) and look for blocking.
How much FREE memory do you have at the OS level and how much room do you have between your current SQL memory and max memory for SQL? Do you have enough free RAM to cover the gap between the current SQL memory load and the max memory? How heavy is the CPU being hit? are you running 100% CPU while SQL is slow? If so, what is using the CPU? If it is SQL, then I'd adjust MAXDOP and cost threshold for parallelism to a better value. IF the CPU is spiking and it is not SQL related, then I'd investigate what's up there.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 4, 2024 at 7:03 am
Hi,
sorry for the late answer.
Yes this wait was pointed to some problem with memory, so we collected memory clerks, and this time was higher only MEMORYCLERK_SQLQERESERVATIONS
We have full max memory, but Page Life is more than 5k daily or about this value. For OS we have about 1TB
We don't have 100% CPU, but a little bit is bigger than in the normal state.
We didn't found the root cause yet... but from last time maybe it can cause a procedure that will trigger the start of more automatic update statistics together on big tables with full samples.
The problem is, that automatic statistics from background processes is not possible to kill
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply