How can we improve Power BI?

Automatic use of NOLOCKS when querying tables

When you connect to tables, SQL is written in the background. However this doesn't include the use of WITH (NOLOCK). This can cause performance issues if the DWH is updated during the day. Having a toggle button to add this or not would be really useful.

38 votes
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

NickiT shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

4 comments

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • Jordon Pilling commented  ·   ·  Flag as inappropriate

    Whilst Bryan Swan is correct, I still think this should be an option. We've just had a user lockup our data warehouse because he tried selecting 2 billion rows without filters & the DWH rebuild procs all locked up & we had to resort to killing his SQL PID.

  • Bryan Swan commented  ·   ·  Flag as inappropriate

    NOLOCK has significant issues, you should not address performance via this method, look instead to caching of dashboards, Gateway Updates, refactoring queries. With NOLOCK hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. This means that the query may see dirty and inconsistent data.

    This is not a good idea to apply as a rule. Even if this dirty read behavior is OK for your mission critical web based application, a NOLOCK scan can cause 601 error which will terminate the query due to data movement as a result of lack of locking protection. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/

  • Peter commented  ·   ·  Flag as inappropriate

    Try creating your query in sql via a view with the nolock and then point BI to the view instead of having the query in BI

Feedback and Knowledge Base

Ready to get started?

Try new features of Power BI today by signing up and learn more about our powerful suite of apps.