Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Power BI maxing connections to DB :( Can we populate multiple tables with single Sql.Database call?

I am assisting my team troubleshoot an issue with a Power BI report we are developing. We have a rather complex data model in the source SQL database, so we have created 5-6 views to better manage the data. We have a requirement to use DirectQuery, as one key requirement for the report is that the most up-to-date data in the database is visible, rather than having a delay in loading/caching the data. We also have the single data source, just the one database.

 

When we run the report, we see a spike of 200-500 connections to the database from the specific user for the report data source, and those connections don't close. This is clearly an issue and unsustainable for any product. We have a ticket open with Microsoft premium support to address the connections not closing, but in the meantime, I'm wondering if we're doing something wrong inside the report?

 

When I view the queries in the query editor, we basically have one query for each view, and it's a simple:

 

let
  Source = Sql.Database(Server, Database)
  query_view_name = Source{[Schema ......]}[Data]
in
  query_view_name

(I don't have the raw code in front of me, but that's the gist of it.)

 

It seems to me, based on analytics in the database, that "Sql.Database" is opening a new connection every time this view is called. And with 5-6 views, that's 5-6 connections at a minimum; then each time a filter is changed, it's more connections, and it's compounds from there until the database connection pool is maxed out.

 

Is there a way to populate all the tables using a single connection to the database? Why would Power BI be using so many connections? Can we populate multiple tables in the advanced query editor? Using DirectQuery, are there any suggestions for what we can look at/troubleshoot/change in the report?

 

Thanks!

1 REPLY 1
TeigeGao
Solution Sage
Solution Sage

Hi @Anonymous ,

Based on my test, when PowerBI desktop uses direct query to connect to a table or view, it will create a session to SQL Server, and it will keep this session sleep to wait command. Based on my searching, we can't change this configure.

Snipaste_2019-04-15_16-01-53.png

But when we uses these data in multiple report or create any filter, it will not add the session.

Best Regards,

Teige

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.