Direct Query memory consumption is too high - investigation needed

[ladislavDRmax]

Dear MS team,

we would like to ask you for additional investigation of our issue which is related to Power BI Direct query configuration. We have one data model which is shared on our Premium Capacity P2. Our issue is that when 20 users connect to report which is connected to dataset, we are reaching the capacity limits. We don't know how it is possible that data model which runs in DirectQuery mode can reach dataset limit of 50 GB. As we don't have any proper tool for investigation, we would like to ask you to help us with investigation of the issue.

We would like to know what can cause the issue and what has to be improve not getting this issue any more. We would like to know exactly what part of our solution causing the issue (which table) and what part of the solution consume the capacity soo much as from database perspective queries look ok.

[v-cazheng-msft]

Hi @ladislavDRmax ,

May I know whether you enable Premium Gen 2 for your Premium capacity? What’s the size of your dataset? Did you arrange any scheduled refresh during these 20 users viewed this DQ report?

If you are using Premium Gen 1 capacity, then the resources used are limited as the table in the official documents: What is Microsoft Power BI Premium? - Power BI | Microsoft Docs. These resources including memory are used to support front-end and back-end activities. No matter what kind of activities it is, the dataset will be loaded into memory and expand firstly, which means the size of the dataset will grow to even ten times of its current size. If the activity is refresh, then Power BI will duplicate the dataset to support the possible users interactions like viewing reports. In addition, other activities such as the queries generated to retrieve data will consume the memory as well. For how capacities function, you could refer to What is Microsoft Power BI Premium? - Power BI | Microsoft Docs.

If you have enabled Premium Gen 2 capacity, then the infrastructure changes. You with get fewer memory restrictions with it. For more details, you could refer to What is Power BI Premium Gen2? - Power BI | Microsoft Docs.

So, if you see this issue frequently, then I would recommend you enable Premium Gen 2 or upgrade your SKU for more resources. If you don’t want to purchase bigger SKU, you may check your scheduled refresh of the datasets in this capacity and move them to some off-work time to avoid the peak.

Best Regards,

Community Support Team _ Caiyun