프로덕션 서버 튜닝 로드 줄이기

적용 대상:SQL ServerAzure SQL Database

데이터베이스 엔진 튜닝 관리자는 쿼리 최적화 프로그램에서 워크로드를 분석하고 튜닝 권장 사항을 만듭니다. 프로덕션 서버에서 이 분석을 수행하면 서버 부하가 추가되고 튜닝 세션 중에 서버 성능이 저하될 수 있습니다. 프로덕션 서버 외에도 테스트 서버를 사용하여 튜닝 세션 중에 서버 부하에 미치는 영향을 줄일 수 있습니다.

데이터베이스 엔진 튜닝 관리자가 테스트 서버를 사용하는 방법

테스트 서버를 사용하는 일반적인 방법은 프로덕션 서버에서 테스트 서버로 모든 데이터를 복사하고, 테스트 서버를 조정한 다음, 프로덕션 서버에서 권장 사항을 구현하는 것입니다. 이 프로세스는 프로덕션 서버에 미치는 성능 영향을 제거하지만 최적의 솔루션은 아닙니다. 예를 들어 프로덕션 서버에서 테스트 서버로 많은 양의 데이터를 복사하는 데 상당한 시간과 리소스가 소비될 수 있습니다. 또한 테스트 서버 하드웨어의 성능은 프로덕션 서버 하드웨어 보다 뒤쳐지는 것이 보통입니다. 튜닝 프로세스는 쿼리 최적화 프로그램에서 사용되며, 생성되는 권장 사항은 부분적으로 기본 하드웨어를 기반으로 합니다. 테스트 및 프로덕션 서버 하드웨어가 동일하지 않으면 데이터베이스 엔진 튜닝 관리자 권장 사항 품질이 저하됩니다.

이러한 문제를 방지하기 위해 데이터베이스 엔진 튜닝 관리자는 대부분의 튜닝 부하를 테스트 서버에 오프로드하여 프로덕션 서버의 데이터베이스를 조정합니다. 프로덕션 서버 하드웨어 구성 정보를 사용하고 실제로 프로덕션 서버에서 테스트 서버로 데이터를 복사하지 않고 이 작업을 수행합니다. 데이터베이스 엔진 튜닝 관리자는 프로덕션 서버에서 테스트 서버로 실제 데이터를 복사하지 않습니다. 메타데이터 및 필요한 통계만 복사합니다.

다음 단계에서는 테스트 서버에서 프로덕션 데이터베이스를 튜닝하는 프로세스를 간략하게 설명합니다.

1. 테스트 서버를 사용하려는 사용자가 양쪽 서버에 존재하는지 확인합니다.

   시작하기 전에 테스트 서버를 사용하여 프로덕션 서버의 데이터베이스를 조정하려는 사용자가 두 서버에 모두 있는지 확인합니다. 이렇게 하려면 테스트 서버에서 사용자와 해당 사용자의 로그인을 만들어야 합니다. 두 컴퓨터에서 sysadmin 고정 서버 역할의 멤버인 경우 이 단계가 필요하지 않습니다.

2. 테스트 서버에서 워크로드를 조정합니다.

   테스트 서버에서 작업을 튜닝하려면 dta 명령줄 유틸리티가 있는 XML 입력 파일을 사용해야 합니다. XML 입력 파일에서 TuningOptions 부모 요소 아래의 다른 하위 요소에 대한 값을 지정하는 것 외에도 TestServer 하위 요소가 있는 테스트 서버의 이름을 지정합니다.

   튜닝 프로세스 중에 데이터베이스 엔진 튜닝 관리자가 테스트 서버에 셸 데이터베이스를 만듭니다. 이 셸 데이터베이스를 만들고 튜닝하기 위해 데이터베이스 엔진 튜닝 관리자는 다음과 같이 프로덕션 서버를 호출합니다.

   1. 데이터베이스 엔진 튜닝 관리자는 프로덕션 데이터베이스에서 테스트 서버 셸 데이터베이스로 메타데이터를 가져옵니다. 이 메타데이터에는 빈 테이블, 인덱스, 뷰, 저장 프로시저, 트리거 등이 포함됩니다. 이렇게 하면 테스트 서버 셸 데이터베이스에 대해 워크로드 쿼리를 실행할 수 있습니다.

   2. 데이터베이스 엔진 튜닝 관리자는 쿼리 최적화 프로그램이 테스트 서버에서 쿼리를 정확하게 최적화할 수 있도록 프로덕션 서버에서 통계를 가져옵니다.

   3. 데이터베이스 엔진 튜닝 관리자는 프로덕션 서버에서 프로세서 수와 사용 가능한 메모리를 지정하는 하드웨어 매개 변수를 가져와 쿼리 최적화 프로그램에서 쿼리 계획을 생성하는 데 필요한 정보를 제공합니다.

3. 데이터베이스 엔진 튜닝 관리자가 테스트 서버 셸 데이터베이스 튜닝을 완료하면 튜닝 권장 사항이 생성됩니다.

4. 테스트 서버를 튜닝하여 받은 권장 사항을 프로덕션 서버에 적용합니다.

다음 그림은 테스트 서버 및 프로덕션 서버 시나리오를 보여 줍니다.

예

우선 튜닝 작업을 수행하려는 사용자가 테스트 서버와 프로덕션 서버에 모두 존재하는지 확인합니다.

사용자 정보가 테스트 서버에 복사되면 데이터베이스 엔진 튜닝 관리자 XML 입력 파일에서 테스트 서버 튜닝 세션을 정의할 수 있습니다. 다음 예제 XML 입력 파일은 데이터베이스 엔진 튜닝 관리자를 사용하여 데이터베이스를 튜닝하는 테스트 서버를 지정하는 방법을 보여 줍니다.

이 예제에서는 데이터베이스가 MyDatabaseName 튜닝되고 있습니다 MyServerName. Transact-SQL 스크립트 MyWorkloadScript.sql는 워크로드로 사용됩니다. 이 워크로드에는 에 대해 MyDatabaseName실행되는 이벤트가 포함됩니다. 튜닝 프로세스의 일부로 발생하는 이 데이터베이스에 대한 대부분의 쿼리 최적화 프로그램 호출은 상주하는 셸 데이터베이스에 MyTestServerName의해 처리됩니다. 셸 데이터베이스는 메타데이터 및 통계로 구성됩니다. 이 작업 결과로 튜닝 오버헤드가 테스트 서버로 오프로드됩니다. 데이터베이스 엔진 튜닝 관리자가 이 XML 입력 파일을 사용하여 튜닝 권장 사항을 생성하는 경우 인덱스만<FeatureSet>IDX</FeatureSet>(), 분할하지 않고, 기존 물리적 디자인 구조를 MyDatabaseName유지할 필요가 없습니다.

<?xml version="1.0" encoding="utf-16" ?>  
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/dta">  
  <DTAInput>  
    <Server>  
      <Name>MyServerName</Name>  
      <Database>  
        <Name>MyDatabaseName</Name>  
      </Database>  
    </Server>  
    <Workload>  
      <File>MyWorkloadScript.sql</File>  
    </Workload>  
    <TuningOptions>  
      <TestServer>MyTestServerName</TestServer>  
      <FeatureSet>IDX</FeatureSet>  
      <Partitioning>NONE</Partitioning>  
      <KeepExisting>NONE</KeepExisting>  
    </TuningOptions>  
  </DTAInput>  
</DTAXML>  

참고 항목

테스트 서버 사용 시 고려 사항
XML 입력 파일 참조(데이터베이스 엔진 튜닝 관리자)