SQL 자동튜닝 설정 코드

 

-- 10초에 한번씩 사용자가 던지는 쿼리문들을 자동으로 튜닝을 할지 말지 확인을 한다.

 

 

지금 자동튜닝되는 쿼리가 있는지 확인하는거

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

 

 

 

 

 

자동튜닝 설정하는 코드

CREATE EVENT SESSION 
	[Not Plan] 
ON SERVER

ADD EVENT
	QDS.AUTOMATIC_TUNING_PLAN_REGRESSION_DETECTION_CHECK_COMPLETED	(--해당되는 이름
		WHERE(
				-- SQL 서버가 계획 변경을 감지했는지 확인
				([is_regression_detected] = (1))	
			AND
				-- 계획이 수정되었는지 확인(1으로 할 경우엔 새로운 계획을사용, 0은 옛날것으로 돌아가라)
				([is_regression_corrected] = (0))
			AND
				-- 0은 기본값~
				([option_id] = (0))
		)
	)
ADD TARGET	package0.event_file(SET FILENAME='Not Plan')
WITH(STARTUP_STATE=ON)
GO