Power BI data refresh locking all other users out of database

[Anonymous]

Dear all,

We are having a problem appear last week when refreshing against a SQL server though we haven't changed anything.

"Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Even though it says it has timed out overnight in the morning the process spawned by power bi has locked all other users out of the database and the only way to kill it is to disable the user account that power bi is using to access the data.

This is quite unfortunate as now we have to change our entire process around the use of power bi as we can't let it look at any live databases owing to the fact that it may kill them.

Does anyone know what the likely cause of this is?

[jdubs]

How would you use NOLOCK in a SQL data source where you're not using a specific query? 

e.g.,

let
Source = Sql.Database("sqlserver", "sql_database", [CommandTimeout=#duration(0,0,120,0)]),
dbo_FilteredTable = Source{[Schema="dbo",Item="FilteredTable"]}[Data],

[acsjfuller]

Did anybody find a resoltion or workaround for this issue?

[Anonymous · Accepted solution]

Hi,

I found a solution. I don't know if this is the actual solution, but we haven't had the problem again.

If I am getting the data from a SQL server then I do so as SQL query, where you have the option to specify the query, and I put a WITH(NOLOCK) clause in the FROM section of the SQL query - an example query is below. This will stop your query locking down the table to other users.

You don't have to worry about this with Oracle as they don't have the same problem around lock states as SQL server. However, I do this anytime I am using SQL Server DBs now. This will stop any query folding that may take place as PBI can't push its queries into SQL anymore. So as a result in my larger projects I am now pushing as much data transformation and analysis into the SQL queries. I have noticed that this performs much better than any queries written in Power Query, and to be fair there isn't anything that Power Query can do that you can't do in SQL - apart from when you are having to mashup different data sources, which obviously you can't do with a SQL qry. However, if things are coming out of the same database then you want to use SQL to avoid all the performance issues of Power Query. 

Plus another bonus is that your processing is done on your sql server which means that you won't end up having queries timing out or running out of juice when they try and refresh on the server.

Basically, I am finding PBI is great for bringing together different data sources and for providing sharable visualisations, but it is really bad as a data transformation and querying tool. I hope Microsoft improves the performane and also provides some more documentation about how the M engine works.

Example qry below using WITH(NOLOCK).

let
   qry =
   "SELECT
       *
    FROM 
 TABLEA WITH(NOLOCK)
    LEFT JOIN 
 TABLEB WITH(NOLOCK)
 ON TABLEA.X = TABLEB.Y",

    run_sql = Sql.Database(myserver, mydatabase, [Query=qry, CommandTimeout=#duration(0, 0, 30, 0)])
in
    run_sql

[dataviznz]

Is NOLOCK still the recommended approach with the latest PBIRS version?

[Anonymous]

I am still using NOLOCK in any situation where I interface with a production MS SQL server as I can't afford to take down databases.

[Seth_C_Bauer]

@Anonymous Another option here is to tune your query. How long does it take to run in SSMS? And are you really using every single column from both tables? With a two table join, is the "left outer" necessary or could you use an inner join? Do you have the appropriate indexes on the tables? If you can get away with dirty reads (nolock), it will solve your locking issues, but I also wonder if you could improve the performance with standard tuning techniques.

"Select *" makes me cringe...

[Anonymous]

Hi,

Thanks for the suggestions. I should say the SELECT * was just an example.

The queries we are using in production are well tuned and have been running fine since I implemented a NOLOCK. Haven't had a problem with refresh for a number of months. They only take the columns they need.

When the DBAs diagnosed the problems, which occurred multiple times locking out all other users from writing to the DB in question,  it was traced to queries coming from the data gateway which were locking up a production database. Once we implemented NOLOCK on the select queries haven't had any problems since then.

So my standard operating procedure is to put as much processing on the DB to the server and if using a MS database stick a NOLOCK on the select query. Use Power Query to mashup different sources which it is great at and try and keep any heavy away from it.

I have also had related problems with the Gateway timing out with large queries that aren't a problem if I rewrite them in SQL.

As we go along we try and move as much as possible into our Oracle systems which don't suffer from the same read/write lock issues as MS databases.

[Seth_C_Bauer]

@Anonymous How large was the dataset that Power BI was trying to access? Did you run out of memory on the box and that is what locked everyone out?.. 

[Anonymous]

The main source dataset is only 100,000 rows, which is expanded to 6.5 million rows in the power bi data model.

I don't think it would be memory as the database is accessed by a many different users simultaneously during the day who would be pulling a reasonable amount of data.

Also we haven't had any problems until recently, nor have there haven't been any major changes to the data source and no changes to the Power BI data model.

Furthermore, I can refresh all my data on the desktop and then publish it so it would appear to be something wrong with the service.

I was talking to some of the affected users and they said they could still view the user interface of the database, however they could not add or delete any entries.

Is there a data log that we can look through? As we need to do a forensic investigation to work out what has happened before we can let power bi look at any of our production databases again.

[Seth_C_Bauer]

@Anonymous You could look at the Event logs on the server that hosts the gateway to see if there were any additional messages. Details on the bottom of this page. Otherwise look at the SQL Server logs to see what was happening before things locked up.

Are you refreshing on a schedule or is this Direct Query? How many people have access to the Dashboard?

[Anonymous]

Hi,

I will have a look through the logs. There are only about 6 people with access to the dashboards as it is in pilot phase. Not using direct query, refreshing on a schedule, for 1 AM, but it had seemed to have locked up the server through the morning.

Thanks for your help!