SQL Server 복원 성능 최적화

 

·       Version : SQL Server

 

SQL Server에서 백업 파일을 복원할때 빠르게 복원하기 위한 최적화 방법을 소개한다. 방법을 사용한다고 해서 무조건 빠르게 복원되지는 않으며, 사용할 있는 시스템 리소스에 따라 최적화된 옵션을 제공함으로써 빠르게 복원할 있게 유도 하는 것이다.

 

데이터베이스 백업 복원에 대한 통계를 확인하기 위해 추적 플래그 3213, 3605 설정한다.

DBCC TRACEON (3213, -1)

DBCC TRACEON (3605, -1)

 

데이터베이스를 복원하면, SQL 이벤트 로그에서 아래와 같은 내용을 확인할 있다.

 

기본 설정을 사용하면  최대 전송크기는 1024K 이고 버퍼수는 6인것을 확인할 있다. 이때 사용되는 버퍼 공간은 6MB이다.

버퍼공간 = 최대 전송크기 X 버퍼

 

여기서 주목해야 부분은 메모리 제한(Memory limit) 이다. 현재 필자의 메모리 제한은 4095 MB (사용자 마다 다름)이지만 사용되는 버퍼 공간은 6MB 이다. 따라서 버퍼 공간을 늘려 복원 속도를 높일 있다. 최대 전송 크기 버퍼수 변경은 데이터베이스 복원시 추가 매개변수로 사용할 있다.

아래 예시는 최대 메모리 제한까지 사용하기 위해서 MAXTRANSFERSIZE 4096K 설정하고, BUFFERCOUNT 1000으로 설정하였다.

RESTORE DATABASE [DB] FROM DISL = N'D:\DB\BACKUP\DB.BAK' WITH REPLACE, STATS = 5, MAXTRANSFERSIZE = 4194302, BUFFERCOUNT = 1000

 

추가 매개변수를 사용하여 데이터베이스를 복원할 경우, 복원의 속도는 빨라지겠지만 동일 서버에서 운영되는 다른 서비스에 영향을 미칠 수도 있다. 해당 옵션을 사용하기 전에 시스템의 리소스 가용능력, 사용량등을 확인할 있도록 한다.

중요한것은 실제 운영 환경에 반영하기전에 테스트 환경에서 먼저 검증을 있도록 한다.

 

[참고자료]

·       Optimizing Backup and Restore Performance in SQL Server : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190954(v=sql.105)?redirectedfrom=MSDN

 

 

2020-02-28/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, Database Restore, SQL Server Recovery Step, 데이터베이스 복원, Database Recovery Process, BUFFERCOUNT, MAXTRANSFERSIZE

SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상

 

·       Version : SQL Server 2016 Enterprise Edition

 

SQL Server에서 트랜잭션 로그를 사용하여 데이터를 복원시, 평소와 다르게 매우 오래 걸리는 현상이 발생하였다. 처음에는 I/O 서브 시스템을 의심하고 물리장비 까지 교체하였으나, 증상을 동일하였다. 여러가지 가설을 세웠고, 원인 분석 결과, Slow query 트랜잭션 로그의 복원시간과 관련이 있다는 것을 발견할 있었다.

 

필자의 운영 환경은 10 마다 트랜잭션 로그 백업을 진행하고, 백업된 로그는 다른 서버에서 Read Only(STAND BY) 복원하여 부서에서 사용할 있도록 로그 쉬핑을 구성하였다. 10 마다 발생하는 로그의 양은 10MB~ 20MB 정도로 평상시 복원하는데 1 정도 소요되었다. 그런데 어느날 부터 복원시간이 증가하여 30분정도 걸렸다. 그러다 보니, 연관된 여러 ETL 시스템 리포트 시스템에 영향을 주게 되었었다.

아래 그래프는 트랜잭션로그 복원시 디스크의 Idle Time 캡처한 것으로 파란색이 평상시의 상태이고 빨간색과 노란색이 문제가 발생했던 때의 상태이다. 트랜잭션 로그 복원이 진행되는  동안 엄청난 I/O 발생하는 것을 확인할 있다.

 

트랜잭션 복원이 진행되는 동안 단계에 대한 작업시간을 확인하기 위해서 DBCC TRACE 명령을 사용하여 Inter log SQL Server이벤트 로그에 기록하도록 하였다.

DBCC TRACEON(3004, -1)

DBCC TRACEON(3605, -1)

DBCC TRACEON(3213, -1)

 

트레이스 적용 트랜잭션로그 복원을 진행하고, 아래와 같은 이벤트 로그를 확인할 있었다.

 

이벤트 로그를 확인해보면 redo, undo 관한 시간이 표시되어 있는데, Slow 쿼리가 발생할 경우 트랜잭션 커밋이나 롤백이 발생하지 않은 상태(더티페이지)에서 백업이 진행되고, 다음에 쿼리 실행이 완료되어 다름 트랜잭션 로그에 반영되었을때, 해당 쿼리의 결과에 따라, 데이터가 롤백되거나 커밋을 해야해서 (특히 롤백이 문제다) 데이터 페이지, 인덱스 페이지등에 반영하느라 디스크 I/O 오버헤드가 발생하고 전체적인 복원 시간이 느려졌다. 아래와 같은 단계로 진행된다.

 

1 단계 : 분석. 트랜잭션 로그의 마지막 체크 포인트에서 시작한다. 단계는 SQL Server 중지 더티 페이지 테이블 (DPT) 확인하고 구성한다. 활성 트랜잭션 테이블은 SQL Server 중지 커밋되지 않은 트랜잭션으로 구성된다.

 

2 단계 : 다시 실행. 단계는 데이터베이스를 SQL 서비스가 중지 시점의 상태로 되돌린다. 가장 오래된 커밋되지 않은 트랜잭션이 롤백의 시작점 이다. DPT 최소 로그 시퀀스 이름 ( 로그 레코드에 LSN으로 레이블이 지정됨) SQL Server 페이지에서 작업을 다시 실행해야하는 번째 시간이며 가장 오래된 열린 트랜잭션에서 다시 시작하여 기록 작업을 다시 실행해야 한다. 필요한 잠금 장치를 확보 한다.

 

3 단계 : 실행 취소. 여기에서 1 단계에서 식별 활성 트랜잭션 (SQL Server 중단 커밋되지 않은) 목록이 개별적으로 롤백된다. SQL Server 트랜잭션에 대한 트랜잭션 로그 항목 간의 링크를 따른다. SQL Server 중지 커밋되지 않은 트랜잭션은 모두 취소된다.

 

 

정리하면, Slow 쿼리 증가로 인해 활성 트랜잭션이 많아졌고, 트랜잭선 로그 백업이 진행될때 더티페이지가 증가함에 따라 해당 로그 파일로 복원시 롤백 커밋 작업이 진행되어 이때 I/O 오버헤드가 발생하여 전체적인 복원이 느려졌다.

 

[참고자료]

·       Understanding How Restore and Recovery of Backups Work in SQL Server  : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191455(v=sql.105)?redirectedfrom=MSDN

·       SQL Server Mysteries: The Case of the Not 100% RESTORE : https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/sql-server-mysteries-the-case-of-the-not-100-restore

·       SQLskills SQL101: Why is restore slower than backup : https://www.sqlskills.com/blogs/paul/sqlskills-sql101-why-is-restore-slower-than-backup/

·       SQL Server Database Recovery Process Internals – database STARTUP Command : https://www.sqlshack.com/sql-server-database-recovery-process-internals-database-startup-command/

 

 

2020-02-27/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, Database Restore, Log shipping, 트랜잭션 로그 복원, SQL Server Recovery Step, 데이터베이스 복원, 로그 전달, 로그쉬핑, Database Recovery Process

네트워크 드라이브에 데이터베이스 복원하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server에서 데이터베이스 복원작업을 진행 할 때 SSMS를 사용할 경우 로컬 드라이브만 표시 된다. 원격지의 네트워크 드라이브에 데이터베이스를 복원할 때 드라이브 목록에 네트워크 드라이브를 추가할 수 있는 방법에 대해서 알아 본다.

 

데이터베이스를 복원할 때 로컬의 드라이브만 표시 된다.

 

네트워크 드라이브를 추가 하기 위해 Windows에서 네트워크 드라이브를 매핑해야 한다.

 

네트워크 드라이브 매핑이 완료 되었으면 SQL Server에서 해당 네트워크 드라이브를 식별하기 위해 xp_cmdshell 명령을 사용해야 한다. Xp_cmdshell은 기본적으로 비활성화 되어 있으므로 sp_configure adufud을 사용하여 활성화 한다.

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

 

EXEC sp_configure 'xp_cmdshell',1

GO

RECONFIGURE

GO

 

Xp_cmdshell 명령으로 SQL에 대한 공유 드라이브를 정의 한다.

EXEC XP_CMDSHELL 'net use H: \\RemoteServerName\ShareName'

 

매핑된 새 드라이브를 확인하기 위해 다음 스크립트를 실행하면 매핑 된 드라이브에 있는 모든 파일의 목록을 보여준다.

EXEC XP_CMDSHELL 'Dir H:'

 

네트워크 드라이브의 파일의 목록이 조회가 된다면 정상적으로 연결되었다. SSMS에서 데이터베이스 복원 할 때 로컬 드라이브 외에 네트워크 드라이브 경로가 추가 된 것을 확인 할 수 있다.

 

매핑된 드라이브를 삭제는 다음 스크립트를 사용 한다.

EXEC XP_CMDSHELL 'net use H: /delete' /pre>

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-from-within-ssms/

 

2015-03-04 / 강성욱 / http://sqlmvp.kr

 

 

데이터베이스 복원, sqlserver, mssql, 네트워크 복원, 네트워크 드라이브 매핑, Restore to Networkdrive, 원격지 DB복원

최소한의 다운타임으로 데이터베이스 이동하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server를 운영하다보면 디스크의 공간 부족 또는 디스크의 성능 등으로 인하여 새로운 드라이브로 데이터베이스를 이동해야하는 상황이 발생 할 수 있다. 이때 대용량 데이터베이스를 최소한의 다운타임으로 이동하기 위한 여러가지 방법에 대해서 알아보자. 각 방법에는 장단점이 있다.

 

[데이터베이스 분리 후 이동하여 연결하기]

데이터베이스를 이동 할 때 많이 사용하는 방법이다. 데이터베이스를 분리하여 데이터 파일을 이동한 다음 데이터베이스를 연결 하는 방법이다.

exec sp_detach_db DBName

 

File Move

 

exec sp_attach_db DBName, Filepath

 

 

장점

  • 데이터베이스를 이동하는 가장 쉬운 방법이다.
  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.

단점

  • 파일이 이동하는 동안 데이터베이스를 사용할 수 없다.
  • 복제 된 데이터베이스가 분리 된 경우 배포를 할 수 없다.
  • 데이터베이스가 분리 되면 모든 메타데이터가 삭제 된다. 어떤 로그인의 계정의 기본 데이터베이스인 경우 mater 데이터베이스로 변경 된다. 또한 데이터베이스간 소유권이 끊어진다.
  • 데이터베이스를 분리 하기 전 스냅샷을 모두 삭제 해야 한다.
  • 데이터베이스가 미러링 되어 있는 경우 미러링 세션이 종료 될 때 까지 분리 할 수 없다.

 

 

[데이터베이스 백업 후 백업 파일을 이용하여 복원]

데이터베이스를 백업하고 WITH MOVE switch를 사용하여 새 파일의 위치를 지정하는 방법이다.

 

장점

  • 백업과 복원 방법만 알면 누구나 할 수 있다.
  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.

단점

  • 데이터베이스 복원이 완료 될 때까지 데이터베이스나 파일 그룹에 액세스 할 수 없다.
  • 백업 후 데이터가 변경된 경우 최근 백업을 다시 복원해야 한다.

 

 

[데이터베이스 OFFLINE 후 데이터베이스 이동]

데이터베이스를 오프라인으로 설정하고 수동으로 파일을 이동. 데이터베이스의 메타 데이터를 업데이트하는 명령을 실행하고 다시 온라인 시키는 방법이다.

 

장점

  • 데이터베이스 분리처럼 Detach – Attach 방법을 사용한다.
  • 테이블과 인덱스에 조각화를 생성하지 않는다.
  • 데이터베이스 분리와 달리 메타 데이터를 보존 한다.
  • 배포 및 미러된 데이터베이스도 작업 할 수 있다.

단점

  • 파일이 이동하는 동안 데이터베이스에 액세스 할 수 없다.

 

 

[새로운 파일 그룹을 추가하여 이동]

새로운 파일 그룹을 생성하고 테이블과 인덱스를 새로운 파일그룹에 다시 작성한다. 작업이 완료되면 기존 파일 그룹 삭제가 가능하다.

 

장점

  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.
  • 데이터베이스 메타 데이터가 보존 된다.

단점

주 파일 그룹에 대해서는 작동 하지 않는다.

  • 데이터베이스 설계가 변경되기 때문에 신중한 테스트가 필요하다.
  • 이전의 방법보다 많은 작업이 필요하다.

 

 

[로그 전달을 사용한 데이터베이스 이동]

로그 전달을 구성하여 데이터베이스를 이동한다. 복구모드가 심플일 경우 전체 복구 모두로 전환하여 구성 할 수 있다.

 

장점

  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.
  • 데이터베이스 메타 데이터가 보존된다.
  • 로그 전달 중에도 사용자는 기존 데이터베이스에 계속 액세스 할 수 있다.

단점

  • SQL Server Agent 실행이 필요하다.
  • 공유 폴더를 사용하기 때문에 폴더 사용 권한 검토가 필요하다.
  • 로그 전달에 대한 전문적인 지식이 필요하다.

 

 

[수동으로 로그 전달 및 복원]

필자가 주로 사용하는 방법으로 대용량 데이터베이스의 경우 전체 백업을 수행하여 먼저 복원해 놓고 주기적으로 트랜잭션 로그 백업을 복사하여 복원하는 방법이다. 마지막에 사용자 접근을 차단하고 마지막 로그 백업을 실행하여 새로운 데이터베이스에 복원한다.

 

장점

  • 테이블과 인덱스에 대한 조각화를 생성하지 않는다.
  • 데이터베이스 복원 시 이름 변경 및 파일 경로 변경 가능하다.
  • 데이터베이스 메타 데이터가 보존된다.
  • 기존 데이터베이스에는 사용자가 계속 액세스 할 수 있다.
  • 로그 전달보다 적은 구성이 사용된다.(백업, 복원만 하면 됨)

단점

  • 백업을 복원할 때 WITH MOVE 옵션을 사용해야 한다.

 

 

[DBCC SHRINK FILE 후 데이터베이스 이동]

DBCC SHRINKFILE 명령을 사용하여 빈 공간을 축소. 데이터베이스를 이동하는 방법이다. (굳이 추천하고 싶은 방법은 아니다.) 이 방법은 위의 모든 방법보다 가장 느린 방법이다. 이 방법은 성능에 영향을 미칠 수 있으므로 주의해야 한다.

 

장점

  • SHRINK FILE 작업 시 사용자가 데이터베이스에 계속 액세스 할 수 있다.

단점

  • 다른 방법보다 더 오래 걸릴 수 있다.
  • 테이블과 인덱스에 조각화를 많이 생산한다.
  • 로그가 기록되므로 전체 복구 모드의 경우 적합하지 않다.
  • SQL 로그 전달이 구성되어 있는 경우 권장되지 않는다.
  • 많은 시스템 리소스(특히 디스크IO)를 소비하므로 시스템 성능에 문제가 발생 할 수 있다.

 

 

대용량의 데이터베이스를 이동해야 하는 경우 각 운영 환경에 맞는 방법을 선택하여 최적의 시나리오를 구성하여 연습을 해야 한다. 특히 백업과 파일이동, 복원의 경우 해당 파일의 용량, 복사에 따른 네트워크 속도, 디스크의 성능 등을 고려하여 시간을 산정하면 최소한의 다운타임으로 이동이 가능하리라 생각한다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3212/options-to-move-a-big-sql-server-database-to-a-new-drive-with-minimal-downtime/

 

 

2014-04-24 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 데이터베이스 백업, 트랜잭션 백업, 전체 백업, 데이터베이스 이동, 파일그룹, 데이터베이스 복원

+ Recent posts