The truncate option is fast and efficient but did you know that it takes a certain lock where you could actually be blocked?
What am I talking about? When you issue a truncate it takes a Sch-M lock and it uses this when it is moving the allocation units to the deferred drop queue. So if it takes this lock and you look at the locking compatibility matrix below you will see what can cause a conflict (C).
So another workload that takes either a Sch-M or Sch-S lock means a conflict. So I will show you how your truncate is blocked by a select statement with a nolock hint which essentially takes a Sch-S lock.
The proof lies within sys.dm_tran_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 = 8
Here you see the SPID 57 being the truncate command in a WAIT request status.
Also confirmed via sp_whoisactive.
You probably knew this already but it is nice seeing it with examples.
Pingback: Dew Drop - August 1, 2018 (#2778) - Morning Dew
Pingback: Blocking A Truncate Statement – Curated SQL