<div id="readability-page-1" class="page"><div><h2>소개</h2>
<p>사담으로 시작합니다. 제가 개발한 저희 회사 (주)씨퀄로의 SQL Server 전문 모니터링 솔루션인 <a href="http://www.sqlbigeyes.com/" target="_blank" rel="noopener">SQLBigEyes Professional</a>로 기업 고객사에서 데모나 교육을 진행할 때 자주 나오는 질문이 한 가지 있습니다 바로 “특정 저장 프로시저의 실행 계획 확인 시, 매개변수 값을 알 수 있나요?” 라는 것입니다.</p>
<p>[<strong>그림-쿼리 실행 계획에서 “매개변수” 목록 확인, “컴파일 값”만 출력된다</strong>]</p>
<p><img src="https://visualdb.net/wp-content/uploads/2023/10/SQLServer2022-10.png" alt="" width="1564" height="550" sizes="(max-width: 1564px) 100vw, 1564px">결론은 “바로 알 수는 없는니다, 대신에 쿼리 추적용 도구 예를 들어 프로파일러나 확장이벤트 등을 사용해야 합니다.” 라고 답변을 하곤 했었죠(SQLBigEyes에는 좀 더 쉽고 편리하게 기능이 구현되어 있습니다). 저 역시도 고객들의 마음처럼 SQL Server에서 저장 프로시저 혹은 매개변수 쿼리의 실제 호출 시 사용한 매개변수 값을 실행 계획에서 제공해주면 좋겠다는 바램을 가지고 있었습니다.</p>

<p>앗! 드디어? 라는 생각이 들었습니다. 이름 그대로 RUNTIME PARAMETER를 수집하도록 설정합니다.</p>
<p>1. <strong>DB 구성 옵션</strong>이므로 적용할 DB별로 아래와 같은 명령으로 동작을 설정하거나 해제합니다.</p>
<p>참고. 위 옵션의 기본적인 설명 및 참고는 MS 문서 “<a href="https://learn.microsoft.com/ko-kr/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver16?wt.mc_id=DP-MVP-5685" target="_blank" rel="noopener">ALTER DATABASE SCOPED CONFIGURATION</a>“를 보세요.</p>
<div>
<p>ALTER DATABASE SCOPED CONFIGURATION<br>
SET FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION = ON</p>
</div>
<p>2. 저장 프로시저나 매개변수 쿼리가 호출 중인 상태에서 아래와 같은 방식으로 <strong>실행계획을 확인</strong>합니다.</p>
<div>
<p>SELECT<br>
qs.query_plan,<br>
rq.*<br>
FROM sys.dm_exec_requests AS rq<br>
INNER JOIN sys.dm_exec_sessions AS se ON se.session_id = rq.session_id<br>
CROSS APPLY sys.dm_exec_query_statistics_xml(se.session_id) AS qs<br>
WHERE<br>
se.is_user_process = 1<br>
AND rq.session_id &lt;&gt; @@spid;</p>
</div>
<p>[<strong>그림-“런타임 값”도 출력된다</strong>]</p>
<h4><img src="https://visualdb.net/wp-content/uploads/2023/10/SQLServer2022-10-4.png" alt="" width="1293" height="549" sizes="(max-width: 1293px) 100vw, 1293px">주의 사항</h4>
<p>MS 문서에 위와 같은 일종의 주의 사항이 언급되어 있습니다. 쿼리의 런타임 매개변수 값을 실행 계획에 포함시키는 것은 CPU나 메모리 등의 부하가 상당이 많이 발생할 수 있다는 것입니다. 그 부하가 얼마나될지는 아직 알 수 없구요.</p>
<p>[<strong>그림-중요 사항으로 표시된 내용</strong>]</p>
<p><img src="https://visualdb.net/wp-content/uploads/2023/10/SQLServer2022-10-2.png" alt="" width="1219" height="293" sizes="(max-width: 1219px) 100vw, 1219px">따라서 위 기능의 적용은 개발, 테스트, 문제 해결을 위해 필요한 경우 혹은 평소 DB 부하가 워낙 적어서(혹은 놀고 있어서^^) 부하가 추가되더라도 문제될게 없는 시스템이라면 충분히 가능할 것입니다. 저라면 위의 경우 기본적으로 적용해 보고 나서 부하량의 차이를 본 뒤에 결정하겠습니다.</p>
<h2>SQL Server 2017(CU31+), 2019(CU19+)를 위한 추적 플래그 2446</h2>
<p>Trace Flag 2446을 시작옵션 또는 전역 옵션으로 설정하면 SQL Server 2017, 2019에서도(위 CU 버전 만족 시) 위와 같은 런타임 매개변수 값 확인이 가능하다고 소개합니다. 2019에서 개인적으로 테스트해 본 결과 정상적으로 동작했습니다.</p>
<p><strong>참고</strong>. TF2446에 대한 MS 문서 “<a href="https://learn.microsoft.com/ko-kr/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-statistics-xml-transact-sql?view=azuresqldb-current?wt.mc_id=DP-MVP-5685" target="_blank" rel="noopener">sys.dm_exec_query_statistics_xml</a>” 그리고 “<a href="https://learn.microsoft.com/ko-kr/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver16https://learn.microsoft.com/ko-kr/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-statistics-xml-transact-sql?view=azuresqldb-current?wt.mc_id=DP-MVP-5685" target="_blank" rel="noopener">DBCC TRACEON</a>“를 참조하세요.</p>
<p>더불어 동일한 주의 사항이 문서에 언급되어 있으므로 꼭 확인하시기 바랍니다.</p>
<p>[<strong>그림-“경고” 사항으로 언급된 내용</strong>]</p>
<h2><img src="https://visualdb.net/wp-content/uploads/2023/10/SQLServer2022-10-3.png" alt="" width="1219" height="156" sizes="(max-width: 1219px) 100vw, 1219px">정리</h2>
<p>사용자들이 그렇게 필요로 하던 기능이 드디어 추가되었습니다. 주의 사항을 고려해서 사용하고 실무에 도움이 되기를 희망합니다.</p>

<p>모두 건강하세요.<br>
김정선 드림</p>
</div><div>
			<p><img alt="" src="https://secure.gravatar.com/avatar/9e858557f785e172ec712c13488407cf?s=80&amp;d=mm&amp;r=g" height="80" width="80"></p>			<div>
				<h4><span>Author:</span>&nbsp;Jungsun Kim</h4>
				<p><a href="https://visualdb.net/" rel="author">https://visualdb.net</a></p><p>(주)씨퀄로 대표컨설턴트/이사, 
인프런 지식공유자, 
탈잉 튜터, 
스킬서포트 교육센터 강사, 
Microsoft Data Platform MVP</p>
			</div>
		</div></div>

김정선의 Data 이야기

소개
사담으로 시작합니다. 제가 개발한 저희 회사 (주)씨퀄로의 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
2. 저장 프로시저나 매개변수 쿼리가 호출 중인 상태에서 아래와 같은 방식으로 실행계획을 확인합니다.
SELECT
[그림-“런타임 값”도 출력된다]
주의 사항
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“를 참조하세요.
더불어 동일한 주의 사항이 문서에 언급되어 있으므로 꼭 확인하시기 바랍니다.
[그림-“경고” 사항으로 언급된 내용]
정리
사용자들이 그렇게 필요로 하던 기능이 드디어 추가되었습니다. 주의 사항을 고려해서 사용하고 실무에 도움이 되기를 희망합니다.
 
모두 건강하세요.
김정선 드림

SQL Server 2022-10-FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION

댓글