out of memory issue in Fabric Datawarehouse

[dinesh01]

I am running a SQL query that inserts data into table from a set of views. I am receiving below error message. Is it possible to increase the memory with out changing in Fabric capacity ? How much memory available for one SQL query in fabric datawarehouse.

error message:

Msg 701, Level 17, State 123, Line 38
There is insufficient system memory in resource pool 'default' to run this query.

[v-huijiey-msft]

Hi @dinesh01 ,

Thanks for @AndyDDC 's reply. There is indeed no direct function to monitor memory and other resources.

I recommend this feature to you because I have used it when using SSMS. It is very convenient to use when connecting to SQL Server or Azure SQL.

I noticed that it is gray when connecting to warehouse using SSMS. You can give suggestions to Home (microsoft.com) to provide traces for lakehouse/warehouse.

You can use the code block of the notebook to run SQL statements. Using "resources" to monitor can indirectly see the resource usage. If the core is too high, you can consider setting the high concurrency mode in the workspace setting.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[dinesh01]

Thanks for the reply. Going forward If possible please try the suggestions from your side and post the links. All SQL products are not same.

[v-huijiey-msft]

Hi @dinesh01 ,

It looks like you are experiencing the problem "There is not enough memory to run this query".

First, you can reduce memory usage by simplifying your SQL query statements.

Second, use DMVs to monitor query execution and resource usage. This can help you determine which parts of the query are using the most memory.

More information about DMVs can be found in the official documentation below:

Monitor connections, sessions, and requests using DMVs - Microsoft Fabric | Microsoft Learn

Follow the performance guidelines provided by the Fabric data warehouse. This includes ensuring that the data warehouse is well designed and that queries perform well.

More information on performance guidelines can be found in the official documentation below:

Warehouse performance guidelines - Microsoft Fabric | Microsoft Learn

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[dinesh01]

Thanks for the reply. Is it possible to increase the memory with out changing in Fabric capacity ? How much memory available for one SQL query in fabric datawarehouse.

[v-huijiey-msft]

Hi @dinesh01 ,

Memory cannot be increased without directly changing Fabric capacity.

In Fabric Data Warehouse, the amount of memory available for a SQL query is allocated dynamically, depending on the overall capacity of the system and the current workload.

If you want to see how much resources are being used by the current query, you can run the SQL statement into lakehouse's notebook and select Resources to view it:

You can also use the SSMS(SQL Server Management Studio) tool to run queries to monitor resource usage, see the official documentation below:

Monitor CPU usage on SQL Server and Azure SQL - Microsoft Community Hub

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[AndyDDC]

OP has specified Fabric Warehouse, not Lakehouse (so can't see Spark resources as it's not this engine).  There's no out of the box monitoring of resources like memory using SSMS, and there are no system tables/DMVs that expose memoty consumption.

[dinesh01]

The below link is not working when I connected to SQL Analytic end point from SSMS. I am not getting Reports -> performance dashboard option from object exploer. I am ussing SSMS 20.2 version.

Monitor CPU usage on SQL Server and Azure SQL - Microsoft Community Hub

[AndyDDC · Accepted solution]

This won't work for Fabric Warehouse as it's not supported.