The other day, during SQL Server Performance Tuning Practical Workshop, I walked into a very interesting situation. While tuning a query we had a situation when we had to check if the query is locked during the execution of not. To test our theory out we had to put a nolock hint for every single table of the long query. However, it was no way possible to do so as the Stored Procedure was super huge and involved over 90 tables and 14 views in multiple SQL Statements. It was indeed a huge challenge for a developer team to modify that SP. If you ever face such situations, you should not stress out. There is a much simpler way to read uncommitted data.
Before you continue reading this blog post, please note that I personally do not prefer to use to NOLOCK hints in my business, as it will read the dirty data and often reading uncommitted data creates problems with database integrity. There are many different ways to tune your query rather than using NOLOCK hint or using read uncommitted transaction isolation.
Let us see first a simple example how NOLOCK hint works with multiple tables.
SELECT * FROM [Application].[Cities] ct WITH (NOLOCK) INNER JOIN [Application].[StateProvinces] sp WITH (NOLOCK) ON ct.StateProvinceID = sp.StateProvinceID GO
Now we will convert the same script to use read uncommitted transaction isolation.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO SELECT * FROM [Application].[Cities] ct INNER JOIN [Application].[StateProvinces] sp ON ct.StateProvinceID = sp.StateProvinceID GO SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO
Technically, there is absolutely no difference between the performance and impact of both the methods. I prefer to use the second method more often as it is easier to write and test. Again, if possible, I like to stay away from reading uncommitted methods to read the data.
Reference: Pinal Dave (https://blog.SQLAuthority.com)
