이글은 MS SQL Server에서 수백만건의 데이터 행을 삭제할때 성능감소를 낮게 유지하기 위함을 위한 가이드이다.
- 특정테이블 'ExampleTable' 에 있는 수백만건이 있다.
- TRUNCATE 문을 권한불충분이나 다른 문제로 수행할 수 없는 조건이다.
이때 당신이 단일 트랜잭션에서 테이블에 있는 모든 행을 제거하려만 다음과 같이 해야 한다.
DELETE FROM ExampleTable
위 구문을 수행하는 순간 SQL서버는 트랜잭션을 처리하기 위하여 트랜잭션 로그에 모든 변경 사항을 작성하고, 완전한 테이블 잠금을 수행할 것이다.
위 설명은 잠재적으로 큰 문제를 몇가지 가지고 있다.
- 트랜잭션 로그는 변경사항이 기록되는 만큼 확장 할 수 있다. 테이블이 큰 경우 당신이 수행한 구문은 트랜잭션 로그 디스크에 있는 모든 공간을 사용할 위험을 가진다.
- 테이블의 잠금 시간동안 다른 응용프로그램이 해당 테이블에 액세스를 필요로 하는 경우, 응용프로그램은 테이블을 사용할 수 있을때까지 기다려야 한다. 응용프로그램에 타임아웃이 설정되어있는경우 응용프로그램을 사용하는 사용자는 정상적인 응답결과를 받지 못 할 것이다.
- 트랜잭션 로그가 증가함에 따라 트랜잭션 로그디스크도 팽창(?)되며 로그디스크를 공유하는 모든 데이터 베이스에서 성능저하가 일어날 수 있다.
- SQL Server 버퍼 풀에 할당 된 메모리의 양에 따라, 다른 쿼리의 성능을 하락시킬 수 있다.
- 수행된 쿼리가 너무 오래 걸리는것을 염려하여 수행중인 해당 쿼리 프로세스를 강제로 죽일 경우 SQL Server에서는 롤백처리에 대한 많은시간을 소비하게 될 수 있다.
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