March 7, 2025 06:05 by
PeterEasy fix bottlenecks that may be resolved with the appropriate tuning techniques are frequently the cause of SQL Server performance problems. The DELETE statement will be the main topic of this little blog.
Even in simple recovery mode, DELETE statements have the drawback of consuming transaction log space and requiring unnecessary logical reads. Whereas TRUNCATE eliminates every row of a table or partition at the storage for a far quicker and more effective operation, DELETE is a row-based action that produces a lot of logical reads. Although both DELETE and TRUNCATE eliminate data from a table, their behavior varies with regard to rollback capabilities, recovery, logging, and performance.
Performance-wise, the DELETE statement is problematic because it necessitates locking and logging. Because each delete is recorded separately in the transaction log, ROLLBACK is possible. However, when using a WHERE clause to remove specific data within a statement, deleting data with foreign key restrictions, triggering triggers, or conducting rollbacks (outside of a transaction), DELETEs are required. It will be far more effective to use a DROP or TRUNCATE to remove every row if none of these conditions are met. Instead of performing a single action, TRUNCATE dealslocates all of the table's pages, which improves efficiency.
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!
HostForLIFEASP.NET SQL Server 2022 Hosting
