The Effect of NOLOCK on Performance
-
I maintain that presenting the use of nolock as a tuning tool is both dangerous and missleading.
That there is a use for read uncommitted I will agree in certain circumstances, and lets use isolation levels here to make sure everyone understands exactly what we're talking about, although I'd always prefer to lean towards read only databases or filegroups. There's always going to be some gain if you tell sql server not to issue shared locks but compared to normal tuning and optimisation I think the risk outweighs any possible gains.
I also take exception to the phrase "Using hints in a query is something that most DBAs don't ever seem to bother with" - there's a REALLY REALLY good reason we generally don't - telling the optimiser that you know better is not recomended. I'd also be more interested if the tests had been run on a server rather than a laptop, I also note that your plans show parallelism, I suggest you try running your tests using profiler, IO stats often do not show additional io generated by parallel plans .. as I remarked first time around I tried a couple of simple tests on a server and could not see any difference.
I'd also question testing against table scans, this is something your average DBA tries to avoid.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I wonder if maybe it could be a good idea on a read-only database. Many situations I've worked with in the past had a 'readonly' db used as a datamart for reporting.
There would be little contention for records on that database, and little chance of a record being changed unless the read-only db was being repopulated... Something to think about anyway.
Mark
-
Be very careful: even with READ COMMITTED isolation level you can get inconsistent results in your reports:
-
I think there is good reason to warn people of dangers, but I've always been a practical DBA, using whatever is in my toolbox and works. If inconsistent results are a problem, then certainly re-examine your use of NOLOCK (or any other tool).
I hate that so often we don't get details or good information on the various tools available to us, like undocumented tools. I think we'd be better off if everything was very well documented, especially the dangers, and let people use it when it works in their environment.
-
If you SET the DB to read-only all locking mechanisms will be bypassed!
* Noel -
I think we've pretty much beat the subject of when and when not to use NOLOCK to death and the author pretty much summed up all that in his new introduction to the article. I give him credit for having the hair to come back with that summary after everyone got through with the previous article on the same subject.
At this point, I'd be more concerned with asking why someone is storing a date as VARCHAR or using a WHILE loop to create simple test data!!!!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good one, Jeff - you got me there! While loop indeed. Tsk, Tsk :-).CROSS JOIN would have perhaps been much better, or perhaps you can recommend something better?
And the bit about a date field varchar - eh, you got me there - I missed that one! Remember this was just a quick and messy script set up to create test data, but I'll try to be more careful next time. 🙂
Cheers,
Wayne
-
Hello, just wanted to add my two cents, now that the Canadian is worth more and has been for longer than most have expected 🙂
Please don't use nolock on tables that have text/ntext/image data types, it causes excessive cpu useage according to Idera's tools, plus gives 7105 errors frequently in sql server logs, which cannot be good for any instance in production.
Huggy Bear, Huggy ji, Pugo, Huge...token mcdba since 2001.
[font="Verdana"]Town of Mount Royal, QC
SQL Server DBA since '99
MCDBA, MCITP, PMP, MVP '10, Azure Data Platform Data Engineer
hugo@intellabase.com [/font]
https://drive.google.com/file/d/1qnyiGWyGvDz6Q2VtLPGEsRufy9CUqw-t/view (MCDBA 2001, data eng associate coming asap) -
Hi,
Using the LOCK hint to SELECT query it is a very good issue. I have tested it in my production environment and it is very useful. Before that it was a lot of lock waits and deadlocks. There is an ERP application which is running here and the isolation level of transactions was set in the code of this application. So, I have used this hint.
I want to point the author of this article to make tests of this hint on concurrential systems. There is a huge difference between using and not using LOCK hint.
In Theory, theory and practice are the same...In practice, they are not. -
This is an excellent article with the author's notes, though the line warning about only using it where a dirty read is acceptable cannot be overemphasized. I have a couple of particularly heavily queried tables that I have a procedure automatically generate reports on. Due to their heavy use, there are often more locks on. A couple of weeks ago, I added a no lock clause and saw the average running time drop from over an hour to under twenty minutes.
The key though was that the report was returning summarized information and the management really needed an estimate more than an exact number. I could accept the risk of dirty reads and even duplicate row returns. Nolock should be used with greatest caution when the integrity of the results returned is of importance.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/ -
I knew there are dirty reads and assumed the responsability. But from my ERP system point of view, there is no constraint to view dirty data.
In Theory, theory and practice are the same...In practice, they are not. -
I am a big fan of nolock and have used it very successfully (and prudently) over the years. I must admit that this whole thread has made me reconsider how I use it.
There is one thing that has not been clarified. In the case of duplicate rows (which causes me the most concern and is something that we have seen a few times in our environment), does this behavior only occur when 1) a clustered index is updated and 2) a non-sequential clustered index is inserted against? I noticed that Tony Rogerson's example of this involved updating a clustered index, causing the row(s) to physically move because of a page split. Could this behavior occur with non-clustered index modifications as well?
Regards,
Rubes -
If the data is being pulled from a covering non-clustered index, absolutely. I'm not so sure it would happen during a key lookup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Without reading all of the past comments, I am going to post what I believe is a valid reason to use NOLOCK.
We have multiple database servers, support one database per customer, approximately 15 customers per SQL Server (2000, 2005, enterprise edition on clustered servers).
We have data that is customer independant, and environment independant, for example system tables with lists of valid file types. Fortunately none of our columns are text, ntext, varbinary, etc... as pointed out earlier as being an issue. (Excellent point however) Basically we have int, varchar, nvarchar, and datetimes. We use views in the customer databases to look at this data.
The reason I feel safe using it ONLY for this customer independant, evnvironment data views is that the data is very static, not very large, and basically used for data integrity.
The reason we implemented the with NOLOCK hint, was that we had hundreds of thousands of connections and locks in CommonData on our production systems which presented a significant overhead.
We attempted making the institution independant data database read-only, but the delays in changing it to/from read only in order to perform maintenance were very significant and could shut down our production customers.
In a general on-line transaction processing database, I would not recommend the NOLOCK hint, but getting static data from a common database, seems safe to me. Just be sure to keep your common database defragmented to prevent the duplicates.
Brian Munier
Brian
Viewing 14 posts - 76 through 88 (of 88 total)
You must be logged in to reply to this topic. Login to reply