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?