Can a TRUNCATE or DROP table ever be very time consuming?

Question

A couple of months ago, I discovered in a (not so well maintained) database a totally useless table containing 430 million unindexed rows, growing by about 80 000 rows per day.

It was a top candidate for removal. However, I never got the opportunity to achieve this (a.k.a. not the priority). I did not even have the opportunity to try to delete it in a weekly refreshed dev database.

I was wondering whether a simple TRUNCATE or DROP could ever be very time-consuming? I mean, could this operation block a production environment for several minute like DELETEs can when they are not well written. Or are these safe operations?

Answer 1

I mean, could [TRUNCATE OR DROP TABLE] block a production environment for several minute like DELETEs can

For Microsoft SQL Server, in rare cases yes.

Both TRUNCATE and DROP are "metadata only" operations, meaning that they just involve a few changes to the internal catalog tables. So they are never resource-intensive. But they both require Schema Modification locks (Sch-M) on the table.

When you request a Sch-M lock you have to wait until all incompatible locks are released. Every query that references the table needs an incompatible Sch-S lock. After you request the Sch-M lock, you will wait for all running queries to complete, any new query will be blocked behind your Sch-M lock request.

Before SQL 2012 this behavior was slightly different, as NOLOCK/READ UNCOMMITTED queries were able to acquire Sch-S locks while you wait for your Sch-M, and so you might have to wait forever.

In either case the result is that if there are lots of long-running queries against the table, the TRUNCATE or DROP will both take some time, and block other queries while it waits.

Normally this isn't a big deal. If you are DROPing or TRUNCATEing a table, it's unlikely that other sessions will have running queries against it, or need to run new queries that reference it.

For scenarios where a metadata operation needs to be performed on a busy table SQL Server introduced "Low Priority Schema Lock Waits" in SQL 2014. This allows ALTER TABLE or ALTER INDEX to wait for a Sch-M lock without blocking new requests. The session just waits for a lull in the activity, an instant where no session owns a Sch-S lock, and then makes the change. See New functionality in SQL Server 2014 – Part 3 – Low Priority Wait for a detailed explanation of the functionality, the history of how Schema locks behaved before SQL 2012, and the motivation for the enhancement.

Anyway in the unlikely event that you need low priority waits for TRUNCATE table, you can replicate it with ALTER TABLE … SWITCH to a staging table, followed by DROPping the staging table. EG

create table t_stage ...;

alter table t   
switch to t_stage
with ( wait_at_low_priority ( max_duration = 4 minutes, abort_after_wait = self ) )  

drop table t_stage

Answer 2

• MyISAM -- DROP is like a file delete. TRUNCATE performs the C file action of removing all the data but leaving the file in existence.
• InnoDB with its own tablespace (cf innodb_file_per_table) -- like a file delete
• InnoDB without its own tablespace -- lots of blocks to free up; this could take time.

A file drop can be fast or not so fast, depending on the Operating system. Some OS's run around freeing up the blocks right then and there; you have to wait. And, because it is an OS operation, this may interfere with virtually anything else. What OS?

"Minutes" seems long. How many minutes? How many GB?

Answer 3

For MySQL, TRUCNATE is implemented as a DROP ... CREATE. It requires a lock for the DROP, which it will wait for. It does not invoke ON DELETE triggers. It causes an implicit commit. Whether or not it's atomic depends on storage engine.

For more information read,

• https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html

Can it be time consuming? Yes. Is it less-safe than DELETE, but faster -- yes it's an optimization.