데이터베이스 엔진 튜닝 관리자Database Engine Tuning Advisor

MicrosoftMicrosoft DTA(데이터베이스 엔진 튜닝 관리자)는 데이터베이스를 분석하고 쿼리 성능을 최적화하는 데 필요한 사항을 권장합니다.The MicrosoftMicrosoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. 데이터베이스 엔진 튜닝 관리자를 사용하면 데이터베이스 구조나 SQL ServerSQL Server의 내부 구조를 전문적으로 파악하지 못해도 인덱스, 인덱싱된 뷰 또는 테이블 파티션의 최적 집합을 선택 및 작성할 수 있습니다.You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL ServerSQL Server. DTA를 사용하여 다음과 같은 태스크를 수행할 수 있습니다.Using the DTA, you can perform the following tasks.

  • 특정 문제 쿼리의 성능 문제 해결Troubleshoot the performance of a specific problem query

  • 하나 이상의 데이터베이스 전반에서 많은 쿼리 집합 튜닝Tune a large set of queries across one or more databases

  • 잠재적인 물리적 디자인 변경에 대한 탐구 가정(what-if) 분석 수행Perform an exploratory what-if analysis of potential physical design changes

  • 저장소 공간 관리Manage storage space

데이터베이스 엔진 튜닝 관리자의 이점Database Engine Tuning Advisor Benefits

데이터베이스의 구조와 데이터베이스에 대해 실행되는 쿼리를 정확하게 이해하지 못하면 쿼리 성능을 최적화하기 어려울 수 있습니다.Optimizing query performance can be difficult without a full understanding the database structure and the queries that are run against the database. 데이터베이스 엔진 튜닝 관리자는 현재 쿼리 계획 캐시를 분석하거나 작성하는 Transact-SQLTransact-SQL 쿼리의 작업량을 분석한 후 적절한 물리적 디자인을 권장해 주므로 이 태스크를 보다 쉽게 수행할 수 있습니다.The Database Engine Tuning Advisor can make this task easier by analyzing the current query plan cache or by analyzing a workload of Transact-SQLTransact-SQL queries that you create and recommending an appropriate physical design. 고급 데이터베이스 관리자를 위해 DTA는 다른 물리적 디자인 대안에 대한 탐구 가정(what-if) 분석을 수행할 수 있는 강력한 메커니즘을 제공합니다.For more advanced database administrators, DTA exposes a powerful mechanism to perform exploratory what-if analysis of different physical design alternatives. DTA는 다음 정보를 제공할 수 있습니다.The DTA can provide the following information.

  • 쿼리 최적화 프로그램을 사용하여 작업의 쿼리를 분석하여 데이터베이스에 대한 최상의 rowstore 및 columnstore 인덱스 조합을 권장합니다.Recommend the best mix of rowstore and columnstore indexes for databases by using the query optimizer to analyze queries in a workload.

  • 작업에서 참조된 데이터베이스에 대한 정렬된 파티션 또는 정렬되지 않은 파티션을 권장합니다.Recommend aligned or non-aligned partitions for databases referenced in a workload.

  • 작업에서 참조된 데이터베이스를 위한 인덱싱된 뷰를 권장합니다.Recommend indexed views for databases referenced in a workload.

  • 인덱스 사용을 비롯한 제안된 변경 내용의 영향, 테이블 간 쿼리 분배, 작업에서의 쿼리 성능 등을 분석합니다.Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.

  • 문제가 되는 쿼리를 해결하기 위해 데이터베이스를 튜닝하는 방법을 권장합니다.Recommend ways to tune the database for a small set of problem queries.

  • 디스크 공간 제약 조건과 같은 고급 옵션을 지정하여 권장 구성을 사용자 지정할 수 있도록 합니다.Allow you to customize the recommendation by specifying advanced options such as disk space constraints.

  • 특정 작업에 대한 권장 구성 구현의 효과를 요약하는 보고서를 제공합니다.Provide reports that summarize the effects of implementing the recommendations for a given workload.

  • 데이터베이스 엔진 튜닝 관리자에서 평가할 수 있도록 가상 구성의 형태로 디자인 선택 항목을 사용자가 제공하는 대안을 살펴 봅니다.Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.

  • SQL Server 쿼리 저장소, 계획 캐시, SQL Server Profiler 추적 파일 또는 테이블, SQL 파일을 포함하여 다양한 소스에서 워크로드를 튜닝합니다.Tune workloads from a variety of sources including SQL Server Query Store, Plan Cache, SQL Server Profiler Trace file or table, or a .SQL file.

데이터베이스 엔진 튜닝 관리자는 다음과 같은 쿼리 작업 유형을 처리하도록 설계되었습니다.The Database Engine Tuning Advisor is designed to handle the following types of query workloads.

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

  • OLAP(온라인 분석 처리) 쿼리만 수행하는 작업Online analytical processing (OLAP) queries only

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

  • 쿼리가 많은 작업(쿼리가 데이터 수정보다 많음)Query-heavy workloads (more queries than data modifications)

  • 업데이트가 많은 작업(데이터 수정이 쿼리보다 많음)Update-heavy workloads (more data modifications than queries)

DTA 구성 요소 및 개념DTA Components and Concepts

데이터베이스 엔진 튜닝 관리자 그래픽 사용자 인터페이스Database Engine Tuning Advisor Graphical User Interface
작업을 지정하고 다양한 튜닝 옵션을 선택할 수 있는 사용하기 쉬운 인터페이스입니다.An easy-to-use interface in which you can specify the workload and select various tuning options.

dta 유틸리티dta Utility
데이터베이스 엔진 튜닝 관리자의 명령 프롬프트 버전입니다.The command prompt version of Database Engine Tuning Advisor. dta 유틸리티를 통해 응용 프로그램과 스크립트에서 데이터베이스 엔진 튜닝 관리자의 기능을 사용할 수 있습니다.The dta utility is designed to allow you to use Database Engine Tuning Advisor functionality in applications and scripts.

작업workload
튜닝할 데이터베이스의 대표적인 작업이 포함된 Transact-SQL 스크립트 파일, 추적 파일 또는 추적 테이블입니다.A Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune. SQL Server 2012SQL Server 2012이상에서는 계획 캐시를 작업으로 지정할 수 있습니다.Beginning with SQL Server 2012SQL Server 2012, you can specify the plan cache as the workload. SQL Server 2016SQL Server 2016부터 [쿼리 저장소를 워크로드로 지정](../../relational-databases/performance/tuning-database-using-workload-from-query-store.md)할 수 있습니다.Beginning with with SQL Server 2016SQL Server 2016, you can specify the Query Store as the workload.

XML 입력 파일XML input file
데이터베이스 엔진 튜닝 관리자가 작업 부하를 조정하는 데 사용할 수 있는 XML 형식 파일입니다.An XML-formatted file that Database Engine Tuning Advisor can use to tune workloads. XML 입력 파일은 GUI 및 dta 유틸리티에서 사용할 수 없는 고급 튜닝 옵션을 지원합니다.The XML input file supports advanced tuning options that are not available in either the GUI or dta utility.

제한 사항Limitations and Restrictions

데이터베이스 엔진 튜닝 관리자에는 다음의 제한 사항이 있습니다.The Database Engine Tuning Advisor has the following limitations and restrictions.

  • PRIMARY KEY 또는 UNIQUE 제약 조건을 강제로 실행하는 고유한 인덱스를 추가하거나 삭제할 수 없습니다.It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.

  • 단일 사용자 모드로 설정된 데이터베이스를 분석할 수 없습니다.It cannot analyze a database that is set to single-user mode.

  • 튜닝 권장 구성의 최대 디스크 공간이 실제 사용 가능한 공간을 초과하도록 지정하는 경우 데이터베이스 엔진 튜닝 관리자는 사용자가 지정한 값을 사용합니다.If you specify a maximum disk space for tuning recommendations that exceeds the actual available space, Database Engine Tuning Advisor uses the value you specify. 그러나 권장 구성 스크립트를 실행하여 구현할 때 먼저 디스크 공간이 더 추가되지 않으면 해당 스크립트는 실패할 수 있습니다.However, when you execute the recommendation script to implement it, the script may fail if more disk space is not added first. 최대 디스크 공간은 dta 유틸리티의 -B 옵션으로 지정되거나 고급 튜닝 옵션 대화 상자에 값을 입력하는 방법으로 지정될 수 있습니다.Maximum disk space can be specified with the -B option of the dta utility, or by entering a value in the Advanced Tuning Options dialog box.

  • 보안을 위해 데이터베이스 엔진 튜닝 관리자는 원격 서버에 있는 추적 테이블에서 작업을 튜닝할 수 없습니다.For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. 이 제한을 해결하려면 추적 테이블 대신 추적 파일을 사용하거나 추적 테이블을 원격 서버에 복사할 수 있습니다.To work around this limitation, you can use a trace file instead of a trace table or copy the trace table to the remote server.

  • -B 옵션 또는 고급 튜닝 옵션 대화 상자를 사용하여 튜닝 권장 구성에 맞게 최대 디스크 공간을 지정할 때처럼 제약 조건을 설정할 때 데이터베이스 엔진 튜닝 관리자는 기존의 인덱스를 삭제해야 할 수도 있습니다.When you impose constraints, such as those imposed when you specify a maximum disk space for tuning recommendations (by using the -B option or the Advanced Tuning Options dialog box), Database Engine Tuning Advisor may be forced to drop certain existing indexes. 이 경우 데이터베이스 엔진 튜닝 관리자 권장 구성의 결과로 인해 예상 향상률에 미치지 못할 수 있습니다.In this case, the resulting Database Engine Tuning Advisor recommendation may produce a negative expected improvement.

  • dta 유틸리티의 -A 옵션을 사용하거나 튜닝 옵션 탭에서 튜닝 시간 제한 을 선택하여 튜닝 시간을 제한하도록 제약 조건을 지정할 때 데이터베이스 엔진 튜닝 관리자는 정확한 예상 향상률이 나타나도록 하고 이제까지 소비된 작업에 대한 분석 보고서를 생성하기 위해 해당 시간 제한을 초과할 수도 있습니다.When you specify a constraint to limit tuning time (by using the -A option with the dta utility or by checking Limit tuning time on the Tuning Options tab), Database Engine Tuning Advisor may exceed that time limit to produce an accurate expected improvement and the analysis reports for whatever portion of the workload has been consumed so far.

  • 데이터베이스 엔진 튜닝 관리자는 다음 상황에서 권장을 수행할 수 없습니다.Database Engine Tuning Advisor might not make recommendations under the following circumstances:

    1. 튜닝 중인 테이블에 포함된 데이터 페이지가 10페이지 미만입니다.The table being tuned contains less than 10 data pages.

    2. 권장 인덱스로 현재 물리적 데이터베이스 설계에 대한 쿼리 성능이 충분히 향상되지는 않습니다.The recommended indexes would not offer enough improvement in query performance over the current physical database design.

    3. 데이터베이스 엔진 튜닝 관리자를 실행하는 사용자가 db_owner 데이터베이스 역할 또는 sysadmin 고정 서버 역할의 멤버가 아닙니다.The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. 작업의 쿼리는 데이터베이스 엔진 튜닝 관리자를 실행하는 사용자의 보안 컨텍스트에서 분석됩니다.The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. 사용자는 db_owner 데이터베이스 역할의 멤버여야 합니다.The user must be a member of the db_owner database role.

  • 데이터베이스 엔진 튜닝 관리자는 튜닝 세션 데이터 및 기타 정보를 msdb 데이터베이스에 저장합니다.Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. msdb 데이터베이스를 변경하면 튜닝 세션 데이터가 손실될 수 있습니다.If changes are made to the msdb database you may risk losing tuning session data. 이 위험을 제거하려면 msdb 데이터베이스에 적합한 백업 전략을 구현합니다.To eliminate this risk, implement an appropriate backup strategy for the msdb database.

성능 고려 사항Performance Considerations

데이터베이스 엔진 튜닝 관리자는 분석할 때 상당한 양의 프로세서와 메모리 리소스를 사용합니다.Database Engine Tuning Advisor can consume significant processor and memory resources during analysis. 프로덕션 서버의 속도가 느려지는 것을 피하려면 다음 전략 중 하나를 따르십시오.To avoid slowing down your production server, follow one of these strategies:

  • 서버가 사용되고 있지 않을 때 데이터베이스를 튜닝합니다.Tune your databases when your server is free. 데이터베이스 엔진 튜닝 관리자는 유지 관리 태스크의 성능에 영향을 줄 수 있습니다.Database Engine Tuning Advisor can affect maintenance task performance.

  • 테스트 서버/프로덕션 서버 기능을 사용합니다.Use the test server/production server feature. 자세한 내용은 프로덕션 서버 튜닝 로드 줄이기를 참조하세요.For more information, see Reduce the Production Server Tuning Load.

  • 데이터베이스 엔진 튜닝 관리자가 분석할 물리적 데이터베이스 설계 구조만 지정합니다.Specify only the physical database design structures you want Database Engine Tuning Advisor to analyze. 데이터베이스 엔진 튜닝 관리자는 여러 옵션을 제공하지만 필요한 옵션만 지정합니다.Database Engine Tuning Advisor provides many options, but specifies only those that are necessary.

xp_msver 확장 저장 프로시저에 대한 종속성Dependency on xp_msver Extended Stored Procedure

데이터베이스 엔진 튜닝 관리자는 전체 기능을 제공하기 위해 xp_msver 확장 저장 프로시저에 의존합니다.Database Engine Tuning Advisor depends on the xp_msver extended stored procedure to provide full functionality. 이 확장 저장 프로시저는 기본적으로 튜닝됩니다.This extended stored procedure is turned on by default. 데이터베이스 엔진 튜닝 관리자는 이 확장 저장 프로시저를 사용하여 튜닝 중인 데이터베이스가 있는 컴퓨터에서 프로세서 개수 및 사용 가능한 메모리를 인출합니다.Database Engine Tuning Advisor uses this extended stored procedure to fetch the number of processors and available memory on the computer where the database that you are tuning resides. xp_msver 을 사용할 수 없는 경우 데이터베이스 엔진 튜닝 관리자는 데이터베이스 엔진 튜닝 관리자에서 실행 중인 컴퓨터의 하드웨어 특징을 가정합니다.If xp_msver is unavailable, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. 데이터베이스 엔진 튜닝 관리자에서 실행 중인 컴퓨터의 하드웨어 특징을 알 수 없는 경우 프로세서는 하나이고 메모리는 1024MB라고 가정합니다.If the hardware characteristics of the computer where Database Engine Tuning Advisor is running are not available, one processor and 1024 megabytes (MBs) of memory are assumed.

권장되는 파티션 수는 두 가지 값, 즉, 프로세서 수 및 사용 가능한 메모리에 따라 달라지므로 이 종속성은 분할 권장 구성에 영향을 줍니다.This dependency affects partitioning recommendations because the number of partitions recommended depends on these two values (number of processors and available memory). 또한 이 종속성은 테스트 서버를 사용하여 프로덕션 서버를 튜닝할 때 튜닝 결과에 영향을 줍니다.The dependency also affects your tuning results when you use a test server to tune your production server. 이 시나리오에서 데이터베이스 엔진 튜닝 관리자는 xp_msver 을 사용하여 프로덕션 서버에서 하드웨어 속성을 인출합니다.In this scenario, Database Engine Tuning Advisor uses xp_msver to fetch hardware properties from the production server. 테스트 서버에 대한 작업을 튜닝한 후에 데이터베이스 엔진 튜닝 관리자는 이러한 하드웨어 속성을 사용하여 권장 구성을 만듭니다.After tuning the workload on the test server, Database Engine Tuning Advisor uses these hardware properties to generate a recommendation. 자세한 내용은 xp_msver(Transact-SQL)을 참조하세요.For more information, see xp_msver (Transact-SQL).

데이터베이스 엔진 튜닝 관리자 태스크Database Engine Tuning Advisor Tasks

다음 표에는 일반적인 데이터베이스 엔진 튜닝 관리자 태스크 및 이러한 태스크를 수행하는 방법에 대해 설명하는 항목이 나열되어 있습니다.The following table lists common Database Engine Tuning Advisor tasks and the topics that describe how to perform them.

데이터베이스 엔진 튜닝 관리자 태스크Database Engine Tuning Advisor Task 항목Topic
데이터베이스 엔진 튜닝 관리자를 초기화하고 시작합니다.Initialize and start the Database Engine Tuning Advisor.

계획 캐시를 지정하거나, 스크립트를 만들거나, 추적 파일 또는 추적 테이블을 생성하여 작업을 만듭니다.Create a workload by specifying the plan cache, by creating a script, or by generating a trace file or trace table.

데이터베이스 엔진 튜닝 관리자 그래픽 사용자 인터페이스 도구를 사용하여 데이터베이스를 튜닝합니다.Tune a database by using the Database Engine Tuning Advisor graphical user interface tool.

작업을 튜닝할 XML 입력 파일을 만듭니다.Create XML input files to tune workloads.

데이터베이스 엔진 튜닝 관리자 사용자 인터페이스 옵션에 대한 설명을 봅니다.View descriptions of the Database Engine Tuning Advisor user interface options.
데이터베이스 엔진 튜닝 관리자 시작 및 사용Start and Use the Database Engine Tuning Advisor
데이터베이스 튜닝 작업의 결과를 봅니다.View the results of the database tuning operation.

튜닝 권장 구성을 선택하고 구현합니다.Select and implement tuning recommendations.

작업에 대해 가정(what-if) 탐구 분석을 수행합니다.Perform what-if exploratory analysis against the workload.

기존 튜닝 세션을 검토하거나, 기존 튜닝 세션에 따라 세션을 복제하거나,Review existing tuning sessions, clone sessions based on existing ones
추가적인 평가나 구현을 위해 기존 튜닝 권장 구성을 편집합니다.or edit existing tuning recommendations for further evaluation or implementation.

데이터베이스 엔진 튜닝 관리자 사용자 인터페이스 옵션에 대한 설명을 봅니다.View descriptions of the Database Engine Tuning Advisor user interface options.
데이터베이스 엔진 튜닝 관리자의 출력 보기 및 작업View and Work with the Output from the Database Engine Tuning Advisor