데이터베이스 엔진 튜닝 관리자

Microsoft DTA(데이터베이스 엔진 튜닝 관리자)는 데이터베이스를 분석하고 쿼리 성능을 최적화하는 데 필요한 사항을 권장합니다. 데이터베이스 엔진 튜닝 관리자를 사용하면 데이터베이스 구조나 SQL Server의 내부 구조를 전문적으로 파악하지 못해도 인덱스, 인덱싱된 뷰 또는 테이블 파티션의 최적 집합을 선택 및 작성할 수 있습니다. DTA를 사용하여 다음과 같은 태스크를 수행할 수 있습니다.

  • 특정 문제 쿼리의 성능 문제 해결

  • 하나 이상의 데이터베이스 전반에서 많은 쿼리 집합 튜닝

  • 잠재적인 물리적 디자인 변경에 대한 탐구 가정(what-if) 분석 수행

  • 저장소 공간 관리

데이터베이스 엔진 튜닝 관리자의 이점

데이터베이스의 구조와 데이터베이스에 대해 실행되는 쿼리를 정확하게 이해하지 못하면 쿼리 성능을 최적화하기 어려울 수 있습니다. 데이터베이스 엔진 튜닝 관리자는 현재 쿼리 계획 캐시를 분석하거나 작성하는 Transact-SQL 쿼리의 작업량을 분석한 후 적절한 물리적 디자인을 권장해 주므로 이 태스크를 보다 쉽게 수행할 수 있습니다. 고급 데이터베이스 관리자를 위해 DTA는 다른 물리적 디자인 대안에 대한 탐구 가정(what-if) 분석을 수행할 수 있는 강력한 메커니즘을 제공합니다. DTA는 다음 정보를 제공할 수 있습니다.

  • 쿼리 최적화 프로그램을 사용하여 작업의 쿼리를 분석하여 데이터베이스에 대한 최상의 인덱스 조합을 권장합니다.

  • 작업에서 참조된 데이터베이스에 대한 정렬된 파티션 또는 정렬되지 않은 파티션을 권장합니다.

  • 작업에서 참조된 데이터베이스를 위한 인덱싱된 뷰를 권장합니다.

  • 인덱스 사용을 비롯한 제안된 변경 내용의 영향, 테이블 간 쿼리 분배, 작업에서의 쿼리 성능 등을 분석합니다.

  • 문제가 되는 쿼리를 해결하기 위해 데이터베이스를 튜닝하는 방법을 권장합니다.

  • 디스크 공간 제약 조건과 같은 고급 옵션을 지정하여 권장 구성을 사용자 지정할 수 있도록 합니다.

  • 특정 작업에 대한 권장 구성 구현의 효과를 요약하는 보고서를 제공합니다.

데이터베이스 엔진 튜닝 관리자는 다음과 같은 쿼리 작업 유형을 처리하도록 설계되었습니다.

  • OLTP(온라인 트랜잭션 처리) 쿼리만 수행하는 작업

  • OLAP(온라인 분석 처리) 쿼리만 수행하는 작업

  • OLTP 및 OLAP 쿼리가 혼합된 작업

  • 쿼리가 많은 작업(쿼리가 데이터 수정보다 많음)

  • 업데이트가 많은 작업(데이터 수정이 쿼리보다 많음)

DTA 구성 요소 및 개념

  • 데이터베이스 엔진 튜닝 관리자 그래픽 사용자 인터페이스
    작업을 지정하고 다양한 튜닝 옵션을 선택할 수 있는 사용하기 쉬운 인터페이스입니다.

  • dta 유틸리티
    데이터베이스 엔진 튜닝 관리자의 명령 프롬프트 버전입니다. dta 유틸리티를 통해 응용 프로그램과 스크립트에서 데이터베이스 엔진 튜닝 관리자의 기능을 사용할 수 있습니다.

  • 작업
    튜닝할 데이터베이스의 대표적인 작업이 포함된 Transact-SQL 스크립트 파일, 추적 파일 또는 추적 테이블입니다. SQL Server 2012 이상에서는 계획 캐시를 작업으로 지정할 수 있습니다.

  • XML 입력 파일
    데이터베이스 엔진 튜닝 관리자가 작업 부하를 조정하는 데 사용할 수 있는 XML 형식 파일입니다. XML 입력 파일은 GUI 및 dta 유틸리티에서 사용할 수 없는 고급 튜닝 옵션을 지원합니다.

제한 사항

데이터베이스 엔진 튜닝 관리자에는 다음의 제한 사항이 있습니다.

  • PRIMARY KEY 또는 UNIQUE 제약 조건을 강제로 실행하는 고유한 인덱스를 추가하거나 삭제할 수 없습니다.

  • 단일 사용자 모드로 설정된 데이터베이스를 분석할 수 없습니다.

  • 튜닝 권장 구성의 최대 디스크 공간이 실제 사용 가능한 공간을 초과하도록 지정하는 경우 데이터베이스 엔진 튜닝 관리자는 사용자가 지정한 값을 사용합니다. 그러나 권장 구성 스크립트를 실행하여 구현할 때 먼저 디스크 공간이 더 추가되지 않으면 해당 스크립트는 실패할 수 있습니다. 최대 디스크 공간은 dta 유틸리티의 -B 옵션으로 지정되거나 고급 튜닝 옵션 대화 상자에 값을 입력하는 방법으로 지정될 수 있습니다.

  • 보안을 위해 데이터베이스 엔진 튜닝 관리자는 원격 서버에 있는 추적 테이블에서 작업을 튜닝할 수 없습니다. 이 제한을 해결하려면 추적 테이블 대신 추적 파일을 사용하거나 추적 테이블을 원격 서버에 복사할 수 있습니다.

  • -B 옵션 또는 고급 튜닝 옵션 대화 상자를 사용하여 튜닝 권장 구성에 맞게 최대 디스크 공간을 지정할 때처럼 제약 조건을 설정할 때 데이터베이스 엔진 튜닝 관리자는 기존의 인덱스를 삭제해야 할 수도 있습니다. 이 경우 데이터베이스 엔진 튜닝 관리자 권장 구성의 결과로 인해 예상 향상률에 미치지 못할 수 있습니다.

  • dta 유틸리티의 -A 옵션을 사용하거나 튜닝 옵션 탭에서 튜닝 시간 제한을 선택하여 튜닝 시간을 제한하도록 제약 조건을 지정할 때 데이터베이스 엔진 튜닝 관리자는 정확한 예상 향상률이 나타나도록 하고 이제까지 소비된 작업에 대한 분석 보고서를 생성하기 위해 해당 시간 제한을 초과할 수도 있습니다.

  • 데이터베이스 엔진 튜닝 관리자는 다음 상황에서 권장을 수행할 수 없습니다.

    1. 튜닝 중인 테이블에 포함된 데이터 페이지가 10페이지 미만입니다.

    2. 권장 인덱스로 현재 물리적 데이터베이스 설계에 대한 쿼리 성능이 충분히 향상되지는 않습니다.

    3. 데이터베이스 엔진 튜닝 관리자를 실행하는 사용자가 db_owner 데이터베이스 역할 또는 sysadmin 고정 서버 역할의 멤버가 아닙니다. 작업의 쿼리는 데이터베이스 엔진 튜닝 관리자를 실행하는 사용자의 보안 컨텍스트에서 분석됩니다. 사용자는 db_owner 데이터베이스 역할의 멤버여야 합니다.

  • 데이터베이스 엔진 튜닝 관리자는 튜닝 세션 데이터 및 기타 정보를 msdb 데이터베이스에 저장합니다. msdb 데이터베이스를 변경하면 튜닝 세션 데이터가 손실될 수 있습니다. 이 위험을 제거하려면 msdb 데이터베이스에 적합한 백업 전략을 구현합니다.

성능 고려 사항

데이터베이스 엔진 튜닝 관리자는 분석할 때 상당한 양의 프로세서와 메모리 리소스를 사용합니다. 프로덕션 서버의 속도가 느려지는 것을 피하려면 다음 전략 중 하나를 따르십시오.

  • 서버가 사용되고 있지 않을 때 데이터베이스를 튜닝합니다. 데이터베이스 엔진 튜닝 관리자는 유지 관리 태스크의 성능에 영향을 줄 수 있습니다.

  • 테스트 서버/프로덕션 서버 기능을 사용합니다. 자세한 내용은 프로덕션 서버 튜닝 로드 줄이기를 참조하십시오.

  • 데이터베이스 엔진 튜닝 관리자가 분석할 물리적 데이터베이스 설계 구조만 지정합니다. 데이터베이스 엔진 튜닝 관리자는 여러 옵션을 제공하지만 필요한 옵션만 지정합니다.

xp_msver 확장 저장 프로시저에 대한 종속성

데이터베이스 엔진 튜닝 관리자는 전체 기능을 제공하기 위해 xp_msver 확장 저장 프로시저에 의존합니다. 이 확장 저장 프로시저는 기본적으로 튜닝됩니다. 데이터베이스 엔진 튜닝 관리자는 이 확장 저장 프로시저를 사용하여 튜닝 중인 데이터베이스가 있는 컴퓨터에서 프로세서 개수 및 사용 가능한 메모리를 인출합니다. xp_msver을 사용할 수 없는 경우 데이터베이스 엔진 튜닝 관리자는 데이터베이스 엔진 튜닝 관리자에서 실행 중인 컴퓨터의 하드웨어 특징을 가정합니다. 데이터베이스 엔진 튜닝 관리자에서 실행 중인 컴퓨터의 하드웨어 특징을 알 수 없는 경우 프로세서는 하나이고 메모리는 1024MB라고 가정합니다.

권장되는 파티션 수는 두 가지 값, 즉, 프로세서 수 및 사용 가능한 메모리에 따라 달라지므로 이 종속성은 분할 권장 구성에 영향을 줍니다. 또한 이 종속성은 테스트 서버를 사용하여 프로덕션 서버를 튜닝할 때 튜닝 결과에 영향을 줍니다. 이 시나리오에서 데이터베이스 엔진 튜닝 관리자는 xp_msver을 사용하여 프로덕션 서버에서 하드웨어 속성을 인출합니다. 테스트 서버에 대한 작업을 튜닝한 후에 데이터베이스 엔진 튜닝 관리자는 이러한 하드웨어 속성을 사용하여 권장 구성을 만듭니다. 자세한 내용은 xp_msver(Transact-SQL)을 참조하십시오.

데이터베이스 엔진 튜닝 관리자 태스크

다음 표에는 일반적인 데이터베이스 엔진 튜닝 관리자 태스크 및 이러한 태스크를 수행하는 방법에 대해 설명하는 항목이 나열되어 있습니다.

데이터베이스 엔진 튜닝 관리자 태스크

항목

  • 데이터베이스 엔진 튜닝 관리자를 초기화하고 시작합니다.

  • 계획 캐시를 지정하거나, 스크립트를 만들거나, 추적 파일 또는 추적 테이블을 생성하여 작업을 만듭니다.

  • 데이터베이스 엔진 튜닝 관리자 그래픽 사용자 인터페이스 도구를 사용하여 데이터베이스를 튜닝합니다.

  • 작업을 튜닝할 XML 입력 파일을 만듭니다.

  • 데이터베이스 엔진 튜닝 관리자 사용자 인터페이스 옵션에 대한 설명을 봅니다.

데이터베이스 엔진 튜닝 관리자 시작 및 사용

  • 데이터베이스 튜닝 작업의 결과를 봅니다.

  • 튜닝 권장 구성을 선택하고 구현합니다.

  • 작업에 대해 가정(what-if) 탐구 분석을 수행합니다.

  • 기존 튜닝 세션을 검토하거나, 기존 튜닝 세션에 따라 세션을 복제하거나, 추가적인 평가나 구현을 위해 기존 튜닝 권장 구성을 편집합니다.

  • 데이터베이스 엔진 튜닝 관리자 사용자 인터페이스 옵션에 대한 설명을 봅니다.

데이터베이스 엔진 튜닝 관리자의 출력 보기 및 작업