Why do TRUNCATE and DROP TABLE complete so fast?

Question: I’ve often wondered why it is that I can drop a table with billions of records in it and it completes immediately. Even if it it just deallocating pages, that’s going to take a lot of time. Can you explain what’s going on?

Answer: The answer is the deferred-drop mechanism.

  • Perform a lock ‘probe’ of each of the eight pages in the extent (i.e. perform an acquire-exclusive-lock-and-immediately-release-it operation).
  • If all eight pages locks can be probed successfully, mark the extent deallocated.

The problem is that the extent X lock needs to be held until the end of the transaction, otherwise another thread could allocate and use the extent, making rollback of the drop or truncate very problematic. The more extents there are, the more X locks there are, and the more lock memory is used. With a large enough table trying to be dropped/truncated in one go, back in the SQL Server 2000 days it was possible to run out of memory and the operation would fail.

In SQL Server 2000 SP3 we introduced the deferred drop mechanism. This works by unhooking the allocations for a table and placing them on the deferred drop queue. A background task then does the deallocation process in relatively small batches, ensuring there is no possibility of running out of lock memory and the operation failing.

The upshot of this is that a drop/truncate of a large table will complete immediately now – as the only operation being performed at the time of the drop/truncate is the manipulation of allocation metadata.

This also explains why a drop/truncate appears to be non-logged. The transaction that does the allocation metadata manipulation doesn’t generate many log records. If you wait a few minutes and look in the log again though, you’ll see thousands of log records – each deallocating an extent. That’s the deferred drop background task at work.

Discuss this Blog Entry 1

on Jun 27, 2012
Just gave this a try. I created a large table, truncated the table and looked at the log file size (no change). I then looked at the log file size again just a few seconds later and sure enough, it jumped in size.

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×