question

LAgerlund avatar image
0 Votes"
LAgerlund asked LAgerlund commented

Synapse "There is insufficient system memory in resource pool 'VDWFrontendPool' to run this query"

I got the following error when I try to copy data from a t-sql view in Synapse Serverless:

There is insufficient system memory in resource pool 'VDWFrontendPool' to run this query

I just though that memory shouldn't be an issue in serverless. the underlying t-sql is a simple select statement from a folder containing parquet-files.






azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT edited

Hello @LAgerlund,

Welcome to the Microsoft Q&A platform.

Update: As per investigation from the backend logs, we had found that you had number of queries that target 100k+ files on dfs api.

Number of files targeted with openrowset is 139592. Wildcard matching is filtering out very little, remaining number of files to be processed is 133525
Avg file size: 48MB (min 23MB, max 33 GB).

This is hitting problem with large number of files to pass wildcard expansion.
If it is possible to decrease number of files in the dataset or to break a single query into multiple queries?



It’s an intermittent issue. I would suggest you re-try again and check if that works.

In case, if you are facing the same issue, please do share the screenshot of the error message.

I'm able to see how much data was processed during the current day, week, or month, execute the following T-SQL statement:

 SELECT * FROM sys.dm_external_data_processed

95192-image.png

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


Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.



image.png (84.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LAgerlund avatar image
0 Votes"
LAgerlund answered LAgerlund commented

Thanks for the quick reply.

It succeeded the second time. But it's still a concern.

Below the usage and the error message.



Operation on target Sattelite failed: Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'There is insufficient system memory in resource pool 'VDWFrontendPool' to run this query.',Source=,''Type=System.Data.SqlClient.SqlException,Message=There is insufficient system memory in resource pool 'VDWFrontendPool' to run this query.,Source=.Net SqlClient Data Provider,SqlErrorNumber=701,Class=17,ErrorCode=-2146232060,State=123,Errors=[{Class=17,Number=701,State=123,Message=There is insufficient system memory in resource pool 'VDWFrontendPool' to run this query.,},],'


95203-usage.jpg




usage.jpg (55.9 KiB)
faildescription.jpg (43.1 KiB)
faildescription.jpg (43.1 KiB)
usage.jpg (55.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.