카디널리티 추정(SQL Server)Cardinality Estimation (SQL Server)

이 항목은 다음에 적용됩니다. 예SQL Server(2016부터)예Azure SQL Database아니요Azure SQL Data Warehouse아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

이 문서에서는 SQL 시스템에 대한 최상의 CE(카디널리티 추정) 구성을 평가 및 선택하는 방법을 보여 줍니다.This article illustrates how you can assess and choose the best cardinality estimation (CE) configuration for your SQL system. 대부분의 시스템에서는 가장 정확한 최신 CE를 활용합니다.Most systems benefit from the latest CE because it is the most accurate. CE는 쿼리에서 반환될 행 수를 예측합니다.The CE predicts how many rows your query will likely return. 카디널리티 예측은 쿼리 최적화 프로그램에서 최적의 쿼리 계획을 생성하는 데 사용됩니다.The cardinality prediction is used by the Query Optimizer to generate the optimal query plan. 보다 정확한 추정을 통해 쿼리 최적화 프로그램은 일반적으로 최적의 쿼리 계획을 생성하는 작업을 더 잘 수행할 수 있습니다.With more accurate estimations, the Query Optimizer can usually do a better job of producing a more optimal query plan.

응용 프로그램 시스템에서 새 CE로 인해 중요 쿼리의 계획이 더 느린 계획으로 변경될 수 있습니다.Your application system could possibly have an important query whose plan is changed to a slower plan due to the new CE. 이러한 쿼리는 다음 중 하나일 수 있습니다.Such a query might be like one of the following:

  • 여러 인스턴스가 동시에 자주 실행되는 OLTP(온라인 트랜잭션 처리) 쿼리An OLTP (online transaction processing) query that runs so frequently that multiple instance of it often run concurrently.
  • OLTP 업무 시간 중 많은 집계가 실행되는 SELECTA SELECT with substantial aggregation that runs during your OLTP business hours.

새 CE보다 느리게 실행되는 쿼리를 확인할 수 있습니다.You have techniques for identifying a query that performs slower with the new CE. 또한 성능 문제를 해결하는 방법에 대한 옵션도 있습니다.And you have options for how to address the performance issue.

CE 버전Versions of the CE

1998년 Microsoft SQL Server 7.0에서 호환성 수준 70으로 CE가 크게 업데이트되었습니다.In 1998, a major update of the CE was part of Microsoft SQL Server 7.0, for which the compatibility level was 70. 이후 업데이트는 SQL Server 2014SQL Server 2014에서 시작되었으며 호환성 수준 120 이상을 의미합니다.Subsequent updates started with SQL Server 2014SQL Server 2014, meaning compatibility levels 120 and above. 수준 120 이상의 CE 업데이트는 최신 데이터 웨어하우징 및 OLTP 워크로드에서 잘 작동하는 가정 및 알고리즘을 통합합니다.The CE updates for levels 120 and above incorporate assumptions and algorithms that work well on modern data warehousing and on OLTP workloads.

호환성 수준: COMPATIBILITY_LEVEL에 대해 다음 Transact-SQL 코드를 사용하여 데이터베이스가 특정 수준에 있는지 확인할 수 있습니다.Compatibility level: You can ensure your database is at a particular level by using the following Transact-SQL code for COMPATIBILITY_LEVEL.

SELECT ServerProperty('ProductVersion');  
go  

ALTER DATABASE <yourDatabase>  
    SET COMPATIBILITY_LEVEL = 130;  
go  

SELECT d.name, d.compatibility_level  
    FROM sys.databases AS d  
    WHERE d.name = 'yourDatabase';  
go  

호환성 수준 120 이상으로 설정된 SQL Server 데이터베이스의 경우 추적 플래그 9481을 활성화하면 시스템에서 CE 버전 70이 사용됩니다.For a SQL Server database set at compatibility level 120 or above, activation of the trace flag 9481 forces the system to use the CE version 70.

레거시 CE: 호환성 수준 120 이상으로 설정된 SQL Server 데이터베이스의 경우 데이터베이스 범위 구성 변경을 사용하여 데이터베이스 수준에서 CE 버전 70을 활성화할 수 있습니다.Legacy CE: For a SQL Server database set at compatibility level 120 and above, the CE version 70 can be can be activated by using the at the database level by using the ALTER DATABASE SCOPED CONFIGURATION.

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = ON;  
go  

SELECT name, value  
    FROM sys.database_scoped_configurations  
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  

또는 SQL Server 2016SQL Server 2016 SP1부터 쿼리 힌트USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')를 사용할 수 있습니다.Or starting with SQL Server 2016SQL Server 2016 SP1, the Query Hint USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

SELECT CustomerId, OrderAddedDate  
   FROM OrderTable  
   WHERE OrderAddedDate >= '2016-05-01'; 
   OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

쿼리 저장소: SQL Server 2016SQL Server 2016에 처음 도입된 쿼리 저장소는 쿼리의 성능을 검사하는 유용한 도구입니다.Query store: Starting with SQL Server 2016SQL Server 2016, the query store is a handy tool for examining the performance of your queries. 쿼리 저장소가 사용하도록 설정된 경우 Management StudioManagement Studio의 데이터베이스 노드 아래에 있는 개체 탐색기쿼리 저장소 노드가 표시됩니다.In Management StudioManagement Studio, in the Object Explorer under your database node, a Query Store node is displayed when the query store is enabled.

ALTER DATABASE <yourDatabase>  
    SET QUERY_STORE = ON;  
go  

SELECT  
        q.actual_state_desc AS [actual_state_desc-ofQueryStore],  
        q.desired_state_desc,  
        q.query_capture_mode_desc  
    FROM  
        sys.database_query_store_options  AS q;  
go  

ALTER DATABASE <yourDatabase>  
    SET QUERY_STORE CLEAR;  

Management Studio의 최신 릴리스를 설치하고 종종 업데이트하는 것이 좋습니다.We recommend that you install the latest release of Management Studio and update it often.

카디널리티 추정 프로세스를 추적하기 위한 또 다른 옵션은 확장 이벤트 query_optimizer_estimate_cardinality를 사용하는 것입니다.Another option for tracking the cardinality estimation process is to use the extended event named query_optimizer_estimate_cardinality. 다음 T-SQL 코드 샘플은 SQL ServerSQL Server에서 실행됩니다.The following T-SQL code sample runs on SQL ServerSQL Server. C:\Temp(경로 변경 가능)에 .xel 파일을 씁니다.It writes a .xel file to C:\Temp\ (although you can change the path). Management StudioManagement Studio에서 .xel 파일을 열면 사용자에게 친숙한 방식으로 세부 정보가 표시됩니다.When you open the .xel file in Management StudioManagement Studio, its detailed information is displayed in a user friendly manner.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  

CREATE EVENT SESSION Test_the_CE_qoec_1  
    ON SERVER  
    ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
    (  
        ACTION (sqlserver.sql_text)  
            WHERE (  
                sql_text LIKE '%yourTable%'  
                and sql_text LIKE '%SUM(%'  
            )  
    )  
    ADD TARGET package0.asynchronous_file_target   
        (SET  
            filename = 'c:\temp\xe_qoec_1.xel',  
            metadatafile = 'c:\temp\xe_qoec_1.xem'  
        );  
go  

ALTER EVENT SESSION Test_the_CE_qoec_1  
    ON SERVER  
    STATE = START;  --STOP;  
go  

SQL 데이터베이스SQL Database용 확장 이벤트에 대한 자세한 내용은 SQL Database의 확장 이벤트를 참조하세요.For information about extended events as tailored for SQL 데이터베이스SQL Database, see Extended events in SQL Database.

CE 버전 평가 단계Steps to assess the CE version

다음 단계를 사용하여 가장 중요한 쿼리의 성능이 최신 CE에서 저하되는지 평가할 수 있습니다.Next are steps you can use to assess whether any of your most important queries perform less well under the latest CE. 일부 단계는 이전 섹션에 표시되는 코드 샘플을 실행하여 수행됩니다.Some of the steps are performed by running a code sample presented in a preceding section.

  1. Management StudioManagement Studio를 엽니다.Open Management StudioManagement Studio. SQL ServerSQL Server 데이터베이스가 가장 높은 호환성 수준으로 설정되어 있는지 확인합니다.Ensure your SQL ServerSQL Serverdatabase is set to the highest available compatibility level.

  2. 다음 예비 단계를 수행합니다.Perform the following preliminary steps:

    1. Management StudioManagement Studio를 엽니다.Open Management StudioManagement Studio.

    2. T-SQL을 실행하여 SQL ServerSQL Server 데이터베이스가 가장 높은 호환성 수준으로 설정되어 있는지 확인합니다.Run the T-SQL to ensure that your SQL ServerSQL Server database is set to the highest available compatibility level.

    3. 데이터베이스에서 해당 LEGACY_CARDINALITY_ESTIMATION 구성이 OFF로 설정되었는지 확인합니다.Ensure that your database has its LEGACY_CARDINALITY_ESTIMATION configuration turned OFF.

    4. 쿼리 저장소를 지웁니다.CLEAR your query store. 물론 쿼리 저장소가 ON 상태인지 확인합니다.Of course, ensure your query store is ON.

    5. SET NOCOUNT OFF; 문을 실행합니다.Run the statement: SET NOCOUNT OFF;

  3. SET STATISTICS XML ON; 문을 실행합니다.Run the statement: SET STATISTICS XML ON;

  4. 중요한 쿼리를 실행합니다.Run your important query.

  5. 결과 창의 메시지 탭에서 영향 받는 실제 행 수를 확인합니다.In the results pane, on the Messages tab, note the actual number of rows affected.

  6. 결과 창의 결과 탭에서 XML 형식의 통계가 포함된 셀을 두 번 클릭합니다.In the results pane on the Results tab, double-click the cell that contains the statistics in XML format. 그래픽 쿼리 계획이 표시됩니다.A graphic query plan is displayed.

  7. 그래픽 쿼리 계획의 첫 번째 상자를 마우스 오른쪽 단추로 클릭하고 속성을 클릭합니다.Right-click the first box in the graphic query plan, and then click Properties.

  8. 나중에 다른 구성과 비교하기 위해 다음 속성 값을 기록합니다.For later comparison with a different configuration, note the values for the following properties:

    • CardinalityEstimationModelVersionCardinalityEstimationModelVersion.

    • 예상 행 수Estimated Number of Rows.

    • 예상 I/O 비용및 행 수 예측이 아닌 실제 성능과 관련된 여러 유사 예상 속성Estimated I/O Cost, and several similar Estimated properties that involve actual performance rather than row count predictions.

    • 논리 연산물리적 연산.Logical Operation and Physical Operation. 병렬 처리 로 설정하는 것이 좋습니다.Parallelism is a good value.

    • 실제 실행 모드.Actual Execution Mode. 보다 일괄 처리로 설정하는 것이 좋습니다.Batch is a good value, better than Row.

  9. 예상 행 수와 실제 행 수를 비교합니다.Compare the estimated number of rows to the actual number of rows. CE의 부정확도 단위가 1%(높거나 낮음)인가요, 아니면 10%인가요?Is the CE inaccurate by 1% (high or low), or by 10%?

  10. SET STATISTICS XML OFF;를 실행합니다.Run: SET STATISTICS XML OFF;

  11. T-SQL을 실행하여 데이터베이스의 호환성 수준을 한 수준(예: 130 -> 120) 낮춥니다.Run the T-SQL to decrease the compatibility level of your database by one level (such as from 130 down to 120).

  12. 모든 비 임시 단계를 다시 실행합니다.Rerun all the non-preliminary steps.

  13. 두 실행에서 CE 속성 값을 비교합니다.Compare the CE property values from the two runs.

    • 최신 CE의 부정확도가 이전 CE보다 낮은가요?Is the inaccuracy percentage under the newest CE less than under the older CE?
  14. 마지막으로 두 실행의 다양한 성능 속성 값을 비교합니다.Finally, compare the various performance property values from the two runs.

    • 쿼리에서 서로 다른 두 개의 CE 추정에서 다른 계획을 사용했나요?Did your query use a different plan under the two differing CE estimations?

    • 최신 CE에서 쿼리가 더 느리게 실행되었나요?Did your query run slower under the latest CE?

    • 이전 CE의 다른 계획을 사용할 때 쿼리 실행률이 더 향상되지 않는 한 대부분의 경우 최신 CE를 사용하는 것이 좋습니다.Unless your query runs better and with a different plan under the older CE, you almost certainly want the latest CE.

    • 그러나 쿼리가 이전 CE의 계획을 사용할 때 더 빠르게 실행되는 경우 시스템에서 더 빠른 계획을 사용하도록 하고 CE를 무시하는 것이 좋습니다.However, if your query runs with a faster plan under the older CE, consider forcing the system to use the faster plan and to ignore the CE. 이러한 방식으로 모든 쿼리에 최신 CE를 사용하면서 필요한 경우 더 빠른 계획을 유지할 수 있습니다.This way you can have the latest CE on for everything, while keeping the faster plan in the one odd case.

최상의 쿼리 계획을 활성화하는 방법How to activate the best query plan

새로운 CE를 사용할 경우 쿼리에 대해 더 느린 쿼리 계획이 생성된다고 가정합니다.Suppose that with the new CE a slower query plan is generated for your query. 이러한 경우 더 빠른 계획을 활성화해야 하는 몇 가지 옵션이 있습니다.Here are some options you have to activate the faster plan.

전체 데이터베이스에 대해 호환성 수준을 최신보다 더 낮게 설정할 수 있습니다.You could set the compatibility level to a value lower than the latest available, for your whole database.

  • 이렇게 하면 레거시 CE가 활성화되지만 모든 쿼리가 정확도가 떨어지는 이전 CE에 종속됩니다.This activates the Legacy CE, but it makes all queries subject to the older and less accurate CE.

  • 또한 이전 수준 호환성은 쿼리 최적화 프로그램의 향상된 기능을 사용할 수 없습니다.Further the previous level compatibility also loses excellent improvements in the query optimizer.

LEGACY_CARDINALITY_ESTIMATION을 사용하면 전체 데이터베이스에서 이전 CE 또는 특정 쿼리만 사용면서 쿼리 최적화 프로그램의 향상된 기능을 사용할 수 있습니다.You could use LEGACY_CARDINALITY_ESTIMATION to have the whole database use the older CE, or just a specific query, while retaining the improvements in the query optimizer.

최상의 제어를 위해 테스트 중 SQL 시스템에서 이전 CE를 사용하여 생성된 계획을 사용하도록 설정할 수 있습니다.For the finest control, you could force the SQL system to use the plan that was generated with the older CE during your testing. 원하는 계획을 고정 한 다음 전체 데이터베이스에서 최신 호환성 수준 및 CE를 사용하도록 설정할 수 있습니다.After you pin your preferred plan, you can set your whole database to use the latest compatibility level and CE. 옵션은 다음에 자세하게 설명합니다.The option is elaborated next.

특정 쿼리 계획을 강제로 실행하는 방법How to force a particular query plan

쿼리 저장소는 시스템에서 특정 쿼리 계획을 사용하도록 설정할 수 있는 다양한 방법을 제공합니다.The query store gives you different ways that you can force the system to use a particular query plan:

  • sp_query_store_force_plan을 실행합니다.Execute sp_query_store_force_plan.

  • Management StudioManagement Studio에서 쿼리 저장소 노드를 확장하고 리소스를 가장 많이 사용하는 노드를 마우스 오른쪽 단추로 클릭한 다음 리소스를 가장 많이 사용하는 노드 보기를 클릭합니다.In Management StudioManagement Studio, expand your Query Store node, right-click Top Resource Consuming Nodes, and then click View Top Resource Consuming Nodes. 계획 강제 적용계획 강제 적용 해제라는 레이블이 있는 단추가 표시됩니다.The display shows buttons labeled Force Plan and Unforce Plan.

    쿼리 저장소에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.For more information about the query store, see Monitoring Performance By Using the Query Store.

CE 개선 사례Examples of CE improvements

이 섹션에서는 최신 릴리스의 CE에 구현된 향상 기능을 활용하는 쿼리 예제를 설명합니다.This section describes example queries that benefit from the enhancements implemented in the CE in recent releases. 사용자가 특정 작업을 수행할 필요가 없는 배경 정보입니다.This is background information that does not call for specific action on your part.

예제 A. CE는 통계가 마지막으로 수집되었을 때보다 최대값이 더 높을 수 있다는 것을 인식합니다.Example A. CE understands maximum value might be higher than when statistics were last gathered

OrderTable에 대한 통계가 마지막으로 수집된 날짜가 2016-04-30이고 최대 OrderAddedDate가 2016-04-30이라고 가정합니다.Suppose statistics were last gathered for OrderTable on 2016-04-30, when the maximum OrderAddedDate was 2016-04-30. 호환성 수준 120 이상의 CE는 오름차순 데이터를 가진 OrderTable의 열에 통계에 의해 기록된 최대값보다 더 큰 값이 있을 수 있다는 것을 이해합니다.The CE for compatibility level 120 (and for higher levels) understands that columns in OrderTable which have ascending data might have values larger than the maximum recorded by the statistics. 이러한 인식은 다음과 같은 SQL SELECT에 대한 쿼리 계획을 향상시킵니다.This understanding improves the query plan for SQL SELECTs such as the following.

SELECT CustomerId, OrderAddedDate  
    FROM OrderTable  
    WHERE OrderAddedDate >= '2016-05-01';  

예제 B. CE는 동일한 테이블에 대해 필터링된 예측이 종종 서로 연관된다는 것을 이해합니다.Example B. CE understands that filtered predicates on the same table are often correlated

다음 SELECT에는 Model 및 ModelVariant에 대해 필터링된 조건자가 있습니다.In the following SELECT we see filtered predicates on Model and ModelVariant. Model이 'Xbox'일 때 Xbox에 One이라는 변형이 있다면 ModelVariant가 'One'일 가능성이 있음을 직관적으로 이해할 수 있습니다.We intuitively understand that when Model is 'Xbox' there is a chance the ModelVariant is 'One', given that Xbox has a variant called One.

수준 120의 CE는 동일한 테이블의 두 열 Model 및 ModelVariant 간에 상호 연결이 있을 수 있다는 것을 이해합니다.The level 120 CE understands there might be a correlation between the two columns on the same table, Model and ModelVariant. CE는 쿼리에 의해 반환될 행 수를 더 정확하게 예측하고 쿼리 최적화 프로그램에서 더 최적의 계획을 생성합니다.The CE makes a more accurate estimation of how many rows will be returned by the query, and the query optimizer generates a more optimal plan.

SELECT Model, Purchase_Price  
    FROM dbo.Hardware  
    WHERE  
        Model  = 'Xbox'  AND  
        ModelVariant = 'One';  

예제 C. CE에서 더 이상 서로 다른 테이블의 필터링된 예측이 상호 연결되어 있다고 가정하지 않습니다.Example C. CE no longer assumes any correlation between filtered predicates from different tablescc

최신 작업 및 실제 비즈니스 데이터에 대한 새로운 연구 결과 서로 다른 테이블의 예측 필터는 보통 서로 상호 연결되지 않습니다.With extense new research on modern workloads and actual business data reveal that predicate filters from different tables usually do not correlate with each other. 다음 쿼리에서는 CE가 s.type 및 r.date가 서로 연결되지 않은 것으로 간주합니다.In the following query, the CE assumes there is no correlation between s.type and r.date. 따라서 CE는 반환 행 수를 더 적게 예측합니다.Therefore the CE makes a lower estimate of the number of rows returned.

SELECT s.ticket, s.customer, r.store  
    FROM  
                   dbo.Sales    AS s  
        CROSS JOIN dbo.Returns  AS r  
    WHERE  
        s.ticket = r.ticket  AND  
        s.type   = 'toy'     AND  
        r.date   = '2016-05-11';  

참고 항목See Also

성능 모니터링 및 튜닝Monitor and Tune for Performance
SQL Server 2014 카디널리티 추정기로 쿼리 계획 최적화Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
쿼리 힌트Query Hints
쿼리 저장소를 사용하여 성능 모니터링Monitoring Performance By Using the Query Store
쿼리 처리 아키텍처 가이드Query Processing Architecture Guide