Sql-server – SQL Server “empty table” is slow after deleting all (12 million) records

sql serversql-server-2008

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?!?

Best Answer

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.

Related Question