SQL Server performance issues often stem from easy fix bottle necks that can be fixed with the right tuning strategies. This short blog will focus on the DELETE statement.
The problem with DELETE statements is that it requires excessive logical reads and consumes transaction log space, even in simple recovery mode. DELETE is a row-based operation and generates large number of logical reads whereas TRUNCATE removes all of the rows of a table or partition at the storage, for a much faster and more efficient operation. Both DELETE and TRUNCATE remove data from a table, but they behave differently in terms of performance, recovery, logging, and rollback capabilities.
The issue with the DELETE statement when it comes to performance is that is requires locking and logging. Each Delete is individually logged in the transaction log allowing for ROLLBACK. While DELETEs are needed when removing specific data within a statement using a WHERE clause, removing data with foreign keys constraints, firing triggers or performing rollbacks (outside of a transactions). If none of these conditions apply, using a DROP or TRUNCATE will be much more efficient when removing all rows. The performance gains when TRUNCATE is performed instead comes from the deallocation of all the pages in the table rather than a logged operation.
If you can eliminate the need to DELETE the data by using TRUNCATE or DROP instead you can get an immediate performance boost for the query, stored procedure or function. Let’s take a look at a very simple example.
Example
CREATE TABLE ExampleTable ( ID INT IDENTITY(1,1) PRIMARY KEY, Item VARCHAR(100) ); INSERT INTO ExampleTable (Item) SELECT TOP 1000000 'SampleItem' FROM [Production].[TransactionHistoryArchive]; -- Using table to generate rows SET STATISTICS TIME ON; DELETE FROM ExampleTable; SET STATISTICS TIME OFF; SET STATISTICS TIME ON; TRUNCATE TABLE ExampleTable; SET STATISTICS TIME OFF;
RESULTS
- DELETE
(89253 rows affected)
SQL Server Execution Times:
CPU time = 328 ms, elapsed time = 1851 ms.
(89253 rows affected)
- TRUNCATE
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
Completion time: 2025-02-27T11:16:28.1156110-05:00
You can easily see the difference.
During code reviews be sure to test the difference in the operations and see if the DELETE is better replaced by something. If this is not feasible be sure to properly index for the DELETE operation for better efficiency. Remember to keep one key point in mind, because TRUNCATE is not logged, it cannot be rolled back UNLESS it is inside an explicit transaction. So use this power carefully!
