NickiT on 16 Mar 2016 16:32:17
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.
- Comments (4)
RE: Automatic use of NOLOCKS when querying tables
Is this under consideration?
RE: Automatic use of NOLOCKS when querying tables
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.
RE: Automatic use of NOLOCKS when querying tables
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/
RE: Automatic use of NOLOCKS when querying tables
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