MSSQL 대량 데이터 삭제 요령

아래글은 SQL Server 대량 데이터 삭제 · GitHub 에서 갖고왔습니다.

이글은 MS SQL Server에서 수백만건의 데이터 행을 삭제할때 성능감소를 낮게 유지하기 위함을 위한 가이드이다.

전제조건은 다음과 같다.

• 특정테이블 ‘ExampleTable’ 에 있는 수백만건이 있다.
• TRUNCATE 문을 권한불충분이나 다른 문제로 수행할 수 없는 조건이다.

이때 당신이 단일 트랜잭션에서 테이블에 있는 모든 행을 제거하려만 다음과 같이 해야 한다.

DELETE FROM ExampleTable

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

위 설명은 잠재적으로 큰 문제를 몇가지 가지고 있다.

• 트랜잭션 로그는 변경사항이 기록되는 만큼 확장 할 수 있다. 테이블이 큰 경우 당신이 수행한 구문은 트랜잭션 로그 디스크에 있는 모든 공간을 사용할 위험을 가진다.
• 테이블의 잠금 시간동안 다른 응용프로그램이 해당 테이블에 액세스를 필요로 하는 경우, 응용프로그램은 테이블을 사용할 수 있을때까지 기다려야 한다. 응용프로그램에 타임아웃이 설정되어있는경우 응용프로그램을 사용하는 사용자는 정상적인 응답결과를 받지 못 할 것이다.
• 트랜잭션 로그가 증가함에 따라 트랜잭션 로그디스크도 팽창(?)되며 로그디스크를 공유하는 모든 데이터 베이스에서 성능저하가 일어날 수 있다.
• SQL Server 버퍼 풀에 할당 된 메모리의 양에 따라, 다른 쿼리의 성능을 하락시킬 수 있다.
• 수행된 쿼리가 너무 오래 걸리는것을 염려하여 수행중인 해당 쿼리 프로세스를 강제로 죽일 경우 SQL Server에서는 롤백처리에 대한 많은시간을 소비하게 될 수 있다.

해결 방안으로는 아래 데모코드와 같이 TOP절을 사용하여 LOOP내에서 작은 양의 행을 반복적으로 삭제하는 것이다.

SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP (1000)
FROM LargeTable
END

또는

DoItAgain:
DELETE TOP (1000)
FROM ExampleTable

IF @@ROWCOUNT > 0
GOTO DoItAgain

필요한 경우 WHERE 절을 추가하여 세부 조건을 필터링 할수도 있고, 오류처리/트랜잭션(COMMIT / ROLLBACK)등을 추가 하여 사용할 수 있다.

끝.

원문) http://dbadiaries.com/how-to-delete-millions-of-rows-using-t-sql-with-reduced-impact

응용예시

WHILE Exists(Select TOP 1 [날짜컬럼] FROM [테이블] WITH(NOLOCK)        
WHERE [날짜컬럼] < dateadd( d , -180 , getdate()))

BEGIN

DELETE TOP (5000) FROM [테이블]

WHERE [날짜컬럼] < dateadd( d , -180 , getdate())

END

검색에 CPU 점유율과 디스크 IO를 피하기 위해서는 날짜컬럼에 인덱스를 추가하는걸 권고 합니다. 위에 설명에도 트랜잭션의 단순화 그리고 삭제로 인한 LOCK 지연이나 블럭 방지를 위해서는 작은 단위로 반복구문을 통하여 문제를 해결할수가 있습니다.

속도도 인덱스가 걸린 컬럼을 기준으로 삭제를 진행하면 매우 빠르기에 응용예시 구문을 잘 활용하면 도움이 될거 같습니다.