Forum Discussion
Power BI data refresh locking all other users out of database
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
Advocate IVIs NOLOCK still the recommended approach with the latest PBIRS version?
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.
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...
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.