SQL Server Performance

T-SQL Performance Issue when deleting large data

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by fokeman80, Jun 13, 2011.

  1. fokeman80 New Member

    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
  2. Madhivanan Moderator

    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

Share This Page