The last SQL Server Users Group meeting was great! We had 55+ attend and Joe Webb’s presentation was highly rated by everyone.
Joe’s presentation, “Tips and Tricks for Writing Better Queries” has also been uploaded to
http://nashville.sqlpass.org . Thanks again to Quest Software and Robert Half Technology for sponsoring this meeting.
In our meeting we also discussed a 2000 vs. 2005 problem involving NO LOCK.The question was:
The company was using the NO LOCK hint in SQL Server 2000 to increase performance since they tightly controlled the data changes on the database. Under SQL Server 2000 there were no reported issues with using the NO LOCK hint. However, when they upgraded to SQL Server 2005 they started getting failures in the application layer occasionally (once per month). They have traced this issue back to the fact that the query using the NO LOCK hint is returning two rows for a single query containing the row prior to change and the same row after the change. Again, this only happens infrequently (once per month) but causes the application to fail when it occurs. The question was asked if NO LOCK should be used, and if there was a difference between NO LOCK and READ UNCOMMITTED.
Cannon Loughry (Database Platform Specialist, Microsoft) researched the problem and sent us this response:
I have discussed the NO LOCK hint question raised at our last SQL Server User Group with several individuals internally. There is agreement, backed up in the article below, that the NO LOCK and READ UNCOMMITTED hints are synonymous. However, while these are generally used to improve performance when possible given the state of data and reporting purposes, the article referenced below shows our best practice for accomplishing this same goal without the risk of having issues arise without the potential for the problem described during our meeting.
SQL Server 2005 Row Versioning-Based Transaction Isolation: http://technet.microsoft.com/en-us/library/ms345124.aspx I have not been able to find any reference to the NO LOCK hints working differently in SQL Server 2005 from the way they behaved in 2000 as was indicated during the call.
Our next meeting will be the middle of May or early June and we will be sending out an update when we get the topic and speaker confirmed with a location.