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

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