Is your feature request related to a problem?
When running into memory contention in a node, we run a resource manager check before query execution, and on failure return an error: Insufficient resources to run the query, quit. This error lacks context on what resource is insufficient & it isn't clear that this happens before query execution instead of being due to the query.

This ultimately directs to the ResourceMonitor check being an isHealthy boolean:

Which doesn't give any context to the caller on why it's unhealthy.

What solution would you like?
Instead of a boolean, a resource monitor could return some sort of status object which indicates the type of threshold being broken.

public abstract ResourceStatus checkResource();

This status might support methods like isHealthy and getDescription, would give context to the caller that can be presented to the user. With some error message wrangling, the current insufficient resources error can be reworked into a 429 with an error like:

Unable to begin processing the query due to insufficient memory. Check the cluster memory pressure.
To raise the query memory cutoff, update the `plugins.query.memory_limit` setting (current: 85%).

What alternatives have you considered?
N/A

Do you have any additional context?

# source=big5 | head;
TransportError(500, 'RuntimeException', {
  'error': {
    'reason': 'There was internal problem at backend',
    'details': 'java.sql.SQLException: exception while executing query: insufficient resources to run the query, quit.',
    'type': 'RuntimeException'
  }, 'status': 500
})

Internal ticket (for AWS employees): https://t.corp.amazon.com/V2003709229