[MS-SQL] 대용량 데이터 삭제하기

대용량 데이터 행 삭제 작업 시 실행 조건 없이 삭제 할 경우 성능 감소 및 큰 문제를 몇가지 가지고 있다.

 

SQL 서버는 트랜잭션을 처리하기 위하여 트랜잭션 로그에 모든 변경 사항을 작성하고, 완전한 테이블 잠금을 수행할 것이다.

 

예를 들어 서비스중인 응용프로그램에서 해당 테이블에 액세스를 필요로 하는 경우, 응용프로그램은 테이블을 사용할 수 있을때까지 기다려야 하며. 응용프로그램에 타임아웃이 설정되어있는경우 응용프로그램을 사용하는 사용자는 정상적인 응답결과를 받지 못 할 것이다.

 

해결 방안으로 아래 테스트 코드를 참고하여 TOP LOOP 내에서 행을 반복적으로 삭제 처리해본다.

 

1. Log 테스트용 테이블 생성

CREATE TABLE [dbo].[Log](
    [id] [int] NOT NULL,
    [logDateTime] [datetime] NULL,
 CONSTRAINT [PK_LogTest] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

2. While문을 이용한 테스트 데이터 생성
데이터 만건을 넣은후에 테스트.

declare @loopCnt int 
declare @logDateTime datetime

set @loopCnt = 1
set @logDateTime = '2001-01-01'

while (@loopCnt < 10000)
begin

    INSERT INTO [dbo].[Log] ([id],[logDateTime]) VALUES (@loopCnt,@logDateTime)
    set @loopCnt = @loopCnt + 1
    set @logDateTime = DATEADD(second,1,@logDateTime)

end

 

3. DELTE TOP문을 이용한삭제
일괄로 삭제시 시간이 오래 걸리므로 TOP문을 사용해서 데이터를 삭제.

이때 Loop문을 사용하여 DELETE문사이에 적절한 Delay를 주어 시스템 멈추는 부분을 최소화 하길 추천한다. 

삭제문의 경우는 복구가 어려우므로 충분한 테스트를 하신후에 사용하길. 

@@rowcount는 전역변수이므로 delete top문을 loop문 마지막에 위치.

delete top(1000)    from LogTest where logdatetime < '2021-12-01 10:00'

while (@@rowcount > 0)
BEGIN

    --delay 100m/s
    waitfor delay '0:0:0:100'

    delete top (1000)  from Log where logdatetime < '2021-12-01 10:00'

END