SQL Server “empty table” is slow after deleting all (12 million) records?

I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a new data set.

However, commands that once ran instantly on an empty table such as count(*) and select top 1000 in SQL Management Studio now take eons to run.

SELECT COUNT(*) FROM TABLE_NAME 

took over 11 minutes to return 0, and SELECT TOP 1000 took almost 10 minutes to return an empty table.

I’ve also noticed that the free space on my hard drive has literally disappeared (down from about 100G to 20G). The only thing that happened between was a single query I ran:

DELETE FROM TABLE_NAME

What in the world is going on?!?

You’ve already been told about why TRUNCATE would be so much faster/better/sexier than DELETE, but there’s still a question left to address:

Why is SELECT slower after DELETE completed?

That is because DELETE has only ghosted the rows. The table is just as big as when it had 12M rows, even though it has none. To count the rows (0), it takes just as much time it took to count 12M rows. In time the ghost cleanup process will garbage collect these ghosted records and deallocate pages that contained only ghosts, and your SELECTs will speed up. But right now if you check Skipped Ghosted Records/sec in perfmon is probably skyrocketing during SELECT COUNT(*). You could also speed up things by rebuilding the table: ALTER TABLE ... REBUILD.

TRUNCATE would had also taken care of this problem, as it leaves no ghosts behind.

See also Inside the Storage Engine: Ghost cleanup in depth.

DELETE statements delete rows from a table one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Since you mentioned your table had huge data (12 million records), after deletion of which your Hard disk is out of space, check the size of your Database Log file. It would have most probably grown.

a Better way would have been:

TRUNCATE TABLE_NAME

(This was originally a comment to @DaveE’s answer, but I’ve put it into its own answer because it got long)

TRUNCATE is a logged operation. It has to be otherwise it’s not ACID-compliant. However, differences between TRUNCATE and DELETE:

• Log space usage: TRUNCATE only logs pages/extents* freed, whereas DELETE logs individual rows.
• Lock usage: TRUNCATE will generally use less locks, since it takes a table lock and page locks, as opposed to DELETE which uses row locks**.
• IDENTITY sequences: TRUNCATE resets the identity sequence on a table, if present.

(* An extent = 8 pages. TRUNCATE will log/remove extents if they’re all from that one table, otherwise it’ll log/remove pages from mixed extents.

** One side effect of this is that DELETE FROM TABLE can potentially leave empty pages allocated to the table, depending on whether the operation can get an exclusive table lock or not.)

So (back to the original question), TRUNCATE TABLE is conclusively better than DELETE FROM TABLE if you’re emptying the table out but want to keep the structure (NB: TRUNCATE can’t be used on a table that’s referenced by a foreign key from another table).

As noted in @Tullo’s comment, also check your database’s recovery model – if it’s full, then you either need to start taking log backups, or change your recovery model to simple. Once you’ve done either of those, you’ll probably want to shrink your log file as a once-off operation (NB: log file only) in order to reclaim all that free space.

Finally, another thing to be aware of – table statistics. run UPDATE STATISTICS <TABLENAME>' afterTRUNCATE/DELETE` so the query optimiser doesn’t get tripped up by old statistics.

(NOTE: I am not a DBA) DELETE is a logged operation, and it doesn’t free the space used. You probably have a large transaction log taking up space and table scans running on the ’empty’ table space. I’d guess you need to clear the transaction log and shrink your database. This StackOverflow article should get you started.

And use TRUNCATE TABLE when you want to do this in the future.

EDIT: My statement about TRUNCATE not being logged was in error. removed.