Delete Statement Doesn't Use Index

902 Views Asked by At

I've got a table with the following schema...

CREATE TABLE [dbo].[Object1](
[id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Column1] [datetime] NOT NULL,
[Column2] [int] NOT NULL,
[Column4] [char](4) NOT NULL,
[Column5] [money] NOT NULL,
[Column6] [bigint] NOT NULL,
[Column7] [char](6) NOT NULL,
[Column3] [bit] NOT NULL,
[Column8] [bigint] NULL,
CONSTRAINT [pk_Object1] PRIMARY KEY NONCLUSTERED 
([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)) 
GO
ALTER TABLE [dbo].[Object1] ADD  CONSTRAINT [DF_Object1_Column1]  DEFAULT (getutcdate()) FOR [Column1]
GO
ALTER TABLE [dbo].[Object1] ADD  CONSTRAINT [DF_Object1_Column3]  DEFAULT ((0))     FOR [Column3]
GO

Each night, a delete query runs against this table...

delete from Object1 
where ( Column1 < DATEADD(dd, -40, GETUTCDATE()) )
OR ( Column3 = ? and Column1 < DATEADD(dd, -3, GETUTCDATE()))

The table has

  1. Index2 (id) - clustered, unique located on PRIMARY
  2. Index3 (id) - nonclustered located on PRIMARY
  3. Index1 (id) - nonclustered, unique, primary key located on PRIMARY
  4. Index4 (Column7) - nonclustered located on PRIMARY
  5. Index5 (Column2, Column7, Column4, Column5) - nonclustered located on PRIMARY

The query produces the following plan...

Execution Plan for Delete Statement

The statement causes blocking in a highly concurrent table. The blocking ripples into system errors.

If I recreate "Object1_IX" to cover the "Object1" field, this still happens. The table has 6,568,449-rows and trims about 200,000 a night.

0

There are 0 best solutions below