데이터베이스 엔진 튜닝 관리자를 사용하여 Amazon RDS for SQL Server DB 인스턴스의 데이터베이스 워크로드 분석 - Amazon Relational Database Service

데이터베이스 엔진 튜닝 관리자를 사용하여 Amazon RDS for SQL Server DB 인스턴스의 데이터베이스 워크로드 분석

데이터베이스 엔진 튜닝 관리자는 Microsoft에서 제공하는 클라이언트 애플리케이션으로, 데이터베이스 워크로드를 분석하고 실행하는 쿼리 종류에 따라 Microsoft SQL Server 데이터베이스에 대한 최적의 인덱스 집합을 권장합니다. SQL Server Management Studio와 마찬가지로 튜닝 어드바이저 역시 SQL Server 기반 Amazon RDS DB 인스턴스에 연결되어 있는 클라이언트 컴퓨터에서 실행됩니다. 여기서 클라이언트 컴퓨터는 기업 네트워크 내 온프레미스에서 실행되는 로컬 컴퓨터가 될 수도 있고, 혹은 Amazon RDS DB 인스턴스와 동일한 리전에서 실행되는 Amazon EC2 Windows 인스턴스가 될 수도 있습니다.

이 섹션에서는 튜닝 어드바이저의 분석 워크로드를 캡처하는 방법에 대해 살펴보겠습니다. Amazon RDS는 SQL Server 인스턴스에 대한 호스트 액세스를 제한하기 때문에 이 방법은 워크로드 캡처에 우선적으로 사용되는 프로세스입니다. 자세한 내용은 Microsoft 설명서의 데이터베이스 엔진 튜닝 관리자를 참조하세요.

튜닝 어드바이저를 사용하려면 먼저 워크로드를 어드바이저에게 제공해야 합니다. 여기서 워크로드란 튜닝하려는 데이터베이스에 실행하는 Transact-SQL 문의 집합을 말합니다. 데이터베이스 엔진 튜닝 어드바이저는 데이터베이스를 튜닝할 때 트레이스 파일, 트레이스 테이블, Transact-SQL 스크립트 또는 XML 파일을 워크로드 입력 수단으로 사용합니다. Amazon RDS에서는 클라이언트 컴퓨터의 파일이나 클라이언트 컴퓨터에 액세스할 수 있는 Amazon RDS for SQL Server DB의 데이터베이스 테이블을 워크로드로 사용할 수도 있습니다. 하지만 파일이든, 테이블이든 상관없이 튜닝하려는 데이터베이스에 대한 쿼리는 재실행에 적합한 형식에 따라 저장되어야 합니다.

튜닝 어드바이저의 효과를 극대화하려면 워크로드가 최대한 사실적이어야 합니다. 워크로드 파일이나 테이블은 DB 인스턴스에 대한 트레이스를 수행하여 생성할 수 있습니다. 트레이스가 실행 중일 때도 DB 인스턴스에 가해지는 부하를 시뮬레이션하거나 정상적인 부하로 애플리케이션을 실행할 수 있습니다.

트레이스는 클라이언트 측과 서버 측, 두 가지 유형이 있습니다. 클라이언트 측 트레이스는 설치가 더욱 쉬울 뿐만 아니라 트레이스 이벤트가 캡처되는 것을 SQL Server 프로파일러에서 실시간으로 볼 수 있습니다. 반면 서버 측 트레이스는 설치가 더욱 복잡할 뿐만 아니라 몇 가지 Transact-SQL 스크립트를 작성해야 합니다. 그뿐만 아니라 트레이스가 Amazon RDS DB 인스턴스의 파일에 기록되기 때문에 스토리지 공간을 차지하게 됩니다. DB 인스턴스는 스토리지가 가득 찬 상태가 되어 스토리지 공간이 부족할 경우 더 이상 사용할 수 없기 때문에 실행 중인 서버 측 트레이스가 사용하는 스토리지 공간을 계속해서 추적해야 합니다.

클라이언트 측 트레이스의 경우, SQL Server 프로파일러에 트레이스 데이터가 충분히 캡처되면 트레이스를 로컬 컴퓨터의 파일이나 클라이언트 컴퓨터에 액세스할 수 있는 DB 인스턴스의 데이터베이스 테이블에 저장하여 워크로드 파일을 생성할 수 있습니다. 하지만 클라이언트 측 트레이스를 사용할 때는 과도한 부하 시 트레이스가 쿼리를 모두 캡처하지 못한다는 커다란 단점이 있습니다. 이로 인해 데이터베이스 엔진 튜닝 어드바이저의 분석 효과가 약해질 수 있습니다. 따라서 과도한 부하에서 트레이스를 실행하면서 트레이스 세션 중 모든 쿼리를 캡처해야 하는 경우에는 서버 측 트레이스를 사용하는 것이 바람직합니다.

서버 측 트레이스에서는 DB 인스턴스의 트레이스 파일을 적합한 워크로드 파일로 가져오거나, 완료된 트레이스를 DB 인스턴스의 테이블에 저장할 수 있습니다. 또한 SQL Server 프로파일러를 사용해 트레이스를 로컬 컴퓨터의 파일에 저장하거나 튜닝 어드바이저를 사용해 DB 인스턴스의 트레이스 테이블을 읽을 수 있습니다.

SQL Server DB 인스턴스의 클라이언트 측 트레이스 실행

SQL Server DB 인스턴스의 클라이언트 측 트레이스를 실행하는 방법

  1. SQL Server 프로파일러를 시작합니다. 이 프로파일러는 SQL Server 인스턴스 폴더 내 Performance Tools 폴더에 설치되어 있습니다. 클라이언트 측 트레이스를 시작하려면 트레이스 정의 템플릿을 로드하거나 정의해야 합니다.

  2. SQL Server 프로파일러 파일 메뉴에서 New Trace(새 트레이스)를 선택합니다. [Connect to Server] 대화 상자에 DB 인스턴스 엔드포인트, 포트, 마스터 사용자 이름 그리고 트레이스를 실행할 데이터베이스의 암호를 입력합니다.

  3. [Trace Properties] 대화 상자에 트레이스 이름을 입력한 다음 트레이스 정의 템플릿을 선택합니다. 기본 템플릿인 TSQL_Replay는 애플리케이션으로 제공됩니다. 이 템플릿을 편집하여 트레이스를 정의할 수 있습니다. [Trace Properties] 대화 상자의 [Events Selection] 탭 아래 있는 이벤트와 이벤트 정보를 편집합니다.

    추적 정의 템플릿 및 SQL Server 프로파일러를 사용하여 클라이언트 측 추적을 지정하는 방법에 대한 자세한 내용은 Microsoft 설명서의 데이터베이스 엔진 튜닝 관리자를 참조하세요.

  4. 클라이언트 측 트레이스를 시작하여 DB 인스턴스에 대한 SQL 쿼리 실행을 실시간으로 모니터링합니다.

  5. 트레이스가 완료되면 파일 메뉴에서 Stop Trace(트레이스 중지)를 선택합니다. 결과를 파일이나 DB 인스턴스의 트레이스 테이블로 저장합니다.

SQL Server DB 인스턴스의 서버 측 트레이스 실행

스크립트를 작성하여 서버 측 트레이스를 생성하는 것은 복잡할 뿐만 아니라 이 문서의 범위에서 벗어납니다. 따라서 여기서는 예제로 사용할 수 있는 샘플 스크립트만 다룹니다. 클라이언트 측 트레이스와 마찬가지로 이 트레이스의 목적 역시 데이터베이스 엔진 튜닝 어드바이저를 사용해 열 수 있도록 워크로드 파일 또는 트레이스를 생성하는 데 있습니다.

다음은 서버 측 트레이스를 시작하여 세부 정보를 워크 로드에 캡처하기 위해 요약된 예제 스크립트입니다. 트레이스가 처음에는 D:\RDSDBDATA\Log 디렉터리의 RDSTrace.trc 파일에 저장되지만 100MB마다 롤오버되어 이후부터는 RDSTrace_1.trc, RDSTrace_2.trc 등의 이름으로 트레이스 파일이 저장됩니다.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace'; DECLARE @max_file_size BIGINT = 100; DECLARE @on BIT = 1 DECLARE @rc INT DECLARE @traceid INT EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size IF (@rc = 0) BEGIN EXEC sp_trace_setevent @traceid, 10, 1, @on EXEC sp_trace_setevent @traceid, 10, 2, @on EXEC sp_trace_setevent @traceid, 10, 3, @on . . . EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler' EXEC sp_trace_setstatus @traceid, 1 END

다음은 트레이스를 중단하는 스크립트 예제입니다. 단, 이전 스크립트에서 생성된 트레이스는 명시적으로 트레이스가 중단되거나, 프로세스의 디스크 공간이 부족해질 때까지 계속 실행됩니다.

DECLARE @traceid INT SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) WHERE property = 5 AND value = 1 AND traceid <> 1 IF @traceid IS NOT NULL BEGIN EXEC sp_trace_setstatus @traceid, 0 EXEC sp_trace_setstatus @traceid, 2 END

서버 측 트레이스 결과는 데이터베이스 테이블로 저장한 후 fn_trace_gettable 함수를 이용하면 튜닝 어드바이저의 워크로드로 사용할 수 있습니다. 다음 명령은 RDSTrace_1.trc 등의 모든 롤오버 파일을 포함해 D:\rdsdbdata\Log 디렉터리에서 RDSTrace.trc라는 이름의 모든 파일 결과를 현재 데이터베이스에서 RDSTrace라는 이름의 테이블로 로드합니다.

SELECT * INTO RDSTrace FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default);

예를 들어 RDSTrace_1.trc 같이 특정 롤오버 파일을 테이블에 저장하려면 롤오버 파일의 이름을 지정한 다음 fn_trace_gettable의 마지막 파라미터를 default가 아닌 1로 변경합니다.

SELECT * INTO RDSTrace_1 FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1);

트레이스를 이용한 튜닝 어드바이저 실행

로컬 파일이든 데이터베이스 테이블이든 상관없이 트레이스가 생성되면 이제 DB 인스턴스에 대해 튜닝 어드바이저를 실행할 수 있습니다. Amazon RDS와 함께 튜닝 어드바이저를 사용하는 방법은 독립된 원격 SQL Server 인스턴스를 사용할 때와 프로세스가 동일합니다. 그 밖에 클라이언트 컴퓨터에서 튜닝 어드바이저 UI를 사용하거나 명령줄에서 dta.exe 유틸리티를 사용할 수도 있습니다. 하지만 두 가지 경우 모두 DB 인스턴스 엔드포인트를 사용하여 Amazon RDS DB 인스턴스에 연결한 후 마스터 사용자 이름과 마스터 사용자 암호를 입력해야만 튜닝 어드바이저를 사용할 수 있습니다.

다음 코드 예제는 dta.cnazcmklsdei.us-east-1.rds.amazonaws.com 엔드포인트에서 Amazon RDS DB 인스턴스에 대해 dta.exe 명령줄 유틸리티를 사용하는 방법을 나타냅니다. 이 예에는 마스터 사용자 이름 admin 및 마스터 사용자 암호 test가 포함되며 튜닝할 예제 데이터베이스의 이름은 C:\RDSTrace.trc라는 머신 이름으로 지정됩니다. 또한 예제 명령줄 코드를 보면 트레이스 세션 이름을 RDSTrace1로 지정하였으며, 로컬 컴퓨터에 출력되는 파일 이름을 SQL 출력 스크립트의 경우 RDSTrace.sql로, 결과 파일의 경우 ​RDSTrace.txt로, 그리고 분석한 XML 파일의 경우 RDSTrace.xml로 지정하였습니다. 마지막으로 RDSDTA 데이터베이스 이름을 RDSTraceErrors​로 지정한 오류 테이블도 있습니다.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

다음 예제 명령줄 코드는 입력 워크로드가 RDSTrace 데이터베이스에서 RDSDTA라는 이름의 원격 Amazon RDS 인스턴스에 저장된 테이블이라는 점을 제외하고 모두 동일합니다.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

dta 유틸리티 명령줄 파라미터의 전체 목록은 Microsoft 설명서의 dta 유틸리티를 참조하세요.