Hi all, I've a problem when deleting 1.2 millions of 7.3 millions row. it takes very long time.file size is 16gb. working on intel xeon 2.0GHz (4 cpus) and 4 GB of RAM. The SQL syntax is : DELETE Material WHERE MatCode Like 'E9AAAA%' Table Structure : CREATE TABLE [dbo].[Material]( [MatId] [int] IDENTITY(1,1) NOT NULL, [GroupId] [int] NULL, [MatCode] [nchar](20) NULL, [MatDescr] [nvarchar](300) NULL, [UOM] [char](10) NULL, [InputBy] [nchar](36) NULL, [InputDt] [smalldatetime] NULL, [UpdateBy] [nchar](36) NULL, [UpdateDate] [smalldatetime] NULL, CONSTRAINT [PK_Material] PRIMARY KEY CLUSTERED ( [MatId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] when i saw on task manager, it's only takes small resources, under 10% processor usage (1 cpu working and others relatively idle) and only 1.6gb memory. can anyone help me how to optimize it ? Thanks. fokeman
Can you try this? set rowcount 10000 DELETE Material WHERE MatCode Like 'E9AAAA%' while @@ROWCOUNT> 0 Begin set rowcount 10000 DELETE Material WHERE MatCode Like 'E9AAAA%' end