E_LOW_MEMORY_CONDITION Query Execution lacks memory resource to complete (message: bad allocation)

Question

Hi all, I'm new to ADX and I need your support.

I used to use this query a week ago and it went well both in ADX and PBI

but somehow it shows an error today as attached in PBI (in ADX is still working).

Here is the query I used for reference.

let T1 = (
Quality_crqs_result
| where id_factory == "229" or id_factory == "232"
//| where fe_created >= datetime(2024-08-15)
| distinct id_factory,id_pqs_history,id_defect,nm_result_green,nm_result_yellow,nm_result_red,fe_created
|lookup kind=inner (vw_Quality_crqs
    | summarize arg_max(nm_version,*) by id_defect
    |where id_factory == "232" or id_factory == "229")
 on id_defect
//| where id_defect in (24392
//,24391
//,24387
//,24380
//,24363
//,24342
//,24314
//,24286
//,24275
//,24262
//,24255
//,24248
//,24246
//,24243)
| distinct id_factory,id_pqs_history,id_defect,nm_result_green,nm_result_yellow,nm_result_red,fe_created,id_crqs,de_crqs,de_defect,id_category,de_category,id_subcategory,de_subcategory
);
T1
| join kind=inner hint.strategy=broadcast (Quality_crqs_historical
    | where id_factory == "229" or id_factory == "232"
    //| where id_pqs == 1723
    //| where id_line == 50250
    //| where dt_scheduled >= datetime(2024-08-15)
    | where isempty(dt_modified) == false // remove non-numerics
    | summarize arg_max(dt_modified,*) by id_pqs_observation
    | distinct  id_factory,id_pqs_observation,id_pqs,id_line,dt_scheduled,fl_shift,fl_state,dt_modified
    )
on $left.id_pqs_history == $right.id_pqs_observation
| distinct  fe_created,id_factory,id_pqs_history,id_defect,id_crqs,de_crqs,de_defect,id_category,de_category
,id_subcategory,de_subcategory,nm_result_yellow,nm_result_red,nm_result_green,id_line,fl_shift,fl_state,dt_scheduled,dt_modified
| lookup kind=inner hint.strategy=shuffle (Line
    | summarize arg_max(fe_modification,*) by id_line
    | where id_factory  == "229" or id_factory =="232"
    | where fl_active == 1
    | where fl_deleted == 0
    | distinct id_line,de_line,id_factory,fe_modification)
    on id_line
| distinct  fe_created,id_factory,id_pqs_history,id_defect,id_crqs,de_crqs,de_defect,id_category,de_category
,id_subcategory,de_subcategory,nm_result_yellow,nm_result_red,nm_result_green,id_line,fl_shift,fl_state,dt_scheduled,dt_modified,de_line
;

Answer 1

Hello @Sangsattaya, Manatsanan,

welcome to this moderated Azure community forum.

The fact it ran last week could have several reasons.

Back then there could have been less rows, no other queries were running at the same time (less load) or the cluster configuration could have changed.

When googling for 'E_LOW_MEMORY_CONDITION' every hit is hinting the KQL query needs to be optimized.

Here are several pointers with KQL best practices.

Next to that, the first T1 part has a summarize so this load could be taken away by using a materialized view.

If optimization is not an option, scaling up or scaling out could be the next option.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.

Answer 2

@Sangsattaya, Manatsanan

Welcome to the Microsoft Q&A and thank you for posting your questions here

The error message "E_LOW_MEMORY_CONDITION Query Execution lacks memory resource to complete (message: bad allocation)" indicates that the query execution has run out of memory resources to complete the operation. This error is specific to Azure Data Explorer (ADX) and Power BI (PBI).

Possible Causes

• Large dataset: The query is processing a large dataset, which requires significant memory resources.
• Complex query: The query has multiple joins, lookups, and aggregations, which can consume a lot of memory.
• Insufficient cluster resources: The ADX cluster may not have sufficient resources (e.g., nodes, CPU, memory) to handle the query.

To resolve this issue, try the following steps:

Optimize the Query

• Simplify the query: Break down the query into smaller, more manageable parts to reduce memory consumption.
• Remove unnecessary columns: Only select the columns that are necessary for the analysis.
• Use efficient join types: Consider using innerunique or leftanti joins instead of inner joins

1. Use Lookup Instead of Join: If possible, replace the join operator with the lookup operator, which can be more memory-efficient.
2. Scale Up Resources: Consider increasing the memory resources allocated to your Power BI environment. This might involve scaling up your Power BI Premium capacity or optimizing the data model to reduce memory usage.
3. Break Down the Query: If the query is too complex, try breaking it down into smaller, more manageable parts and then combining the results.
4. Check for Non-Optimized Workloads: Ensure that there are no other non-optimized workloads running against your cluster that might be consuming memory resources.

for more details check below links

https://techcommunity.microsoft.com/t5/azure-data-explorer/partial-query-failure-low-memory-condition-kusto/m-p/536386

https://learn.microsoft.com/en-us/answers/questions/935952/e-low-memory-condition

Hope this helps. Do let us know if you any further queries.