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 toErrorReports.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):