소개
사담으로 시작합니다. 제가 개발한 저희 회사 (주)씨퀄로의 SQL Server 전문 모니터링 솔루션인 SQLBigEyes Professional로 기업 고객사에서 데모나 교육을 진행할 때 자주 나오는 질문이 한 가지 있습니다 바로 “특정 저장 프로시저의 실행 계획 확인 시, 현재 매개변수 값을 알 수 있나요?” 라는 것입니다.
[그림-쿼리 실행 계획에서 “매개변수” 목록 확인, “컴파일 값”만 출력된다]
결론은 “바로 알 수는 없습니다, 대신에 쿼리 추적용 도구 예를 들어 프로파일러나 확장이벤트 등을 사용해야 합니다.” 라고 답변을 하곤 했었죠(SQLBigEyes에는 좀 더 쉽고 편리하게 기능이 구현되어 있습니다). 저 역시도 고객들의 마음처럼 SQL Server에서 저장 프로시저 혹은 매개변수 쿼리의 실제 호출 시 사용된 매개변수 값을 실행 계획에서 제공해주면 좋겠다는 바램을 가지고 있었습니다.
FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION
앗! 드디어? 라는 생각이 들었습니다. 이름 그대로 RUNTIME PARAMETER를 수집하도록 설정합니다.
1. DB 구성 옵션이므로 적용할 DB별로 아래와 같은 명령으로 동작을 설정하거나 해제합니다.
참고. 위 옵션의 기본적인 설명 및 참고는 MS 문서 “ALTER DATABASE SCOPED CONFIGURATION“를 보세요.
ALTER DATABASE SCOPED CONFIGURATION
SET FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION = ON
2. 저장 프로시저나 매개변수 쿼리가 호출 중인 상태에서 아래와 같은 방식으로 실행계획을 확인합니다.
SELECT
qs.query_plan,
rq.*
FROM sys.dm_exec_requests AS rq
INNER JOIN sys.dm_exec_sessions AS se ON se.session_id = rq.session_id
CROSS APPLY sys.dm_exec_query_statistics_xml(se.session_id) AS qs
WHERE
se.is_user_process = 1
AND rq.session_id <> @@spid;
[그림-“런타임 값”도 출력된다]
주의 사항
MS 문서에 위와 같은 일종의 주의 사항이 언급되어 있습니다. 쿼리의 런타임 매개변수 값을 실행 계획에 포함시키는 것은 CPU나 메모리 등의 부하가 상당이 많이 발생할 수 있다는 것입니다. 그 부하가 얼마나될지는 아직 알 수 없구요.
[그림-중요 사항으로 표시된 내용]
따라서 위 기능의 적용은 개발, 테스트, 문제 해결을 위해 필요한 경우 혹은 평소 DB 부하가 워낙 적어서(혹은 놀고 있어서^^) 부하가 추가되더라도 문제될게 없는 시스템이라면 충분히 가능할 것입니다. 저라면 위의 경우 기본적으로 적용해 보고 나서 부하량의 차이를 본 뒤에 결정하겠습니다.
SQL Server 2017(CU31+), 2019(CU19+)를 위한 추적 플래그 2446
Trace Flag 2446을 시작옵션 또는 전역 옵션으로 설정하면 SQL Server 2017, 2019에서도(위 CU 버전 만족 시) 위와 같은 런타임 매개변수 값 확인이 가능하다고 소개합니다. 2019에서 개인적으로 테스트해 본 결과 정상적으로 동작했습니다.
참고. TF2446에 대한 MS 문서 “sys.dm_exec_query_statistics_xml” 그리고 “DBCC TRACEON“를 참조하세요.
더불어 동일한 주의 사항이 문서에 언급되어 있으므로 꼭 확인하시기 바랍니다.
[그림-“경고” 사항으로 언급된 내용]
정리
사용자들이 그렇게 필요로 하던 기능이 드디어 추가되었습니다. 주의 사항을 고려해서 사용하고 실무에 도움이 되기를 희망합니다.
모두 건강하세요.
김정선 드림
