delete query takes forever

Question

I got a reasonable simple query:

With RowsToDelete AS
(
    SELECT TOP 500 Id
    FROM ErrorReports
    WHERE IncidentId = 611
)
DELETE FROM RowsToDelete 

However, it do not complete. I have tried several times. The last time I waited 8 minutes before canceling it.

ErrorReports contains about 22 000 rows. ErrorReportOrigins about the same.

Estimated execution plan:

Actual execution plan (for top 10, takes 28 seconds to complete):

Execution plan: https://www.brentozar.com/pastetheplan/?id=S1jUXDruz

Client stats:

What I've tried:

• ErrorReportOrigins did not have a clustered index (id), only a FK to ErrorReports.Id. I've added an id column (pk&identity).
• I've rebuilt all indexes (using this).
• Tried to delete from ErrorReportOrigins first (using the same CTE). No difference
• (The original CTE had a ORDER BY Id, I removed it to see if there was a difference)

I'm lost. Why on earth does it take so long? All SELECT statements are fast. And the DB isn't really big. The ErrorReports table is the largest one.

(This is an SQL Azure DB in an elastic pool)

Update

CREATE TABLE [dbo].[ErrorReports](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [IncidentId] [int] NOT NULL,
    [ErrorId] [varchar](36) NOT NULL,
    [ApplicationId] [int] NOT NULL,
    [ReportHashCode] [varchar](20) NOT NULL,
    [CreatedAtUtc] [datetime] NOT NULL,
    [SolvedAtUtc] [datetime] NULL,
    [Title] [nvarchar](100) NULL,
    [RemoteAddress] [varchar](45) NULL,
    [Exception] [ntext] NOT NULL,
    [ContextInfo] [ntext] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Indexes:

CREATE NONCLUSTERED INDEX [Application_GetWeeklyStats] ON [dbo].[ErrorReports]
(
    [ApplicationId] ASC,
    [CreatedAtUtc] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

CREATE NONCLUSTERED INDEX [ErrorReports_IncidentId] ON [dbo].[ErrorReports]
(
    [IncidentId] ASC,
    [CreatedAtUtc] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

The data is fairly large (used datalength in the query):

Answer 1

It seems likely the very large ntext data is highly fragmented, causing a large amount of random I/O (or other inefficiencies) when locating LOB fragments to delete. Maybe the elastic thingy needs more I/O horsepower too.

You may need to export and reload the data to solve this problem. Copying to a new table, dropping the old, then renaming the new would also work.

Unless you have an extremely good reason not to, I suggest you also change the data type from the old, deprecated ntext to the replacement nvarchar(max) while reloading the data.

It may also simply be a SQL Server limitation with large LOBs. See the related Q & A Slow Delete's of LOB data in SQL Server.

The usual recommendation is to move to an alternate storage solution when data averages 1MB or more. See the SQL Server technical paper FILESTREAM Storage in SQL Server 2008 by Paul S. Randal for details. This is not yet supported in Azure SQL Database, unfortunately.

Perhaps you would be better storing the large LOB data in Azure Blob storage instead, and only holding a link in the database itself.

Answer 2

Not sure if it will make a difference but this syntax should work:

DELETE TOP (500)
FROM ErrorReports
WHERE IncidentId = 611

I tested on a simple table and get a very simple query plan with a single index scan.

Answer 3

IMO ,you should reconsider the size of [Exception] ,[ContextInfo] .

for example ,is there any row in table where len(Exception)>1000

I think varchar(1500) is enough,it will improve your given query.

May be size cannot altered.

Alternatively you can go for RBAR

DECLARE @IncidentId INT = 611
DECLARE @TopSize INT = 10 --try 15,20 etc
DECLARE @BatchSize INT = 10 --try 15,20 etc
DECLARE @MaxLimit INT = 500
DECLARE @RowCount INT = 0

BEGIN TRY
    WHILE (@TopSize <= @MaxLimit)
    BEGIN
        DELETE TOP (@TopSize)
        FROM ErrorReports
        WHERE IncidentId = @IncidentId

        SET @RowCount = @@RowCount

        --PRINT @TopSize
        IF (
                @RowCount = 0
                OR @RowCount IS NULL
                )
            BREAK;
        ELSE
            SET @TopSize = @TopSize + @BatchSize
    END
END TRY

BEGIN CATCH
    --catch error
END CATCH