I read the thing from http://www.sql-server-performance.com/ and I am sure that our web and SQL servers are about 100x over sized for the application. While are ASP.Net (VB) app may demonstrate some hesitation from time to time I am more inclined to blame poor VB.Net coding techniques before slow SQL. The point being the NOLOCK is being added to SELECTS that are not part of a transaction and were using the SQL data adapter to return datasets or single column values.
Also I am not even sure it's being used correctly. The OLM has the example:
SELECT au_lname FROM authors WITH (NOLOCK)
However I am seeing it formatted like this:
SELECT au_lname FROM authors (NOLOCK)
I am by no mean an expert, I follow what I read in books or from examples from others. And I have never read in a book go crazy with NOLOCK because it's the bomb!
Any thoughts? I am trying to learn as much as I can before I raise my hand and say this might be a bad idea.
Thankshttp://www.sql-server-performance.com/lock_contention_tamed_article.asp|||This is the article that I read.
I guess I need to learn if I am having a lock contention problem? I have been using the Enterprise manager and watching for locks and there does not appear to be any issues. I am also trying a demo of "Spotlight on SQL" and it shows just a few database level shared locks and no blocking locks.
I am guessing I would see blocking locks if there was contention which would warrant the use of the NOLOCK correct?|||In general, I use NOLOCK when I have a problem, and generally, I see the problem when the front end is MS Access, because of the way folks tend to use Access (binding to the entire table).|||To be honest, NOLOCK used to be very helpful when you were stuck with COM+'s serialized isolation level. My biggest problem with table hints is that they are just that, hints, not instructions. You can write some lovely SQL statement that works for days, months, years. Then all of sudden it stops working, why? Because SQL has decided that it no longer wants to take notice of your hint.
So I'd say, like has already been said, use them when you *need* to use them. Normally sorting out your isolation levels will solve most the of the problems.
No comments:
Post a Comment