데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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



출처: https://sqlmvp.tistory.com/1257?category=618825 [Database Lab]
No. Subject Author Date Views
2150 SQL Server 2019 에서 업그레이드된sp_estimate_data_compression_savings 프로시저 (컬럼스토어 압축율 예상) jevida(강성욱) 2019.03.26 521
2149 SQL Server 2019 에서 추가된sys.dm_db_page_info, sys.fn_PageResCracker 기능으로 대기 관련 정보 확인 jevida(강성욱) 2019.03.26 752
2148 SQL Server 2019에서 향상된 Rowstore batch mode jevida(강성욱) 2019.03.26 415
2147 SQL Server 2016부터 도입된 USE HINT를 사용한 추적 플래그 활성화 jevida(강성욱) 2019.03.26 433
2146 In-memory optimized table에 사용되는 Hash Index jevida(강성욱) 2019.03.26 387
2145 VM환경에서 AG를 구성하였을때VSS 백업 동작 변경 jevida(강성욱) 2019.03.25 394
2144 SQL Server 2016 향상된 가용성 그룹 – 데이터베이스 수준의 상태 탐지 장애조치 jevida(강성욱) 2019.03.25 417
2143 SQL Server 2016 대용량 데이터 로드시 최소 로깅(minimal logging) 과Batch Size jevida(강성욱) 2019.03.25 307
2142 SQL Server 설치시 발생하는 1638 오류 jevida(강성욱) 2019.03.25 409
2141 SQL Server 666코드의 고유 식별자 오류 jevida(강성욱) 2019.03.25 293
2140 SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅 jevida(강성욱) 2019.03.25 348
2139 SQL Server Scheduling and Yielding 트러블슈팅 jevida(강성욱) 2019.03.25 349
2138 SQL Server 2016 Tempdb 경합(contention) 최적화 jevida(강성욱) 2019.03.25 431
» XEvent를 사용하여 Auto tuning 작업 모니터링 jevida(강성욱) 2019.03.25 442
2136 SQL Server In-Memory OLTP에 ASP.NET 세션 상태 저장하기 jevida(강성욱) 2019.03.25 349
2135 SQL Server에서 JSON 데이터 저장하기 jevida(강성욱) 2019.03.25 414
2134 Azure SQL에서 네트워크를 구성하는 방법 jevida(강성욱) 2019.03.25 231
2133 SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링 jevida(강성욱) 2019.03.25 435
2132 SQL Server 2017 소규모 시스템에서 향상된 리소스 사용 jevida(강성욱) 2019.03.25 256
2131 클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭 jevida(강성욱) 2019.03.25 393





XE Login