Issue with Loading Data from SQL Database over VPN in Power BI

[Vcesano]

Hi everyone,

I'm experiencing a data loading issue in Power BI when connecting to an SQL database over a VPN.

I’m loading multiple tables, some of which contain a large number of records. Additionally, I’m unable to modify the queries in Power Query, as I’m replicating someone else’s work to keep an existing report functioning. Although the current setup isn’t ideal in terms of best practices, I need to maintain it as is while working on an optimized solution separately.

When I attempt to load the data, I frequently receive this error:

OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Transaction (Process ID 61) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Unexpected exception.

It seems the load might be overloading the connection, possibly due to the number of tables and records. Does anyone have advice on managing or reducing the load under these constraints, or suggestions for handling large data loads in Power BI?

Thank you very much for any assistance!

[Ritaf1983 · Accepted solution]

Hi @Vcesano 

Handling large data loads over a VPN in Power BI can indeed be challenging, especially with the deadlock issue you’re experiencing. Here are some potential ways to mitigate the load and improve stability:

1. Load Tables Incrementally:

   • Try loading the tables one at a time rather than all at once. This can reduce the risk of overloading the database connection and may avoid deadlocks.
   • You can disable load for all but one table, refresh that table, then proceed to the next one.

2. Adjust Query Timeout:

   • In Power BI, go to Options > Current File > Data Load and increase the timeout duration. A longer timeout might allow the database more time to process each query, helping to avoid deadlocks.

3. Optimize Query Execution in SQL Server:

   • If you have permissions, use SQL Server’s WITH (NOLOCK) hint in queries to reduce locking conflicts. This hint allows reading uncommitted data, which may be acceptable for certain reporting scenarios.
   • Alternatively, work with your database administrator to identify potential ways to optimize or index tables to improve query performance.

4. Break Down Data Loads with Partitions or Views:

   • If you can’t modify the queries directly in Power Query, consider setting up SQL views or table partitions on the server side. You can create views that only load specific subsets of data, reducing the amount of data Power BI pulls in one go.

5. Batch Processing with SQL Stored Procedures:

   • If feasible, you could use stored procedures to batch process data in SQL Server. Power BI can then execute the stored procedure instead of querying raw tables, which can reduce deadlock issues by controlling the data load more granularly.

6. Increase Connection Pool Size:

   • Increasing the connection pool on the SQL Server may help with handling multiple queries simultaneously. This requires collaboration with your database administrator to adjust server settings, but it may reduce deadlock issues in concurrent loads.

If none of these steps completely resolve the issue, consider engaging with your IT or database team to explore whether network stability over VPN is contributing to the problem.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.