February 3, 2014 at 5:43 am
BWFC (2/3/2014)
What transaction level should I be setting it to? I assume, to make sure it's clean data I should be using read committed. I know it's not great but we are quite heavily reliant on the 'silver bullet' nolock method so we don't tend to use the transaction levels. I'm very much the new boy so I'm open to all (good) advice.
If you're going to be dumping nolock hints everywhere, any isolation level you like as the hints overrule it.
If you have blocking problems, have you considered using one of the row version isolation levels and ditching the nolocks? Consistent data, no risk of duplicate or missing rows and readers don't block writers (though heavier on tempDB than currently)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2014 at 5:58 am
I don't think we have much in the way of blocking problems, although it's not something that would necessarily come to my attention if we did. My boss or the other senior guy would probably pick that up, I tend to just write the reports for now. We do have a dedicated reporting server to apparently mitigate some of those problems though.
As I keep saying though, I'm the new boy trying to learn as fast as I can. I've no idea about row version isolation levels but I have picked up enough from round here to know that nolocks are to be avoided. The problem is that one of the previous staff swore by them and I think that's become a bit ingrained. I'm really dubious about them now and love to be able to provide a viable alternative. I've a horrible feeling that the sacrifice of accuracy over speed was made once and it may be hard to bring people back, particularly in the business.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 3, 2014 at 6:26 am
BWFC (2/3/2014)
I don't think we have much in the way of blocking problems, although it's not something that would necessarily come to my attention if we did.
If you have nolock on every query, you won't have blocking problems.
I've seen an official report (one that, if submitted wrong could have got the CEO several years imprisonment) off by over 20% because the nolock hint was used and some rows were read twice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2014 at 6:39 am
GilaMonster (2/3/2014)
BWFC (2/3/2014)
I don't think we have much in the way of blocking problems, although it's not something that would necessarily come to my attention if we did.If you have nolock on every query, you won't have blocking problems.
I've seen an official report (one that, if submitted wrong could have got the CEO several years imprisonment) off by over 20% because the nolock hint was used and some rows were read twice.
That makes complete sense. As far as I'm aware, pretty much every report we run has nolocks in it. I'm also conscious of official reports being out. We're currently being looked at very closely by the government and I'm personally surprised not one of the auditors has said something like 'about these nolocks...'
Is there a definitive article about the alternatives, or is it a case of 'it depends'?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 3, 2014 at 6:50 am
BWFC (2/3/2014)
I'm personally surprised not one of the auditors has said something like 'about these nolocks...'
No, they won't. Most aren't technical and won't have any idea of the implications. now, they might say something about data discrepancies...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2014 at 7:00 am
GilaMonster (2/3/2014)
BWFC (2/3/2014)
I'm personally surprised not one of the auditors has said something like 'about these nolocks...'No, they won't. Most aren't technical and won't have any idea of the implications. now, they might say something about data discrepancies...
Apparently one of them has a SQL Server background so it wouldn't be completely out of the question, and they have asked for code at times.
The data discrepancies they've found have so far been along the lines of 'we know the contract says that but that's not what it meant'.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply