통계 스크립트를 생성하여 SQL Server 통계 전용 데이터베이스를 만드는 방법

이 문서에서는 SQL Server 통계 전용 데이터베이스를 만들기 위해 데이터베이스 메타데이터를 사용하여 통계 스크립트를 생성하는 방법을 알아봅니다.

원래 제품 버전: SQL Server 2014, SQL Server 2012, SQL Server 2008

원래 KB 번호: 914288

소개

DBCC CLONEDATABASE는 성능 문제를 조사하기 위해 데이터베이스의 스키마 전용 복제본을 생성하는 기본 방법입니다. 를 사용할 수 없는 경우에만 이 문서의 절차를 사용합니다 DBCC CLONEDATABASE.

Microsoft SQL Server 쿼리 최적화 프로그램은 다음 유형의 정보를 사용하여 최적의 쿼리 계획을 결정합니다.

  • 데이터베이스 메타데이터
  • 하드웨어 환경
  • 데이터베이스 세션 상태

일반적으로 테스트 시스템에서 쿼리 최적화 프로그램의 동작을 재현하려면 이러한 모든 유형의 정보를 시뮬레이션해야 합니다.

Microsoft 고객 지원 서비스는 쿼리 최적화 프로그램 문제를 조사하기 위해 데이터베이스 메타데이터의 스크립트를 생성하도록 요청할 수 있습니다. 이 문서에서는 통계 스크립트를 생성하는 단계를 설명하고 쿼리 최적화 프로그램에서 정보를 사용하는 방법도 설명합니다.

참고

이 데이터 내에 저장된 키에는 PII 정보가 포함될 수 있습니다. 예를 들어 테이블에 통계가 있는 전화 번호 열이 포함된 경우 각 단계의 높은 키 값은 생성된 통계 스크립트에 포함됩니다.

전체 데이터베이스 스크립팅

통계 전용 복제 데이터베이스를 생성하는 경우 개별 개체를 스크립팅하는 대신 전체 데이터베이스를 스크립팅하는 것이 더 쉽고 안정적일 수 있습니다. 전체 데이터베이스를 스크립팅할 때 다음과 같은 이점이 제공됩니다.

  • 문제를 재현하는 데 필요한 종속 개체 누락 문제를 방지할 수 있습니다.
  • 필요한 개체를 선택하려면 더 적은 단계가 필요합니다.

데이터베이스에 대한 스크립트를 생성하고 데이터베이스에 대한 메타데이터에 수천 개의 개체가 포함된 경우 스크립팅 프로세스는 상당한 CPU 리소스를 사용합니다. 사용량이 많은 시간에 스크립트를 생성하거나 두 번째 옵션 인 개별 개체 스크립트 를 사용하여 개별 개체에 대한 스크립트를 생성하는 것이 좋습니다.

쿼리에서 참조하는 각 데이터베이스를 스크립팅하려면 다음 단계를 수행합니다.

  1. SQL Server Management Studio 엽니다.

  2. 개체 탐색기데이터베이스를 확장한 다음 스크립팅할 데이터베이스를 찾습니다.

  3. 데이터베이스를 마우스 오른쪽 단추로 클릭하고 작업을 가리킨 다음 스크립트 생성을 선택합니다.

  4. 스크립트 마법사에서 올바른 데이터베이스가 선택되어 있는지 확인합니다. 전체 데이터베이스 및 모든 데이터베이스 개체 스크립트를 클릭하여 선택한 다음, 다음을 선택합니다.

  5. 스크립트 옵션 선택 대화 상자에서 고급 단추를 선택하여 다음 설정을 기본값에서 다음 표에 나열된 값으로 변경합니다.

    스크립팅 옵션 선택할 값
    Ansi 안쪽 여백 True
    오류 발생 후 스크립팅 계속 True
    종속 개체에 대한 스크립트 생성 True
    시스템 제약 조건 이름 포함 True
    스크립트 데이터 정렬 True
    스크립트 로그인 True
    스크립트 개체 수준 권한 True
    스크립트 통계 통계 및 히스토그램 스크립트
    인덱스 스크립트 True
    스크립트 트리거 True

    참고

    스키마에 dbo 이외의 로그인이 소유한 개체가 포함되어 있지 않으면 스크립트 로그인 옵션 및 스크립트 개체 수준 권한 옵션이 필요하지 않을 수 있습니다.

  6. 확인을 선택하여 변경 내용을 저장하고 고급 스크립팅 옵션 페이지를 닫습니다.

  7. 파일에 저장을 선택하고 단일 파일 옵션을 선택합니다.

  8. 선택 항목을 검토하고 다음을 선택합니다.

  9. 완료를 선택합니다.

개별 개체 스크립팅

전체 데이터베이스를 스크립팅하는 대신 특정 쿼리에서 참조하는 개별 개체만 스크립팅할 수 있습니다. 그러나 절을 사용하여 모든 데이터베이스 개체를 WITH SCHEMABINDING 만들지 않는 한 시스템 테이블의 sys.depends 종속성 정보가 항상 정확하지 않을 수 있습니다. 이러한 부정확성으로 인해 다음 문제 중 하나가 발생할 수 있습니다.

  • 스크립팅 프로세스는 종속 개체를 스크립팅하지 않습니다.

  • 스크립팅 프로세스에서 개체를 잘못된 순서로 스크립팅할 수 있습니다. 스크립트를 성공적으로 실행하려면 생성된 스크립트를 수동으로 편집해야 합니다.

따라서 데이터베이스에 개체가 많고 스크립팅하는 데 너무 오래 걸리지 않는 한 개별 개체를 스크립팅하는 것은 권장되지 않습니다. 스크립트 개별 개체를 사용해야 하는 경우 다음 단계를 수행합니다.

  1. SQL Server Management Studio 데이터베이스를 확장한 다음 스크립팅할 데이터베이스를 찾습니다.

  2. 데이터베이스를 마우스 오른쪽 단추로 클릭하고 다른 데이터베이스로 스크립트를 가리킨 다음 만들기 대상을 가리킨 다음 파일을 선택합니다.

  3. 파일 이름을 입력한 다음 저장을 선택합니다.

    핵심 데이터베이스 컨테이너가 스크립팅됩니다. 이 컨테이너에는 파일, 파일 그룹, 데이터베이스 및 속성이 포함됩니다.

  4. 데이터베이스를 마우스 오른쪽 단추로 클릭하고 작업을 가리킨 다음 스크립트 생성을 선택합니다.

  5. 올바른 데이터베이스가 선택되어 있는지 확인한 다음, 다음을 선택합니다.

  6. 개체 유형 선택 대화 상자에서 특정 데이터베이스 개체 선택을 선택하고 문제가 있는 쿼리에서 참조하는 모든 데이터베이스 개체 형식을 선택합니다.

    예를 들어 쿼리에서 테이블만 참조하는 경우 테이블을 선택합니다. 쿼리가 보기를 참조하는 경우 보기 및 테이블을 선택합니다. 문제가 있는 쿼리가 사용자 정의 함수를 사용하는 경우 함수를 선택합니다.

  7. 쿼리에서 참조하는 모든 개체 형식을 선택한 경우 다음을 선택합니다.

  8. 스크립팅 옵션 설정 대화 상자에서 고급 단추를 선택하고 다음 설정을 기본값에서 고급 스크립팅 옵션 페이지의 다음 표에 나열된 값으로 변경합니다.

    스크립팅 옵션 선택할 값
    Ansi Padding True
    오류 발생 후 스크립팅 계속 True
    시스템 제약 조건 이름 포함 True
    종속 개체에 대한 스크립트 생성 True
    스크립트 데이터 정렬 True
    스크립트 로그인 True
    스크립트 개체 수준 권한 True
    스크립트 통계 통계 및 히스토그램 스크립트
    USE DATABASE 스크립트 True
    인덱스 스크립트 True
    스크립트 트리거 True

    참고

    스키마에 dbo 이외의 로그인이 소유한 개체가 포함되지 않는 한 스크립트 로그인스크립트 개체 수준 권한 옵션이 필요하지 않을 수 있습니다.

  9. 확인을 선택하여 고급 스크립팅 옵션 페이지를 저장하고 닫습니다.

    7단계에서 선택한 각 데이터베이스 개체 유형에 대한 대화 상자가 나타납니다.

  10. 각 대화 상자에서 특정 테이블, 뷰, 함수 또는 기타 데이터베이스 개체를 선택한 다음 , 다음을 선택합니다.

  11. 파일로 스크립트 옵션을 선택한 다음, 3단계에서 입력한 것과 동일한 파일 이름을 지정합니다.

  12. 마침을 선택하여 스크립팅을 시작합니다.

    스크립팅이 완료되면 스크립트 파일을 Microsoft 지원 엔지니어에게 보냅니다. Microsoft 지원 엔지니어는 다음 정보를 요청할 수도 있습니다.

    • 프로세서 수와 실제 메모리의 양을 포함한 하드웨어 구성입니다.

    • 쿼리를 실행할 때 활성화된 SET 옵션입니다.

    SQLDiag 보고서 또는 SQL Profiler 추적을 전송하여 이 정보를 이미 제공했을 수 있습니다. 이 정보를 제공하기 위해 다른 방법을 사용했을 수도 있습니다.

정보 사용 방법

다음 표에서는 쿼리 최적화 프로그램이 이 정보를 사용하여 쿼리 계획을 선택하는 방법을 설명하는 데 도움이 됩니다.

메타 데이터

옵션 설명
제약 조건 쿼리 최적화 프로그램은 제약 조건을 사용하여 쿼리와 기본 스키마 간의 모순을 검색하는 경우가 많습니다. 예를 들어 쿼리에 절이 WHERE col = 5 포함되어 있고 CHECK (col < 5) 기본 테이블에 제약 조건이 있는 경우 쿼리 최적화 프로그램은 일치하는 행이 없다는 것을 알고 있습니다. 쿼리 최적화 프로그램은 null 허용 가능성에 대해 비슷한 유형의 추론을 만듭니다. 예를 들어 WHERE col IS NULL 절은 열의 null 허용 여부와 열이 외부 조인의 외부 테이블에서 온 것인지 여부에 따라 true 또는 false라고 합니다. FOREIGN KEY 제약 조건이 있으면 카디널리티와 적절한 조인 순서를 결정하는 데 유용합니다. 쿼리 최적화 프로그램은 제약 조건자 정보를 사용하여 조인을 제거하거나 조건자를 단순화할 수 있습니다. 이러한 변경으로 기본 테이블에 액세스하기 위한 요구 사항이 제거될 수 있습니다.
통계 통계 정보에는 인덱스 및 통계 키의 선행 열 분포를 보여 주는 밀도와 히스토그램이 포함되어 있습니다. 조건자의 특성에 따라 쿼리 최적화 프로그램은 밀도, 히스토그램 또는 둘 다를 사용하여 조건자의 카디널리티를 예측할 수 있습니다. 정확한 카디널리티 추정을 위해서는 최신 통계가 필요합니다. 카디널리티 추정치는 운영자의 비용을 예측하는 입력으로 사용됩니다. 따라서 최적의 쿼리 계획을 얻으려면 카디널리티 추정치가 양수해야 합니다.
테이블 크기(행 및 페이지 수) 쿼리 최적화 프로그램은 히스토그램 및 밀도를 사용하여 지정된 조건자가 true 또는 false일 확률을 계산합니다. 최종 카디널리티 추정치는 자식 연산자가 반환하는 행 수에 확률을 곱하여 계산됩니다. 테이블 또는 인덱스의 페이지 수는 IO 비용을 예측하는 요인입니다. 테이블 크기는 검사 비용을 계산하는 데 사용되며 인덱스 검색 중에 액세스할 페이지 수를 추정할 때 유용합니다.
데이터베이스 옵션 여러 데이터베이스 옵션이 최적화에 영향을 줄 수 있습니다. AUTO_CREATE_STATISTICSAUTO_UPDATE_STATISTICS 옵션은 쿼리 최적화 프로그램이 새 통계를 만들거나 오래된 통계를 업데이트할지 여부에 영향을 줍니다. 매개 변수화 수준은 입력 쿼리가 쿼리 최적화 프로그램에 전달되기 전에 입력 쿼리가 매개 변수화되는 방식에 영향을 줍니다. 매개 변수화는 카디널리티 추정에 영향을 줄 수 있으며 인덱싱된 뷰 및 다른 유형의 최적화와 일치하는 것을 방지할 수도 있습니다. 설정으로 DATE_CORRELATION_OPTIMIZATION 인해 최적화 프로그램은 열 간의 상관 관계를 검색합니다. 이 설정은 카디널리티 및 비용 예측에 영향을 줍니다.

환경

옵션 설명
세션 SET 옵션 설정은 ANSI_NULLS 식이 NULL = NULL true로 평가되는지 여부에 영향을 줍니다. 외부 조인에 대한 카디널리티 추정은 현재 설정에 따라 변경될 수 있습니다. 또한 모호한 식도 변경될 수 있습니다. 예를 들어 식은 col = NULL 설정에 따라 다르게 평가됩니다. 그러나 식은 col IS NULL 항상 동일한 방식으로 평가됩니다.
하드웨어 리소스 정렬 및 해시 연산자의 비용은 SQL Server 사용할 수 있는 메모리의 상대적 양에 따라 달라집니다. 예를 들어 데이터 크기가 캐시보다 큰 경우 쿼리 최적화 프로그램은 데이터가 항상 디스크로 스풀되어야 한다는 것을 알고 있습니다. 그러나 데이터 크기가 캐시보다 훨씬 작은 경우 작업은 메모리에서 수행될 가능성이 높습니다. SQL Server 또한 서버에 둘 이상의 프로세서가 있고 힌트 또는 최대 병렬 처리 수준 구성 옵션을 사용하여 MAXDOP 병렬 처리를 사용하지 않도록 설정하지 않은 경우 다른 최적화를 고려합니다.

참고 항목