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 (6)
RE: Automatic use of NOLOCKS when querying tables
A trigger like this will make the user always read uncommitted records (anyone smarter than me can confirm it's the same as NOLOCK?)CREATE TRIGGER SetIsolationLevel
ON ALL SERVER WITH EXECUTE AS 'your_login_name'
FOR LOGON
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
END;
RE: Automatic use of NOLOCKS when querying tables
Whilst I agree with the idea overall, I disagree with the use of the word "automatic" in this idea.
This SHOULD NOT be done automatically, but should be able to be applied by a knowledgeable user on a per-query basis, probably in the same manner that you can toggle 'Enable Load' and 'Include in report refresh'.
Whilst writing views set to READ UNCOMMITTED is workable, it's not quick/easy when just trying to temporarily bypass deadlocks when developing a report.
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