SQL Server

효과적인 데이터베이스 유지 관리를 위한 유용한 팁

Paul S. Randal

한 눈에 보기:

• 데이터 및 트랜잭션 로그 파일 관리
• 인덱스 조각화 제거
• 정확한 최신 통계 유지
• 손상된 데이터베이스 페이지 검색
• 효과적인 백업 전략 수립

목차

데이터 및 로그 파일 관리
인덱스 조각화
통계
손상 검색
백업
요약

한 주에 몇 번씩 프로덕션 데이터베이스를 효과적으로 유지 관리하는 방법에 대한 질문을 받곤 합니다. 때로는 새로운 솔루션을 구현하고

새로운 데이터베이스의 특성에 맞게 유지 관리 방식을 세밀하게 조정할 수 있는 방법을 물어보는 DBA도 있습니다. 하지만 전문 DBA는 아니지만 여러 가지 이유로 데이터베이스를 소유하고 책임을 맡게 된 사람들로부터 이러한 질문을 더 자주 받습니다. 이러한 역할을 담당하는 사람들을 "비자발적 DBA"라고 부르겠습니다. 이 글의 목적은 모든 비자발적 DBA를 위한 가장 좋은 데이터베이스 유지 관리 방법에 대한 입문서를 제공하는 것입니다.

IT 분야의 대부분 작업과 절차와 마찬가지로 효과적인 데이터베이스 유지 관리를 위한 하나의 규격화된 솔루션은 없지만, 어떤 솔루션에서나 몇 가지 주요 영역에서 대해서는 공통적으로 다뤄야 합니다. 가장 중요한 영역을 5개 꼽자면 다음과 같습니다. 순서는 중요도와 상관이 없습니다.

• 데이터 및 로그 파일 관리
• 인덱스 조각화
• 통계
• 손상 검색
• 백업

제대로 관리되지 않은 데이터베이스는 이러한 영역 중 하나 이상에서 문제가 발생할 수 있으며, 이로 인해 결국 응용 프로그램 성능 저하 또는 작동 중지 및 데이터 손실로 이어질 수 있습니다.

이 글에서는 이러한 문제가 중요한 이유와, 문제를 완화할 수 있는 몇 가지 간단한 방법에 대해 설명합니다. 여기에서는 SQL Server­® 2005를 기준으로 설명하지만, SQL Server 2000과 곧 발표될 SQL Server 2008에서 찾을 수 있는 주요 차이점에 대해서도 언급할 것입니다.

데이터 및 로그 파일 관리

데이터베이스에 대한 책임을 맡을 때 항상 가장 먼저 확인해야 하는 영역은 데이터 및 (트랜잭션) 로그 파일 관리와 관련된 설정입니다. 구체적으로 다음과 같은 사항을 확인해야 합니다.

• 데이터와 로그 파일이 서로 구분되어 있으며 다른 모든 것들과 격리되어 있는가
• 자동 증가가 올바르게 구성되었는가
• 인스턴트 파일 초기화가 구성되었는가
• 자동 축소가 사용되지 않으며, 축소가 유지 관리 계획의 일부가 아닌가

데이터와 로그 파일은 별도의 볼륨에 있는 것이 가장 좋으며, 파일을 생성하고 확장하는 다른 응용 프로그램과 볼륨을 공유할 경우 파일 조각화 문제가 발생할 수 있습니다. 데이터 파일의 경우 과도한 파일 조각화는 쿼리 성능 저하에 어느 정도 영향을 줄 수 있으며, 특히 대량의 데이터를 검색할 때는 더욱 그렇습니다. 로그 파일은 성능에 매우 큰 영향을 줄 수 있으며, 필요할 때 각 파일 크기가 조금씩 증가되도록 자동 증가가 설정되었을 때는 특히 그렇습니다.

로그 파일은 내부적으로 VLF(Virtual Log File)라는 섹션으로 나누어지며, 로그 파일에 조각화가 많을수록 VLF도 많아집니다. 예를 들어, 로그 파일에 200개의 VLF가 있으면 로그 읽기(트랜잭션 복제/롤백 등의 경우), 로그 백업 및 SQL Server 2000의 트리거와 같은 로그 관련 작업에 좋지 않은 영향을 줄 수 있습니다. SQL Server 2005에서는 트랜잭션 로그 대신 행 버전 관리 프레임워크로 트리거 구현이 변경되었습니다.

데이터 및 로그 파일 크기 관리와 관련하여 가장 좋은 방법은 적당한 초기 크기로 파일을 생성하는 것입니다. 데이터 파일의 경우 단기간에 걸쳐 데이터베이스에 추가될 수 있는 추가 데이터를 고려하여 초기 크기를 결정해야 합니다. 예를 들어, 데이터의 초기 크기가 50GB이지만 앞으로 6개월 동안 50GB의 데이터가 더 추가될 것이라면, 크기를 여러 번 증가시켜 해당 크기에 도달하도록 하는 것보다 지금 데이터 파일을 100GB로 생성하는 것이 좋습니다.

하지만 로그 파일의 경우에는, 트랜잭션 크기(오래 실행되는 트랜잭션은 완료될 때까지 로그에서 제거할 수 없음) 및 로그 백업 빈도(로그의 비활성 부분 제거)와 같은 요소를 고려하여 좀 더 복합적으로 결정해야 합니다. 자세한 내용은 SQLskills.com에서 인기 있는 블로그 게시물인 "8 Steps to Better Transaction Log Throughput(높은 트랜잭션 로그 처리량을 위한 8단계)"을 참조하십시오. 이 글은 제 아내인 Kimberly Tripp이 작성했습니다.

초기 크기를 설정했으면 파일 크기를 자주 모니터링하여 적절한 시점에 수동으로 사전에 크기를 증가시켜야 합니다. 자동 증가는 비정상적인 이벤트가 발생할 경우 필요에 따라 파일이 증가할 수 있도록 만약을 위한 보호 조치로 남겨 두어야 합니다. 파일 관리를 전적으로 자동 증가에 맡기는 것을 반대하는 이유는 조금씩 자동 증가가 이루어지면 파일 조각화가 발생하고, 자동 증가는 예기치 않은 시점에 응용 프로그램 작업을 지연시키는 시간 소비 프로세스가 될 수 있기 때문입니다.

자동 증가 크기는 백분율이 아닌 특정 값으로 설정하여 자동 증가가 실행되는 데 필요한 시간과 공간을 제한해야 합니다. 100GB 데이터 파일을 예로 들 경우, 10%가 아닌 5GB의 고정된 자동 증가 크기로 설정하는 것이 좋습니다. 이렇게 설정하면 파일이 커질 때마다 용량이 10GB, 11GB, 12GB 등으로 계속 증가하는 대신 최종 파일 크기가 어떻게 되든지 항상 5GB씩 증가하게 됩니다.

트랜잭션 로그는 수동 또는 자동 증가를 통해 커지면 항상 0으로 초기화됩니다. 데이터 파일은 SQL Server 2000에서 동일한 기본 동작으로 수행되지만, SQL Server 2005부터는 파일을 0으로 초기화하는 작업을 생략하고 증가 및 자동 증가를 즉시 실행하는 인스턴트 파일 초기화를 사용할 수 있습니다. 대부분의 예상과 달리 이 기능은 모든 SQL Server 에디션에서 사용할 수 있습니다. 자세한 내용을 보려면 SQL Server 2005 또는 SQL Server 2008의 온라인 설명서 색인에서 "인스턴트 파일 초기화"를 찾아 보십시오.

마지막으로 축소는 어떠한 경우에도 사용하지 않도록 해야 합니다. 축소는 데이터나 로그 파일의 크기를 줄이는 데 사용할 수 있지만 리소스를 많이 사용하는 동시에 매우 비효과적인 프로세스로서, 데이터 파일의 논리 검색 조각화의 양이 크게 늘어나고(아래 참조) 성능 저하로 이어질 수 있습니다. SQL Server 2005 온라인 설명서의 축소 항목에는 이러한 취지의 경고가 포함되어 있습니다. 하지만 특수한 경우 개별 데이터 및 로그 파일을 수동으로 축소하는 것은 괜찮습니다.

자동 축소는 백그라운드에서 30분마다 실행되어 데이터베이스 자동 축소 옵션이 설정된 데이터베이스를 축소하려고 시도하는 가장 좋지 않은 기능입니다. 자동 축소는 여유 공간이 25% 이상인 데이터베이스만 축소한다는 점에서 예측이 불가능한 프로세스라고 볼 수 있습니다. 자동 축소는 리소스를 많이 사용하고 성능 저하 조각화를 유발하므로 거의 모든 환경에서 현명한 계획이 아닙니다. 따라서 자동 축소를 사용하지 않도록 설정해야 하며 그와 함께 다음 사항도 고려해야 합니다.

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;

정기적인 유지 관리 계획에 수동 데이터베이스 축소 명령을 포함시키는 것도 좋지 않습니다. 유지 관리 계획을 통해 데이터베이스가 축소된 후 데이터베이스가 계속해서 증가한다면 데이터베이스에 그만큼 실행할 공간이 필요하기 때문입니다.

가장 좋은 방법은 데이터베이스가 안정적인 상태의 크기로 증가하도록 두고 축소가 실행되지 않도록 하는 것입니다. 축소를 사용하는 데이터베이스에 대한 추가 정보 및 SQL Server 2005의 새로운 알고리즘에 대한 설명은 이전 MSDN® 블로그 blogs.msdn.com/sqlserverstorageengine/archive/tags/Shrink/default.aspx를 참조하십시오.

인덱스 조각화

파일 시스템 수준 및 로그 파일 내에서의 조각화와 별도로 테이블 및 인덱스 데이터를 저장하는 구조의 데이터 파일 내에서도 조각화가 발생할 수 있습니다. 데이터 파일 내에서 발생할 수 있는 조각화에는 다음 두 가지 기본 유형이 있습니다.

• 개별 데이터 및 인덱스 페이지 내의 조각화(내부 조각화)
• 페이지를 구성하는 인덱스 또는 테이블 구조 내의 조각화(논리 검색 조각화 및 익스텐트 검색 조각화)

내부 조각화는 페이지에 빈 공간이 많이 있는 경우입니다. 그림 1과 같이 데이터베이스에서 각 페이지의 크기는 8KB이고 96바이트의 페이지 헤더가 있으므로 한 페이지는 약 8096바이트의 테이블 또는 인덱스 데이터를 저장할 수 있습니다. 데이터 및 행 구조에 대한 특정 테이블 및 인덱스 내부는 제가 작성한 Inside The Storage Engine 범주의 블로그 sqlskills.com/blogs/paul을 참조하십시오. 각 테이블 또는 인덱스 레코드가 페이지 크기의 절반을 초과하면 페이지당 단일 레코드만 저장할 수 있으므로 빈 공간이 발생할 수 있습니다. 이 경우 인덱스 키를 GUID와 같이 임의의 삽입 지점을 유발하지 않는 것으로 변경하는 등 테이블 또는 인덱스 스키마 변경이 필요하므로 수정하기가 매우 어렵거나 불가능합니다.

그림 1 데이터베이스 페이지의 구조(크게 보려면 이미지 클릭)

일반적으로 내부 조각화는 삽입, 업데이트 및 삭제와 같이 페이지에 빈 공간을 남길 수 있는 데이터 수정으로 인해 많이 발생합니다. 채우기 비율을 잘못 관리할 경우에도 조각화를 유발할 수 있습니다. 자세한 내용은 온라인 설명서를 참조하십시오. 테이블/인덱스 스키마 및 응용 프로그램의 특성에 따라 이 빈 공간은 일단 만들어지면 재사용하지 못할 수 있으며, 데이터베이스에서 계속 증가하는 사용 불가능한 공간으로 남을 수 있습니다.

평균 레코드 크기가 400바이트인 1억 개의 행 테이블을 예로 들어 보겠습니다. 시간이 지남에 따라 응용 프로그램의 데이터 수정 패턴에 따라 각 페이지에는 평균 2800바이트의 여유 공간이 남게 됩니다. 테이블에서 필요한 총 공간은 약 59GB입니다. 8096-2800 / 400 = 13레코드(8KB 페이지당)이고 1억을 13으로 나누면 페이지 수가 계산됩니다. 공간이 낭비되지 않는다면 페이지당 20개의 레코드가 들어가므로 필요한 총 공간은 38GB로 줄어듭니다. 많이 절약된다는 것을 알 수 있습니다.

이렇게 데이터/인덱스 페이지에서 낭비된 공간으로 인해 동일한 양의 데이터를 보관하기 위한 더 많은 페이지가 필요하게 됩니다. 결과적으로 더 많은 디스크 공간을 사용할 뿐만 아니라, 쿼리에서 동일한 양의 데이터를 읽기 위해 더 많은 I/O가 필요합니다. 그리고 이러한 모든 추가 페이지는 데이터 캐시에서 더 많은 공간을 차지하므로 서버 비용이 늘어납니다.

논리 검색 조각화의 원인은 페이지 분할이라는 작업입니다. 논리 검색 조각화는 인덱스 키 정의에 따라 레코드를 특정 인덱스 페이지에 삽입해야 하는데 해당 페이지에 데이터를 삽입할 충분한 공간이 없을 경우에 발생합니다. 페이지는 반으로 분할되고 레코드의 약 50%가 새로 할당된 페이지로 이동합니다. 이 새 페이지는 대개 이전 페이지와 물리적으로 연속되어 있지 않으므로 조각화되었다고 말합니다. 익스텐트 검색 조각화는 개념적으로 유사합니다. 테이블/인덱스 구조 내의 조각화는 전체 테이블/인덱스 또는 WHERE 절 쿼리(예: SELECT * FROM MyTable WHERE Column1 > 100 AND Column1 < 4000)로 제한된 테이블/인덱스에 대해 SQL Server에서 효율적인 검색을 수행할 수 있는 능력에 영향을 줍니다.

그림 2는 새로 생성되어 채우기 비율이 100%이고 조각화가 없는 인덱스 페이지를 보여 줍니다. 이 페이지는 가득 차 있으며 페이지의 물리적 순서가 논리적 순서와 일치합니다. 그림 3은 무작위 삽입/업데이트/삭제 후 발생할 수 있는 조각화를 보여 줍니다.

그림 2 새로 생성되어 조각화가 없는 인덱스 페이지, 채우기 비율이 100%인 페이지(크게 보려면 이미지 클릭)

그림 3 무작위 삽입, 업데이트 및 삭제 후 내부 및 논리 검색 조각화가 발생한 인덱스 페이지(크게 보려면 이미지 클릭)

경우에 따라 테이블/인덱스 스키마를 변경하여 조각화를 막을 수도 있지만, 위에서 언급한 대로 이 작업은 매우 어렵거나 불가능합니다. 이러한 작업이 불가능한 경우 인덱스 재작성이나 재구성 등으로 이미 발생한 조각화를 제거하는 방법을 사용할 수 있습니다.

인덱스 재작성은 제대로 압축되고 최대한 연속된 새 인덱스 복사본을 만든 후 이전의 조각화된 인덱스를 삭제하는 작업입니다. SQL Server에서는 이전 인덱스를 제거하기 전에 새 인덱스 복사본을 만들므로 데이터 파일에 인덱스 크기에 해당하는 여유 공간이 있어야 합니다. SQL Server 2000에서는 인덱스 재작성 작업이 항상 오프라인으로 수행되었습니다. 하지만 SQL Server 2005 Enterprise Edition에서는 몇 가지 제한 사항이 있지만 온라인으로 수행할 수 있습니다. 반면, 재구성 작업은 기본 알고리즘을 사용하여 인덱스를 압축하고 조각 모음을 수행합니다. 실행할 추가 공간은 8KB만 필요하며 항상 온라인으로 실행됩니다. 실제로 SQL Server 2000에서 저는 인덱스 재작성의 공간 효율적인 대안으로 인덱스 재구성 코드를 온라인으로 수행하도록 작성했습니다.

SQL Server 2005에서 검토할 명령은 인덱스 재작성을 위한 ALTER INDEX … REBUILD 명령과 인덱스 재구성을 위한 ALTER INDEX … REORGANIZE 명령입니다. 이 구문은 SQL Server 2000 명령 DBCC DBREINDEX와 DBCC INDEXDEFRAG를 각각 대체합니다.

이러한 방식 간에는 생성되는 트랜잭션 로깅의 양, 데이터베이스에 필요한 여유 공간의 양, 프로세스를 작업 손실 없이 중단 가능한지 여부 등 많은 장단점이 있습니다. 이러한 장단점과 추가 정보에 대한 백서는 microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx에서 찾아 볼 수 있습니다. 이 문서는 SQL Server 2000을 기준으로 하지만 원리와 개념은 이후 버전에도 적용됩니다.

일부 사용자들은 어떤 인덱스가 조각화되며 조각화 제거를 통해 이점을 얻을 수 있는지에 대해 생각하지도 않고, 유지 관리 계획 옵션 등을 사용하여 매일 밤 또는 매주 모든 인덱스를 재작성하거나 재구성하기도 합니다. 최소의 노력으로 정상적인 상태를 유지하려는 비자발적 DBA에게는 이 방법이 좋은 솔루션이 될 수 있지만, 리소스가 중요시되는 대규모 데이터베이스나 시스템의 경우에는 현명한 선택이 아닐 수 있습니다.

좀더 정교한 방법은 DMV sys.dm_db_index_physical_stats(또는 SQL Server 2000의 DBCC SHOWCONTIG)를 사용하여 어떤 인덱스가 조각화되었는지 정기적으로 확인하고 작업 수행 여부 및 방법을 선택하는 것입니다. 백서에서는 이러한 경우 선택할 수 있는 방법에 대해서도 설명합니다. 또한 SQL Server 2005의 DMV sys.dm_db_index_physical_stats에 대한 온라인 설명서 항목의 예제 D(msdn.microsoft.com/­library/ms188917) 또는 SQL Server 2000 이상의 DBCC SHOWCONTIG에 대한 온라인 설명서 항목의 예제 E(at msdn.microsoft.com/library/aa258803)에서 이 필터링을 수행할 수 있는 몇 가지 예제 코드를 참조할 수 있습니다.

어떤 방법을 사용하든지 간에 정기적으로 조각화를 검사하고 수정하는 것이 좋습니다.

쿼리 프로세서는 SQL Server에서 사용할 테이블과 인덱스 및 결과를 얻기 위해 수행할 작업 등과 같이 쿼리를 실행하기 위한 방법, 즉 쿼리 계획을 결정하는 부분입니다. 이러한 결정 프로세스에서 고려해야 할 가장 중요한 사항 중 하나는 테이블이나 인덱스 내에서 열에 대한 데이터 값의 분포를 설명하는 통계입니다. 통계는 쿼리 프로세스에 유용하도록 반드시 정확하고 최신이어야 하며, 그렇지 않으면 좋지 않은 쿼리 계획이 선택될 수 있습니다.

통계는 테이블/인덱스 데이터를 읽고 관련 열에 대한 데이터 분포를 확인하여 생성됩니다. 특정 열에 대한 모든 데이터 값을 검색하는 전체 검색으로 통계를 작성할 수도 있고, 사용자가 지정한 데이터의 비율(샘플링 검색)을 기준으로 작성할 수도 있습니다. 열에서 값의 분포가 균일할 경우 샘플링 검색이 적합하며, 이 경우 전체 검색보다 통계 생성 및 업데이트 속도가 더 빠릅니다.

그림 4와 같이 AUTO_CREATE_STATISTICS 및 AUTO_UPDATE_STATISTICS 데이터베이스 옵션을 설정하면 통계를 자동으로 생성하고 유지 관리할 수 있습니다. 이러한 옵션은 기본적으로 설정되어 있지만, 데이터베이스를 상속 받은 경우 확인해 두는 것이 좋습니다. 경우에 따라 통계가 최신이 아닐 수 있는데, 이 경우 특정 통계에 대해 UPDATE STATISTICS 작업을 사용하여 수동으로 업데이트할 수 있습니다. 또는 최신이 아닌 모든 통계를 업데이트하는 sp_updatestats 저장 프로시저를 사용할 수도 있습니다. SQL Server 2000에서 sp_updatestats는 최신 여부와 상관 없이 모든 통계를 업데이트합니다.

그림 4 SQL Server Management Studio를 통해 데이터베이스 설정 변경(크게 보려면 이미지 클릭)

정기적인 유지 관리 계획의 일부로 통계를 업데이트하려는 경우 알아 두어야 할 사항이 있습니다. UPDATE STATISTICS 및 sp_updatestats 명령은 이전에 지정된 샘플링 수준이 있는 경우 기본적으로 이 수준을 사용하며, 전체 검색보다 비용이 적게 듭니다. 인덱스 재작성은 전체 검색을 사용하여 통계를 자동으로 업데이트합니다. 인덱스 재작성 후 수동으로 통계를 업데이트할 경우 통계의 정확성이 낮아질 수 있습니다! 수동 업데이트의 샘플링 검색이 인덱스 재작성으로 생성된 전체 검색을 덮어쓸 경우 이와 같은 현상이 발생합니다. 반면, 인덱스 재구성은 통계를 전혀 업데이트하지 않습니다.

다시 말해서, 전체 인덱스 재작성 전이나 후의 어느 시점에 모든 통계를 업데이트하는 유지 관리 계획을 사용하는 사용자들이 많은데 이 경우 자신도 모르게 통계의 정확성이 낮아질 수 있습니다. 모든 인덱스를 자주 재작성하도록 선택할 경우 통계도 영향을 받습니다. 조각화 제거를 사용하는 좀더 복잡한 경로를 거치도록 선택하는 경우 통계 유지 관리에 대해서도 신경을 써야 합니다. 다음은 제가 제안하는 방법입니다.

• 인덱스를 분석하여 작업 대상 인덱스와 조각화 제거를 수행할 방법을 결정합니다.
• 재작성되지 않은 모든 인덱스에 대해 통계를 업데이트합니다.
• 인덱싱되지 않은 모든 열에 대한 통계를 업데이트합니다.

통계에 대한 자세한 내용은 "Microsoft® SQL Server 2005의 쿼리 최적화 프로그램에서 사용하는 통계" 백서(microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx)를 참조하십시오.

손상 검색

지금까지 성능 측면에서 유지 관리에 대해 설명했습니다. 이제 주제를 바꾸어 손상 검색 및 완화에 대해 다뤄 보겠습니다.

여러분이 관리하고 있는 데이터베이스에는 어느 정도 중요한 정보가 들어 있을 것입니다. 이러한 정보를 위해 데이터가 손상되지 않고 재해 발생 시 복구할 수 있도록 해야 합니다. 여기서는 전체 재해 복구 및 고가용성 전략에 대한 구체적인 내용은 다루지 않지만 이를 위해 시작할 수 있는 몇 가지 간단한 사항을 알려 드리겠습니다.

대부분의 손상은 "하드웨어"로 인해 발생합니다. 왜 "하드웨어"라고 강조했을까요? 여기서 하드웨어는 "SQL Server 아래의 I/O 하위 시스템에 있는 것"을 말합니다. I/O 하위 시스템은 운영 체제, 파일 시스템 드라이버, 장치 드라이버, RAID 컨트롤러, 케이블, 네트워크 및 실제 디스크 드라이브와 같은 요소로 구성됩니다. 이러한 위치에서 많은 문제가 발생할 수 있습니다.

가장 많이 발생하는 문제 중 하나는 디스크 드라이브에서 데이터베이스 페이지를 쓰고 있을 때 전원 문제가 발생하는 경우입니다. 전원이 나가기 전에 드라이브에서 쓰기를 완료할 수 없는 경우 또는 쓰기 작업이 캐시에 저장되는데 드라이브의 캐시를 비울 수 있는 충분한 배터리 백업이 없는 경우 디스크에 불완전한 페이지 이미지가 남게 됩니다. 8KB 데이터베이스 페이지는 실제로 16개의 연속된 512바이트 디스크 섹터로 구성되기 때문에 이와 같은 문제가 발생합니다. 불완전한 쓰기의 경우 새 페이지의 일부 섹터가 쓰여졌지만 이전 페이지 이미지의 일부 섹터는 남아 있습니다. 이러한 상황을 조각난 페이지라고 합니다. 이러한 상황을 어떻게 검색할 수 있을까요?

SQL Server에는 이러한 상황을 검색할 수 있는 메커니즘이 있습니다. 즉, 페이지의 모든 섹터에서 2비트를 저장해 두고 특정 패턴을 이 위치에 씁니다. 이 작업은 페이지가 디스크에 쓰여지기 바로 전에 수행됩니다. 페이지를 읽을 때 패턴이 동일하지 않으면 SQL Server에서 페이지가 조각났음을 알고 오류를 발생시킵니다.

SQL Server 2005 이상부터는 페이지 체크섬이라는 좀 더 포괄적인 메커니즘을 사용하여 페이지의 손상을 검색할 수 있습니다. 즉, 페이지를 쓰기 바로 전에 페이지에 전체 페이지 체크섬을 작성한 다음 조각난 페이지 검색과 마찬가지로 페이지를 읽을 때 테스트합니다. 페이지 체크섬을 사용하도록 설정하면 페이지를 버퍼 풀로 읽어와 일정하게 변경한 후 다시 디스크에 쓰고 페이지 체크섬으로 보호해야 합니다.

따라서 SQL Server 2000에서는 조각난 페이지 검색을, SQL Server 2005 이상에서는 페이지 체크섬을 사용하도록 설정하는 것이 좋습니다. 페이지 체크섬을 사용하려면 다음과 같이 하십시오.

ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;

SQL Server 2000의 경우 조각난 페이지 검색을 사용하려면 다음과 같이 하십시오.

ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;

이러한 메커니즘을 사용하면 페이지를 읽을 때만 페이지가 손상되었는지 확인할 수 있습니다. 어떻게 하면 할당된 모든 페이지를 손쉽게 읽을 수 있을까요? 이를 위한 가장 좋은 방법 그리고 다른 종류의 손상을 찾을 수 있는 방법은 DBCC CHECKDB 명령을 사용하는 것입니다. 이 명령은 지정된 옵션과 상관없이 항상 데이터베이스의 모든 페이지를 읽으므로 페이지 체크섬 또는 조각난 페이지 검색을 수행합니다. 또한 쿼리를 실행할 때 사용자가 손상 문제를 발견할 경우 알 수 있도록 경고를 설정해야 합니다. 심각도 24 경고를 사용하면 위에서 설명한 모든 문제에 대해 통지를 받을 수 있습니다(그림 5).

그림 5 모든 심각도 24 오류에 대한 경고 설정(크게 보려면 이미지 클릭)

따라서 또 하나의 좋은 방법은 데이터베이스에 대해 정기적으로 DBCC CHECKDB를 실행하여 무결성을 확인하는 것입니다. 이 명령의 다양한 변형이 존재하며 이 명령을 실행하는 간격에 대한 많은 의견이 있습니다. 애석하게도 아직 이 문제에 대한 백서는 없습니다. 하지만 DBCC CHECKDB는 제가 SQL Server 2005에 대해 작성한 코드의 중요한 부분을 차지하므로 이와 관련하여 블로그에 포괄적으로 작성해 놓았습니다. 제 블로그(sqlskills.com/blogs/paul)의 "CHECKDB From Every Angle" 범주에서 일관성 검사와 권잘 절차 및 사용 방법에 대한 심도 높은 다양한 자료를 참조할 수 있습니다. 비자발적 DBA의 경우 가장 좋은 방법은 전체 데이터베이스 백업(아래 참조)을 수행할 때마다 DBCC CHECKDB를 실행하는 것입니다. 다음 명령을 실행할 것을 권장합니다.

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, 
  ALL_ERRORMSGS;

이 명령을 실행하여 결과가 출력되면 DBCC가 데이터베이스에서 손상을 발견한 것입니다. 그렇다면 문제는 DBCC CHECKDB로 손상이 발견될 경우 어떻게 하느냐는 것입니다. 그래서 백업이 필요한 것입니다.

손상이나 기타 재해가 발생할 경우 복구할 수 있는 가장 효과적인 방법은 백업으로 데이터베이스를 복원하는 것입니다. 여기에서는 백업을 보유하고 있으며 백업 자체는 손상되지 않았다고 가정합니다. 많은 사용자들은 백업이 없을 때 심하게 손상된 데이터베이스를 다시 실행하려면 어떻게 해야 하는지에 대해 묻곤 합니다. 간단히 답하면, 어떤 형태로든 비즈니스 로직과 관계형 데이터 무결성에 큰 위험 요소가 될 수 있는 데이터 손실을 감수하지 않고서는 불가능합니다.

따라서 정기적인 백업을 수행하는 것이 무엇보다 중요합니다. 백업 및 복원을 사용하는 구체적인 방법은 이 문서의 범위에서 벗어나지만, 백업 전략을 수립하는 방법에 대해 간략하게 설명하겠습니다.

첫째, 정기적으로 전체 데이터베이스 백업을 수행해야 합니다. 그러면 나중에 어떤 특정 시점에서 복원할 수 있습니다. 전체 데이터베이스 백업은 BACKUP DATABASE 명령을 사용하여 수행할 수 있습니다. 예제는 온라인 설명서를 참조하십시오. 추가로 보호하기 위해 WITH CHECKSUM 옵션을 사용할 수 있습니다. 이 옵션은 읽는 페이지의 페이지 체크섬이 있는 경우 이 체크섬의 유효성을 검사하고 전체 백업에 대해 체크섬을 계산합니다. 업무상 손실을 감당할 수 있는 데이터 또는 작업의 양을 고려하여 백업 빈도를 선택해야 합니다. 예를 들어, 하루에 한 번 전체 데이터베이스 백업을 수행할 경우 재해 발생 시 최대 하루 분량의 데이터를 잃을 수 있습니다. 전체 데이터베이스 백업만 사용할 경우 트랜잭션 로그 증가 관리와 관련된 복잡성을 피하기 위해 SIMPLE 복구 모델(일반적으로 복구 모드라고 함)을 사용해야 합니다.

둘째, 백업이 손상될 것을 대비하여 며칠 동안의 백업을 보관해 둡니다. 며칠 전의 백업은 백업이 전혀 없는 것보다 낫습니다. 또한 RESTORE WITH VERIFYONLY 명령을 사용하여 백업의 무결성을 확인해야 합니다. 자세한 내용은 온라인 설명서를 참조하십시오. 백업을 만들 때 WITH CHECKSUM 옵션을 사용한 경우 유효성 검사 명령을 실행하면 백업 체크섬이 유효한지 확인하고, 백업 내 페이지의 모든 페이지 체크섬을 다시 확인합니다.

셋째, 매일 전체 데이터베이스를 백업해도 문제 발생 시 데이터/작업 손실이 업무상 감당할 수 있는 범위를 초과한다면 차등 데이터베이스 백업을 사용해 볼 수 있습니다. 차등 데이터베이스 백업은 전체 데이터베이스 백업을 기준으로 하며, 마지막 전체 데이터베이스 백업 이후의 모든 변경 레코드를 포함합니다. 일반적으로 차등 백업과 증분 백업을 동일한 것으로 알고 있는데 그렇지 않습니다. 예를 들어, 4시간마다 차등 데이터베이스 백업과 함께 매일 전체 데이터베이스 백업을 수행할 수 있습니다. 차등 백업은 하나의 특정 시점 복구 옵션을 추가로 제공합니다. 전체 데이터베이스 및 차등 데이터베이스 백업만 사용하는 경우에도 SIMPLE 복구 모델을 사용해야 합니다.

복구에서 가장 마지막 방법은 로그 백업을 사용하는 것입니다. 로그 백업은 FULL(또는 BULK_LOGGED) 복구 모델에서만 사용할 수 있으며 이전 로그 백업 이후 생성된 모든 로그 레코드의 백업을 제공합니다. 전체 데이터베이스 그리고 경우에 따라 차등 데이터베이스의 정기 백업과 함께 로그 백업을 유지 관리하면 최근 몇 분 전으로의 복구를 포함하여 복구 가능한 특정 시점 수에 제한이 없습니다. 단점은 로그 백업을 수행하여 공간을 확보하지 않으면 트랜잭션 로그가 계속 증가한다는 것입니다. 예를 들어, 매일 전체 데이터베이스 백업, 4시간마다 차등 데이터베이스 백업 그리고 12시간마다 로그 백업을 수행할 수 있습니다.

백업 전략 및 설정 방법을 결정하는 일은 복잡할 수 있습니다. 적어도 정기적인 전체 데이터베이스 백업을 통해 복구 가능한 특정 시점을 최소한 하나 이상 보유하고 있어야 합니다.

이와 같이 데이터베이스의 무결성과 가용성을 유지하려면 반드시 해야 할 몇 가지 작업이 있습니다. 다음은 데이터베이스에 대한 책임을 맡은 비자발적 DBA가 수행해야 할 최종 확인 목록입니다.

• 과도한 트랜잭션 로그 파일 조각화를 제거합니다.
• 자동 증가를 올바르게 설정합니다.
• 예약된 축소 작업을 취소합니다.
• 인스턴트 파일 초기화를 설정합니다.
• 인덱스 조각화를 검색하고 제거하기 위한 정기적인 프로세스를 수행합니다.
• AUTO_CREATE_STATISTICS 및 AUTO_UPDATE_STATISTICS를 설정하고, 통계를 업데이트하기 위한 정기적인 프로세스를 수행합니다.
• 페이지 체크섬(SQL Server 2000의 경우 조각난 페이지 검색)을 설정합니다.
• DBCC CHECKDB를 실행하기 위한 정기적인 프로세스를 수행합니다.
• 특정 지점 복구를 위해 전체 데이터베이스 백업, 차등 및 로그 백업에 대한 정기적인 프로세스를 수행합니다.

이 글에서는 T-SQL 명령을 사용했지만 Management Studio에서도 많은 작업을 수행할 수 있습니다. 이 글이 효과적인 데이터베이스 유지 관리를 위한 유용한 정보가 되기를 바랍니다. 의견이나 질문은 paul@sqlskills.com으로 보내 주십시오.

Paul S. Randal은 SQLskills.com의 관리 이사 겸 SQL Server MVP이며, 1999년부터 2007년까지 Microsoft의 SQL Server 저장소 엔진 팀에서 근무했습니다. Paul 이사는 재해 복구, 고가용성 및 데이터베이스 유지 관리 분야의 전문가입니다. 블로그 주소는 SQLskills.com/blogs/paul입니다.

© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다.