SQL Server/SQL Server Tip

XEvent를 사용하여 Auto tuning 작업 모니터링

SungWookKang 2019. 3. 25. 05:28
반응형

XEvent 사용하여 Auto tuning 작업 모니터링

 

·         Version : SQL Server 2017

 

SQL Server 2017 부터 Auto tuning 기능이 도입되어 사용자 쿼리에 대해 SQL plan change regression방식으로 쿼리의 플랜이 변경되었을때 변경 사항을 감지하고 나은 플랜을 사용하도록 자동 조정한다. SQL Server 쿼리에 대해 마지막으로 성공한 플랜을 추적하고 기존의 플랜과 비교하여 마지막 실행 플랜이 좋다고 판단되는 경우 강제로 마지막 플랜으로  변경 한다. 이번 포스트에서는 XEvent  활용하여 Auto tuning 적용되어 강제로 플랜이 변경 되었을때 추적하는 방법에 대해서 알아본다.  

 

Automatic tuning process 변경된 플랜을 감지하지만 마지막으로 실행된 플랜이 기존의 플랜과 비교하여 성능 차이가 충분히 높지 않으면 마지막으로 사용된 플랜을 강제로 적용하지 않는다. Auto tuning 프로스세가 현재의 플랜과 마지막에 실행된 플랜과 비교할때XEvent qds.automatic_tuning_plan_regression_detection_check_completed 이벤트가 실행되어 추적할 있다.

CREATE EVENT SESSION [APC - plans that are not corrected] ON SERVER

 

ADD EVENT qds.automatic_tuning_plan_regression_detection_check_completed(

WHERE ((([is_regression_detected]=(1))

  AND ([is_regression_corrected]=(0)))

  AND ([option_id]=(0))))

ADD TARGET package0.event_file(SET filename=N'plans_that_are_not_corrected')

WITH (STARTUP_STATE=ON);

GO

 

ALTER EVENT SESSION [APC - plans that are not corrected] ON SERVER STATE = start;

GO

 

·         is_regression_detected : SQL Server 플랜 변경을 감지 했음을 의미

·         is_regression_corrected : 플랜이 수정 되었음을 의미

·         option_id : 항상 0 (FORCE_LAST_GOOD_PLAN SQL Server2017 첫번째 자동 튜닝 옵션임)

 

 XEvent 다른 중요한 필드는 cpu_gain으로 권장되는 플랜이 기존의 플랜을 대신하게 경우 향상되는 CPU 예상비용이10.000.000 마이크로 세컨드( 10 CPU second)보다 작으면 새로운 플랜이 강제로 적용되지 않을 있다.

 

새로운 플랜이 강제로 적용되면 Auto tuning 지속적으로 강제 적용된 플랜을 모니터링하고 성능이 저하되지 않는지 확인한다. 성능 저하가 발생하면 Query Optimized 새로운 계획을 생성할지(recompile) 기존 계획을 유지할지 결정한다. SQL Server 플랜 검증을 완료할 때마다 XEvent  qds.automatic_tuning_plan_regression_verification_check_completed  이벤트가 실행된다.

  

CREATE EVENT SESSION [APC - Reverted plan corrections] ON SERVER

 

ADD EVENT qds.automatic_tuning_plan_regression_verification_check_completed(

    WHERE ((([is_regression_detected]=(1))

      AND ([is_regression_corrected]=(1)))

      AND ([option_id]=(0))))

ADD TARGET package0.event_file(SET filename=N'reverted_plan_corrections')

WITH (STARTUP_STATE=ON);

GO

 

ALTER EVENT SESSION [APC - Reverted plan corrections] ON SERVER STATE = start;

GO

 

·         is_regression_detected : SQL Server 강제 플랜을 감지하면 1 설정

·         is_regression_corrected : SQL Server Query Optimized 새로운 계획을 만들면 1 설정됨

 

SQL Server 강제로 새로 적용된 플랜이 기존 플랜보다 좋지 않은 경우 새로운 플랜은 적용되지 않으며 데이터베이스 엔진은 컴파일과정을 통해 새로운 플랜을 생성한다.  SQL 엔진에서 강제된 플랜이 기존의 플랜보다 우수하다고 확인되면 다시 컴파일 되기 전까지 강제된 플랜이 유지 된다.

 

 

[참고자료]

·         https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-2017

·         https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/07/18/monitoring-automatic-tuning-actions-using-xevents/

 

 

 

2018-06-19 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MS SQL, SQL 2017, SQL Auto Tuning, Query Optimized, XEvent, Monitoring automatic tuning actions using XEvents


반응형