CREATE STATISTICS(Transact-SQL)

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance Azure Synapse Analytics Microsoft Fabric의 SQL 분석 엔드포인트 Microsoft Fabric의 웨어하우스

하나 이상의 테이블 열, 인덱싱된 뷰 또는 외부 테이블에 대한 쿼리 최적화 통계를 만듭니다. 대부분의 쿼리에서 쿼리 최적화 프로그램은 고품질 쿼리 계획에 필요한 통계를 이미 생성합니다. 경우에 따라 쿼리 성능을 향상시키기 위해 쿼리 디자인을 사용하여 추가 통계 CREATE STATISTICS 를 만들거나 수정해야 합니다.

자세한 내용은 통계를 참조하세요.

참고 항목

Microsoft Fabric의 통계에 대한 자세한 내용은 패브릭 데이터 웨어하우징의 통계를 참조 하세요.

Transact-SQL 구문 표기 규칙

구문

SQL Server, Azure SQL Database 및 Azure SQL Managed Instance에 대한 구문입니다.

-- Create statistics on an external table
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WITH FULLSCAN ] ;

-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ , ...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
        ]
    ];

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_constant ]

Azure Synapse Analytics 및 Analytics Platform System(PDW)에 대한 구문입니다.

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

Microsoft Fabric의 구문입니다.

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

인수

statistics_name

만들 통계의 이름입니다.

table_or_indexed_view_name

통계를 만들 테이블, 인덱싱된 뷰 또는 외부 테이블의 이름입니다. 다른 데이터베이스에 대한 통계를 만들려면 정규화된 테이블 이름을 지정합니다.

column [ ,...n]

통계에 포함할 하나 이상의 열입니다. 열의 우선 순위는 왼쪽에서 오른쪽이어야 합니다. 히스토그램을 만드는 데에는 첫 번째 열만이 사용됩니다. 모든 열은 밀도라는 열간 상관 관계 통계에 사용됩니다.

다음 예외 사항을 제외하고 인덱스 키 열로 지정할 수 있는 모든 열을 지정할 수 있습니다.

• xml, 전체 텍스트 및 FILESTREAM 열을 지정할 수 없습니다.

• 계산 열은 데이터베이스 QUOTED_IDENTIFIER 설정이 있는 경우에만 ARITHABORT 지정할 수 있습니다ON.

• CLR 사용자 정의 형식 열은 이진 순서 정렬이 지원될 경우에만 지정할 수 있습니다. 사용자 정의 형식 열의 메서드 호출로 정의된 계산 열은 메서드가 결정적 메서드로 표시된 경우에 지정할 수 있습니다.

WHERE <filter_predicate>

통계 개체를 만들 때 포함할 행 하위 집합을 선택하는 식을 지정합니다. 필터 조건자를 사용하여 만든 통계를 필터링된 통계라고 합니다. 필터 조건자는 간단한 비교 논리를 사용하며 계산 열, UDT 열, 공간 데이터 형식 열 또는 hierarchyID 데이터 형식 열을 참조할 수 없습니다. 리터럴을 사용한 NULL 비교는 비교 연산자에서 허용되지 않습니다. 대신 IS NULL 및 IS NOT NULL 연산자를 사용합니다.

다음은 Production.BillOfMaterials 테이블에 대한 필터 조건자의 몇 가지 예입니다.

• WHERE StartDate > '20000101' AND EndDate <= '20000630'
• WHERE ComponentID IN (533, 324, 753)
• WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

필터 조건자에 대한 자세한 내용은 필터링된 인덱스 만들기를 참조 하세요.

FULLSCAN

적용 대상: SQL Server 2016 (13.x) SP 1 CU 4, SQL Server 2017 (14.x) CU 1 이상 버전

모든 행을 검사하여 통계를 컴퓨팅합니다. FULLSCAN 동일한 SAMPLE 100 PERCENT 결과를 갖습니다. FULLSCAN 옵션과 함께 SAMPLE 사용할 수 없습니다.

생략하면 SQL Server는 샘플링을 사용하여 통계를 만들고 고품질 쿼리 계획을 만드는 데 필요한 샘플 크기를 결정합니다.

Microsoft Fabric의 Warehouse에서는 단일 열 FULLSCAN 및 단일 열 SAMPLE기반 통계만 지원됩니다. 옵션이 포함되지 SAMPLE 않으면 통계가 생성됩니다.

SAMPLE number { PERCENT | ROWS }

통계를 만들 때 사용할 쿼리 최적화 프로그램의 테이블 또는 인덱싱된 뷰에서 대략적인 백분율 또는 행 수를 지정합니다. 의 경우 PERCENT숫자는 0에서 100까지일 수 있으며, 숫자의 경우 ROWS0에서 총 행 수까지일 수 있습니다. 쿼리 최적화 프로그램에서 샘플링하는 실제 행의 비율이나 개수는 지정된 비율이나 개수와 일치하지 않을 수 있습니다. 예를 들어, 쿼리 최적화 프로그램에서는 데이터 페이지의 모든 행을 검색합니다.

SAMPLE 는 기본 샘플링에 따라 쿼리 계획이 최적이 아닌 특수한 경우에 유용합니다. 대부분의 경우 쿼리 최적화 프로그램에서 이미 샘플링을 사용하고 있으며 고품질 쿼리 계획을 만드는 데 필요한 대로 기본적으로 통계적으로 유의한 샘플 크기를 결정하기 때문에 지정할 SAMPLE 필요가 없습니다.

SAMPLE FULLSCAN 옵션과 함께 사용할 수 없습니다. 지정되거나 FULLSCAN 지정되지 않은 경우 SAMPLE 쿼리 최적화 프로그램은 샘플링된 데이터를 사용하고 기본적으로 샘플 크기를 계산합니다.

0 PERCENT 또는 0 ROWS로 지정하지 않는 것이 좋습니다. 0 PERCENT 지정되거나 0 ROWS 지정되면 통계 개체가 만들어지지만 통계 데이터는 포함되지 않습니다.

Microsoft Fabric의 Warehouse에서는 단일 열 FULLSCAN 및 단일 열 SAMPLE기반 통계만 지원됩니다. 옵션이 포함되지 FULLSCAN 않으면 통계가 생성됩니다.

PERSIST_SAMPLE_PERCENT = { ON | OFF }

ON통계는 샘플링 비율을 명시적으로 지정하지 않는 후속 업데이트에 대한 생성 샘플링 비율을 유지합니다. OFF통계 샘플링 비율이 샘플링 비율을 명시적으로 지정하지 않는 후속 업데이트에서 기본 샘플링으로 다시 설정됩니다. 기본값은 OFF입니다.

참고 항목

테이블이 잘린 경우 잘린 HoBT에서 작성된 모든 통계가 기본 샘플링 비율을 사용하도록 되돌아갑니다.

STATS_STREAM *=***stats_stream

정보를 제공하기 위해서만 확인됩니다. 지원 안 됨. 향후 호환성은 보장되지 않습니다.

NORECOMPUTE

자동 통계 업데이트 옵션(AUTO_STATISTICS_UPDATEstatistics_name)을 사용하지 않도록 설정합니다. 이 옵션을 지정하면 쿼리 최적화 프로그램에서 statistics_name에 대해 진행 중인 모든 통계 업데이트를 완료하고 이후의 업데이트를 비활성화합니다.

통계 업데이트를 다시 사용하도록 설정하려면 DROP STATISTICS를 사용하여 통계를 제거한 다음 옵션 없이 NORECOMPUTE 실행 CREATE STATISTICS 합니다.

Warning

통계 자동 업데이트를 사용하지 않도록 설정하면 쿼리 최적화 프로그램에서 테이블과 관련된 쿼리에 대한 최적의 실행 계획을 선택하지 못할 수 있습니다. 이 옵션은 정규화된 데이터베이스 관리자만 사용해야 합니다.

이 옵션에 대한 AUTO_STATISTICS_UPDATE 자세한 내용은 ALTER DATABASE SET 옵션을 참조 하세요. 통계 업데이트를 비활성화하고 다시 활성화하는 방법은 통계를 참조하세요.

INCREMENTAL = {ON | OFF}

적용 대상: SQL Server 2014(12.x) 이상

생성 ON되는 통계는 파티션 통계당입니다. 경우 OFF통계는 모든 파티션에 대해 결합됩니다. 기본값은 OFF입니다.

파티션별 통계가 지원되지 않으면 오류가 생성됩니다. 다음 통계 유형에 대해서는 증분 통계가 지원되지 않습니다.

• 기본 테이블을 기준으로 파티션 정렬되지 않은 인덱스를 사용하여 작성된 통계
• Always On 읽기 가능한 보조 데이터베이스에 대해 작성된 통계입니다.
• 읽기 전용 데이터베이스에 대해 작성된 통계입니다.
• 필터링된 인덱스에 대해 작성된 통계입니다.
• 뷰에 대해 작성된 통계입니다.
• 내부 테이블에 대해 작성된 통계입니다.
• 공간 인덱스 또는 XML 인덱스를 사용하여 작성된 통계입니다.

MAXDOP = max_degree_of_parallelism

적용 대상: SQL Server 2016 (13.x) SP 2, SQL Server 2017 (14.x) CU 3 이상 버전

통계 작업 중에 최대 병렬 처리 수준 구성 옵션을 재정의합니다. 자세한 내용은 최대 병렬 처리 수준 구성(서버 구성 옵션)을 참조하세요. 병렬 계획 실행에 사용되는 프로세서 수를 제한하는 데 사용합니다 MAXDOP . 최대값은 64개입니다.

max_degree_of_parallelism은 다음 중 하나일 수 있습니다.

• 1: 병렬 계획 생성을 표시하지 않습니다.
• >1: 병렬 인덱스 작업에 사용되는 프로세서의 최대 수를 지정된 수로 제한합니다.
• 0 (기본값): 현재 시스템 워크로드에 따라 실제 프로세서 수 이하를 사용합니다.

update_stats_stream_option

정보를 제공하기 위해서만 확인됩니다. 지원 안 됨. 향후 호환성은 보장되지 않습니다.

AUTO_DROP = { ON | OFF }

적용 대상: SQL Server 2022(16.x) 이상 버전 및 Azure SQL Database, Azure SQL Managed Instance

SQL Server 2022(16.x) 이전에는 사용자 데이터베이스의 사용자 또는 타사 도구에서 통계를 수동으로 만드는 경우 해당 통계 개체가 고객이 원하는 스키마 변경을 차단하거나 방해할 수 있습니다.

SQL Server 2022(16.x) AUTO_DROP 부터 이 옵션은 기본적으로 모든 새 데이터베이스와 마이그레이션된 데이터베이스에서 사용하도록 설정됩니다. 이 AUTO_DROP 속성을 사용하면 통계 개체에 의해 후속 스키마 변경이 차단되지 않고 필요에 따라 통계가 삭제되도록 모드에서 통계 개체를 만들 수 있습니다. 이러한 방식으로 사용하도록 설정된 수동으로 만든 통계는 AUTO_DROP 자동 생성된 통계처럼 동작합니다.

참고 항목

자동 생성된 통계에서 Auto_Drop 속성을 설정하거나 설정 해제하려고 하면 오류가 발생할 수 있습니다. 자동 생성된 통계는 항상 자동 삭제를 사용합니다. 일부 백업은 복원될 때 다음에 통계 개체가 업데이트될 때까지(수동 또는 자동으로) 이 속성을 잘못 설정했을 수 있습니다. 그러나 자동 생성 통계는 항상 자동 삭제 통계처럼 동작합니다. 이전 버전에서 SQL Server 2022(16.x)로 데이터베이스를 복원하는 경우 데이터베이스에서 실행 sp_updatestats 하여 통계 AUTO_DROP 기능에 대한 적절한 메타데이터를 설정하는 것이 좋습니다.

자세한 내용은 AUTO_DROP 옵션을 참조하세요.

사용 권한

다음 중 한 가지 권한이 필요합니다.

• ALTER TABLE
• 사용자가 테이블 소유자
• db_ddladmin 고정 데이터베이스 역할의 멤버 자격

설명

SQL Server는 통계를 만들기 전에 tempdb를 사용하여 샘플링된 행을 정렬합니다.

외부 테이블에 대한 통계

외부 테이블 통계를 만들 때 SQL Server는 외부 테이블을 임시 SQL Server 테이블로 가져온 다음, 통계를 만듭니다. 샘플 통계의 경우 샘플링된 행만 가져옵니다. 외부 테이블이 큰 경우 전체 검사 옵션 대신 기본 샘플링을 사용하는 것이 더 빠릅니다.

외부 테이블이 데이터 형식으로 사용하거나 DELTA 데이터 형식으로 사용하는 PARQUETCSVDELIMITEDTEXT경우 외부 테이블은 명령당 CREATE STATISTICS 하나의 열에 대한 통계만 지원합니다.

필터링된 조건을 적용한 통계

필터링된 통계는 잘 정의된 데이터의 하위 집합에서 선택하는 쿼리에 대한 쿼리 성능을 높일 수 있습니다. WHERE 절의 필터 조건자를 사용하여 통계에 포함되는 데이터의 하위 집합을 선택합니다.

CREATE STATISTICS를 사용하는 경우

CREATE STATISTICS를 사용하는 경우에 대한 자세한 내용은 통계를 참조하세요.

필터링된 통계의 종속성 참조

sys.sql_expression_dependencies 카탈로그 뷰에서는 필터링된 통계 조건자의 각 열을 참조 종속성으로 추적합니다. 필터링된 통계를 만들기 전에 테이블 열에서 수행하는 작업을 고려합니다. 필터링된 통계 조건자에서 정의된 테이블 열의 정의를 삭제, 이름 바꾸기 또는 변경할 수 없습니다.

제한 사항

• 외부 테이블에 대해서는 통계 업데이트가 지원되지 않습니다. 외부 테이블에 대한 통계를 업데이트하려면 통계를 삭제하고 다시 만듭니다.
• 정적 개체당 최대 64개의 열을 나열할 수 있습니다.
• 이 MAXDOP 옵션은 , ROWCOUNT및 옵션과 STATS_STREAMPAGECOUNT 호환되지 않습니다.
• MAXDOP 옵션은 Resource Governor 워크로드 그룹 MAX_DOP 설정으로 제한됩니다(사용된 경우).
• CREATE 및 DROP STATISTICS 외부 테이블은 Azure SQL Database에서 지원되지 않습니다.

예제

이 문서의 Transact-SQL 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

A. CREATE STATISTICS에 SAMPLE number PERCENT 사용

다음 예제에서는 AdventureWorks2022 데이터베이스 테이블의 5% BusinessEntityID 및 EmailPromotion 열에 Person 대한 무작위 샘플을 사용하여 통계를 만듭니다ContactMail1.

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. CREATE STATISTICS에 FULLSCAN 및 NORECOMPUTE 사용

다음 예에서는 NamePurchase 테이블의 BusinessEntityID 및 EmailPromotion 열에서 모든 행에 대한 Person 통계를 만듭니다. 통계의 자동 다시 계산 기능은 사용하지 않습니다.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. CREATE STATISTICS를 사용하여 필터링된 통계 만들기

다음 예에서는 필터링된 통계 ContactPromotion1을 만듭니다. 데이터베이스 엔진은 데이터의 50%를 샘플링한 다음, EmailPromotion이 2인 행을 선택합니다.

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. 외부 테이블에 대한 통계 만들기

열 목록을 제공하는 것을 제외하고, 외부 테이블에 대한 통계를 만들 때 내려야 하는 유일한 결정은 통계를 만드는 방법이 행 샘플링인지 아니면 모든 행 검사인가 하는 것입니다. CREATE 및 DROP STATISTICS 외부 테이블은 Azure SQL Database에서 지원되지 않습니다.

SQL Server는 통계를 만들기 위해 외부 테이블의 데이터를 임시 테이블로 가져오기 때문에 전체 검사 옵션이 훨씬 더 오래 걸립니다. 큰 테이블의 경우 일반적으로 기본 샘플링 방법으로 충분합니다.

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. FULLSCAN 및 PERSIST_SAMPLE_PERCENT와 함께 CREATE STATISTICS 사용

다음 예제에서는 테이블 및 열의 Person 모든 행에 EmailPromotion BusinessEntityID 대한 통계를 만들고 NamePurchase 샘플링 비율을 명시적으로 지정하지 않는 모든 후속 업데이트에 대해 100% 샘플링 비율을 설정합니다.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

AdventureWorksDW 데이터베이스를 사용하는 예제

F. 두 열에 대한 통계 만들기

다음 예제에서는 DimCustomer 테이블의 CustomerKey 및 EmailAddress 열을 기반으로 CustomerStats1 통계를 만듭니다. 통계는 Customer 테이블에 있는 행의 통계적으로 의미 있는 샘플링을 기반으로 작성됩니다.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. 전체 검사를 사용하여 통계 만들기

다음 예제에서는 DimCustomer 테이블의 모든 행 검사를 기반으로 CustomerStatsFullScan 통계를 만듭니다.

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. 샘플 비율을 지정하여 통계 만들기

다음 예제에서는 DimCustomer 테이블 행의 50% 검사를 기반으로 CustomerStatsSampleScan 통계를 만듭니다.

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

9\. CREATE STATISTICS를 AUTO_DROP과 함께 사용

자동 삭제 통계를 사용하려면 통계 만들기 또는 업데이트의 “WITH” 절에 다음을 추가하기만 하면 됩니다.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

기존 통계에 대한 자동 삭제 설정을 평가하려면 sys.stats의 auto_drop 열을 사용합니다.

SELECT object_id, [name], auto_drop
FROM sys.stats;

관련 콘텐츠

• 통계
• 패브릭 데이터 웨어하우징의 통계
• UPDATE STATISTICS(Transact-SQL)
• sp_updatestats(Transact-SQL)
• DBCC SHOW_STATISTICS(Transact-SQL)
• DROP STATISTICS(Transact-SQL)
• sys.stats(Transact-SQL)
• sys.stats_columns(Transact-SQL)