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)등을 추가 하여 사용할 수 있다.

끝.

 

출처 : MSSQL 대량 데이터 삭제 요령 – 블로그 술이 (sooli.com)