Forum Discussion
Power BI Premium DirectQuery Bottleneck Investigation
So both your "on-premise database" and "on-premise data gateway" are actually hosted on AWS? Sorry to hear that. This means you have some insane network traffic going back and forth across the VPN.
Do you have access to the PostGreSQL server query log? Can you monitor that while your users interact with the report?
Same for the gateway VMs - can you run a performance monitor while users are interacting with the reports? Are the cluster members hitting max memory?
Super UserSo both your "on-premise database" and "on-premise data gateway" are actually hosted on AWS? Sorry to hear that. This means you have some insane network traffic going back and forth across the VPN.
Do you have access to the PostGreSQL server query log? Can you monitor that while your users interact with the report?
Same for the gateway VMs - can you run a performance monitor while users are interacting with the reports? Are the cluster members hitting max memory?
lbendlin wrote:Do you have access to the PostGreSQL server query log?
Somewhat. I have been primarily using the Diagnostics option in Power BI Desktop to capture the logs and then scrape through those to find the underlying queries. Unfortunately it is often difficult to determine which queries are used for which visualisation. One odd thing I have identified is that typically the long queries as reported by Power BI's logs take 4 times the duration in Power BI as running the query independently (and database load has been extremely low).
lbendlin wrote:Can you monitor that while your users interact with the report?
Our DBA has been monitoring the database while a user interacts with the reports and they have not identified any issues on the database end.
lbendlin wrote:Same for the gateway VMs - can you run a performance monitor while users are interacting with the reports? Are the cluster members hitting max memory?
We found that we were hitting high CPU usage on the Gateway and have since added an additional node to the cluster and increased its size. We are currently having issues getting Npgsql working on the new node, so I haven't been able to verify the results of this, but it may be promising.
It sounds like we have the basics covered for now and we are at least looking in the right places. Are there any other techniques we may have missed?
lbendlin wrote:So both your "on-premise database" and "on-premise data gateway" are actually hosted on AWS? Sorry to hear that. This means you have some insane network traffic going back and forth across the VPN.
Fortunately the source of the data loaded into the database is also hosted on AWS.