Queries getting queued but pool has enough memory
Except for lack of memory, what would cause a query to spend time in resource queue?
An application's queries should run faster than they are based on tests, but because it is spending time in queue (resource_acquisitions - queue_entry_timestamp vs acquisition_timestamp) it's duration is getting extended (e.g. 15 minutes in queue, 1 minute execution).
Our memory and CPU levels are below threshold, only issue we have is IO, would that affect this?
Thank you.
0
Comments
It could be that the resource pool is already at its MAXCONCURRENCY.
Queries could also be queueing on threads or file handles if your OS configuration isn't correct.
It wouldn't be IO because the query is queueing and won't do any IO until it starts to actually execute.
--Sharon
Hi Sharon,
Thanks for the response.
We have max_concurrency set to null.
Planned concurrency is at 24 on each of the 8 nodes.
From observation, we seldom hit even over 10 concurrent queries running.
But will observe these metrics when the issue occurs.
Thanks again.