The truncate option is fast and efficient but did you know that it takes a certain lock where you could actually be blocked?
NOLOCK, some say it’s a fast option for queries and will never cause blocking, I say it’s quite dirty and MAY cause blocking.
I used to think that a NOLOCK (which is the same as read uncommitted) hint actually meant that a “no lock” was used, I was very wrong and the naming doesn’t really help. Let’s have a look.
I will run a random SELECT statement with the hint, such as
SELECT * FROM Sales.BigSalesOrderDetailEnlarged WITH (NOLOCK)
Then I will check the “go to” DMV for locks.
SELECT resource_type, resource_associated_entity_id, request_status, request_mode,request_session_id, resource_description FROM sys.dm_tran_locks WHERE resource_database_id = 13
A Sch-S (schema stability) lock is taken. This is a lightweight lock; the only lock that can conflict with this is a Sch-m (schema modification) lock. (C = Conflict). This means that a NOLOCK can actually block for example against an ALTER TABLE command.
Below shows the example of when my NOLOCK query is being blocked by ALTER INDEX commands:
SELECT resource_type, resource_associated_entity_id, request_status, request_mode,request_session_id, resource_description FROM sys.dm_tran_locks
So there you go, it can be involved in blocking.
This is my example data.
In Window 1 using SQL Server management Studio I begin an open UPDATE statement – notice that it has NOT been committed.
BEGIN TRAN UPDATE [HumanResources].[Department] SET Name = 'Global' WHERE DepartmentID = 16
In window 2 I do a NOLOCK select statement.
SELECT * FROM HumanResources.Department (NOLOCK)
Now that’s dirty.