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

  • 아티클

적용 대상:SQL Server

Microsoft DTA(데이터베이스 엔진 튜닝 관리자)는 데이터베이스를 분석하여 쿼리 성능을 최적화하는 데 활용할 수 있는 권장 사항을 제공합니다. 데이터베이스 엔진 튜닝 관리자를 사용하여 데이터베이스 구조 또는 SQL Server의 내부를 잘 모르고 최적의 인덱스, 인덱싱된 뷰 또는 테이블 파티션 집합을 선택하고 만들 수 있습니다. DTA를 사용하여 다음 태스크를 수행할 수 있습니다.

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

  • 하나 이상의 데이터베이스에서 대규모 쿼리 집합 튜닝

  • 잠재적인 물리적 디자인 변경에 대한 예비 가상 분석 수행

  • 스토리지 공간 관리

참고 항목

데이터베이스 엔진 튜닝 관리자는 Azure SQL Database 또는 Azure SQL Managed Instance에 대해 지원되지 않습니다. 그 대신에 Azure SQL Database 및 Azure SQL Managed Instance의 모니터링 및 성능 튜닝에서 권장되는 전략을 고려해 보세요. Azure SQL Database의 경우 Azure SQL Database에 대한 Database Advisor 성능 권장 사항도 참조하세요.

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

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

  • 쿼리 최적화 관리자를 사용하여 워크로드의 쿼리를 분석하여 데이터베이스에 대한 rowstore 및 columnstore 인덱스의 최상의 조합을 권장합니다.

  • 워크로드에서 참조되는 데이터베이스에 대해 정렬된 파티션 또는 정렬되지 않은 파티션을 권장합니다.

  • 워크로드에서 참조되는 데이터베이스에 대해 인덱싱된 뷰를 권장합니다.

  • 인덱스 사용량, 테이블 간의 쿼리 분포 및 워크로드의 쿼리 성능을 포함하여 제안된 변경 내용의 효과를 분석합니다.

  • 작은 문제 쿼리 집합에 대해 데이터베이스를 튜닝하는 방법을 권장합니다.

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

  • 지정된 워크로드에 대한 권장 사항을 구현하는 효과를 요약하는 보고서를 제공합니다.

  • 데이터베이스 엔진 튜닝 관리자에서 평가할 수 있도록 가상 구성의 형태로 디자인 선택 항목을 사용자가 제공하는 대안을 살펴 봅니다.

  • SQL Server 쿼리 저장소, 계획 캐시, SQL Server Profiler 추적 파일 또는 테이블 또는 을 비롯한 다양한 원본에서 워크로드를 조정합니다. SQL 파일입니다.

데이터베이스 엔진 튜닝 관리자는 다음과 같은 유형의 쿼리 워크로드를 처리하도록 설계되었습니다.

  • OLTP(온라인 트랜잭션 처리) 쿼리만

  • OLAP(온라인 분석 처리) 쿼리만

  • OLTP 및 OLAP 쿼리 혼합

  • 쿼리가 많은 워크로드(데이터 수정보다 더 많은 쿼리)

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

DTA 구성 요소 및 개념

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

dta 유틸리티
데이터베이스 엔진 튜닝 관리자의 명령 프롬프트 버전입니다. dta 유틸리티는 애플리케이션 및 스크립트에서 데이터베이스 엔진 튜닝 관리자 기능을 사용할 수 있도록 설계되었습니다.

작업
튜닝하려는 데이터베이스의 대표적인 워크로드를 포함하는 Transact-SQL 스크립트 파일, 추적 파일 또는 추적 테이블입니다. SQL Server 2012(11.x)부터 계획 캐시를 워크로드로 지정할 수 있습니다. SQL Server 2016(13.x)부터 쿼리 저장소를 워크로드로 지정할 수 있습니다.

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(MB)의 메모리가 있다고 가정합니다.

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

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

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

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

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

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

XML 입력 파일을 만들어 워크로드를 튜닝합니다.

데이터베이스 엔진 튜닝 관리자 사용자 인터페이스 옵션에 대한 설명을 봅니다.		 데이터베이스 엔진 튜닝 관리자 시작 및 사용
데이터베이스 튜닝 작업의 결과를 봅니다.

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

워크로드에 대해 가상 예비 분석을 수행합니다.

기존 튜닝 세션을 검토하고 기존 세션을 기반으로 세션을 복제합니다.
또는 추가 평가 또는 구현을 위해 기존 튜닝 권장 사항을 편집합니다.

데이터베이스 엔진 튜닝 관리자 사용자 인터페이스 옵션에 대한 설명을 봅니다.		 데이터베이스 엔진 튜닝 관리자의 출력 보기 및 작업