이 문서에서는 SQL Server에서 통계 전용 데이터베이스를 만들기 위해 데이터베이스 메타데이터를 사용하여 통계 스크립트를 생성하는 방법을 알아봅니다.
원래 제품 버전: SQL Server
원래 KB 번호: 914288
소개
DBCC CLONEDATABASE는 성능 문제를 조사하기 위해 데이터베이스의 스키마 전용 복제본을 생성하는 데 선호되는 방법입니다. 사용할 수 없는 경우에만 이 문서의 절차를 사용합니다 DBCC CLONEDATABASE.
Microsoft SQL Server의 쿼리 최적화 프로그램은 다음 유형의 정보를 사용하여 최적의 쿼리 계획을 결정합니다.
- 데이터베이스 메타데이터
- 하드웨어 환경
- 데이터베이스 세션 상태
일반적으로 테스트 시스템에서 쿼리 최적화 프로그램의 동작을 재현하려면 이러한 모든 유형의 정보를 시뮬레이션해야 합니다.
Microsoft 고객 지원 서비스는 쿼리 최적화 프로그램 문제를 조사하기 위해 데이터베이스 메타데이터의 스크립트를 생성하도록 요청할 수 있습니다. 이 문서에서는 통계 스크립트를 생성하는 단계에 대해 설명하고 쿼리 최적화 프로그램에서 정보를 사용하는 방법을 설명합니다.
참고 항목
이 데이터 내에 저장된 키에는 PII 정보가 포함될 수 있습니다. 예를 들어 테이블에 통계가 있는 전화 번호 열이 포함된 경우 각 단계의 높은 키 값은 생성된 통계 스크립트에 포함됩니다.
전체 데이터베이스 스크립깅
통계 전용 복제 데이터베이스를 생성하는 경우 개별 개체를 스크립팅하는 대신 전체 데이터베이스를 스크립팅하는 것이 더 쉽고 안정적일 수 있습니다. 전체 데이터베이스를 스크립깅할 때 다음과 같은 이점이 제공됩니다.
- 문제를 재현하는 데 필요한 종속 개체 누락 문제를 방지합니다.
- 필요한 개체를 선택하려면 더 적은 단계가 필요합니다.
데이터베이스에 대한 스크립트를 생성하고 데이터베이스에 대한 메타데이터에 수천 개의 개체가 포함된 경우 스크립팅 프로세스는 상당한 CPU 리소스를 사용합니다. 사용량이 많은 시간에 스크립트를 생성하거나 두 번째 옵션 인 개별 개체 스크립트를 사용하여 개별 개체 에 대한 스크립트를 생성하는 것이 좋습니다.
쿼리에서 참조하는 각 데이터베이스를 스크립딩하려면 다음 단계를 수행합니다.
SQL Server Management Studio를 엽니다.
개체 탐색기 데이터베이스를 확장한 다음 스크립트할 데이터베이스를 찾습니다.
데이터베이스를 마우스 오른쪽 단추로 클릭하고 작업을 가리킨 다음 스크립트 생성을 선택합니다.
스크립트 마법사에서 올바른 데이터베이스가 선택되어 있는지 확인합니다. 전체 데이터베이스 및 모든 데이터베이스 개체 스크립트를 클릭하여 선택한 다음, 다음을 선택합니다.
스크립트 옵션 선택 대화 상자에서 고급 단추를 선택하여 다음 설정을 기본값에서 다음 표에 나열된 값으로 변경합니다.
스크립팅 옵션 선택할 값 안시 패딩 참 오류 발생시 스크립팅 계속 참 종속 개체에 대해 스크립트 생성 참 시스템 제약 조건 이름 포함 참 데이터 정렬 스크립팅 참 로그인 스크립팅 참 스크립트 개체 수준 권한 참 통계 스크립팅 통계 및 히스토그램 스크립트 인덱스 스크립팅 참 트리거 스크립팅 참 참고 항목
스키마에 dbo 이외의 로그인이 소유한 개체가 포함되어 있지 않으면 스크립트 로그인 옵션 및 스크립트 개체 수준 사용 권한 옵션이 필요하지 않을 수 있습니다.
확인을 선택하여 변경 내용을 저장하고 고급 스크립팅 옵션 페이지를 닫습니다.
파일에 저장을 선택하고 단일 파일 옵션을 선택합니다.
선택 항목을 검토하고 다음을 선택합니다.
마침을 선택합니다.
개별 개체 스크립깅
전체 데이터베이스를 스크립팅하는 대신 특정 쿼리에서 참조하는 개별 개체만 스크립팅할 수 있습니다. 그러나 절을 사용하여 모든 데이터베이스 개체를 WITH SCHEMABINDING 만들지 않는 한 시스템 테이블의 sys.depends 종속성 정보가 항상 정확하지는 않을 수 있습니다. 이 부정확성으로 인해 다음 문제 중 하나가 발생할 수 있습니다.
스크립팅 프로세스는 종속 개체를 스크립팅하지 않습니다.
스크립팅 프로세스는 잘못된 순서로 개체를 스크립팅할 수 있습니다. 스크립트를 성공적으로 실행하려면 생성된 스크립트를 수동으로 편집해야 합니다.
따라서 데이터베이스에 개체가 많고 스크립팅이 너무 오래 걸리지 않는 한 개별 개체를 스크립팅하는 것은 권장되지 않습니다. 스크립트 개별 개체를 사용해야 하는 경우 다음 단계를 수행합니다.
SQL Server Management Studio에서 데이터베이스를 확장 한 다음 스크립트할 데이터베이스를 찾습니다.
데이터베이스를 마우스 오른쪽 단추로 클릭하고 스크립트 데이터베이스를 가리킨 다음 CREATE To를 가리킨 다음 파일을 선택합니다.
파일 이름을 입력한 다음 저장을 선택합니다.
핵심 데이터베이스 컨테이너가 스크립핑됩니다. 이 컨테이너에는 파일, 파일 그룹, 데이터베이스 및 속성이 포함됩니다.
데이터베이스를 마우스 오른쪽 단추로 클릭하고 작업을 가리킨 다음 스크립트 생성을 선택합니다.
올바른 데이터베이스가 선택되어 있는지 확인한 다음, 다음을 선택합니다.
개체 유형 선택 대화 상자에서 특정 데이터베이스 개체 선택을 선택하고 문제가 있는 쿼리에서 참조하는 모든 데이터베이스 개체 형식을 선택합니다.
예를 들어 쿼리에서 테이블만 참조하는 경우 테이블을 선택합니다. 쿼리가 뷰를 참조하는 경우 뷰 및 테이블을 선택합니다. 문제가 있는 쿼리에서 사용자 정의 함수를 사용하는 경우 Functions를 선택합니다.
쿼리에서 참조하는 모든 개체 형식을 선택한 경우 다음을 선택합니다.
스크립팅 옵션 설정 대화 상자에서 고급 단추를 선택하고 다음 설정을 기본값에서 고급 스크립팅 옵션 페이지의 다음 표에 나열된 값으로 변경합니다.
스크립팅 옵션 선택할 값 안시 패딩 참 오류 발생시 스크립팅 계속 참 시스템 제약 조건 이름 포함 참 종속 개체에 대해 스크립트 생성 참 데이터 정렬 스크립팅 참 로그인 스크립팅 참 스크립트 개체 수준 권한 참 통계 스크립팅 통계 및 히스토그램 스크립트 USE DATABASE 스크립팅 참 인덱스 스크립팅 참 트리거 스크립팅 참 참고 항목
스키마에 dbo 이외의 로그인이 소유한 개체가 포함되어 있지 않으면 스크립트 로그인 및 스크립트 개체 수준 사용 권한 옵션이 필요하지 않을 수 있습니다.
확인을 선택하여 고급 스크립팅 옵션 페이지를 저장하고 닫습니다.
7단계에서 선택한 각 데이터베이스 개체 유형에 대한 대화 상자가 나타납니다.
각 대화 상자에서 특정 테이블, 뷰, 함수 또는 기타 데이터베이스 개체를 선택한 다음 다음을 선택합니다.
[파일로 스크립트] 옵션을 선택한 다음, 3단계에서 입력한 것과 동일한 파일 이름을 지정합니다.
마침을 선택하여 스크립팅을 시작합니다.
스크립팅이 완료되면 스크립트 파일을 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_STATISTICS 및 AUTO_UPDATE_STATISTICS 옵션은 쿼리 최적화 프로그램에서 새 통계를 만들거나 오래된 통계를 업데이트할지에 영향을 줍니다. 매개 변수화 수준은 입력 쿼리가 쿼리 최적화 프로그램에서 전달되기 전에 입력 쿼리가 매개 변수화되는 방식에 영향을 줍니다. 매개 변수화는 카디널리티 예측에 영향을 줄 수 있으며 인덱싱된 뷰 및 다른 유형의 최적화에 대한 일치를 방지할 수도 있습니다. 이 DATE_CORRELATION_OPTIMIZATION 설정을 통해 최적화 프로그램은 열 간의 상관 관계를 검색합니다. 이 설정은 카디널리티 및 비용 예측에 영향을 줍니다. |
환경
| 옵션 | 설명 |
|---|---|
| 세션 SET 옵션 | 이 설정은 ANSI_NULLS 식이 NULL = NULL true로 평가되는지 여부에 영향을 줍니다. 외부 조인에 대한 카디널리티 예측은 현재 설정에 따라 변경될 수 있습니다. 또한 모호한 식도 변경될 수 있습니다. 예를 들어 식은 col = NULL 설정에 따라 다르게 평가됩니다. 그러나 식은 col IS NULL 항상 동일한 방식으로 평가됩니다. |
| 하드웨어 리소스 | 정렬 및 해시 연산자의 비용은 SQL Server에서 사용할 수 있는 메모리의 상대적 양에 따라 달라집니다. 예를 들어 데이터 크기가 캐시보다 큰 경우 쿼리 최적화 프로그램은 데이터가 항상 디스크에 스풀되어야 한다는 것을 알고 있습니다. 그러나 데이터 크기가 캐시보다 훨씬 작은 경우 메모리에서 작업이 수행될 가능성이 높습니다. 또한 SQL Server는 서버에 둘 이상의 프로세서가 있고 힌트 또는 최대 병렬 처리 수준 구성 옵션을 사용하여 MAXDOP 병렬 처리를 사용하지 않도록 설정하지 않은 경우 다른 최적화를 고려합니다. |