I need to delete a large number of records from a huge monster non-partitioned table on a Microsoft SQL Server 2005 database. Table contains about 8.388.483.218 records, I need to delete 1.069.012.556 of them. Because of the lack of disk space for tlog, and because I'd like to delete them gradually to avoid performance issues, I am using this query:
WHILE 1=1 BEGIN
WITH EventsTop1000 AS (SELECT TOP(100000) * FROM [MYTABLE] WHERE [timestamp] < CONVERT (DATETIME, '2008-07-01 00:00:00', 102)) DELETE EventsTop1000
IF (@@ROWCOUNT = 0) BREAK;
END
It runs very slowly and I need to speed up things if possible. Execution plan shows that about 62% is used into Clustered Index Delete step and another 33% is used for a non-clustered index delete (table has two indexes). Any idea? What about dropping indexes, delete and then rebuild indexes?
Best Answer
Try something like this:-
set rowcount 100000
delete from [mytable] where [timestamp] < CONVERT (DATETIME, '2008-07-01 00:00:00', 102) go 1000
Doing it this was removes the logging load, so you won't grow the LDF file as much (assuming Simple Logging).