How can I delete a large set of records on a live database that maxes out our DTUs?

Question

We have a table in our database that has over 32 million records and we need to purge about 30 million of them based on a query. When we try this it maxes out our DTUs and brings all other queries to a halt. Since this is a live database, what would be the best way to handle this? We've tried increasing our DTUs to the max temporarily, but that still brought everything to a crawl. One option we haven't tried is copying the table to another database on it's own elastic pool separate from production, and running the cleanup there. Then deleting everything that isn't new (the live table will still be adding new records while we do this process) and inserting the ~ 2 million remaining records. Has anyone tried copying that many records to an existing table? Does that eat up all the DTUs? Since we need to maintain the production access to the 2 million records we need to keep, doing a delete then a full insert might not be an option unless we schedule some down maintenance time. We also have an on-premise SQL server that would be MUCH faster that Azure. Is there a way to download a copy of the data locally, doing our cleanup there, then uploading the results?

Answer 1

I would create an empty replica of the big table, copy to the replica the 2 million rows, then drop the original table, and finally rename the replica as the original table.

I would also try to delete the rows in batches of 100,000 or less. You an even add a delay after each delete executes.

while exists (select 1 from your_table where <your_condition>)
delete top(100000) from your_table
where <your_condition>

You can also copy data from one database to another using an elastic query.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential  
WITH IDENTITY = '<username>',
SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='<server>.database.windows.net',
    DATABASE_NAME='<db>',
    CREDENTIAL= SQL_Credential
);

CREATE EXTERNAL TABLE [dbo].[source_table] (
    [Id] BIGINT NOT NULL,
    ...
)
WITH
(
    DATA_SOURCE = RemoteReferenceData
)

SELECT *
 INTO target_table
FROM source_table